PadhaikoAll subjects

Advanced Data Wrangling and Transformation (part 1: 3.1–3.3)

Advanced Python Programming for Data Science (ENCT 325)

Open in the Padhaiko app

3.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)

Definition

Pandas merge, join, and concat

Relational Join Types in pd.merge(left, right, how=...)
Join Type (how=)Retained KeysMissing Side Values
"inner" (default)Intersection of keys present in both DataFramesNone (unmatched rows dropped)
"left"All keys from left DataFrameFilled with NaN where right has no match
"right"All keys from right DataFrameFilled with NaN where left has no match
"outer"Union of keys from both DataFramesFilled with NaN on whichever side lacks a match
Python
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), 3

Reshaping and Pivoting (pivot, pivot_table, melt, stack/unstack)

AspectWide Format (pivot / pivot_table / unstack)Long / Tidy Format (melt / stack)
df.pivot(index, columns, values)Long → WideReshapes 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 → LongUnpivots column headers into a variable column and a value column (tidy data)
df.stack() / df.unstack()Column ↔ Row IndexMoves innermost column level into row MultiIndex (stack) or back (unstack)
Python
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)

  1. Detection: df.isna().sum() counts missing values per column; df.dropna(thresh=k) keeps rows with at least k non-null entries.
  2. 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, column mean() (symmetric numeric), median() (skewed numeric with outliers), or mode()[0] (categorical).
    • Sequential / Group imputation: ffill() / bfill() or interpolate(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_missing flag preserves signal when missingness itself is informative (Missing Not At Random).

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:

Time-Series Data (DatetimeIndex, Resampling, Rolling Windows)

Definition

Core Pandas Time-Series Operations


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

Feature Scaling

Comparison of Feature Scaling Methods
MethodFormulaOutput Range / PropertiesSensitive 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) / stdMean 0, std 1; unboundedModerately (mean and std shift with extremes)Linear/logistic regression, SVM, PCA, k-means
Robust Scaling(x - median) / IQRMedian 0, IQR 1 (IQR = Q3 - Q1)No (median and IQR ignore extreme tails)Datasets with heavy outliers

Categorical Encoding

AspectOne-Hot EncodingOrdinal / 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 integersCompact; invalid for nominal features like city in linear/distance models
Frequency / Target EncodingReplaces category with training-set count or smoothed target meanHandles high-cardinality IDs (zip_code); requires out-of-fold / smoothing to avoid overfitting

Worked Examples

Example 3.1

3.1

Given a training feature vector x = [20, 30, 40, 50, 80]:

  1. Compute the Min-Max scaled values for 20, 50, and 80.
  2. Compute the Z-score of x = 50 using the population mean and population standard deviation.
  3. Compute the Robust Scaled value of x = 50 using Q1 = 30, median = 40, and Q3 = 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, so std = sqrt(424) ≈ 20.591.
  • Z-score for x = 50: (50 - 44.0) / 20.591 ≈ 6.0 / 20.591 ≈ 0.291.
Answer:
  • Min-Max: 20 → 0.0, 50 → 0.5, 80 → 1.0.
  • Standardization (Z-score): mean = 44.0, std ≈ 20.591, so z(50) ≈ 0.291.
  • Robust Scaling: median = 40, IQR = 50 - 30 = 20, so robust(50) = 0.50.
Example 3.2

3.2

Suppose a daily sensor DataFrame has missing values and irregular readings:

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:

Python
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.
Answer:

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.

Example 3.3

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():

  1. State that both convert long format → wide format using (index, columns, values).
  2. Explain the crucial difference: df.pivot() performs pure reshaping and raises a ValueError if duplicate (index, columns) entries exist; df.pivot_table() supports an aggfunc (default "mean") to aggregate duplicate cells and optionally adds margins=True totals.

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.

Answer:

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)

Question 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 both A and B.
  • "left" retains every key from A, filling unmatched B columns with NaN.
  • "outer" retains the union of all keys from A and B, filling missing sides with NaN.

Evaluating on Keys [1, 2, 3] and [2, 3, 4]:

  • how="inner" → keys [2, 3].
  • how="left" → keys [1, 2, 3] (key 1 has NaN for B columns).
  • how="outer" → keys [1, 2, 3, 4] (key 1 lacks B; key 4 lacks A).
Answer:

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).

Question 2

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.
Answer:

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.

Question 3

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:
Python
df["salary"] = df["salary"].fillna(
    df.groupby("dept")["salary"].transform("mean")
)
Answer:

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")).

Question 4

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 treat Biratnagar > Pokhara > Kathmandu as arithmetic order.
  • Setting drop_first=True drops one redundant dummy column (the reference category) to avoid the dummy variable trap (perfect multicollinearity, where dummy columns sum to 1).

Ordered pd.Categorical Benefits:

Python
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.

Answer:

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.

Question 5

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.

Answer:

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.

← Data Sources and APIsAdvanced Data Wrangling and Transformation (part 2: 3.4–3.6) →