Advance Pandas
Python tutorial · PySpark.in
Advanced Pandas Concepts
Window Functions
Window functions perform calculations over a fixed-size sliding window of rows.
Example: rolling(3).mean() calculates the mean of the current row and previous 2 rows.
Useful for trend analysis, moving averages, stock prices, sales forecasting.
```
import pandas as pd
df = pd.DataFrame({
"ID": [101, 102, 103, 104, 105],
"Name": ["Aman", "Rekha", "Megha", "Rohan", "Sima"],
"Age": [20, 22, 21, 23, 22],
"Marks": [85, 90, 78, 88, 92],
"Gender": ["Male", "Female", "Female", "Male", "Female"],
"City": ["Delhi", "Mumbai", "Delhi", "Pune", "Mumbai"]
})
print(df)
df["Sales"] = [120, 150, 130, 160, 170]# To demonstrate rolling mean, let’s add a "Sales" column first
# Now apply rolling mean with window size 3
df["rolling_mean"] = df["Sales"].rolling(3).mean()
print(df)
```
Expanding Window: Expanding means cumulative calculation from the first row till the current row.
Example: expanding().mean() gives a cumulative average.
Useful when data grows over time and you want a running performance indicator (e.g., sales progress).
```
import pandas as pd
df = pd.DataFrame({
"ID": [101, 102, 103, 104, 105],
"Name": ["Aman", "Rekha", "Megha", "Rohan", "Sima"],
"Age": [20, 22, 21, 23, 22],
"Marks": [85, 90, 78, 88, 92],
"Gender": ["Male", "Female", "Female", "Male", "Female"],
"City": ["Delhi", "Mumbai", "Delhi", "Pune", "Mumbai"],
"Sales": [120, 150, 130, 160, 170]
})
print(df)
df["cum_avg"] = df["Sales"].expanding().mean()
print(df)
```
Cumulative Operations
Cumulative functions apply calculations continuously across rows:
Function | Meaning |
|---|---|
cumsum() | Running total |
cummax() | Highest value observed so far |
cummin() | Lowest value observed so far |
cumprod() | Multiply all values progressively |
```
import pandas as pd
df = pd.DataFrame({
"ID": [101, 102, 103, 104, 105],
"Name": ["Aman", "Rekha", "Megha", "Rohan", "Sima"],
"Age": [20, 22, 21, 23, 22],
"Marks": [85, 90, 78, 88, 92],
"Gender": ["Male", "Female", "Female", "Male", "Female"],
"City": ["Delhi", "Mumbai", "Delhi", "Pune", "Mumbai"],
"Sales": [120, 150, 130, 160, 170]
})
print(df)
df["cum_sum"] = df["Sales"].cumsum()
print(df)
df["cum_max"] = df["Sales"].cummax()
df["cum_min"] = df["Sales"].cummin()
df["cum_prod"] = df["Sales"].cumprod()
```
MultiIndex (Hierarchical Indexing):
MultiIndex lets you use multiple columns as index levels, e.g., City + Gender. It helps in group-based slicing, better data organization, and fast subsetting.
Example: df.loc[("Delhi", "Male")] selects all records for Male customers from Delhi.
```
import pandas as pd
df = pd.DataFrame({
"ID": [101, 102, 103, 104, 105],
"Name": ["Aman", "Rekha", "Megha", "Rohan", "Sima"],
"Age": [20, 22, 21, 23, 22],
"Marks": [85, 90, 78, 88, 92],
"Gender": ["Male", "Female", "Female", "Male", "Female"],
"City": ["Delhi", "Mumbai", "Delhi", "Pune", "Mumbai"],
"Sales": [120, 150, 130, 160, 170]
})
print(df)
df_multi = df.set_index(["City", "Gender"])
print(df_multi)# Setting two columns as index
print(df_multi.loc[("Delhi", "Male")])# Selecting data from MultiIndex, returns rows matching City=Delhi & Gender=Male
print(df_multi.loc[[("Delhi", "Male"), ("Mumbai", "Female")]])# For multiple selections
df_multi = df_multi.reset_index() # Reset MultiIndex back to normal
print(df_multi)
```
Categorical Data
Used to convert string columns into category type to save memory & improve performance. Categorical type converts string columns into fixed category codes. It reduces memory usage and improves performance for repeated values like City, Gender, Category.
Useful when working with large datasets.
```
import pandas as pd
df = pd.DataFrame({
"Name": ["Aman", "Rekha", "Megha", "Rohan", "Sima"],
"Age": [20, 22, 21, 23, 22],
"Marks": [85, 90, 78, 88, 92],
"Gender": ["M", "F", "F", "M", "F"],
"City": ["Delhi", "Mumbai", "Delhi", "Pune", "Mumbai"]
})
df["Gender"] = df["Gender"].astype("category")
df["City"] = df["City"].astype("category")
print(df.dtypes)
```
String Operations
Pandas supports vectorized string processing using .str.
Examples:
- .str.upper() → convert to uppercase
- .str[:2] → extract substring
- .str.startswith("A") → check prefix
Fast alternative to Python string loops.
```
import pandas as pd
df = pd.DataFrame({
"Name": ["Aman", "Rekha", "Megha", "Rohan", "Sima"],
"Age": [20, 22, 21, 23, 22],
"Marks": [85, 90, 78, 88, 92],
"Gender": ["M", "F", "F", "M", "F"],
"City": ["Delhi", "Mumbai", "Delhi", "Pune", "Mumbai"]
})
# 1. Uppercase the names
df["Name"] = df["Name"].str.upper()
# 2. Extract the first 2 letters of city
df["City_Code"] = df["City"].str[:2]
# 3. Check if Name starts with the letter 'A'
df["StartsWithA"] = df["Name"].str.startswith("A")
print(df)
```
Exporting Cleaned Data
After performing cleaning and transformations, we can export the dataframe.
```
hide
import pandas as pd
df = pd.DataFrame({
"Name": ["Aman", "Rekha", "Megha", "Rohan", "Sima"],
"Age": [20, 22, 21, 23, 22],
"Marks": [85, 90, 78, 88, 92],
"Gender": ["M", "F", "F", "M", "F"],
"City": ["Delhi", "Mumbai", "Delhi", "Pune", "Mumbai"]
})
df.to_csv("cleaned_data.csv", index=False)
df.to_excel("cleaned_data.xlsx", index=False)
```
Note:
index=False avoids adding extra table index column in the saved file. Used to save and share processed datasets.
Important Shortcuts & Tricks
Show all rows/columns
Default Pandas hides long tables or output by default.. To display everything:
```
import pandas as pd
df = pd.DataFrame({
"Name": ["Aman", "Rekha", "Megha", "Rohan", "Sima"],
"Age": [20, 22, 21, 23, 22],
"Marks": [85, 90, 78, 88, 92],
"Gender": ["M", "F", "F", "M", "F"],
"City": ["Delhi", "Mumbai", "Delhi", "Pune", "Mumbai"]
})
pd.set_option("display.max_rows", None)
pd.set_option("display.max_columns", None)
print(df)
```
Now all rows and all columns will always be visible during printing.
Copy DataFrame
- Creates a deep copy (changes in df2 will not affect df and vice-versa).
- Changes in the new DataFrame do not affect the original.
```
import pandas as pd
df = pd.DataFrame({
"Name": ["Aman", "Rekha", "Megha", "Rohan", "Sima"],
"Age": [20, 22, 21, 23, 22],
"Marks": [85, 90, 78, 88, 92],
"Gender": ["M", "F", "F", "M", "F"],
"City": ["Delhi", "Mumbai", "Delhi", "Pune", "Mumbai"]
})
df2 = df.copy()
df2["Age"][0] = 100
print(df)
print(df2)
```
Note:
Only df2 changes. df remains the same.
Check duplicates
To see how many rows are duplicated:
```
import pandas as pd
df = pd.DataFrame({
"Name": ["Aman", "Rekha", "Megha", "Rohan", "Sima", "Aman"],
"Age": [20, 22, 21, 23, 22, 20],
"Marks": [85, 90, 78, 88, 92, 85],
"Gender": ["M", "F", "F", "M", "F", "M"],
"City": ["Delhi", "Mumbai", "Delhi", "Pune", "Mumbai", "Delhi"]
})
print("🔹 Original DataFrame:")
print(df)
print("\nNumber of duplicate rows:", df.duplicated().sum())
```
Remove duplicates
```
import pandas as pd
df = pd.DataFrame({
"Name": ["Aman", "Rekha", "Megha", "Rohan", "Sima", "Aman"],
"Age": [20, 22, 21, 23, 22, 20],
"Marks": [85, 90, 78, 88, 92, 85],
"Gender": ["M", "F", "F", "M", "F", "M"],
"City": ["Delhi", "Mumbai", "Delhi", "Pune", "Mumbai", "Delhi"]
})
print("🔹 Original DataFrame:")
print(df)
print("\nNumber of duplicate rows:", df.duplicated().sum())
# Remove duplicates
df_cleaned = df.drop_duplicates()
print("\n🔹 DataFrame after removing duplicates:")
print(df_cleaned)```
Duplicate Handling
- df.duplicated().sum() → count duplicates
- df.drop_duplicates() → remove duplicates
Very useful for data cleaning, surveys, logs, and merged datasets.
Example With Artificial Duplicate (for understanding)
```
import pandas as pd
df = pd.DataFrame({
"Name": ["Aman", "Rekha", "Megha", "Rohan", "Sima"],
"Age": [20, 22, 21, 23, 22],
"Marks": [85, 90, 78, 88, 92],
"Gender": ["M", "F", "F", "M", "F"],
"City": ["Delhi", "Mumbai", "Delhi", "Pune", "Mumbai"]
})
df.loc[5] = ["Aman", 20, 85, "M", "Delhi"] # Add duplicate row
print(df.duplicated().sum()) # Now -> 1 duplicate
df = df.drop_duplicates()
```
Rename columns
To rename one or more columns: Helpful for making column names readable, standardized, and consistent.
```
import pandas as pd
df = pd.DataFrame({
"Name": ["Aman", "Rekha", "Megha", "Rohan", "Sima"],
"Age": [20, 22, 21, 23, 22],
"Marks": [85, 90, 78, 88, 92],
"Gender": ["M", "F", "F", "M", "F"],
"City": ["Delhi", "Mumbai", "Delhi", "Pune", "Mumbai"]
})
df.rename(columns={"Marks": "Score", "City": "Location"}, inplace=True)
print(df)
```
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