pandas II — Cleaning, Time Series & the Excel↔Python Bridge

Learning objectives

By the end you can:

1. Explain pandas' vocabulary for missing data: NaN/None/pd.NA, isna()/notna(), dropna(), fillna(), .ffill()/.bfill(), .interpolate(), and choose correctly among them for a given gap.
2. Coerce types deliberately: astype(), pd.to_numeric(..., errors="coerce"), pd.to_datetime(..., dayfirst=...), and the category dtype, including the exact locale trap (dd/mm vs. mm/dd) that also bites Excel's Change Type, and its pandas-side fix.
3. Find and remove duplicate rows correctly with duplicated()/drop_duplicates(subset=, keep=), distinguishing an exact duplicate from a merely-similar row.
4. Clean and reshape text with the .str accessor (strip, replace, contains, extract, split, case normalization) and explain why a merge or group-by key must always be cleaned first.
5. Reshape data both ways: pd.melt() to unpivot a wide crosstab into tidy long form, and .pivot_table() to pivot it back, and state why "tidy" (one row per observation) is the shape every downstream tool wants.
6. Combine frames correctly: pd.concat() to stack same-shaped tables (Append) versus pd.merge() to join on a key (Merge), including every join kind (how="left"/"right"/"inner"/"outer") and how to isolate an anti-join.
7. Aggregate with .groupby(...).agg(...), and read its result correctly (including its default sort order, and its most common surprise).
8. Build and use a real DatetimeIndex: pd.to_datetime, set_index, pd.date_range, tz_localize/tz_convert, as the foundation every time-series operation below assumes.
9. Resample a time series to a new frequency (.resample(...).last()/.mean()/.ohlc()), and explain why this is not the same operation as slicing every Nth row.
10. Compute rolling and expanding statistics (.rolling(window).mean()/.std()), and shift, difference, and percent-change a series (.shift(), .diff(), .pct_change()), including a first, mechanics-only look at pd.merge_asof for a point-in-time join.
11. Recite the canonical Excel/Power Query ↔ pandas bridge for every step above, in both directions, and apply a concrete decision framework for which tool a given task actually calls for.
12. (Productivity objective: R10 duality.) Use an AI coding assistant to draft a cleaning pipeline or translate an existing Power Query M script into pandas, while verifying every line yourself, running this module's own hidden tests before trusting any of it, and never accepting a translation you cannot personally justify step by step.

The duality, stated once (R10). Objectives 1–11 are the understanding objective: the gate (quiz ≥ 85%, code-pack ≥ 85%) rewards being able to write and explain this pandas yourself, from a blank editor. Objective 12 is the productivity objective: a real developer routinely has an AI assistant draft the first pass of a cleaning script or translate an M query, the skill is knowing enough, from objectives 1–11, to catch the draft when it is subtly wrong, and to never ship a line you cannot defend.

Prerequisites & connections

Builds on. DA1.01 (NumPy & Vectorized Computation) gave you the array mechanics (vectorization, broadcasting, why a loop over a numpy array is the wrong instinct) that pandas is built on top of; every Series here is a labeled numpy array underneath. DA1.02 (pandas I. Data Wrangling) gave you the Series/DataFrame object model itself: construction, .loc/.iloc indexing and selection, basic filtering and sorting, and a first, single pass at reading a clean CSV. This module assumes all of that and does not re-teach it (R1), if you find yourself unsure what .loc[] does or how a DataFrame is shaped, that is DA1.02's territory, not this one's. What DA1.02 did not cover, and what this module owns outright, is the fact that real data arrives dirty (cleaning) and that a huge share of finance data is fundamentally ordered by time in a way a plain row index does not capture (time series), plus the deliberate, named payoff of connecting all of it back to the Excel/Power Query skills you may already carry from the Excel track.

A note if you came from the Excel track. EX4.01 (Power Query I. Import & Clean) and EX4.02 (Power Query II. M & Pipelines) taught you Change Type, Trim/Clean, Fill Down, Remove Duplicates, Unpivot Other Columns, Group By, Append vs. Merge Queries, and the try ... otherwise error-row reflex, and EX7.02 (Excel Capstones, Capstone 3) had you build a real Power Query portfolio tracker on top of all of it. This module leans on every one of those names throughout and duplicates none of their teaching (R1), it assumes you already know what those Power Query operations do and shows you their exact pandas equivalent. If you have not done the Excel track, every mapping below still stands alone: each pandas technique is taught in full here, with the Excel-side name given only as a one-line orientation, not a prerequisite.