Lesson 4 of 8 · 7 min · Working with tables in pandas

Merging, joining and concatenating tables

Covers: pandas 3.x. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

What you will learn

  • Combine two tables with merge
  • Choose inner, left, right and outer joins
  • Merge on columns with different names or on several columns
  • Check that a merge did not change the row count unexpectedly
  • Stack tables with concat

In simple words

Your orders table knows only a customer number, and your customers table knows names and cities. To report "sales by city" you must attach each order's customer details. merge does that: it matches rows from two tables on a shared column, exactly like a SQL join or an Excel XLOOKUP for a whole column at once.

Words to know

  • merge: combining two DataFrames by matching the values of key columns.
  • Key: the column(s) used to match rows, such as customer_id.
  • Inner join: keep only rows that match in both tables.
  • Left join: keep every row of the left table, with missing values where nothing matches.

The two tables

customers = pd.DataFrame({
    "customer_id": [1, 2, 3, 4, 5],
    "name":        ["Asha", "Ravi", "Meena", "Kiran", "Divya"],
    "city":        ["Hyderabad", "Pune", "Chennai", "Hyderabad", None],
})

with the orders table from earlier lessons (six orders for customers 1, 2 and 3).

merge

joined = orders.merge(customers, on="customer_id", how="left")

The result has all six orders, each with name and city added. The how argument chooses the join:

how Keeps Like SQL
"inner" (default) rows with a match in both INNER JOIN
"left" all rows of the left table LEFT JOIN
"right" all rows of the right table RIGHT JOIN
"outer" all rows of both FULL OUTER JOIN
"cross" every pairing CROSS JOIN

For customers with no orders:

all_customers = customers.merge(orders, on="customer_id", how="left")
all_customers[all_customers["order_id"].isna()]      # Kiran and Divya

That gives the same "no match" list as the SQL left join with IS NULL.

Keys with different names, or several keys

orders.merge(customers, left_on="cust_id", right_on="customer_id")   # different names
a.merge(b, on=["region", "month"])                                    # several keys
a.merge(b, left_index=True, right_index=True)                         # join on the index

When both tables have a column with the same name that is not a key, pandas adds suffixes _x and _y. Choose clearer ones with suffixes=("_order", "_customer"), or drop or rename columns before merging.

Check what happened

Merges are a classic source of silent errors. Always check the row count and the matches:

len(orders), len(joined)          # 6, 6 : unchanged for a left join on a unique key
joined["name"].isna().sum()       # how many orders found no customer

Two built-in safeguards:

orders.merge(customers, on="customer_id", how="left", validate="many_to_one")
orders.merge(customers, on="customer_id", how="outer", indicator=True)
  • validate="many_to_one" raises an error if customers has duplicate customer_id values. Other values: "one_to_one", "one_to_many", "many_to_many".
  • indicator=True adds a _merge column saying left_only, right_only or both, so you can count each.

The duplicate-key trap

If the right table has two rows for the same key, every left row matching it is repeated. Suppose customers accidentally listed customer 1 twice. Then Asha's three orders would become six rows, and a later sum of amount would double those orders. This is the pandas twin of the SQL fan-out trap. Prevent it by:

  1. Checking customers["customer_id"].is_unique before the merge.
  2. Using validate="many_to_one".
  3. Aggregating the many-side first, then merging.

Stacking tables: concat

merge adds columns by matching rows; concat stacks tables on top of each other (same columns) or side by side:

all_months = pd.concat([sept, october], ignore_index=True)           # stack rows
wide = pd.concat([a, b], axis=1)                                     # side by side by index

Typical use: combine monthly files.

from pathlib import Path
frames = [pd.read_csv(p) for p in Path("data").glob("orders_*.csv")]
orders_all = pd.concat(frames, ignore_index=True)

Columns that exist in only some tables are filled with missing values. After stacking, check for duplicates with duplicated() (cleaning lessons).

join

DataFrame.join merges on the index by default and is a shortcut when both tables are indexed by their key: orders.set_index("customer_id").join(customers.set_index("customer_id")). Most analysts use merge for everything because it names the keys explicitly.

Common mistakes

  • Merging on columns of different types (integer in one, text in the other); you get no matches. Convert with astype.
  • Keys with stray spaces or different capitalisation, so rows do not match.
  • Not checking the row count after a join.
  • Duplicate keys inflating rows and totals.
  • Using the default inner join and silently losing rows you wanted to keep.

Try it yourself

  1. Merge orders with customers (left join) and compute total amount per city.
  2. List customers who have never ordered.
  3. Make customers have a duplicate key on purpose, merge, and see how totals change. Then add validate.
  4. Use indicator=True with an outer merge and count the _merge categories.
  5. Create two small monthly DataFrames and combine them with concat.

Check yourself

What is the difference between an inner and a left merge?

Inner keeps only rows matching in both tables. Left keeps all rows from the left table, filling missing values where there is no match.

What does validate="many_to_one" do?

It raises an error if the right table's key is not unique, which prevents accidental row duplication.

When would you use concat instead of merge?

To stack tables with the same columns (such as monthly files) or place tables side by side, rather than matching rows on a key.

Why might a merge return no matching rows even though the values look the same?

The key columns may have different types (number versus text) or hidden spaces and case differences.

Interview questions

How do you find rows in one DataFrame that have no match in another?

Merge with how="left", indicator=True and keep rows where _merge == "left_only", or use a left merge and filter for missing values in a right-hand column.

Your total revenue went up after a merge. What happened?

Probably a duplicate key in the other table repeated rows. Check key uniqueness, use validate, and aggregate the many-side before merging.

How do you combine 12 monthly CSV files?

Read each into a DataFrame in a list or generator and call pd.concat(frames, ignore_index=True).

Summary

  • merge joins on key columns; choose how as inner, left, right, outer or cross.
  • Check row counts, use validate and indicator, and watch for duplicate keys.
  • Fix type and whitespace mismatches before merging.
  • concat stacks tables or places them side by side.

Going deeper

See how much of this you can explain

A free mock interview asks you new questions on Python for data analysis with pandas for Data Analyst and scores every answer, with what to fix.