Pandas Data Processing
Pandas is the Swiss Army knife of data science — loading, cleaning, transforming, and aggregating, all in one tool.
1. What You'll Learn
- Core DataFrame and Series operations: creating, indexing, selecting, and filtering
- Hands-on data cleaning: handling missing values, removing duplicates, detecting outliers
- Data transformation: apply/map/transform, groupby aggregation, and pivot_table
- Merging multiple data sources: merge/join/concat — combining Alice's US orders with Bob's China orders for analysis
- Time series processing: date parsing, resampling, and rolling windows
2. A Real Data Analyst's Story
(1) The Pain Point: Messy CSV Data, with Cleaning Eating Up 80% of the Time
Bob got his hands on the raw order data from SalesPredict: out of 200 thousand records, 5% of the amounts were null, 3% were duplicate orders, and the user ID formats were inconsistent. Alice's US data used different field names than Bob's, and Charlie's EUR amounts needed currency conversion. Data cleaning had become the biggest time sink in the ML project.
(2) The Pandas Solution
Pandas provides a complete data-cleaning toolchain — missing-value handling, duplicate detection, type conversion, and multi-source merging — turning data cleaning from manual work into reproducible code.
PYTHON
import pandas as pd
# Load and clean in a pipeline
df = pd.read_csv("orders.csv")
df_clean = (df
.drop_duplicates()
.fillna({"amount": df["amount"].median()})
.assign(amount_usd=lambda x: x["amount"] * x["exchange_rate"])
)
print(f"Clean rows: {len(df_clean)}, Original: {len(df)}")
(3) The Payoff: Cleaning Time Cut from 3 Days to 30 Minutes
Once Bob codified the data-cleaning workflow with Pandas, what used to take 3 days of manual cleaning dropped to 30 minutes of automated execution — and the same pipeline could be reused every time new data came in.
3. Core DataFrame and Series Operations
(1) Creating a DataFrame
PYTHON
import pandas as pd
import numpy as np
# From dictionary
sales_data = pd.DataFrame({
"date": pd.date_range("2024-01-01", periods=5, freq="D"),
"category": ["Electronics", "Clothing", "Food", "Books", "Home"],
"revenue_k_usd": [250, 145, 90, 70, 400],
"orders": [1200, 800, 3000, 500, 600],
})
# From NumPy array
arr = np.random.rand(3, 4)
df_from_arr = pd.DataFrame(arr, columns=["A", "B", "C", "D"])
print(sales_data)
print(f"\nShape: {sales_data.shape}")
print(f"Dtypes:\n{sales_data.dtypes}")
▶ Example: Loading Real SalesPredict Data
PYTHON
import pandas as pd
# Load CSV with type hints
df = pd.read_csv("sales_data.csv", parse_dates=["order_date"])
# Quick overview
print(f"Shape: {df.shape}")
print(f"\nFirst 5 rows:\n{df.head()}")
print(f"\nData types:\n{df.dtypes}")
print(f"\nMemory usage:\n{df.memory_usage(deep=True)}")
Output:
TEXT
# Executed successfully
(2) Selecting and Filtering
▶ Example: Multiple Ways to Select Data
PYTHON
import pandas as pd
df = pd.DataFrame({
"product": ["Laptop", "Phone", "Tablet", "Monitor", "Keyboard"],
"category": ["Electronics", "Electronics", "Electronics", "Electronics", "Accessories"],
"price": [999, 699, 399, 299, 49],
"stock": [50, 200, 100, 80, 500],
})
# Column selection
prices = df["price"] # Series
subset = df[["product", "price"]] # DataFrame
# Row selection with loc (label) and iloc (position)
row = df.loc[0] # First row by label
rows = df.iloc[1:3] # Rows 1-2 by position
# Conditional filtering
expensive = df[df["price"] > 400]
elec_cheap = df[(df["category"] == "Electronics") & (df["price"] < 500)]
print(f"Expensive items:\n{expensive}")
Output:
TEXT
# Executed successfully
| Selection Method | Syntax | Use Case |
|---|---|---|
| Column selection | df["col"] / df[["c1","c2"]] |
Grab one or more columns |
| loc | df.loc[row, col] |
Index by label |
| iloc | df.iloc[r, c] |
Index by position |
| Conditional filtering | df[df["col"] > val] |
Filter by condition |
| query | df.query("price > 400") |
SQL-style filtering |
4. Hands-On Data Cleaning
Data cleaning in Pandas is a pipeline process — each step tackles one class of problem, progressively turning dirty data into clean data:
graph LR
RAW[Raw Data<br/>5% Missing, 3% Dupes] --> DEDUP[Deduplicate<br/>drop_duplicates]
DEDUP --> FILL[Fill Missing<br/>fillna median/mode]
FILL --> OUTLIER[Remove Outliers<br/>IQR clipping]
OUTLIER --> CONVERT[Type Convert<br/>astype / to_datetime]
CONVERT --> CLEAN[Clean Data<br/>Ready for ML]
(1) Handling Missing Values
▶ Example: Diagnosing and Handling Missing Values in SalesPredict
PYTHON
import pandas as pd
import numpy as np
# Simulate data with missing values
df = pd.DataFrame({
"order_id": [1001, 1002, 1003, 1004, 1005, 1006],
"amount_usd": [150, np.nan, 280, np.nan, 95, 320],
"category": ["Electronics", "Clothing", np.nan, "Food", "Books", "Electronics"],
"user_rating": [4.5, 3.8, np.nan, 4.2, np.nan, 5.0],
})
# Diagnose missing values
print(f"Missing count:\n{df.isnull().sum()}")
print(f"\nMissing ratio:\n{df.isnull().mean().round(3)}")
# Strategy 1: Drop rows with any missing
df_drop = df.dropna()
# Strategy 2: Fill with median/mode
df_fill = df.fillna({
"amount_usd": df["amount_usd"].median(),
"category": df["category"].mode()[0],
"user_rating": df["user_rating"].mean(),
})
print(f"\nFilled data:\n{df_fill}")
Output:
TEXT
# Executed successfully
| Strategy | Method | Use Case | Risk |
|---|---|---|---|
| Drop | dropna() |
Missing ratio < 5% | Loss of information |
| Mean imputation | fillna(df["col"].mean()) |
Numeric, roughly normal | Reduces variance |
| Median imputation | fillna(df["col"].median()) |
Numeric, with outliers | Conservative estimate |
| Mode imputation | fillna(df["col"].mode()[0]) |
Categorical | May over-represent the majority |
| Forward/backward fill | fillna(method="ffill") |
Time series | Propagates bias |
(2) Duplicates and Outliers
▶ Example: Detecting and Handling Duplicates and Outliers
PYTHON
import pandas as pd
import numpy as np
df = pd.DataFrame({
"order_id": [1001, 1002, 1002, 1003, 1004],
"amount": [150, 280, 280, 95000, 95],
})
# Duplicate detection
dupes = df.duplicated(subset=["order_id", "amount"])
print(f"Duplicated rows:\n{df[dupes]}")
# Remove duplicates
df_clean = df.drop_duplicates(subset=["order_id"], keep="first")
# Outlier detection with IQR method
def detect_outliers_iqr(series, factor=1.5):
q1, q3 = series.quantile([0.25, 0.75])
iqr = q3 - q1
lower, upper = q1 - factor * iqr, q3 + factor * iqr
return (series < lower) | (series > upper)
outlier_mask = detect_outliers_iqr(df_clean["amount"])
print(f"\nOutliers:\n{df_clean[outlier_mask]}")
# Cap outliers at 99th percentile
cap = df_clean["amount"].quantile(0.99)
df_capped = df_clean.assign(amount=df_clean["amount"].clip(upper=cap))
Output:
TEXT
# Functions defined successfully
5. Data Transformation and Aggregation
(1) apply/map/transform
▶ Example: Feature Transformation
PYTHON
import pandas as pd
df = pd.DataFrame({
"product": ["Laptop", "Phone", "Tablet"],
"price_usd": [999, 699, 399],
"cost_usd": [600, 350, 180],
})
# map: element-wise transform on Series
df["price_level"] = df["price_usd"].map(
lambda x: "High" if x > 700 else ("Mid" if x > 400 else "Low")
)
# apply: row/column-wise transform
df["margin_pct"] = df.apply(
lambda row: (row["price_usd"] - row["cost_usd"]) / row["price_usd"] * 100,
axis=1
)
# transform: same-shape output
df["price_zscore"] = df["price_usd"].transform(
lambda x: (x - x.mean()) / x.std()
)
print(df)
Output:
TEXT
# Executed successfully
(2) Group Aggregation with groupby
▶ Example: Aggregating Sales Metrics by Category
PYTHON
import pandas as pd
df = pd.DataFrame({
"category": ["Elec", "Elec", "Cloth", "Cloth", "Food", "Food"],
"month": ["Jan", "Feb", "Jan", "Feb", "Jan", "Feb"],
"revenue_k": [250, 260, 145, 150, 90, 95],
"orders": [1200, 1250, 800, 820, 3000, 3100],
})
# Single aggregation
cat_revenue = df.groupby("category")["revenue_k"].sum()
print(f"Revenue by category:\n{cat_revenue}")
# Multiple aggregations
cat_stats = df.groupby("category").agg({
"revenue_k": ["sum", "mean", "std"],
"orders": ["sum", "mean"],
})
print(f"\nCategory stats:\n{cat_stats}")
# Named aggregations
cat_named = df.groupby("category").agg(
total_revenue=("revenue_k", "sum"),
avg_revenue=("revenue_k", "mean"),
total_orders=("orders", "sum"),
avg_order_value=("revenue_k", lambda x: x.sum() / df.loc[x.index, "orders"].sum() * 1000),
)
Output:
TEXT
# Executed successfully
(3) Pivot Tables with pivot_table
▶ Example: Monthly Sales Pivot by Category
PYTHON
import pandas as pd
df = pd.DataFrame({
"category": ["Elec"]*3 + ["Cloth"]*3 + ["Food"]*3,
"month": ["Jan", "Feb", "Mar"]*3,
"revenue_k": [250, 260, 270, 145, 150, 155, 90, 95, 100],
})
# Pivot: categories as rows, months as columns
pivot = df.pivot_table(
values="revenue_k",
index="category",
columns="month",
aggfunc="sum",
margins=True, # Add row/column totals
)
print(pivot)
Output:
TEXT
# Executed successfully
| Dimension | groupby | pivot_table |
|---|---|---|
| Output shape | Long | Wide |
| Multiple metrics | ✅ agg on multiple columns | ✅ values on multiple columns |
| Subtotals | ❌ Requires manual work | ✅ margins=True |
| Flexibility | High (any agg) | Medium (fixed aggfunc) |
6. Merging Multiple Data Sources and Time Series
(1) Merge Operations
▶ Example: Merging Alice's US Data + Bob's China Data
PYTHON
import pandas as pd
# Alice's US orders
us_orders = pd.DataFrame({
"order_id": ["US001", "US002", "US003"],
"amount_usd": [150, 280, 95],
"product": ["Laptop", "Phone", "Book"],
})
# Bob's China orders (need currency conversion)
cn_orders = pd.DataFrame({
"order_id": ["CN001", "CN002"],
"amount_cny": [1200, 3500],
"product": ["Phone", "Laptop"],
})
# Concat vertically (append rows)
cn_orders_usd = cn_orders.assign(
amount_usd=cn_orders["amount_cny"] * 0.14 # CNY to USD
).drop(columns=["amount_cny"])
all_orders = pd.concat([us_orders, cn_orders_usd], ignore_index=True)
print(f"Combined orders:\n{all_orders}")
# Merge with product catalog
catalog = pd.DataFrame({
"product": ["Laptop", "Phone", "Book", "Tablet"],
"category": ["Electronics", "Electronics", "Books", "Electronics"],
"margin_pct": [35, 45, 20, 30],
})
enriched = all_orders.merge(catalog, on="product", how="left")
print(f"\nEnriched orders:\n{enriched}")
Output:
TEXT
# Executed successfully
| Merge Type | Syntax | SQL Equivalent | Description |
|---|---|---|---|
| Inner | merge(how="inner") |
INNER JOIN | Intersection |
| Left | merge(how="left") |
LEFT JOIN | Keep all rows from the left table |
| Outer | merge(how="outer") |
FULL JOIN | Union |
| Concat | concat(axis=0) |
UNION | Stack rows vertically |
| Concat | concat(axis=1) |
— | Stack columns side by side |
(2) Time Series Processing
▶ Example: Resampling and Rolling Statistics on SalesPredict Daily Data
PYTHON
import pandas as pd
import numpy as np
# Daily sales data
rng = pd.date_range("2024-01-01", periods=90, freq="D")
daily = pd.DataFrame({
"date": rng,
"revenue": np.random.normal(5000, 1000, 90).cumsum(),
}, index=rng)
# Resample to monthly
monthly = daily["revenue"].resample("M").agg(["first", "last", "mean", "sum"])
print(f"Monthly stats:\n{monthly}")
# Rolling window: 7-day moving average
daily["ma_7d"] = daily["revenue"].rolling(window=7).mean()
daily["ma_30d"] = daily["revenue"].rolling(window=30).mean()
# Percentage change
daily["pct_change"] = daily["revenue"].pct_change()
print(f"\nLast 5 rows with rolling stats:\n{daily.tail()}")
Output:
TEXT
# Executed successfully
❓ FAQ
Q What's the difference between loc and iloc?
A loc indexes by label and includes the endpoint; iloc indexes by position and excludes the endpoint. For example, df.loc[0:3] returns 4 rows, while df.iloc[0:3] returns 3 rows.
Q How do I fix the SettingWithCopyWarning?
A This is a chained-assignment warning. Use
.copy() to explicitly create a copy, or replace chained operations with a single .loc[] assignment. For example, use df.loc[mask, "col"] = value instead of df[mask]["col"] = value.Q How do I keep the grouping column as a regular column after groupby?
A Use the
as_index=False parameter: df.groupby("category", as_index=False).agg(...), or call .reset_index() after the groupby.Q What if merge produces unexpected extra rows?
A Check whether the merge key has duplicates. Use
df.duplicated(subset=key).sum() to check. If there are duplicates, decide on a retention strategy first (deduplicate or aggregate) before merging.Q What if I run out of memory on a large dataset?
A Three strategies: 1) Specify dtypes to reduce memory (e.g., int64 → int32); 2) Use the
chunksize parameter to read in chunks; 3) Use the Dask library for very large datasets.Q What if the freq parameter throws an error on a time series?
A Use
df.asfreq("D") to ensure a regular frequency, or use df.resample("D").asfreq(). Irregular time series need to be resampled before analysis.📖 Summary
- A DataFrame is a two-dimensional tabular data structure and a Series is a one-dimensional array; together they form the core of Pandas
- Three main strategies for missing values: drop (dropna), fill (fillna), and interpolate (interpolate) — choose based on the situation
- groupby + agg handles grouped aggregation, while pivot_table produces wide-format pivots; each has its own use case
- merge performs SQL-style table joins and concat stacks rows/columns — pay attention to the choice of the how parameter
- Time series processing: parse_dates for parsing dates, resample for resampling, rolling for rolling statistics, and pct_change for computing rates of change
📝 Exercises
- Basic (Difficulty ⭐): Load a CSV file, use
info()anddescribe()to get an overview, and count the number of missing values. Hint:df.isnull().sum(). - Intermediate (Difficulty ⭐⭐): Create two DataFrames (Alice's US sales and Charlie's European sales), merge them by product with merge, and compute the global total sales for each product. Hint: unify the amount unit to USD first, then merge.
- Challenge (Difficulty ⭐⭐⭐): On daily sales data, perform: 1) resample to weekly; 2) compute a 4-week rolling average; 3) detect outliers (points that deviate more than 2 standard deviations from the rolling average). Hint:
resample("W")+rolling(4)+ boolean indexing.
← Previous Lesson: NumPy Crash Course | Next Lesson: Visualization with Matplotlib and Seaborn →
