Merge, Join & Concat

Data you need is often spread across several tables. Orders are in one file and city details in another; January's sales are in one export and February's in the next. Combining them correctly is a core skill, and mistakes here are easy to make and hard to notice.

In this lesson you will stack tables on top of each other with pd.concat() and match rows from two tables with merge(), using the same join types you may know from SQL. You will also learn two safety checks that catch silent errors.

Stacking with concat

When two tables have the same columns, such as monthly exports from the same system, pd.concat() stacks them vertically.

import pandas as pd

sales = pd.read_csv("sales.csv").drop_duplicates()
jan = sales[sales["order_date"].str.startswith("2025-01")]
feb = sales[sales["order_date"].str.startswith("2025-02")]
print(len(jan), len(feb))

both = pd.concat([jan, feb], ignore_index=True)
print(len(both))
print(both[["order_id", "order_date"]].iloc[[0, -1]])

tagged = pd.concat({"Jan": jan, "Feb": feb}, names=["month"])
print(tagged.groupby(level="month")["order_id"].count())

86 79
165
     order_id  order_date
0    ORD10001  2025-01-01
164  ORD10165  2025-02-28
month
Feb    79
Jan    86
Name: order_id, dtype: int64

ignore_index=True gives the result a fresh 0, 1, 2 index instead of keeping the old row numbers. Passing a dictionary instead of a list adds a label to each piece, which is handy when the source of each row matters. Columns are matched by name, so the order of columns does not matter; a column present in only one table is filled with NaN in the other's rows.

Matching rows with merge

merge() combines two tables side by side by matching values in a key column, like a lookup in a spreadsheet. Here the orders table gets extra details about each city from a small reference table.

import pandas as pd

orders = pd.DataFrame({
    "order_id": ["A1", "A2", "A3", "A4", "A5"],
    "city": ["Pune", "Kochi", "Pune", "Surat", "Jaipur"],
    "amount": [1299, 649, 899, 2499, 450],
})
cities = pd.DataFrame({
    "city": ["Pune", "Kochi", "Jaipur", "Lucknow"],
    "state": ["Maharashtra", "Kerala", "Rajasthan", "Uttar Pradesh"],
    "zone": ["West", "South", "North", "North"],
})

print(orders.merge(cities, on="city"))                  # inner join (default)
print(orders.merge(cities, on="city", how="left"))      # keep every order

  order_id    city  amount        state   zone
0       A1    Pune    1299  Maharashtra   West
1       A2   Kochi     649       Kerala  South
2       A3    Pune     899  Maharashtra   West
3       A5  Jaipur     450    Rajasthan  North
  order_id    city  amount        state   zone
0       A1    Pune    1299  Maharashtra   West
1       A2   Kochi     649       Kerala  South
2       A3    Pune     899  Maharashtra   West
3       A4   Surat    2499          NaN    NaN
4       A5  Jaipur     450    Rajasthan  North

The default inner join keeps only rows whose key appears in both tables, so the Surat order disappears silently, because Surat is not in the city table. A left join keeps every row of the left table and fills the missing details with NaN. When you are adding information to a main table, how="left" is almost always what you want, because it never loses rows.

Join types

import pandas as pd

orders = pd.DataFrame({"order_id": ["A1", "A2", "A3", "A4", "A5"],
                       "city": ["Pune", "Kochi", "Pune", "Surat", "Jaipur"]})
cities = pd.DataFrame({"city": ["Pune", "Kochi", "Jaipur", "Lucknow"],
                       "zone": ["West", "South", "North", "North"]})

full = orders.merge(cities, on="city", how="outer", indicator=True)
print(full)
print(full["_merge"].value_counts())

print(orders.merge(cities, on="city", how="left_anti"))

  order_id     city   zone      _merge
0       A5   Jaipur  North        both
1       A2    Kochi  South        both
2      NaN  Lucknow  North  right_only
3       A1     Pune   West        both
4       A3     Pune   West        both
5       A4    Surat    NaN   left_only
_merge
both          4
left_only     1
right_only    1
Name: count, dtype: int64
  order_id   city zone
3       A4  Surat  NaN

indicator=True adds a _merge column saying where each row came from: both, left_only or right_only. It is the quickest way to audit a join. The outer join reveals both problems at once: an order from Surat with no city details, and Lucknow, a city with no orders. The anti join, added in pandas 3.0, returns just the unmatched left rows. In older versions you would write the outer join with indicator=True and filter _merge == "left_only".

Different key names and suffixes

Real tables rarely use the same column names. left_on and right_on name the key in each table, and suffixes decides how to rename columns that exist in both.

import pandas as pd

emp = pd.read_csv("employees.csv").drop_duplicates()
emp["department"] = emp["department"].str.strip().str.title().replace({"Engg": "Engineering", "Hr": "HR"})

budgets = pd.DataFrame({
    "dept_name": ["Engineering", "Data Science", "Sales", "Support", "HR", "Finance"],
    "salary": [9.5e7, 4.0e7, 4.5e7, 2.5e7, 1.4e7, 2.0e7],     # yearly salary budget, ₹
})

actual = emp.groupby("department", as_index=False)["salary"].sum()
check = actual.merge(budgets, left_on="department", right_on="dept_name", suffixes=("_actual", "_budget"))
check["used_pct"] = (check["salary_actual"] / check["salary_budget"] * 100).round(1)
print(check[["department", "salary_actual", "salary_budget", "used_pct"]])

     department  salary_actual  salary_budget  used_pct
0  Data Science     44761000.0     40000000.0     111.9
1   Engineering    100623000.0     95000000.0     105.9
2       Finance     22118000.0     20000000.0     110.6
3            HR     19759000.0     14000000.0     141.1
4         Sales     46216000.0     45000000.0     102.7
5       Support     27787000.0     25000000.0     111.1

Both tables had a salary column, so the suffixes turn them into salary_actual and salary_budget. Without suffixes, pandas would use salary_x and salary_y, which are easy to mix up. The result shows that every department has spent more than its budget, HR by the widest margin.

Checking a merge with validate

The most dangerous merge bug is a duplicated key in the lookup table. If a city appears twice in the city table, every order from that city is copied twice, and all your totals double without any error. The validate argument catches it.

import pandas as pd

orders = pd.DataFrame({"order_id": ["A1", "A2", "A3"], "city": ["Pune", "Kochi", "Pune"],
                       "amount": [1299, 649, 899]})
cities = pd.DataFrame({"city": ["Pune", "Kochi", "Pune"], "zone": ["West", "South", "West"]})

bad = orders.merge(cities, on="city", how="left")
print(len(orders), "orders became", len(bad), "rows; total", bad["amount"].sum())

try:
    orders.merge(cities, on="city", how="left", validate="many_to_one")
except Exception as err:
    print(type(err).__name__, ":", err)

good = orders.merge(cities.drop_duplicates("city"), on="city", how="left", validate="many_to_one")
print(len(good), "rows; total", good["amount"].sum())

3 orders became 5 rows; total 5045
MergeError : Merge keys are not unique in right dataset; not a many-to-one merge

Duplicates in right:
 city
Pune ...
3 rows; total 2847

validate="many_to_one" says "many orders can share a city, but each city must appear only once in the right table". pandas raises a MergeError when that is not true. Get into the habit of passing validate and comparing row counts before and after every merge.

Joining on the index

df.join(other) is a shortcut for merging on the index. It is convenient after a groupby, whose result already has the key as its index.

import pandas as pd

weather = pd.read_csv("weather.csv")
rain = weather.groupby("city")["rainfall_mm"].sum().round().rename("rain_mm")
heat = weather[weather["temp_max"] < 60].groupby("city")["temp_max"].max().rename("hottest")
print(rain.to_frame().join(heat))

         rain_mm  hottest
city                     
Chennai   1320.0     39.9
Delhi     1046.0     42.5
Jaipur     436.0     43.0
Kochi     2956.0     36.8
Mumbai    1766.0     37.1

Both Series are indexed by city, so join() lines them up without naming a key. to_frame() turns the first Series into a one-column DataFrame so the result is a table. The filter temp_max < 60 leaves out the two sensor errors before finding each city's hottest day.

Common mistakes

• Using the default inner join without thinking. Unmatched rows vanish silently. Use how="left" and check counts.
• Duplicate keys in a lookup table. Rows multiply and totals inflate. Use validate="many_to_one".
• Keys that look equal but are not. "Pune " with a space never matches "Pune"; numbers stored as text never match numbers. Clean and align types first.
• Appending in a loop. Collect DataFrames in a list and call pd.concat() once.

Key points

• pd.concat([a, b], ignore_index=True) stacks tables with the same columns.
• a.merge(b, on="key", how=...) matches rows; inner keeps matches only, left keeps all left rows.
• indicator=True shows where each row came from; how="left_anti" (new in pandas 3.0) finds unmatched rows.
• left_on, right_on and suffixes handle different key names and repeated columns.
• validate="many_to_one" and row-count checks catch duplicate keys before they inflate totals.

import pandas as pd

sales = pd.read_csv("sales.csv").drop_duplicates()
sales["city"] = sales["city"].str.strip().str.title().replace({"Bangalore": "Bengaluru"})
sales["revenue"] = sales["quantity"] * sales["unit_price"] * (1 - sales["discount"] / 100)

targets = pd.DataFrame({"city": ["Delhi", "Mumbai", "Pune", "Kochi"],
                        "target": [350000, 320000, 180000, 150000]})

by_city = sales.groupby("city", as_index=False)["revenue"].sum()
result = by_city.merge(targets, on="city", how="left", validate="one_to_one")
result["achieved_pct"] = (result["revenue"] / result["target"] * 100).round(1)
print(result.round(0).sort_values("achieved_pct", ascending=False))

        city   revenue    target  achieved_pct
9       Pune  217645.0  180000.0         121.0
8     Mumbai  317139.0  320000.0          99.0
2      Delhi  294885.0  350000.0          84.0
5      Kochi   94283.0  150000.0          63.0
0  Bengaluru  210063.0       NaN           NaN
1    Chennai  178588.0       NaN           NaN
3  Hyderabad  204527.0       NaN           NaN
4     Jaipur  113568.0       NaN           NaN
6    Kolkata  148900.0       NaN           NaN
7    Lucknow  127243.0       NaN           NaN