Data Wrangling·Lesson 31 of 67·intermediate
Joins and reshaping
Merge predictions onto ground truth without silently duplicating rows, and move between the wide shape people read and the long shape tools want.
In this lesson · 20 min
- →Merge two frames with inner, left and outer joins
- →Catch an unintended fan-out with validate and indicator
- →Concatenate frames by row and by column
- →Reshape between wide and long with melt and pivot
- →Expand a column of lists into one row per item with explode
Data arrives split across files: the questions in one, the model’s answers in another, the human labels in a third. Joining them is routine — and the most common way to corrupt a dataset without noticing, because a bad join makes the table bigger rather than raising an error.
merge
indicator=True adds a _merge column saying whether each row was in the left frame, the right
frame or both. Checking it is the fastest way to find the rows you were not expecting:
Different column names, and suffixes
When both sides have a column of the same name, pandas appends the suffixes rather than losing
one. Setting them explicitly turns score_x and score_y into something you can read.
Guarding against a fan-out
If the right-hand frame has duplicate keys, a merge multiplies rows — quietly:
validate= turns that from a silent corruption into an error:
Those two numbers differ, and reporting the first without mentioning the second is how evaluations mislead.
concat
merge combines columns on a key; concat stacks frames:
Wide and long
The same data has two shapes. Wide has one column per variable and is what people read; long has one row per observation and is what plotting and modelling tools want.
pivot goes back the other way:
explode
One document becomes many chunks. explode turns a column of lists into one row per item,
repeating everything else:
Try it yourself
Join predictions onto truth so every question survives, add a boolean correct column, and print coverage: N — how many questions got a prediction.
Reshape wide into long with one row per model and difficulty, using the column names model, difficulty and accuracy.
Check yourself
You merge 1,000 predictions onto 1,000 ground-truth rows and get 1,240 rows back. What happened?
Check yourself
Why evaluate with a left join from ground truth rather than an inner join?
Recap
merge(on=..., how=...): inner keeps matches, left keeps every left row, outer keeps everything.indicator=Trueshows where each row came from.- Pass
validate="one_to_one"(or"many_to_one") on every merge — a silent fan-out moves every number you report. - Evaluate with a left join from ground truth so unanswered examples remain visible.
concatstacks frames by row, or by column withaxis=1.meltgoes wide to long,pivotgoes back.pivotrequires unique pairs;pivot_tableaggregates duplicates.explodeturns a column of lists into one row per item — how a document becomes its chunks.