Combining and Reshaping DataFrames in Pandas: 10 Code-Along Examples

The combining and reshaping article makes a clean split: combining puts two tables together side by side or end to end, and reshaping rearranges one table between wide and long form. This workbook runs both. The first half is the join family, from a plain inner merge through the four join types, anti-joins, validation, chaining, and self-joins, plus the two time-aware merges pandas offers. The second half reshapes, moving a table from wide to long and back. The habit worth forming early: name your join type and your key on purpose, because the silent default is where most merge bugs live.

1. Stack tables end to end with concat

When several tables share the same columns, pd.concat stacks them vertically into one. The keys argument labels where each block came from, which you often need after the pieces have merged into a single frame.

import pandas as pd
jan = pd.DataFrame({"product": ["A", "B"], "revenue": [100, 150]})
feb = pd.DataFrame({"product": ["A", "B"], "revenue": [120, 130]})
mar = pd.DataFrame({"product": ["A", "B"], "revenue": [140, 160]})
# stack them into one long frame
combined = pd.concat([jan, feb, mar], keys=["Jan", "Feb", "Mar"])
print(combined)
# the keys become an outer index level you can group on
monthly_avg = combined.groupby(level=0)["revenue"].mean()
print(monthly_avg)

concat matches on column names and lays the rows out one block after another, so three two-row frames become one six-row frame. The keys argument is the detail that earns its place: it adds an outer index level tagging each row with its source month, so after concatenating you can still group by origin with groupby(level=0). Without keys the rows blend together and you lose track of which came from where. Add join="inner" if the frames do not share every column and you want only the columns they have in common.

2. The basic merge

merge joins two tables on a shared key, aligning rows where the key matches. With no other arguments it performs an inner join, keeping only the customers who have an order and vice versa.

import pandas as pd
customers = pd.DataFrame({
    "customer_id": [1, 2, 3],
    "name": ["Ana", "Ben", "Cara"],
})
orders = pd.DataFrame({
    "order_id": [10, 11, 12],
    "customer_id": [1, 1, 2],
    "amount": [40, 55, 30],
})
# join orders to their customer on the shared key
result = orders.merge(customers, on="customer_id")
print(result)

The on="customer_id" tells pandas which column links the two tables, and each order gains its customer’s name alongside it. Note that Cara, customer 3, does not appear: she has no order, and the default inner join keeps only keys present in both tables. The originals are untouched, since merge returns a new frame. This default-inner behaviour is the first thing to be deliberate about, because it silently drops unmatched rows, which is sometimes what you want and sometimes a bug.

3. The four join types

The how argument chooses which unmatched rows to keep. Left keeps all left rows, right keeps all right rows, outer keeps everything, and each fills the gaps with NaN. Seeing all three at once fixes the difference.

import pandas as pd
products = pd.DataFrame({"product_id": [1, 2, 3], "name": ["Pen", "Pad", "Ink"]})
inventory = pd.DataFrame({"product_id": [2, 3, 4], "stock": [50, 0, 20]})
left = products.merge(inventory, on="product_id", how="left")
right = products.merge(inventory, on="product_id", how="right")
outer = products.merge(inventory, on="product_id", how="outer")
print("LEFT  (all products, stock where known):")
print(left)
print("\nRIGHT (all inventory rows, name where known):")
print(right)
print("\nOUTER (everything, nulls where unmatched):")
print(outer)

Read the NaNs, because they are the whole story. The left join keeps Pen, Pad, and Ink and leaves Pen’s stock as NaN, since product 1 has no inventory row. The right join keeps products 2, 3, and 4 and leaves product 4’s name as NaN, since it has no product row. The outer join keeps all four keys and fills gaps on both sides. Inner, the default from Example 2, would keep only products 2 and 3, the keys present in both. Choosing how deliberately is how you control whether unmatched rows survive.

4. Different key names and duplicate columns

Real tables rarely name the same thing identically. left_on and right_on merge on differently named columns, and suffixesdisambiguates non-key columns that collide.

import pandas as pd
products = pd.DataFrame({"id": [1, 2], "name": ["Pen", "Pad"], "price": [2, 5]})
pricing = pd.DataFrame({"product_id": [1, 2], "price": [3, 6], "region": ["UK", "UK"]})
result = products.merge(
    pricing,
    left_on="id",              # the key column in the left table
    right_on="product_id",     # the differently named key in the right table
    suffixes=("_list", "_sale")  # both tables have a 'price'; label them
)
print(result)
print("\ncolumns:", list(result.columns))

Two things are happening. left_on and right_on let you join id to product_id without renaming anything first, and both key columns appear in the output. And because both tables carry a price column, pandas would otherwise disambiguate them as price_x and price_y, which is unreadable; suffixes=("_list", "_sale") renames them to something meaningful instead. Whenever your merge output has a mystery _x or _y column, it is telling you two columns collided and you should have set suffixes.

5. Anti-join: find the rows that did not match

To find rows with no match, merge with how="left" and indicator=True, then keep the rows marked left_only. This is the pandas equivalent of a SQL anti-join.

import pandas as pd
all_users = pd.DataFrame({"user_id": [1, 2, 3, 4], "name": ["Ana", "Ben", "Cara", "Dan"]})
purchases = pd.DataFrame({"user_id": [1, 3], "amount": [40, 90]})
merged = all_users.merge(purchases, on="user_id", how="left", indicator=True)
print(merged)
# keep only users with NO purchase
never_purchased = merged[merged["_merge"] == "left_only"]
print("\nusers who never purchased:")
print(never_purchased[["user_id", "name"]])

The indicator=True argument adds a _merge column labelling every row as both, left_only, or right_only, which tells you exactly where each row came from. Filtering to left_only keeps the users who appear on the left but had no match on the right, here Ben and Dan, who never purchased. This is the cleanest way to answer “who is in table A but not table B”, and the _mergecolumn is worth reaching for even when you are just debugging a merge that returned surprising counts.

6. Guard against silent fan-out with validate

A merge on a key that is not unique can multiply rows without warning. The validate argument states the relationship you expect and raises an error if the data violates it, catching the bug at merge time.

import pandas as pd
customers = pd.DataFrame({"customer_id": [1, 2], "name": ["Ana", "Ben"]})
# a DUPLICATE key sneaks in: customer 1 appears twice
regions = pd.DataFrame({
    "customer_id": [1, 1, 2],
    "region": ["UK", "EU", "US"],
})
# we EXPECT one region per customer; validate makes that explicit
try:
    result = customers.merge(regions, on="customer_id", validate="one_to_one")
except Exception as e:
    print("validate caught it:", type(e).__name__)
    print(e)
# without validate, the merge silently duplicates Ana
silent = customers.merge(regions, on="customer_id")
print("\nsilent fan-out:", len(silent), "rows from 2 customers")

validate="one_to_one" declares that each key should appear once on both sides, and because customer 1 has two region rows, pandas raises a MergeError instead of quietly duplicating Ana. The alternative, shown at the end, is the silent fan-out: without validation the merge succeeds and returns three rows from two customers, which downstream code will treat as real. Use one_to_many or many_to_one when duplication is expected, but state it, because an unvalidated merge is how a total quietly doubles.

7. Chaining multiple merges

Assembling data from three or more tables is just merges in sequence. Each merge returns a frame you can merge again, so the calls chain left to right.

import pandas as pd
orders = pd.DataFrame({"order_id": [1, 2, 3], "customer_id": [10, 11, 10], "product_id": [100, 100, 200]})
customers = pd.DataFrame({"customer_id": [10, 11], "name": ["Ana", "Ben"]})
products = pd.DataFrame({"product_id": [100, 200], "product": ["Pen", "Pad"], "price": [2, 5]})
enriched = (
    orders
    .merge(customers, on="customer_id")     # attach the customer
    .merge(products, on="product_id")       # then attach the product
)
print(enriched)
# now a full row exists per order, ready to summarise
revenue_by_customer = enriched.groupby("name")["price"].sum()
print("\nrevenue by customer:")
print(revenue_by_customer)

Each merge adds another table’s columns, so after two chained calls every order carries its customer name and its product details on one row. The chaining reads as a pipeline, which is why wrapping it in parentheses and putting one merge per line is the standard style: it stays readable as the chain grows. Once the row is complete, ordinary groupby turns the assembled data into the summary you actually wanted, which is usually the point of the merges.

8. The self-join

Merging a table with itself relates rows within one table, which is how you express hierarchies and same-group comparisons. The suffixes argument is mandatory here, since every column would otherwise collide.

import pandas as pd
staff = pd.DataFrame({
    "employee": ["Ana", "Ben", "Cara", "Dan"],
    "project_id": [1, 1, 2, 2],
})
# pair every employee with every colleague on the same project
pairs = staff.merge(staff, on="project_id", suffixes=("_a", "_b"))
# drop self-pairs and mirror duplicates, keeping one row per pair
pairs = pairs[pairs["employee_a"] < pairs["employee_b"]]
print(pairs)

Joining staff to staff on project_id produces every combination of employees sharing a project, which is how “who works with whom” becomes a query. The two suffixes are required because both copies contribute an employee column. The filter employee_a < employee_b does two jobs at once: it removes rows pairing someone with themselves, and it keeps only one of each mirrored pair, so Ana-Ben appears but Ben-Ana does not. Self-joins power org charts, colleague pairings, and any “compare rows within a group” question.

9. Time-aware merges: merge_ordered and merge_asof

Two specialised merges exist for time series. merge_ordered keeps the result sorted and can forward-fill gaps; merge_asofmatches each row to the nearest earlier key rather than an exact one.

import pandas as pd
revenue = pd.DataFrame({
    "date": pd.to_datetime(["2026-01-01", "2026-03-01", "2026-05-01"]),
    "revenue": [100, 130, 160],
})
target = pd.DataFrame({
    "date": pd.to_datetime(["2026-01-01", "2026-04-01"]),
    "target": [90, 140],
})
# merge_ordered: stay sorted, forward-fill the target across gaps
ordered = pd.merge_ordered(revenue, target, on="date", how="left", fill_method="ffill")
print("merge_ordered with ffill:")
print(ordered)
# merge_asof: match each trade to the most recent price at or before it
prices = pd.DataFrame({
    "time": pd.to_datetime(["2026-01-01 09:00", "2026-01-01 09:05"]),
    "price": [10.0, 10.5],
})
trades = pd.DataFrame({
    "time": pd.to_datetime(["2026-01-01 09:03", "2026-01-01 09:07"]),
    "shares": [100, 200],
})
asof = pd.merge_asof(trades, prices, on="time", direction="backward")
print("\nmerge_asof (nearest earlier price):")
print(asof)

The two solve different time problems. merge_ordered behaves like a normal merge but keeps the output in sorted order and, with fill_method="ffill", carries the last known target forward across the months that have none, so every revenue row has a target to compare against. merge_asof does something a normal merge cannot: it matches each trade to the most recent price at or before its timestamp, with direction="backward", which is exactly how you attach a prevailing price to an event that did not happen on a price tick. Both require the key to be sorted, which is why time-series data is stored in order.

10. Reshape between wide and long

Reshaping rearranges one table without combining anything. melt turns wide columns into long rows, and pivot_table turns long rows back into a wide grid, aggregating as it goes.

import pandas as pd
# WIDE: one row per store, a column per channel
wide = pd.DataFrame({
    "store": ["S1", "S2"],
    "web": [0.10, 0.12],
    "app": [0.15, 0.11],
})
print("wide:\n", wide)
# melt to LONG: one row per store-channel pair
long = wide.melt(id_vars="store", var_name="channel", value_name="rate")
print("\nlong:\n", long)
# pivot_table back to wide, aggregating (mean by default)
back = long.pivot_table(values="rate", index="store", columns="channel")
print("\npivoted back to wide:\n", back)

The two are inverses. melt unpivots: id_vars="store" names the column to keep fixed, and the remaining columns collapse into a channel label and a rate value, so a two-column-per-store grid becomes one row per store-and-channel. This long form is what most plotting and grouping code wants. pivot_table reverses it, spreading the channel values back across columns and, crucially, aggregating any duplicates it finds, taking the mean by default, which is why it is safer than plain pivot when a store-channel pair might appear more than once. Wide is for humans to read; long is for pandas to compute on, and these two functions move between them at will.

Work through these and you have the whole article in practice: concat for stacking, merge and its four join types, left_on/right_on and suffixes for mismatched and colliding columns, the indicator anti-join, validate against silent fan-out, chained merges, the self-join, the two time-aware merges, and the melt/pivot_table reshape pair. The decision the article leaves you with is the one to keep in mind: reach for concat when tables share columns and you are stacking rows, for mergewhen they share a key and you are adding columns, and for melt or pivot_table when the shape is wrong and no second table is involved at all.

Hope this helps