Advanced Data Wrangling and Transformation (part 1: 3.1–3.3)
Open in the Padhaiko app3.1 Advanced Pandas operations: Merging, joining, reshaping, pivoting
Raw datasets rarely arrive in a single analysis-ready table. Relational records must be combined across keys, and wide tables frequently need to be reshaped into tidy long format (or summarized into cross-tabulations).
Combining DataFrames (merge, join, concat)
Pandas merge, join, and concat
pd.merge(left, right, on="key", how="inner"): SQL-style relational join on one or more key columns (orleft_on/right_on). Supportshowin{"inner", "left", "right", "outer", "cross"}andvalidate="one_to_many"to catch accidental many-to-many blowups.left.join(right, how="left"): Convenience method that joins on the index by default (or a column viaon=).pd.concat([df1, df2], axis=0, ignore_index=True): Stacks DataFrames vertically (axis=0, appending rows) or horizontally (axis=1, aligning columns by index).
Join Type (how=) | Retained Keys | Missing Side Values |
|---|---|---|
"inner" (default) | Intersection of keys present in both DataFrames | None (unmatched rows dropped) |
"left" | All keys from left DataFrame | Filled with NaN where right has no match |
"right" | All keys from right DataFrame | Filled with NaN where left has no match |
"outer" | Union of keys from both DataFrames | Filled with NaN on whichever side lacks a match |
import pandas as pd
students = pd.DataFrame({"sid": [1, 2, 3], "name": ["Anil", "Bina", "Charu"]})
scores = pd.DataFrame({"sid": [1, 3, 4], "marks": [88, 92, 75]})
inner_df = pd.merge(students, scores, on="sid", how="inner") # sids 1, 3
left_df = pd.merge(students, scores, on="sid", how="left") # sids 1, 2 (NaN), 3Reshaping and Pivoting (pivot, pivot_table, melt, stack/unstack)
| Aspect | Wide Format (pivot / pivot_table / unstack) | Long / Tidy Format (melt / stack) |
|---|---|---|
df.pivot(index, columns, values) | Long → Wide | Reshapes when (index, columns) pairs are unique (raises ValueError on duplicates) |
df.pivot_table(..., aggfunc="mean") | Long → Wide (Aggregated) | Aggregates duplicate (index, columns) cells using mean, sum, count, etc.; supports margins=True |
pd.melt(df, id_vars, value_vars) | Wide → Long | Unpivots column headers into a variable column and a value column (tidy data) |
df.stack() / df.unstack() | Column ↔ Row Index | Moves innermost column level into row MultiIndex (stack) or back (unstack) |
sales = pd.DataFrame({
"month": ["Jan", "Jan", "Feb", "Feb"],
"region": ["East", "West", "East", "West"],
"rev": [100, 150, 120, 180],
})
wide = sales.pivot(index="month", columns="region", values="rev")
tidy = wide.reset_index().melt(id_vars="month", var_name="region", value_name="rev")3.2 Handling missing, categorical, and time-series data
Real-world datasets contain gaps (NaN, None, pd.NA), high-cardinality or ordinal text labels, and irregular timestamps. Cleaning them systematically prevents silent distortion in models.
Missing Data (NaN, None, pd.NA)
- Detection:
df.isna().sum()counts missing values per column;df.dropna(thresh=k)keeps rows with at leastknon-null entries. - Deletion vs. Imputation:
- Listwise deletion (
dropna()): Safe only when missingness is rare and Missing Completely At Random (MCAR); otherwise shrinks sample size and introduces bias. - Simple imputation (
fillna()): Constant value, columnmean()(symmetric numeric),median()(skewed numeric with outliers), ormode()[0](categorical). - Sequential / Group imputation:
ffill()/bfill()orinterpolate(method="time")for ordered time-series;df.groupby("dept")["salary"].transform(lambda s: s.fillna(s.median()))for grouped imputation. - Missingness indicator: Adding a boolean
col_is_missingflag preserves signal when missingness itself is informative (Missing Not At Random).
- Listwise deletion (
Categorical Data (pd.Categorical)
Converting repeating string columns to category dtype (df["grade"] = df["grade"].astype("category")) stores integer codes pointing to a small lookup table of unique categories:
- Cuts memory usage dramatically (often 5–10x on low-cardinality text columns) and accelerates
groupby()operations. - Supports ordered categories (
pd.Categorical(vals, categories=["Low", "Med", "High"], ordered=True)) so sorting and comparisons (df["risk"] > "Low") follow domain order instead of alphabetical order.
Time-Series Data (DatetimeIndex, Resampling, Rolling Windows)
Core Pandas Time-Series Operations
- Parsing & Indexing:
df["ts"] = pd.to_datetime(df["ts"], utc=True)followed bydf = df.set_index("ts").sort_index()enables slice queries likedf.loc["2026-01":"2026-03"]. - Resampling (
.resample(rule)): Time-basedgroupbythat changes frequency—downsampling (e.g., hourly → daily viadf["kwh"].resample("D").sum()) or upsampling (daily → hourly via.resample("h").ffill()). - Shifting & Differencing:
df["prev"] = df["val"].shift(1)creates lag features;df["val"].diff()computes period-to-period change. - Rolling Windows (
.rolling(window=7)): Computes moving averages or volatility over fixed observation or time windows (df["temp"].rolling("7D").mean()).
3.3 Feature transformation, scaling, and encoding
Machine learning algorithms expect numeric feature matrices on comparable scales. Raw skewed quantities and categorical strings must be transformed mathematically—always fitting parameters on training data only to prevent data leakage.
Feature Transformation
- Log / Power Transforms (
np.log1p, Box-Cox, Yeo-Johnson): Compress heavy right-skewed distributions (such as income or transaction size) toward normality;np.log1p(x) = ln(1 + x)handles zero values safely (x >= 0). - Binning / Discretization:
pd.cut(x, bins=...)creates equal-width intervals;pd.qcut(x, q=4)creates equal-frequency quantile bins (quartiles). - Polynomial & Interaction Features: Adding
x^2orx1 * x2lets linear models capture non-linear relationships.
Feature Scaling
| Method | Formula | Output Range / Properties | Sensitive to Outliers? | Best Suited For |
|---|---|---|---|---|
| Min-Max Scaling | (x - x_min) / (x_max - x_min) | Bounded [0, 1] | Yes (extreme min/max squashes normal values) | Neural networks, image pixels, bounded inputs |
| Standardization (Z-score) | (x - mean) / std | Mean 0, std 1; unbounded | Moderately (mean and std shift with extremes) | Linear/logistic regression, SVM, PCA, k-means |
| Robust Scaling | (x - median) / IQR | Median 0, IQR 1 (IQR = Q3 - Q1) | No (median and IQR ignore extreme tails) | Datasets with heavy outliers |
Categorical Encoding
| Aspect | One-Hot Encoding | Ordinal / Target Encoding |
|---|---|---|
One-Hot Encoding (pd.get_dummies / OneHotEncoder) | Creates one binary 0/1 column per category (drop_first=True avoids linear multicollinearity) | No false numeric ordering; explodes dimensionality if cardinality is huge |
Ordinal Encoding (OrdinalEncoder) | Maps ordered levels (Low=0, Med=1, High=2) to integers | Compact; invalid for nominal features like city in linear/distance models |
| Frequency / Target Encoding | Replaces category with training-set count or smoothed target mean | Handles high-cardinality IDs (zip_code); requires out-of-fold / smoothing to avoid overfitting |
Worked Examples
3.1
Given a training feature vector x = [20, 30, 40, 50, 80]:
- Compute the Min-Max scaled values for
20,50, and80. - Compute the Z-score of
x = 50using the population mean and population standard deviation. - Compute the Robust Scaled value of
x = 50usingQ1 = 30,median = 40, andQ3 = 50.
Min-Max Scaling on [20, 30, 40, 50, 80]: From the data, x_min = 20 and x_max = 80, so span = 80 - 20 = 60:
- For
x = 20:(20 - 20) / 60 = 0.0 - For
x = 50:(50 - 20) / 60 = 30 / 60 = 0.5 - For
x = 80:(80 - 20) / 60 = 60 / 60 = 1.0
Z-Score Standardization: Compute mean and population standard deviation for [20, 30, 40, 50, 80]:
mean = (20 + 30 + 40 + 50 + 80) / 5 = 220 / 5 = 44.0.- Squared deviations:
(20-44)^2 + (30-44)^2 + (40-44)^2 + (50-44)^2 + (80-44)^2 = 576 + 196 + 16 + 36 + 1296 = 2120. - Population variance =
2120 / 5 = 424.0, sostd = sqrt(424) ≈ 20.591. - Z-score for
x = 50:(50 - 44.0) / 20.591 ≈ 6.0 / 20.591 ≈ 0.291.
- Min-Max:
20 → 0.0,50 → 0.5,80 → 1.0. - Standardization (Z-score):
mean = 44.0,std ≈ 20.591, soz(50) ≈ 0.291. - Robust Scaling:
median = 40,IQR = 50 - 30 = 20, sorobust(50) = 0.50.
3.2
Suppose a daily sensor DataFrame has missing values and irregular readings:
- Day 1:
10.0, Day 2:NaN, Day 3:18.0, Day 4:22.0, Day 5:26.0.
Write Pandas code to (a) linearly interpolate Day 2's missing reading, (b) compute a 3-day rolling mean, and (c) calculate the resulting 3-day rolling mean values at Day 3 and Day 4.
Linear Interpolation of Day 2:
import pandas as pd
s = pd.Series([10.0, None, 18.0, 22.0, 26.0],
index=pd.date_range("2026-11-01", periods=5, freq="D"))
s_clean = s.interpolate(method="linear")
roll3 = s_clean.rolling(window=3).mean()Linear interpolation between Day 1 (10.0) and Day 3 (18.0) fills Day 2 with (10.0 + 18.0) / 2 = 14.0.
Computing the 3-Day Rolling Mean: With s_clean = [10.0, 14.0, 18.0, 22.0, 26.0]:
- Day 1 and Day 2:
NaN(fewer than 3 observations in window). - Day 3 (Days 1–3):
(10.0 + 14.0 + 18.0) / 3 = 42.0 / 3 = 14.0. - Day 4 (Days 2–4):
(14.0 + 18.0 + 22.0) / 3 = 54.0 / 3 = 18.0. - Day 5 (Days 3–5):
(18.0 + 22.0 + 26.0) / 3 = 66.0 / 3 = 22.0.
Linear interpolation sets Day 2 to 14.0. The 3-day rolling mean produces [NaN, NaN, 14.0, 18.0, 22.0], giving 14.0 on Day 3 and 18.0 on Day 4.
3.3
How should a student answer an exam question asking: "Distinguish between pivot() and pivot_table(), and explain melt() with a concrete DataFrame example"?
Contrasting pivot() vs. pivot_table():
- State that both convert long format → wide format using
(index, columns, values). - Explain the crucial difference:
df.pivot()performs pure reshaping and raises aValueErrorif duplicate(index, columns)entries exist;df.pivot_table()supports anaggfunc(default"mean") to aggregate duplicate cells and optionally addsmargins=Truetotals.
Explaining melt() with a Small Table: Show pd.melt(df, id_vars=["student"], var_name="subject", value_name="marks") converting a wide student | math | python table into a tidy long student | subject | marks table, noting that wide format suits human reporting while tidy long format suits groupby() and seaborn plotting.
Explain that pivot() reshapes unique (index, columns) pairs while pivot_table() aggregates duplicates via aggfunc, then illustrate pd.melt() reversing wide columns into (id_vars, variable, value) tidy rows with a 2-row input/output table.
Exercises
Practice Exercises — Advanced Data Wrangling (Part 1)
Differentiate between pd.merge(..., how="inner"), how="left", and how="outer". Given A with keys [1, 2, 3] and B with keys [2, 3, 4], state which keys appear in each join result.
Join Definitions:
"inner"retains only keys present in bothAandB."left"retains every key fromA, filling unmatchedBcolumns withNaN."outer"retains the union of all keys fromAandB, filling missing sides withNaN.
Evaluating on Keys [1, 2, 3] and [2, 3, 4]:
how="inner"→ keys[2, 3].how="left"→ keys[1, 2, 3](key1hasNaNforBcolumns).how="outer"→ keys[1, 2, 3, 4](key1lacksB; key4lacksA).
Inner join yields [2, 3]; left join yields [1, 2, 3] (with NaN in B for key 1); outer join yields [1, 2, 3, 4] (with NaN where either table lacks a match).
Explain why Robust Scaling is preferred over Min-Max Scaling when a numeric feature contains extreme outliers. Calculate both the Min-Max scaled value and the Robust scaled value for x = 500 given min = 100, max = 900, Q1 = 300, median = 400, and Q3 = 500.
Why Robust Scaling Resists Outliers: Min-Max scaling divides by (x_max - x_min). A single extreme outlier inflates x_max, compressing all typical values into a narrow sub-interval near 0. Robust scaling subtracts the median and divides by the interquartile range (IQR = Q3 - Q1), ignoring the top 25% and bottom 25% tails.
Numerical Computation at x = 500:
- Min-Max:
(500 - 100) / (900 - 100) = 400 / 800 = 0.50. - Robust:
IQR = Q3 - Q1 = 500 - 300 = 200, so(500 - 400) / 200 = 100 / 200 = 0.50.
Robust Scaling uses median and IQR so extreme outliers do not distort the scale of normal observations, whereas Min-Max compresses normal points when max or min is an outlier. For x = 500, Min-Max = 0.50 and Robust = 0.50.
What is the difference between MCAR, MAR, and MNAR missingness mechanisms? Given column salary = [10, NaN, 30, 20, 40] (in thousands), compute the mean-imputed value for the missing entry and write the Pandas expression to impute it per "dept" group.
Missingness Mechanisms:
- MCAR (Missing Completely At Random): Missingness is independent of both observed and unobserved data (e.g., random sensor packet drop).
- MAR (Missing At Random): Missingness depends on observed variables (e.g., older respondents skipping an online field recorded alongside age).
- MNAR (Missing Not At Random): Missingness depends on the unobserved missing value itself (e.g., very high earners refusing to disclose salary).
Mean Imputation and Grouped Imputation Code:
- Observed values
[10, 30, 20, 40]have mean =(10 + 30 + 20 + 40) / 4 = 100 / 4 = 25.0. - Grouped Pandas imputation:
df["salary"] = df["salary"].fillna(
df.groupby("dept")["salary"].transform("mean")
)MCAR depends on nothing; MAR depends on observed columns; MNAR depends on the missing value itself. The mean-imputed salary is 25.0, and grouped imputation uses df["salary"].fillna(df.groupby("dept")["salary"].transform("mean")).
When should you choose One-Hot Encoding versus Ordinal Encoding, and why does drop_first=True matter for linear regression? Also show how pd.Categorical(..., ordered=True) improves both memory and sorting.
One-Hot vs. Ordinal Encoding:
- Use Ordinal Encoding (
0, 1, 2, ...) only when categories have a genuine monotonic rank (Low < Medium < High). - Use One-Hot Encoding for nominal categories (
Kathmandu,Pokhara,Biratnagar) so the model does not falsely treatBiratnagar > Pokhara > Kathmanduas arithmetic order. - Setting
drop_first=Truedrops one redundant dummy column (the reference category) to avoid the dummy variable trap (perfect multicollinearity, where dummy columns sum to1).
Ordered pd.Categorical Benefits:
sizes = pd.Categorical(["M", "S", "L"], categories=["S", "M", "L"], ordered=True)Storing integer codes instead of repeated Python str objects shrinks memory and speeds groupby, while ordered=True ensures .sort_values() orders S < M < L rather than alphabetical L < M < S.
Use One-Hot Encoding for nominal labels (with drop_first=True in linear models to prevent multicollinearity) and Ordinal Encoding only for naturally ranked categories. pd.Categorical(..., ordered=True) saves memory via integer codes and enforces domain-correct sorting.
Explain resampling (downsample vs. upsample) on a Pandas DatetimeIndex and explain why calling scaler.fit_transform() before train_test_split() causes data leakage. Compute the daily mean for three hourly readings [100, 120, 140] recorded on the same day.
Resampling and Data Leakage:
- Downsampling aggregates fine-grained timestamps to a coarser frequency (e.g.,
.resample("D").mean()), whereas upsampling converts coarse timestamps to a finer grid (.resample("h").ffill()). - Calling
scaler.fit_transform(X)on the full dataset leaks test-set statistics (mean,std,min,max) into training features, producing overly optimistic evaluation metrics that fail on unseen production inputs.
Daily Mean Calculation: mean = (100 + 120 + 140) / 3 = 360 / 3 = 120.0.
Downsampling aggregates higher-frequency timestamps to lower frequency (daily mean of [100, 120, 140] is 120.0), while upsampling expands to finer intervals with fill/interpolation. Fitting a scaler before train_test_split leaks test distribution parameters into training.