Skip to content

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

PythonPython not started
PythonPython not started

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:

PythonPython not started

Different column names, and suffixes

PythonPython not started

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:

PythonPython not started

validate= turns that from a silent corruption into an error:

PythonPython not started
PythonPython not started

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:

PythonPython not started

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.

PythonPython not started

pivot goes back the other way:

PythonPython not started

explode

One document becomes many chunks. explode turns a column of lists into one row per item, repeating everything else:

PythonPython not started

Try it yourself

Exercise 1

Join predictions onto truth so every question survives, add a boolean correct column, and print coverage: N — how many questions got a prediction.

Your turnPython not started
Exercise 2

Reshape wide into long with one row per model and difficulty, using the column names model, difficulty and accuracy.

Your turnPython not started

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=True shows 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.
  • concat stacks frames by row, or by column with axis=1.
  • melt goes wide to long, pivot goes back. pivot requires unique pairs; pivot_table aggregates duplicates.
  • explode turns a column of lists into one row per item — how a document becomes its chunks.