I2 · Working with Data: NumPy, pandas, SQL and Visualisation

Merging and Reshaping Data

Merging combines DataFrames by matching values in key columns, like a SQL join: inner keeps only matches, left keeps every row of the left table, right and outer keep more. Concatenation stacks tables on top of each other or side by side. Reshaping changes a table's layout: melt turns wide tables into long ones, and pivot turns long tables into wide ones.

10 min Intermediate · Sublevel Beginner Jump to the activity

What you need: The earlier topics of this module.

Definition

Orders live in one table, customers in another, product prices in a third. To answer "how much did customers in Pune spend on electronics?", you must bring them together correctly, without losing or duplicating rows.

Merging combines DataFrames by matching values in key columns, like a SQL join: inner keeps only matches, left keeps every row of the left table, right and outer keep more. Concatenation stacks tables on top of each other or side by side. Reshaping changes a table's layout: melt turns wide tables into long ones, and pivot turns long tables into wide ones.

In simple words

Merging is like matching two class registers by roll number so each student's marks and address end up on one line. Reshaping is like rewriting a timetable from a grid into a list, or a list back into a grid.

Overview

  • Real data is spread over several tables that must be combined on shared keys.
  • pd.merge joins tables like SQL joins; pd.concat stacks them.
  • The how argument chooses inner, left, right or outer joins.
  • Reshaping converts between wide tables (one column per item) and long tables (one row per observation).
  • melt makes long data; pivot makes wide data.
  • After every merge, check the row count: duplicated keys can multiply rows unexpectedly.

Why it matters

Real data is spread across many tables and files. Combining them correctly, and spotting when a join silently drops or multiplies rows, is one of the most important and error-prone steps in building a data set for analysis or machine learning.

Key terms

Merge (join)
Combining tables by matching values in key columns.
Key
The column or columns used to match rows, such as customer_id.
Inner and left join
Inner keeps only rows with matches on both sides; left keeps every left row.
Many-to-one
Many rows on one side match a single row on the other, such as many orders per customer.
concat
Stacking DataFrames along rows or columns.
Wide and long format
Wide has one column per variable or period; long has one row per observation.

Types

Combining and reshaping tables uses several types of operation. The join types decide which rows survive a merge.

1. Inner join

Keeps only rows whose key appears in both tables.

  • How it works: pd.merge(students, marks, on="roll_no", how="inner") drops students with no marks and marks with no student.
  • Example: Matching orders with customers to analyse only complete records.
  • When to use it: Use it when you need matching data on both sides; check how many rows were lost.

2. Left (and right) join

Keeps every row of the left table, adding matching data from the right where it exists.

  • How it works: Rows with no match get NaN in the right table's columns; a right join does the reverse; indicator=True shows where each row came from.
  • Example: Adding each student's address from a separate table, keeping students whose address is missing.
  • When to use it: Use it when the left table is your main list and nothing in it should be dropped.

3. Outer join

Keeps all rows from both tables, matched where possible.

  • How it works: Unmatched rows from either side appear with NaN for the other side's columns.
  • Example: Comparing two attendance lists to find names that appear in only one.
  • When to use it: Use it to reconcile two sources and find what is missing from each.

4. Concatenation

Stacking tables on top of each other (rows) or side by side (columns).

  • How it works: pd.concat([jan, feb, mar], ignore_index=True) appends rows with the same columns; axis=1 joins columns by index.
  • Example: Joining twelve monthly sales files into one yearly table.
  • When to use it: Use it when tables have the same structure and simply need stacking.

5. Reshaping: melt, pivot, stack and unstack

Changing a table between wide and long layouts.

  • How it works: melt(id_vars="name", var_name="subject", value_name="marks") turns subject columns into rows; pivot(index, columns, values) does the reverse; stack/unstack move index levels.
  • Example: A wide marks table with maths, science and English columns becomes a long table with one row per student and subject.
  • When to use it: Use long format for groupby and plotting libraries such as seaborn, and wide format for reports.

Explained in detail

Types of join

how=KeepsTypical use
"inner"Only keys present in both tablesAnalysing orders that have known customers
"left"Every row of the left table; missing matches become NaNAdding customer details to every order
"right"Every row of the right tableLess common; swap tables and use left
"outer"Every key from both tablesFinding mismatches between two lists

Use pd.merge(orders, customers, on="customer_id", how="left"). If key columns have different names, use left_on and right_on. These joins come straight from relational database theory and behave exactly like SQL's JOIN.

The dangers: lost and multiplied rows

An inner join silently drops orders whose customer ID is missing from the customer table. A join on a key that is duplicated on both sides multiplies rows: if two orders match a customer listed twice, you get four rows, and totals double. Both errors produce plausible-looking tables. Protect yourself by comparing row counts before and after every merge, using indicator=True to add a column showing whether each row matched on the left, right or both, and using validate="many_to_one", which makes pandas raise an error if the right-hand key is not unique.

Concatenating and reshaping

  • pd.concat([jan, feb, mar], ignore_index=True) stacks monthly files with the same columns into one table.
  • pd.concat([a, b], axis=1) places tables side by side, aligning on the index.
  • Wide data has one column per period: a table of states with columns 2021, 2022, 2023.
  • df.melt(id_vars="state", var_name="year", value_name="population") turns it long: one row per state-year, which suits plotting and groupby.
  • long.pivot(index="state", columns="year", values="population") turns it back to wide, which suits reports.

Most plotting and modelling tools prefer long, tidy data, in which each variable is a column, each observation is a row and each value is a cell. Converting messy spreadsheets into this tidy form is often the single most useful step in preparing data.

When preparing machine-learning data, merges are where leakage often enters: joining in a column that was only filled in after the outcome, or joining aggregates computed over the whole data set. Write down, for every column you merge in, when that information would really be available at prediction time.

How it works

  1. Identify the key and check it is unique on the side where it should be.
  2. Choose the join type deliberately, usually left.
  3. Merge with validate= and indicator=True.
  4. Compare row counts and inspect unmatched rows.
  5. Reshape with melt or pivot into the layout the next step needs.

In code

merging.py
import pandas as pd

orders = pd.DataFrame({"order_id": [1, 2, 3, 4, 5],
                       "customer_id": [10, 11, 10, 12, 99],
                       "amount": [2500, 1800, 900, 3200, 700]})
customers = pd.DataFrame({"customer_id": [10, 11, 12],
                          "city": ["Pune", "Delhi", "Pune"]})

merged = pd.merge(orders, customers, on="customer_id", how="left",
                  validate="many_to_one", indicator=True)
print(merged)
print("Rows before/after:", len(orders), len(merged))
print("Unmatched orders:", merged.loc[merged["_merge"] == "left_only", "order_id"].tolist())
print(merged.groupby("city")["amount"].sum())

wide = pd.DataFrame({"state": ["Kerala", "Goa"], "2022": [35.6, 1.6], "2023": [35.8, 1.6]})
long = wide.melt(id_vars="state", var_name="year", value_name="population_m")
print(long)
print(long.pivot(index="state", columns="year", values="population_m"))

Output

   order_id  customer_id  amount   city     _merge
0         1           10    2500   Pune       both
1         2           11    1800  Delhi       both
2         3           10     900   Pune       both
3         4           12    3200   Pune       both
4         5           99     700    NaN  left_only
Rows before/after: 5 5
Unmatched orders: [5]
city
Delhi    1800
Pune     6600
Name: amount, dtype: int64
    state  year  population_m
0  Kerala  2022          35.6
1     Goa  2022           1.6
2  Kerala  2023          35.8
3     Goa  2023           1.6
year    2022  2023
state             
Goa      1.6   1.6
Kerala  35.6  35.8

Try it yourself

The revenue that doubled overnight

An analyst merges orders with a customer table to report revenue by city.

Match the tool to the job

Which pandas tool fits?

  • Add customer details to every order
  • Stack January, February and March files
  • Turn year columns into rows
  • Turn long data into a report grid

Common mistakes

  • Using an inner join and silently losing unmatched rows.
  • Merging on non-unique keys and multiplying rows.
  • Leaving data in a wide layout that plotting and grouping tools cannot use easily.

Check yourself

Question 1

1. Which join keeps every order, even without a matching customer?

Question 2

2. After a merge, the row count grew unexpectedly. The likely cause is…

Question 3

3. What does melt do?

Summary

  • merge joins tables on keys; choose inner, left, right or outer deliberately.
  • Check row counts and use validate and indicator to catch lost or duplicated rows.
  • concat stacks tables; melt and pivot switch between long and wide layouts.

Finished reading?

Sources: pandas: Merge, join, concatenate and compare · Python for Data Analysis, 3rd edition (Wes McKinney)