Advance Pandas

Python tutorial · PySpark.in

MultiIndex (Hierarchical Indexing)

MultiIndex allows multiple index levels in rows or columns. It is used when data has more than one dimension.

Example

```

import pandas as pd

arrays = [

['India', 'India', 'USA', 'USA'],

['Delhi', 'Mumbai', 'NY', 'LA']

]

index = pd.MultiIndex.from_arrays(arrays, names=('Country', 'City'))

df = pd.DataFrame({'Sales': [100, 200, 150, 300]}, index=index)

print(df)

```

Access Data

```

df.loc['India']

df.loc[('USA', 'LA')]

print(df)

```

Pivot Tables

A pivot table summarizes data using aggregation. Pivot tables are used for summary reports & dashboards.

Example

```

data = {

'City': ['Delhi', 'Delhi', 'Mumbai', 'Mumbai'],

'Year': [2023, 2024, 2023, 2024],

'Sales': [100, 150, 200, 250]

}

df = pd.DataFrame(data)

pd.pivot_table(df, values='Sales', index='City', columns='Year', aggfunc='sum'

```

Example

```

df.pivot(index='City', columns='Year', values='Sales')

print(df)

```

It fails if there is duplicates data exist.

Crosstab

crosstab() computes frequency tables. Crosstab is used in EDA for categorical analysis.

Step 1: Create a DataFrame

```

import pandas as pd

df = pd.DataFrame({

'City': ['Delhi', 'Delhi', 'Mumbai', 'Mumbai', 'Chennai'],

'Year': [2022, 2023, 2022, 2023, 2022]

})

print(df)

```

Step 2: Apply crosstab()

```

ct = pd.crosstab(df['City'], df['Year'])

print(ct)

```

Explanation

What crosstab() does:

• Counts frequency
• Creates a table of categorical data
• Used in EDA (Exploratory Data Analysis)

Here it means:

• How many times each City appears in each Year

Reshaping Data

Sample DataFrame

```

import pandas as pd

df = pd.DataFrame({

'City': ['Delhi', 'Mumbai', 'Chennai'],

'Year': [2022, 2022, 2022],

'Sales': [100, 150, 120]

})

print(df)

```

melt() → Wide to Long

• Used before ML models
• Converts columns into rows
  

```

df_melt = pd.melt(df, id_vars='City', value_vars=['Sales'])

print(df_melt)

```

Explanation (Easy)

• City stays fixed
• Sales column becomes:
  • variable → column name
  • value → actual data

ML models prefer LONG format, so melt() is very important.

stack() → Columns to Rows

First, create a pivot table

```

pivot = pd.pivot_table(

df,

values='Sales',

index='City',

columns='Year'

)

print(pivot)

```

Apply stack()

```

pivot_stack = pivot.stack()

print(pivot_stack)

```

Explanation

• Column (Year) moves down into rows
• Data becomes vertical

stack() = Columns ➝ Rows

unstack() → Rows to Columns

```

x = pivot.stack().unstack()

print(x)

```

Explanation

• unstack() reverses stack()
• Rows move back into columns

unstack() = Rows ➝ Columns

Vectorization

Performing operations on entire arrays instead of loops. Vectorization improves speed & performance.

Bad Way

```

for i in df['Sales']:

print(i * 2)

```

Good Way

```

df['Sales'] = df['Sales'] * 2

print(df)

```

Performance Optimization

Techniques

✔ Vectorization
✔ Use categorical dtype
✔ Avoid loops
✔ Use .loc[]

Example

```

df['City'] = df['City'].astype('category')

print(df)

```

Memory Management

Optimizing memory usage for large datasets. Memory optimization is critical for big data processing.

Example

```

x=df.info(memory_usage='deep')

print(x)

```

Reduce Memory

```

df['Sales'] = df['Sales'].astype('int32')

print(df)

```

Window Functions

Window functions perform calculations over a sliding window of rows instead of the entire dataset. Unlike groupby, they do not reduce rows they return the same number of rows with new calculated values.

Why We Need Window Functions?

✔ Trend analysis
✔ Moving averages
✔ Cumulative totals
✔ Time-series analysis
✔ Business analytics (sales growth, rolling stats)

Sample DataFrame

```

import pandas as pd

df = pd.DataFrame({

'Day': [1, 2, 3, 4, 5],

'Sales': [100, 200, 300, 400, 500]

})

print(df)

```

Rolling Window Function

rolling(window_size) applies a function on fixed-size moving window.

Practical: Rolling Mean (window = 2)

```

df['Rolling_Mean'] = df['Sales'].rolling(2).mean()

print(df)

```

Explanation

Expanding Window Function

expanding() applies a function from start → current row.

Practical: Cumulative Sum

```

df['Cumulative_Sum'] = df['Sales'].expanding().sum()

print(df)

```

Explanation

Common Window Functions

Example: Rolling Sum

```

df['Rolling_Sum'] = df['Sales'].rolling(3).sum()

print(df)

```

Difference: GroupBy vs Window Functions

Types

Chained Indexing Issues

Chained indexing happens when we access data using multiple indexing steps, which may create a copy instead of a view. This causes SettingWithCopyWarning and unreliable updates. Using .loc[] avoids this issue.

Sample DataFrame

```

import pandas as pd

df = pd.DataFrame({

'Name': ['A', 'B', 'C', 'D'],

'Sales': [80, 120, 90, 150]

})

print(df)

```

PROBLEM: Chained Indexing(wrong way)

```

df[df['Sales'] > 100]['Sales'] = 999

print(df)

```

Output (NO CHANGE): Sales values are NOT updated

Why This Happens?

```

Hide

df[df['Sales'] > 100] # creates a TEMPORARY COPY

['Sales'] # modifies the COPY, not original df

```

Pandas does not know whether you want to modify the original DataFrame or the copy.

CORRECT WAY: Use .loc[]

```

df.loc[df['Sales'] > 100, 'Sales'] = 999

print(df)

```

✔ Changes applied correctly
✔ No warning
✔ Safe & recommended

Therefore:

• Never assign values using chained indexing
  
• Always use .loc[row_condition, column]

More Python tutorials

• What is Python and Why is it used for Data Science and Data Engineering?
• How Does Python Work in the Backend? Internal Working of Python
• Top 30 Python Interview Questions for Data Science
• 3-Month Python Roadmap to Excel in Data Science and Machine Learning
• Python Data Types Explained – A Beginner’s Guide
• test

All tutorials · Try the free PySpark compiler · Practice challenges