Tutorials › Python › Merging DataFrames in pandas

Merging DataFrames in pandas

Combine multiple tables with merge and concat — the pandas equivalent of SQL JOINs

Real data lives in multiple tables. Customer data is in one file, orders in another, products in a third. Merging combines them on a matching key — exactly like SQL JOINs. pd.merge() is the SQL JOIN equivalent. pd.concat() stacks DataFrames vertically (appending rows) or horizontally (adding columns side by side).

Examples

pd.merge — joining tables like SQL
import pandas as pd

orders = pd.DataFrame({
    "OrderID": [1, 2, 3],
    "CustomerID": [101, 102, 101],
    "Amount": [5000, 8000, 3000]
})

customers = pd.DataFrame({
    "CustomerID": [101, 102, 103],
    "Name": ["Rahul", "Priya", "Arun"],
    "City": ["Delhi", "Noida", "Mumbai"]
})

# INNER JOIN (default) — only matching rows
pd.merge(orders, customers, on="CustomerID")

# LEFT JOIN — all orders, even without a customer match
pd.merge(orders, customers, on="CustomerID", how="left")

# RIGHT JOIN — all customers
pd.merge(orders, customers, on="CustomerID", how="right")

# FULL OUTER JOIN — all rows from both
pd.merge(orders, customers, on="CustomerID", how="outer")

# Different column names in each table
pd.merge(orders, customers,
         left_on="CustomerID", right_on="CustID")
💡 how="left" is the most common in data work — keep all records from the main table, add info from the lookup table where available.
pd.concat — stack DataFrames
# Vertical concat (append rows) — combine monthly files
jan = pd.read_csv("jan.csv")
feb = pd.read_csv("feb.csv")
mar = pd.read_csv("mar.csv")

combined = pd.concat([jan, feb, mar], ignore_index=True)
# ignore_index=True resets the index to 0,1,2,...

# With source tracking
combined = pd.concat(
    [jan, feb, mar],
    keys=["Jan", "Feb", "Mar"],
    ignore_index=True
)

# Horizontal concat (add columns side by side)
pd.concat([df1, df2], axis=1)

Key Points

  • ✓pd.merge(left, right, on="key") = SQL INNER JOIN by default
  • ✓how="left" keeps all rows from the left DataFrame — most common in practice
  • ✓pd.concat([df1, df2]) appends rows (axis=0 default) — use for combining monthly files
  • ✓ignore_index=True resets the row index after concat — always use it
  • ✓Validate merges: check len() before and after — unexpected rows = duplicate keys

Practice Question

You have a Sales DataFrame and a Products DataFrame. You want all sales records with product details added where available. Which merge type should you use?