Combining DataFrames — stacking with concat, matching with merge

The problem we are solving

The shop's data does not live in one file. It never does.

The March orders are in orders.csv, the table you have used since chapter four. April's orders arrived this week as a separate export, orders_april.csv. What each product costs the shop — and which supplier sells it — is kept by the purchasing team in their own file, products.csv. Nobody put cost into the orders file, because cost is a fact about a product, not about an order.

Now the owner asks one question: how much profit did each supplier bring in over March and April?

No single file can answer that. Profit needs the quantity and selling price (in the orders), the cost (in the products file), and the supplier (also in the products file). And the orders are split across two months.

In a spreadsheet you would copy April's rows under March's, then write a VLOOKUP in a new column to pull the cost for each row, drag it down, and hope. Pandas does the same two jobs in two lines:

• stack tables that hold the same kind of rows — March and April — into one longer table: pd.concat
• match each row of one table to the right row of another by a shared column — each order to its product — and bring the other table's columns across: pd.merge

The lines are short. What makes this chapter matter is what can go wrong without any error: a merge can silently drop orders whose product is missing from the lookup table, or silently duplicate orders when the lookup table lists a product twice. Both give a total that looks perfectly reasonable and is wrong. So most of this chapter is about knowing, before and after the merge, exactly how many rows you should have — and checking.

By the end of this chapter you can

• Tell the two operations apart: stacking rows (concat) and matching on a key (merge)
• Stack tables with pd.concat, fix the repeated index with ignore_index=True, and see what mismatched columns do
• Join two tables on a key with pd.merge, and choose how="inner", "left", "right" or "outer" on purpose
• Audit a merge with indicator=True and protect it with validate="many_to_one"
• Spot and fix row explosion from duplicate keys
• Merge on differently named keys with left_on/right_on, and handle clashing column names with suffixes
• Recognise the merge errors — mismatched key types, missing key columns — and the silent failures that raise nothing

Prerequisites: Adding columns.

Make the files first

This chapter uses four small files. Put them next to your script.

orders.csv — March, the same file as in the previous chapters:

order_id,date,branch,product,category,quantity,price
1001,2024-03-01,north,pen,stationery,12,15.0
1002,2024-03-01,south,notebook,stationery,5,60.0
1003,2024-03-02,north,bag,accessories,2,850.0
1004,2024-03-02,east,bottle,accessories,7,120.0
1005,2024-03-03,north,eraser,stationery,30,8.0
1006,2024-03-03,south,bag,accessories,1,850.0
1007,2024-03-04,east,pen,stationery,20,15.0
1008,2024-03-04,north,bottle,accessories,4,120.0

orders_april.csv — April, same columns. Note the stapler: the shop started selling it in April.

order_id,date,branch,product,category,quantity,price
1009,2024-04-01,north,pen,stationery,15,15.0
1010,2024-04-01,east,notebook,stationery,3,60.0
1011,2024-04-02,south,stapler,stationery,6,95.0
1012,2024-04-02,north,bag,accessories,1,850.0

products.csv — one row per product, kept by purchasing. Two things are deliberately "wrong" here, as they would be in real life: the new stapler has not been added yet, and there is a marker the shop has never sold.

product,cost,supplier
pen,9.0,Alpha
notebook,40.0,Alpha
bag,600.0,Bravo
bottle,80.0,Bravo
eraser,5.0,Alpha
marker,25.0,Alpha

branches.csv — who manages each of the shop's three branches. The column is called branch_name, not branch, because a different person made this file.

branch_name,manager
north,Mira
south,Omar
east,Lena

Before you write the code

Combining tables is the step where a quick line of code most often produces a confident wrong answer. So the planning comes first, and it is mostly questions you answer by looking.

1. Say the question in one sentence

"Profit per supplier, March and April together." That sentence already tells you the final table: one row per supplier, one number per row. Two suppliers exist, Alpha and Bravo, so the answer should have about two rows. If it ends up with three, or with one, something went wrong on the way.

2. Decide which operation each pair of tables needs

There are only two kinds of combining, and the test is simple: do the two tables hold the same kind of row, or different facts about the same thing?

• Same columns, different rows — March orders + April orders. Use pd.concat([a, b]). The result grows downwards: more rows, the same columns.
• A shared key column, different facts — orders + products, linked by product. Use pd.merge(a, b, on="product"). The result grows sideways: the same rows, more columns.

March and April are both "orders" — same columns, more rows — so they are stacked. Orders and products are different things linked by the product name, so they are matched.

3. Look at every table before combining it

You learned in chapter three to check a file as soon as it is read. With several files that habit is not optional: you need the shape of each one to predict the shape of the result.

import pandas as pd

march = pd.read_csv("orders.csv")
april = pd.read_csv("orders_april.csv")
products = pd.read_csv("products.csv")

for name, df in [("march", march), ("april", april), ("products", products)]:
    print(name, df.shape, list(df.columns))

march (8, 7) ['order_id', 'date', 'branch', 'product', 'category', 'quantity', 'price']
april (4, 7) ['order_id', 'date', 'branch', 'product', 'category', 'quantity', 'price']
products (6, 3) ['product', 'cost', 'supplier']

March and April have exactly the same seven columns, in the same order — good for stacking. products shares exactly one column name with them: product. That is the key, the column the match will be made on.

Check that the key has the same type on both sides. A key that is a number in one table and text in the other cannot match:

print(march["product"].dtype, products["product"].dtype)

str str

Both str. They can be compared.

4. Check the key on the lookup side is unique

This is the single most important check in the chapter. Each order needs one cost. If products.csv lists pen twice, every pen order will match twice and appear twice in the result.

print(products["product"].is_unique)
print(products["product"].duplicated().sum())

True
0

is_unique is True and there are zero duplicates: one row per product. Each order can match at most one product row.

5. Check which keys will not find a partner

Before merging, ask: are there orders whose product is missing from products.csv, and products nobody ordered? isin (from the filtering chapter) answers both:

orders = pd.concat([march, april], ignore_index=True)

no_cost = ~orders["product"].isin(products["product"])
print(orders.loc[no_cost, ["order_id", "product"]])

never_sold = ~products["product"].isin(orders["product"])
print(products.loc[never_sold, "product"].tolist())

order_id  product
10      1011  stapler
['marker']

One order — the stapler, 1011 — has no cost on file. One product, the marker, was never sold. Now you know before merging what the merge will have to deal with.

6. Predict the result, then decide

Write the prediction down, as numbers:

• orders has 8 + 4 = 12 rows.
• products has one row per product, so matching cannot multiply orders.
• If every order is kept, the merged table has 12 rows, and one of them (the stapler) has no cost.
• If only matched orders are kept, it has 11.

Which do you want? For a profit report, silently dropping the stapler order would understate sales. Better to keep it, see that its cost is missing, and decide what to do — which is exactly the choice the how= argument makes. After the merge, the very first thing you print is the shape, and you compare it with this prediction.

That is the whole plan: one-sentence question, operation per pair, look at each table, uniqueness of the key, unmatched keys, predicted row count. The rest of the chapter is the tools that carry it out.

Stacking rows with pd.concat

pd.concat takes a list of tables and puts them one under another:

import pandas as pd

march = pd.read_csv("orders.csv")
april = pd.read_csv("orders_april.csv")

both = pd.concat([march, april])
print(both.shape)
print(both[["order_id", "date", "product", "quantity"]])

(12, 7)
   order_id        date   product  quantity
0      1001  2024-03-01       pen        12
1      1002  2024-03-01  notebook         5
2      1003  2024-03-02       bag         2
3      1004  2024-03-02    bottle         7
4      1005  2024-03-03    eraser        30
5      1006  2024-03-03       bag         1
6      1007  2024-03-04       pen        20
7      1008  2024-03-04    bottle         4
0      1009  2024-04-01       pen        15
1      1010  2024-04-01  notebook         3
2      1011  2024-04-02   stapler         6
3      1012  2024-04-02       bag         1

Twelve rows, the same seven columns. Pandas lined the columns up by name, not by position — had April's file listed the columns in a different order, they would still land under the right headings.

But look down the left edge. The index runs 0 to 7, then starts again at 0. concat kept each table's own index, so the labels 0 to 3 now appear twice. That is not a cosmetic problem:

print(both.loc[0, ["order_id", "date"]])

order_id        date
0      1001  2024-03-01
0      1009  2024-04-01

You asked for row 0 and got two rows. Any code that assumes one label means one row — loc, a later merge on the index, updating a single cell — now does something you did not intend. You can check for it:

print(both.index.is_unique)

False

ignore_index=True — number the rows again

When the old index was just a row count (a RangeIndex, as with every read_csv so far), it carries no meaning, so throw it away and let concat number the result from zero:

orders = pd.concat([march, april], ignore_index=True)
print(orders.index.is_unique)
print(orders[["order_id", "date", "product"]].tail(5))

True
    order_id        date   product
7       1008  2024-03-04    bottle
8       1009  2024-04-01       pen
9       1010  2024-04-01  notebook
10      1011  2024-04-02   stapler
11      1012  2024-04-02       bag

Now the labels run 0 to 11, each exactly once. Make ignore_index=True your default when stacking files; leave it off only when the index means something (for example, after set_index("order_id"), where the order ids are already unique).

Keep track of where each row came from

After stacking, nothing in the table says which month a row belongs to. Here the date tells you, but often nothing does. Add a column before concatenating — the technique from the previous chapter — and the information travels with the rows:

march["month"] = "March"
april["month"] = "April"
orders = pd.concat([march, april], ignore_index=True)

print(orders["month"].value_counts())

month
March    8
April    4
Name: count, dtype: int64

The counts double as a check: 8 + 4 = 12, nothing lost.

When the columns do not match

Concat lines up columns by name. A column that exists in only some of the tables still appears in the result — and the rows from tables that did not have it get NaN there. Suppose a one-off order was typed by hand, with a discount column and no date, product, category or price:

manual = pd.DataFrame({
    "order_id": [2001],
    "branch": ["north"],
    "quantity": [3],
    "discount": [0.1],
})

mixed = pd.concat([march.head(2), manual], ignore_index=True)
print(mixed[["order_id", "branch", "price", "discount"]])
print(mixed.isna().sum())

order_id branch  price  discount
0      1001  north   15.0       NaN
1      1002  south   60.0       NaN
2      2001  north    NaN       0.1
order_id    0
date        1
branch      0
product     1
category    1
quantity    0
price       1
month       1
discount    2
dtype: int64

No error. The result has the union of all columns, and the gaps are filled with NaN. That is why stacking deserves the same isna().sum() you learned in the missing-data chapter: a column name spelled differently in one file (Quantity against quantity) does not fail, it produces two half-empty columns. If the number of columns after a concat is larger than in any input, something did not line up.

axis=1 — and why you usually do not want it

pd.concat([a, b], axis=1) puts tables side by side instead, matching rows by index label. It is tempting for "add the product columns to the orders", but it does not look at the product name at all — row 0 of one table goes next to row 0 of the other, whatever they contain. For matching on a column, use merge.

Matching rows with pd.merge

merge takes two tables and a key. For every row of the left table, it finds the rows of the right table with the same key value, and glues their columns together:

products = pd.read_csv("products.csv")

merged = pd.merge(orders, products, on="product")
print(merged.shape)
print(merged[["order_id", "product", "quantity", "price", "cost", "supplier"]])

(11, 10)
    order_id   product  quantity  price   cost supplier
0       1001       pen        12   15.0    9.0    Alpha
1       1002  notebook         5   60.0   40.0    Alpha
2       1003       bag         2  850.0  600.0    Bravo
3       1004    bottle         7  120.0   80.0    Bravo
4       1005    eraser        30    8.0    5.0    Alpha
5       1006       bag         1  850.0  600.0    Bravo
6       1007       pen        20   15.0    9.0    Alpha
7       1008    bottle         4  120.0   80.0    Bravo
8       1009       pen        15   15.0    9.0    Alpha
9       1010  notebook         3   60.0   40.0    Alpha
10      1012       bag         1  850.0  600.0    Bravo

Every order now has its cost and supplier beside it, without a loop and without the order of either file mattering. The key column product appears once, because it is the same on both sides.

Now compare the shape with the plan. We predicted 12 rows if everything was kept — and we have 11. The stapler order, 1011, is gone. No warning, no error.

That is not a bug. It is the default kind of merge, how="inner", doing what it is defined to do: keep only the keys found in both tables. The stapler is not in products, so its order does not survive; the marker is not in orders, so it does not appear either. Had you not predicted 12, you would never have noticed the missing sale.

The four kinds of merge

The how= argument decides what happens to rows whose key has no partner on the other side. Picture the two sets of keys:

• how="inner" (the default) keeps only keys found in both tables. Unmatched rows on either side are dropped. Use it when you only want complete records.
• how="left" keeps every row of the left table. Left rows with no partner get NaN in the right table's columns; unmatched right rows are dropped. Use it to enrich a main table — the most common choice.
• how="right" is the mirror image: every row of the right table is kept, unmatched left rows are dropped.
• how="outer" keeps every key from either side, filling the gaps with NaN. Use it to audit two lists against each other.

The question to ask is: which table is the one whose rows must not disappear? Here, the orders. Every order is a real sale; the products file is a lookup. So the right merge is a left merge, with orders on the left:

merged = pd.merge(orders, products, on="product", how="left")
print(merged.shape)
print(merged[["order_id", "product", "cost", "supplier"]])

(12, 10)
    order_id   product   cost supplier
0       1001       pen    9.0    Alpha
1       1002  notebook   40.0    Alpha
2       1003       bag  600.0    Bravo
3       1004    bottle   80.0    Bravo
4       1005    eraser    5.0    Alpha
5       1006       bag  600.0    Bravo
6       1007       pen    9.0    Alpha
7       1008    bottle   80.0    Bravo
8       1009       pen    9.0    Alpha
9       1010  notebook   40.0    Alpha
10      1011   stapler    NaN      NaN
11      1012       bag  600.0    Bravo

Twelve rows, as predicted. The stapler order is still there, with NaN for cost and supplier — which is honest: the cost is unknown, and you can see that it is. A missing value you can see is far better than a missing row you cannot. From here, the missing-data chapter's tools apply: you can count the gaps with isna().sum(), fill the cost if purchasing tells you it, or report the order separately.

A right merge is the mirror image — every product kept, the marker with no order:

right = pd.merge(orders, products, on="product", how="right")
print(right.shape)
print(right[["order_id", "product", "cost"]].tail(3))

(12, 10)
    order_id product  cost
9     1008.0  bottle  80.0
10    1005.0  eraser   5.0
11       NaN  marker  25.0

Notice order_id changed from whole numbers to 1008.0, 1005.0 and so on. The marker row has no order id, so the column now holds a NaN — and, as you saw in the missing-data chapter, a whole-number column that receives a NaN becomes float64. If ids suddenly grow a .0 after a merge, that is the reason: some rows found no partner.

indicator=True — see where every row came from

An outer merge keeps everything from both sides. Add indicator=True and pandas adds a column, _merge, that says for each row whether its key was found in both tables, only the left, or only the right:

audit = pd.merge(orders, products, on="product", how="outer", indicator=True)
print(audit[["order_id", "product", "cost", "_merge"]])
print(audit["_merge"].value_counts())

order_id   product   cost      _merge
0     1003.0       bag  600.0        both
1     1006.0       bag  600.0        both
2     1012.0       bag  600.0        both
3     1004.0    bottle   80.0        both
4     1008.0    bottle   80.0        both
5     1005.0    eraser    5.0        both
6        NaN    marker   25.0  right_only
7     1002.0  notebook   40.0        both
8     1010.0  notebook   40.0        both
9     1001.0       pen    9.0        both
10    1007.0       pen    9.0        both
11    1009.0       pen    9.0        both
12    1011.0   stapler    NaN   left_only
_merge
both          11
left_only      1
right_only     1
Name: count, dtype: int64

This is the merge's own report card. Eleven rows matched; one order has no product row (left_only: the stapler); one product has no order (right_only: the marker). It is the same information as step 5 of the plan, produced by the merge itself.

The outer merge also sorted the rows by the key — bag, bottle, eraser, … — rather than keeping the order of the orders. Inner and left merges keep the left table's order; outer does not promise it. If order matters, sort afterwards, as in chapter seven.

The _merge column is ordinary data, so you can filter on it. Listing only the problems is one line:

problems = audit[audit["_merge"] != "both"]
print(problems[["order_id", "product", "_merge"]])

order_id  product      _merge
6        NaN   marker  right_only
12    1011.0  stapler   left_only

Run this kind of audit whenever you merge data you did not create. When the problem list is empty, you have proof the two files agree.

The silent danger: duplicate keys

So far products had one row per product. Suppose purchasing adds a second pen supplier at a slightly higher cost and simply appends a row, so pen appears twice:

products_dup = pd.concat(
    [products, pd.DataFrame({"product": ["pen"], "cost": [10.0], "supplier": ["Gamma"]})],
    ignore_index=True,
)
print(products_dup["product"].is_unique)

exploded = pd.merge(march, products_dup, on="product", how="left")
print(march.shape, "->", exploded.shape)
print(exploded[["order_id", "product", "quantity", "cost", "supplier"]])

False
(8, 8) -> (10, 10)
   order_id   product  quantity   cost supplier
0      1001       pen        12    9.0    Alpha
1      1001       pen        12   10.0    Gamma
2      1002  notebook         5   40.0    Alpha
3      1003       bag         2  600.0    Bravo
4      1004    bottle         7   80.0    Bravo
5      1005    eraser        30    5.0    Alpha
6      1006       bag         1  600.0    Bravo
7      1007       pen        20    9.0    Alpha
8      1007       pen        20   10.0    Gamma
9      1008    bottle         4   80.0    Bravo

March had 8 orders; the merge returned 10 rows. Orders 1001 and 1007 — the two pen orders — each appear twice, once per pen row. Nothing failed. But sum the quantity now and the pens are counted twice: 32 extra pens that were never sold.

print(march["quantity"].sum(), exploded["quantity"].sum())

81 113

81 against 113. This is called row explosion, and it is the most expensive merge mistake because the result looks normal. The rule behind it: merge pairs up every matching left row with every matching right row. One-to-one or many-to-one keeps the row count; a key repeated on both sides multiplies it.

A merge that adds rows never says so. The only symptom is a total that is too large, and a total that is too large rarely looks wrong. Compare len() before and after every merge — it is the one check that catches this.

validate= — make pandas check for you

You can state the relationship you expect, and pandas will refuse to merge if the data breaks it. Many orders share one product, so this merge should be many-to-one:

try:
    pd.merge(march, products_dup, on="product", how="left", validate="many_to_one")
except Exception as error:
    print(type(error).__name__ + ":", error)

MergeError: Merge keys are not unique in right dataset; not a many-to-one merge

Duplicates in right:
 product
    pen ...

A MergeError, and it even lists the offending key. The other values are "one_to_one", "one_to_many" and "many_to_many". Writing validate= costs nothing and turns a silent wrong answer into a loud error, so put it on every merge with a lookup table.

Fixing it: decide which duplicate is right

The error tells you that something is wrong; deciding what is right is your job. Look at the duplicates first:

print(products_dup[products_dup["product"].duplicated(keep=False)])

product  cost supplier
0     pen   9.0    Alpha
6     pen  10.0    Gamma

duplicated(keep=False) marks every copy, not just the second. Now there is a business decision to make. If the newest row replaces the old one, keep the last copy:

products_clean = products_dup.drop_duplicates(subset="product", keep="last")
fixed = pd.merge(march, products_clean, on="product", how="left", validate="many_to_one")
print(fixed.shape)
print(fixed.loc[fixed["product"] == "pen", ["order_id", "cost", "supplier"]])

(8, 10)
   order_id  cost supplier
0      1001  10.0    Gamma
6      1007  10.0    Gamma

Back to 8 rows, one per order. If instead both suppliers really sell pens and an order records which one, then supplier belongs in the orders too, and the merge key becomes two columns: on=["product", "supplier"]. The code cannot make that choice; the planning can.

When the key columns have different names

branches.csv calls its column branch_name, while the orders call it branch. on= needs the same name on both sides, so name each side separately with left_on= and right_on=:

branches = pd.read_csv("branches.csv")

with_manager = pd.merge(orders, branches, left_on="branch", right_on="branch_name", how="left")
print(list(with_manager.columns))

['order_id', 'date', 'branch', 'product', 'category', 'quantity', 'price', 'month', 'branch_name', 'manager']

Both key columns are kept, because pandas cannot know you consider them one column. branch and branch_name now hold identical values, so drop one:

with_manager = with_manager.drop(columns="branch_name")
print(with_manager[["order_id", "branch", "manager"]].head(4))

order_id branch manager
0      1001  north    Mira
1      1002  south    Omar
2      1003  north    Mira
3      1004   east    Lena

The alternative is to rename before merging — branches.rename(columns={"branch_name": "branch"}) — and then use plain on="branch". Both are fine; renaming keeps the result tidy without a clean-up step.

When both tables have a column with the same name

If a non-key column exists in both tables, pandas cannot put two columns called price in one table, so it renames them with the suffixes _x (left) and _y (right). Imagine a list-price table that also calls its column price:

list_prices = pd.DataFrame({"product": ["pen", "bag"], "price": [14.0, 800.0]})

compared = pd.merge(march, list_prices, on="product")
print(compared[["order_id", "product", "price_x", "price_y"]])

order_id product  price_x  price_y
0      1001     pen     15.0     14.0
1      1003     bag    850.0    800.0
2      1006     bag    850.0    800.0
3      1007     pen     15.0     14.0

price_x and price_y work, but in three weeks nobody will remember which is which. Name them yourself with suffixes=:

compared = pd.merge(march, list_prices, on="product", suffixes=("_sold", "_list"))
print(compared[["order_id", "price_sold", "price_list"]])

order_id  price_sold  price_list
0      1001        15.0        14.0
1      1003       850.0       800.0
2      1006       850.0       800.0
3      1007        15.0        14.0

Now the columns say what they mean, and the discount given on each order is just price_list - price_sold. (Code that later asks for compared["price"] would get a KeyError, because after the merge no column has that exact name — another reason to choose the names deliberately.)

join — merge by index, briefly

DataFrames also have a .join() method. It is a merge that uses the index of the right table as its key — handy when the lookup table is already indexed by the key:

by_product = products.set_index("product")

joined = orders.join(by_product, on="product")
print(joined[["order_id", "product", "cost", "supplier"]].head(3))

order_id   product   cost supplier
0      1001       pen    9.0    Alpha
1      1002  notebook   40.0    Alpha
2      1003       bag  600.0    Bravo

join defaults to how="left". It does the same job as pd.merge(..., how="left"); merge is the more general tool, and the one used in the rest of this course.

A complete example

Back to the owner's question: profit per supplier over March and April, with every order accounted for.

supplier_profit.py:

import pandas as pd

march = pd.read_csv("orders.csv")
april = pd.read_csv("orders_april.csv")
products = pd.read_csv("products.csv")

# 1. Stack the two months, remembering where each row came from.
march["month"] = "March"
april["month"] = "April"
orders = pd.concat([march, april], ignore_index=True)
print("orders:", orders.shape)

# 2. The lookup key must be unique, or the merge will duplicate orders.
assert products["product"].is_unique

# 3. Attach cost and supplier to every order. Keep every order.
sales = pd.merge(
    orders, products, on="product", how="left",
    validate="many_to_one", indicator=True,
)
print("after merge:", sales.shape)
assert len(sales) == len(orders)

# 4. Report the orders that found no product row, then set them aside.
unmatched = sales[sales["_merge"] == "left_only"]
print("no cost on file:", unmatched["order_id"].tolist(), unmatched["product"].tolist())
sales = sales[sales["_merge"] == "both"].drop(columns="_merge")

# 5. Profit per row, then per supplier and month.
sales["profit"] = sales["quantity"] * (sales["price"] - sales["cost"])
report = (
    sales.groupby(["supplier", "month"], as_index=False)["profit"]
    .sum()
    .sort_values(["supplier", "profit"], ascending=[True, False])
    .reset_index(drop=True)
)
print()
print(report)
print()
print(sales.groupby("supplier")["profit"].sum().sort_values(ascending=False))

orders: (12, 8)
after merge: (12, 11)
no cost on file: [1011] ['stapler']

  supplier  month  profit
0    Alpha  March   382.0
1    Alpha  April   150.0
2    Bravo  March  1190.0
3    Bravo  April   250.0

supplier
Bravo    1440.0
Alpha     532.0
Name: profit, dtype: float64

Why it is written this way:

• Every combining step is followed by a shape. orders: (12, 8) confirms the stack (8 + 4 rows, 7 columns + month). after merge: (12, 11) confirms nothing was dropped or duplicated — 3 new columns: cost, supplier, _merge. The shapes are the cheapest proof that each step did what the plan said.
• The assumptions are written as code. assert products["product"].is_unique and validate="many_to_one" both guard against row explosion; assert len(sales) == len(orders) guards the prediction from the plan. If someone sends next month's products file with a duplicate, the script stops instead of printing an inflated profit.
• how="left" keeps the stapler order, and indicator=True names it. The script does not quietly drop it; it prints [1011] ['stapler'], so the owner knows the April total is missing one sale until purchasing adds a cost. That line is part of the answer, not debug output.
• The month column was added before concat. Otherwise the per-month split in the report would need the dates parsed and converted — possible, but more work for the same result.
• Profit is computed after the merge, because it needs columns from both files. This is the natural order: combine, then add columns (chapter nine), then group (chapter eight), then sort (chapter seven).

The answer: Bravo brought in 1,440 of profit and Alpha 532, with one April sale still waiting for its cost. Bravo sells few items, but bags and bottles carry a far larger margin per item than pens and erasers.

When it breaks

ValueError: You are trying to merge on int64 and str columns for key 'order_id'. If you wish to proceed you should use pd.concat

The key has a different type on each side. Here is a delivery file whose order ids were exported with a # in front. Save it as deliveries.csv — it is also used in "Your turn". (If you still have the riders' log of the same name from the missing-data chapter, overwrite it; this is a different file.)

order_id,status
#1001,delivered
#1002,delivered
#1003,returned
#1005,delivered
#1006,pending

import pandas as pd

march = pd.read_csv("orders.csv")
deliveries = pd.read_csv("deliveries.csv")
print(march["order_id"].dtype, deliveries["order_id"].dtype)

try:
    pd.merge(march, deliveries, on="order_id", how="left")
except ValueError as error:
    print("ValueError:", error)

int64 str
ValueError: You are trying to merge on int64 and str columns for key 'order_id'. If you wish to proceed you should use pd.concat

1001 and "#1001" are different values, and pandas refuses to guess. (The hint about pd.concat is for a different situation; ignore it.) The fix is to make the keys the same type and the same text — remove the #, then convert:

deliveries["order_id"] = deliveries["order_id"].str.removeprefix("#").astype("int64")
status = pd.merge(march, deliveries, on="order_id", how="left")
print(status[["order_id", "product", "status"]])

order_id   product     status
0      1001       pen  delivered
1      1002  notebook  delivered
2      1003       bag   returned
3      1004    bottle        NaN
4      1005    eraser  delivered
5      1006       bag    pending
6      1007       pen        NaN
7      1008    bottle        NaN

The orders with no delivery record get NaN for status — the left merge keeps them, which is exactly what you want when the question is "which orders have not been delivered?"

MergeError: Merge keys are not unique in right dataset; not a many-to-one merge You used validate="many_to_one" and the right table repeats a key. The message lists the duplicates. Do not remove validate to make the error go away — look at the duplicate rows with duplicated(keep=False) and decide which one is correct, as in the row-explosion section.

KeyError: 'branch' The column named in on= is missing from one of the tables:

branches = pd.read_csv("branches.csv")

try:
    pd.merge(march, branches, on="branch")
except KeyError as error:
    print("KeyError:", error)

KeyError: 'branch'

branches calls it branch_name. Print list(df.columns) for both tables; use left_on="branch", right_on="branch_name", or rename one side. A trailing space in a column name ("branch ") causes the same error and is only visible in the list form.

The merge ran, but the new columns are all NaN No error, and every row unmatched. The keys look equal but are not: "north " with a trailing space, "South" with a capital letter, or the same id as text in one file and with a prefix in the other. Check with isin before merging:

branches_messy = pd.DataFrame({"branch_name": ["north ", "South", "east"], "manager": ["Mira", "Omar", "Lena"]})

print(march["branch"].isin(branches_messy["branch_name"]).sum(), "of", len(march), "orders match")

branches_messy["branch_name"] = branches_messy["branch_name"].str.strip().str.lower()
print(march["branch"].isin(branches_messy["branch_name"]).sum(), "of", len(march), "orders match")

2 of 8 orders match
8 of 8 orders match

Two of eight before cleaning, eight of eight after. Clean the key the same way on both sides — str.strip() and one consistent case — before you merge.

The row count went up after a merge The right table has a duplicate key; see "The silent danger" above. Compare len() before and after every merge, and use validate=.

Rows disappeared after a merge You used the default how="inner", and some keys had no partner. Use how="left" with the main table on the left, and indicator=True to see which rows were unmatched.

TypeError: concat() takes 1 positional argument but 2 were given

try:
    pd.concat(march, deliveries)
except TypeError as error:
    print("TypeError:", error)

TypeError: concat() takes 1 positional argument but 2 were given

concat takes one argument, a list of tables. Write pd.concat([march, april]) — the square brackets are the point.

A duplicated index after concat, and loc returns two rows Each input kept its own 0, 1, 2, …. Add ignore_index=True.

Your turn

The delivery team sent deliveries.csv (shown in "When it breaks") — one row per order they have a record for, with order ids written as #1001. The owner wants a delivery report covering March and April together.

Write delivery_report.py so that:

1. Every order of both months is kept — exactly one row per order, checked with an assert on the row count, and the merge protected with validate= and audited with indicator=True.
2. The keys really match — the delivery key is cleaned to the same type as order_id, and the script stops if any order appears twice in the delivery file.
3. Missing means "no record" — orders without a delivery row get the status "no record", never a silent NaN.
4. It prints, in this order: the stacked shape, the merged shape, the _merge counts, the number of orders per status, the orders with no record (order_id, branch, product, sorted by order_id), and the number of delivered orders per branch.

When it is right, the output is exactly:

orders: (12, 7)
after merge: (12, 9)
_merge
left_only     7
both          5
right_only    0
Name: count, dtype: int64

status
no record    7
delivered    3
returned     1
pending      1
Name: count, dtype: int64

    order_id branch   product
3       1004   east    bottle
6       1007   east       pen
7       1008  north    bottle
8       1009  north       pen
9       1010   east  notebook
10      1011  south   stapler
11      1012  north       bag

branch
north    2
south    1
dtype: int64

Before writing any code, explain each number in that output from the files alone: why 12 rows after stacking, why still 12 after the merge, and why 7 orders with no record. If you cannot, the plan is not finished yet.

Then break it on purpose, twice:

• Remove .str.removeprefix("#"). What error do you get, and which line raises it?
• Add a second row for #1003 to deliveries.csv (say, #1003,delivered). What does validate= do now? What would the report have said without it?

Solution

The plan. The question: "for every order in March and April, what is its delivery status?" — so the result has one row per order, 12 rows. March and April have identical columns: stack them with concat and ignore_index=True (8 + 4 = 12). The delivery file is keyed by order id, but as text with a #, so the key needs cleaning before it can match the integer order_id. Each order should appear at most once in it, so the relationship is one-to-one (each order has at most one delivery row, each delivery row one order). Every order must survive, so the orders go on the left and the merge is how="left". Five delivery rows exist, all for March, so 12 − 5 = 7 orders should end up with no record.

delivery_report.py:

import pandas as pd

march = pd.read_csv("orders.csv")
april = pd.read_csv("orders_april.csv")
deliveries = pd.read_csv("deliveries.csv")

# 1. Stack the months.
orders = pd.concat([march, april], ignore_index=True)
print("orders:", orders.shape)

# 2. Clean the key, then check it.
deliveries["order_id"] = deliveries["order_id"].str.removeprefix("#").astype("int64")
assert deliveries["order_id"].is_unique, "an order appears twice in deliveries.csv"

# 3. Keep every order.
report = pd.merge(
    orders, deliveries, on="order_id", how="left",
    validate="one_to_one", indicator=True,
)
print("after merge:", report.shape)
assert len(report) == len(orders)
print(report["_merge"].value_counts())

# 4. Missing status means the delivery team has no record.
report["status"] = report["status"].fillna("no record")

# 5. Orders per status.
print()
print(report["status"].value_counts())

# 6. Orders with no record.
missing = report.loc[report["status"] == "no record", ["order_id", "branch", "product"]]
print()
print(missing.sort_values("order_id"))

# 7. Delivered orders per branch.
delivered = report[report["status"] == "delivered"]
print()
print(delivered.groupby("branch").size())

orders: (12, 7)
after merge: (12, 9)
_merge
left_only     7
both          5
right_only    0
Name: count, dtype: int64

status
no record    7
delivered    3
returned     1
pending      1
Name: count, dtype: int64

    order_id branch   product
3       1004   east    bottle
6       1007   east       pen
7       1008  north    bottle
8       1009  north       pen
9       1010   east  notebook
10      1011  south   stapler
11      1012  north       bag

branch
north    2
south    1
dtype: int64

Every prediction holds: 12 rows after stacking, 12 after the merge, 7 with no record. The _merge counts confirm it independently — 5 both, 7 left_only, 0 right_only (a left merge never produces right_only).

Notes on the choices:

• validate="one_to_one", not "many_to_one". The orders table also has unique order ids, so both sides are unique. The stricter check would also catch a duplicated order id in the orders — for example, if April's file accidentally repeated a March order.
• The assert on is_unique comes before the merge, with a message, so the failure says what is wrong in plain words rather than in merge terms.
• fillna("no record") is applied after the merge, never before: the missing statuses only exist once the orders without a delivery row have been kept by the left merge.
• The east branch has no delivered orders, so it does not appear in the last table at all. Grouping only produces groups that exist in the data — if the owner needs a 0 for east, that is a merge of the branch list with this result, how="left", and a fillna(0).

The two breakages. Without removeprefix, the astype("int64") line fails first with ValueError: invalid literal for int() with base 10: '#1001', because "#1001" cannot be turned into a number — the error points at the cleaning line, which is where the fix belongs. If you also drop the astype, the merge itself raises ValueError: You are trying to merge on int64 and str columns for key 'order_id'. With a second #1003 row, the assert stops the script with AssertionError: an order appears twice in deliveries.csv; take the assert out and validate="one_to_one" raises MergeError: Merge keys are not unique in right dataset; not a one-to-one merge. Without either guard, the report would have shown 13 rows for 12 orders and counted order 1003 twice — once returned, once delivered — and every total below it would have been quietly wrong.