pandas Merge Examples (Run Online)

1Label each stacked piece with keys

keys= adds an outer index level that records where each row came from. names= gives that level a name so reset_index(level="region") can turn it into an ordinary column.

Label each stacked piece with keys

import pandas as pd

north = pd.DataFrame({"product": ["Coffee", "Tea"], "units": [30, 18]})
south = pd.DataFrame({"product": ["Coffee", "Cake"], "units": [22, 9]})

regions = pd.concat([north, south], keys=["North", "South"], names=["region", None])
print(regions.groupby(level="region")["units"].sum())

flat = regions.reset_index(level="region").reset_index(drop=True)
print(flat)

Expected outputregion
North    48
South    31
Name: units, dtype: int64
  region product  units
0  North  Coffee     30
1  North     Tea     18
2  South  Coffee     22
3  South    Cake      9

2Keys with different names, and a tidy result

The ticket table calls the key assignee and the staff table calls it staff_id. After a left merge the duplicate key column is dropped, and tickets nobody owns get a readable label instead of NaN.

Keys with different names, and a tidy result

import pandas as pd

staff = pd.DataFrame({"staff_id": [1, 2, 3], "staff_name": ["Mia", "Leo", "Zara"]})
tickets = pd.DataFrame({"ticket": ["T-1", "T-2", "T-3", "T-4"], "assignee": [2, 1, 2, 9]})

merged = tickets.merge(staff, left_on="assignee", right_on="staff_id", how="left")
merged = merged.drop(columns="staff_id")
merged["staff_name"] = merged["staff_name"].fillna("Unassigned")
print(merged)

Expected output  ticket  assignee  staff_name
0    T-1         2         Leo
1    T-2         1         Mia
2    T-3         2         Leo
3    T-4         9  Unassigned

3Merging on two columns at once

A store sells every month, so neither store nor month identifies a row alone. Passing both to on= matches a row only when both values agree.

Merging on two columns at once

import pandas as pd

targets = pd.DataFrame({"store": ["A", "A", "B", "B"],
                        "month": ["Jan", "Feb", "Jan", "Feb"],
                        "target": [100, 110, 90, 95]})
actuals = pd.DataFrame({"store": ["A", "B", "A", "B"],
                        "month": ["Jan", "Jan", "Feb", "Feb"],
                        "sales": [104, 85, 99, 97]})

both = targets.merge(actuals, on=["store", "month"], how="left")
both["gap"] = both["sales"] - both["target"]
print(both)

Expected output  store month  target  sales  gap
0     A   Jan     100    104    4
1     A   Feb     110     99  -11
2     B   Jan      90     85   -5
3     B   Feb      95     97    2

4Join several tables on the index at once

When every table is indexed by student, join() accepts a list. An outer join keeps students who appear in only some tables, and mean(axis=1) averages across each row while skipping NaN.

Join several tables on the index at once

import pandas as pd

maths = pd.DataFrame({"maths": [72, 85, 64]}, index=["Asha", "Ben", "Chen"])
art = pd.DataFrame({"art": [90, 60]}, index=["Asha", "Chen"])
music = pd.DataFrame({"music": [75, 88, 91]}, index=["Ben", "Chen", "Dina"])

report = maths.join([art, music], how="outer")
print(report)
print(report.mean(axis=1).round(1))

Expected output      maths   art  music
Asha   72.0  90.0    NaN
Ben    85.0   NaN   75.0
Chen   64.0  60.0   88.0
Dina    NaN   NaN   91.0
Asha    81.0
Ben     80.0
Chen    70.7
Dina    91.0
dtype: float64

5Reconcile invoices and payments with indicator

An outer merge with indicator=True shows what is on one list but not the other. Filtering on _merge finds the unpaid invoices and the payments nobody invoiced.

Reconcile invoices and payments with indicator

import pandas as pd

invoices = pd.DataFrame({"invoice": ["INV-1", "INV-2", "INV-3", "INV-4"],
                         "billed": [120.0, 80.0, 45.5, 200.0]})
payments = pd.DataFrame({"invoice": ["INV-1", "INV-3", "INV-5"],
                         "paid": [120.0, 45.5, 60.0]})

audit = invoices.merge(payments, on="invoice", how="outer", indicator=True)
print(audit)
print(audit["_merge"].value_counts().sort_index().to_dict())
print("Unpaid:", audit.loc[audit["_merge"] == "left_only", "invoice"].tolist())
print("Not invoiced:", audit.loc[audit["_merge"] == "right_only", "invoice"].tolist())

Expected output  invoice  billed   paid      _merge
0   INV-1   120.0  120.0        both
1   INV-2    80.0    NaN   left_only
2   INV-3    45.5   45.5        both
3   INV-4   200.0    NaN   left_only
4   INV-5     NaN   60.0  right_only
{'left_only': 2, 'right_only': 1, 'both': 2}
Unpaid: ['INV-2', 'INV-4']
Not invoiced: ['INV-5']

6Let validate stop a bad merge

Employee 2 was entered twice, so a plain merge quietly returns 4 rows for 3 people. With validate="one_to_one" pandas raises a MergeError instead, and after removing the duplicate the check passes.

Let validate stop a bad merge

import pandas as pd

employees = pd.DataFrame({"emp_id": [1, 2, 2, 3],
                          "name": ["Asha", "Ben", "Ben (old)", "Chen"]})
badges = pd.DataFrame({"emp_id": [1, 2, 3], "badge": ["B-10", "B-20", "B-30"]})

print("Rows without validate:", len(employees.merge(badges, on="emp_id")))
try:
    employees.merge(badges, on="emp_id", validate="one_to_one")
except pd.errors.MergeError as err:
    print("Stopped:", str(err).splitlines()[0])

clean = employees.drop_duplicates(subset="emp_id", keep="first")
print("Rows after cleaning:", len(clean.merge(badges, on="emp_id", validate="one_to_one")))

Expected outputRows without validate: 4
Stopped: Merge keys are not unique in left dataset; not a one-to-one merge
Rows after cleaning: 3

Practise next

pandas Merge, Join and Concat exercises → Back to the lesson

Python Dictionaries examples Python CSV Files examples pandas Data Cleaning examples pandas groupby and Pivot Tables examples