Data Wrangling with Pandas: Quick Reference Guide

Data Wrangling with pandas Cheat Sheet

Creating DataFrames

Creating DataFrames by specifying values for each column:
```
df = pd.DataFrame(
    {
        "a": [4, 5, 6],
        "b": [7, 8, 9],
        "c": [10, 11, 12]
    },
    index=[1, 2, 3]
)
```
This DataFrame has indexed rows and specified values for columns a, b, and c.
Creating DataFrames by specifying values for each row:
```
df = pd.DataFrame(
    [
        [4, 7, 10],
        [5, 8, 11],
        [6, 9, 12]
    ],
    index=[1, 2, 3],
    columns=['a', 'b', 'c']
)
```
This DataFrame uses a list of lists to define each row, indexing the rows and specifying columns.

Creating DataFrame with MultiIndex:

df = pd.DataFrame(
    {
        "a": [4, 5, 6],
        "b": [7, 8, 9],
        "c": [10, 11, 12]
    },
    index=pd.MultiIndex.from_tuples(
        [
            ('d', 1),
            ('d', 2),
            ('e', 2)
        ],
        names=['n', 'v']
    )
)

MultiIndex allows hierarchical indexing, providing multiple levels of indexing.

Method Chaining

Chaining methods for cleaner code:

df = (pd.melt(df)
      .rename(columns={
          'variable': 'var',
          'value': 'val'
      })
      .query('val >= 200'))

This snippet demonstrates the chaining of data manipulation methods for improved readability.

Tidy Data

Foundation for wrangling in pandas:
- Each variable is saved in its own column.
- Each observation is saved in its own row.

Reshaping Data

Change layout, sorting, reindexing, renaming:

Melt to gather columns into rows:
```
pd.melt(df)
```
Pivot to spread rows into columns:
```
df.pivot(columns='var', values='val')
```

Concatenate DataFrames vertically or horizontally:

pd.concat([df1, df2])
pd.concat([df1, df2], axis=1)

Sort values in DataFrame:

df.sort_values('mpg')
df.sort_values('mpg', ascending=False)

Rename columns:
```
df.rename(columns={'y': 'year'})
```
Set or reset index:
```
df.set_index()
df.reset_index()
```
Drop columns:
```
df.drop(columns=['Length', 'Height'])
```

Subset Observations - Rows

Extract rows that meet logical criteria:
```
df[df.Length > 7]
```
Remove duplicate rows:
```
df.drop_duplicates()
```
Random sampling:
```
df.sample(frac=0.5)
df.sample(n=200)
```
ISNull/NotNull:
```
df.isnull().sum()
2
df.dropna()
```

Subset Variables - Columns

Select columns:
```
df[['width', 'length', 'species']]
```
Select columns with specific names:
```
df.width
```
Select columns whose name matches regex:
```
df.filter(regex='regex')
```

Subsets - Rows and Columns

.loc and .iloc for selection:

Use df.loc[] and df.iloc[] to select rows, columns, or both.
Use df.at[] and df.iat[] to access a single value by row and column.

.iloc examples:
```
df.iloc[10:20]
df.iloc[:, 1:2, 5]
```

.loc examples:

df.loc[:, 'x2':'x1']
df.loc[:, 'a':'c']
df.loc[df['a'] > 10]

Access single value using index:
```
df.iat[1, 2]
df.at[4, 'A']
```

Using query

Boolean expressions for filtering:

df.query('Length > 7')
df.query('Length > 7 and Width < 8')
df.query('index.str.startswith("abc")', engine="python")

Logic in Python (and pandas)

Symbols and their meaning:
- Less than: <
- Greater than: >
- Less than or equal to: <=
- Greater than or equal to: >=
- Not equal to: !=
- Equality: ==
- isin(values): df.column.isin(values)
- notin(values): ~df.column.isin(values)
- isnull(obj): pd.isnull(obj)
- notnull(obj): pd.notnull(obj)
- any(): df.any(1)
- all(): df.all(1)

Regex (Regular Expressions) Examples

Matching patterns:
- r'^abc$': Matches strings containing a period .
- r'^[a-zA-Z]+$': Matches strings starting with word
- r'^\d{1,9}s': Matches strings starting with word
- r'\b(Spal)': Matches strings beginning with word
- df.columns.regex.r\1,2,3,4,5$: Matches strings ending with word
- r'\baW\b': Matches Nan.
- `r'.*()': Matches strings with 'Species'
- `\baW\b': Matches strings with specific names.

Note: This summary provides an overview of key functionalities and examples, ensuring pandas users can quickly reference and understand the essential operations for effective data manipulation.

Reference: