You have two tables that share a column, customers and their orders, and you need them as one.
That is a join, the single most common data-combining task and the pandas version
of a SQL JOIN.
Joining Tables
The key answers "which row here matches which row there?" The how= argument decides which rows
survive the join. These are the four standard types (with cross for every combination):
| how= | Keeps | SQL | Use when |
|---|---|---|---|
| inner (default) | Only keys in both tables | INNER JOIN | You want only matched records. Silently drops unmatched rows |
| left | All left rows; right cols NaN if no match | LEFT OUTER | Keep every left row (all orders, attach customer if known) |
| right | All right rows; left cols NaN if no match | RIGHT OUTER | Symmetric to left (people usually flip the tables) |
| outer | Union of keys from both | FULL OUTER | Keep everything; see all non-matches |
If a key is duplicated on both sides, the merge produces every combination within that key,
multiplying rows and inflating any sum you compute next. Two defenses:
validate="one_to_many" (and friends) raises an error if the relationship is
wrong, and indicator=True adds a column showing where each row matched. Also clean your keys
first, a stray space ("USA " ≠ "USA") or a dtype mismatch (5 ≠
"5") produces zero matches with no error.
When the tables are the same shape (twelve monthly
files with identical columns), you don't join, you stack: pd.concat([...], ignore_index=True)
glues rows together. Merge matches on a key; concat stacks.
Reshaping: Wide ↔ Long
The same data can wear two shapes. Long (tidy) form, one row per observation, is what plots and models want (see the Data-Cleaning Mindset chapter). Wide form, a category spread across columns, is what a human-readable report wants. You move between them constantly.
| Function | Direction | What it does | Gotcha |
|---|---|---|---|
| melt | wide → long | Collapse columns into name + value columns | Name var_name / value_name |
| pivot | long → wide | Spread one column's values into new columns | Fails on duplicate index/column pairs |
| pivot_table | long → wide | Pivot with aggregation (default mean) | Use this when combos can repeat; margins=True adds totals |
| stack / unstack | cols ↔ index | Move a level between columns and the index | The index-level dual of melt / pivot |
Parsing Dates & Times
A date stored as text is nearly useless: it sorts alphabetically ("10/2020" before
"9/2020"), can't do arithmetic, and won't filter by range. pd.to_datetime turns it
into a real datetime, and then a world of operations opens up.
Parse it
pd.to_datetime(s, errors="coerce", format="mixed"); bad values become NaT (see Finding & Removing Duplicates and Inconsistencies and Handling Missing Data). Use dayfirst= for EU-style dates.
Extract features
The .dt accessor: .dt.year, .dt.month, .dt.dayofweek, .dt.day_name(), .dt.quarter, ready for Feature Engineering.
Resample a series
With a datetime index, df.resample("ME").sum() rebins daily to monthly, the time-series version of groupby.
Do date math
Subtract two datetimes for a duration; .dt.days pulls out the day count.
In current pandas (2.2+), the resample/date_range codes changed: use "ME"
for month-end (not the old "M"), "QE" for quarter, "YE"
for year, and "h", "min", "s" for hour/minute/second.
The old single letters now error, so older tutorials copied verbatim will break.
Cleaning Text & the Workflow
The .str accessor runs string operations across an entire column at once, no loop. It carries the casing and whitespace fixes from the Finding & Removing Duplicates and Inconsistencies chapter and adds the two workhorses of field-wrangling: splitting and extracting.
Split a field
s.str.split(",", expand=True) turns "City, State" into two columns. Strip the pieces afterward.
Extract with regex
s.str.extract(r"(\d+)") pulls the first capture group (a number, a code) into a column.
Standardize
s.str.strip().str.lower() unifies casing and whitespace so joins and group-bys work.
Pad codes
s.str.zfill(6) restores leading zeros stripped from IDs and ZIPs.
Since pandas 2.0, .str.replace treats its pattern as a literal string by default
(regex=False). When you mean a regular expression, you must pass regex=True
explicitly, e.g. s.str.replace(r"\D", "", regex=True) to strip non-digits. Older code assumed
the opposite default.
Combine the raw tables (concat to stack, merge to join, validate the keys) →
reshape to tidy (melt, or pivot_table for summaries) → parse dates and
extract features → clean text with .str. The steps interleave, but the end
state is one analysis-ready table.
Most projects spend more effort getting data into one clean shape than on the modeling itself. A wrong join can silently double your revenue totals; the right reshape makes a chart trivial; a parsed date unlocks a dozen features. These are the unglamorous skills that decide whether everything downstream is built on solid ground, and they lead straight into feature engineering (see Feature Engineering) and EDA (see Exploratory Data Analysis (EDA)).
Combining & Reshaping in Machine Learning & AI
Before any model runs, someone has to assemble the training table, and that is joins and reshapes. Getting the shape and the keys right is quietly where most data-science time actually goes.
| Operation | Where it shows up in ML | Why it matters |
|---|---|---|
| Merge / join | Building the feature table from many sources (a feature store) | Most features come from joining tables on a key; a wrong join type silently drops or duplicates rows |
| Long (tidy) format | The input shape most modeling and plotting tools expect | One row per observation, one column per variable, is what pandas, seaborn, and estimators want |
| Pivot / groupby-aggregate | Turning transactions into per-entity features | Aggregating a log into one row per customer is the core of tabular feature engineering |
Two traps recur. A merge that unexpectedly fans out multiplies your rows, so check the count and use
validate=. And when you join time-stamped features, make sure you only attach information that
was known before the prediction moment, joining in a future value is a subtle but fatal
leak.
Real-World Example: Reshaping Store Sales
This store-sales table arrives in long (tidy) format: 48 rows, one per store-month. Pivot it and you get a compact 4-by-12 report (stores down, months across) that a human can read; melt it back and you have the tidy shape a model wants. The companion notebook also merges it with a small store-to-region lookup to roll sales up by region.
Long format, one row per store-month: month (YYYY-MM),
store (four stores), and units_sold. Reshape it between long and wide, and combine it
with a lookup table, the two everyday moves of getting data into the right shape.
Bring it to life in Python
The companion notebook joins customers to orders (inner vs left), triggers and catches a many-to-many
fan-out with validate=, melts a wide table to long and pivots it back, parses mixed dates
and resamples a daily series to monthly, and splits and extracts fields with the .str accessor.
View opens the rendered notebook instantly (no setup). Open in Colab runs &
edits it live in your browser. To run locally, install numpy, pandas,
matplotlib and launch jupyter notebook.
🎓 Key Takeaways
- ✓merge joins on a key;
how=picks survivors (inner drops unmatched rows, left keeps all left rows); concat stacks same-shaped tables. - ✓Duplicate keys cause a fan-out that multiplies rows; guard with
validate=and clean keys first. - ✓melt reshapes wide→long (for plots/models); pivot_table aggregates long→wide and survives duplicates (plain pivot does not).
- ✓to_datetime + .dt unlock sorting, features, and resample; current pandas uses
"ME"/"QE"/"YE"codes. - ✓The .str accessor cleans text in bulk (split, extract, strip); pass
regex=Truefor patterns.
Practice Challenges
Five short challenges, beginner to intermediate. Try them on paper or in Python before checking the solutions.
Join two tables
Join a products table to an orders table on product_id, once how="inner" and
once how="left". Report the row counts and explain where NaN appears.
Wide to long
A wide table has one column per quarter. Melt it so each row is one (store, quarter, sales).
melt(id_vars="store", var_name="quarter", value_name="sales").Long to a grid
From a long log with repeated region-month pairs, build a region × month table of total amount with row
and column totals. Why pivot_table and not pivot?
aggfunc="sum", margins=True; pivot fails on duplicate pairs.Parse & extract dates
Parse a messy signup_date column, count how many failed, then extract the month name and
day of week from the valid dates.
to_datetime(errors="coerce", format="mixed"); .dt.month_name(), .dt.day_name().Clean text
From entries like "Order #1042 - Austin, TX", extract the numeric order id and split the
"City, State" part into two columns.
str.extract(r"#(\d+)") and str.split(",", expand=True).A fully-worked solutions notebook walks through all five challenges in the same visual style. Try them yourself first, then compare.
Quiz: Test Yourself
Eight quick questions on combining and reshaping. Answer them, hit Check Answers, and keep refining until you score 100%. Your progress is saved, so you can hop back to the chapter and return anytime.