Tutorials › Python › Merging DataFrames

Merging DataFrames

Combine DataFrames with merge and concat — the Pandas equivalent of SQL JOINs

Merging is how you combine data from multiple tables. Pandas merge() works exactly like SQL JOINs. concat() stacks DataFrames vertically or horizontally. These are essential when your data comes from multiple files or sources and needs to be combined before analysis.

Examples

merge — SQL-style joins
import pandas as pd

# Two DataFrames
orders = pd.DataFrame({
    "OrderID": [1, 2, 3, 4],
    "CustomerID": [101, 102, 101, 103],
    "Amount": [45000, 18000, 62000, 9000],
})
customers = pd.DataFrame({
    "CustomerID": [101, 102, 104],
    "Name": ["Rahul", "Priya", "Amit"],
    "City": ["Delhi", "Noida", "Gurgaon"],
})

# INNER JOIN — only matching rows (default)
pd.merge(orders, customers, on="CustomerID")
# Returns orders 1, 2, 3 (customer 103 has no name, 104 has no orders)

# LEFT JOIN — all orders, fill unmatched customers with NaN
pd.merge(orders, customers, on="CustomerID", how="left")
# Returns all 4 orders; order 4 (customer 103) gets NaN for Name/City

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

# OUTER JOIN — everything 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 analysis — keep all your fact records and pull in matching dimension attributes.
concat — stacking DataFrames
# Stack rows (vertical concat — like UNION in SQL)
q1 = pd.read_csv("sales_q1.csv")
q2 = pd.read_csv("sales_q2.csv")
combined = pd.concat([q1, q2], ignore_index=True)

# ignore_index=True resets the row index (0,1,2,...)
# without it: index 0-99 from q1, then 0-89 from q2 (duplicates)

# Add a source label
q1["quarter"] = "Q1"
q2["quarter"] = "Q2"
combined = pd.concat([q1, q2], ignore_index=True)

# Stack columns (horizontal concat)
pd.concat([df1, df2], axis=1)

# Combine a list of DataFrames (common pattern)
dfs = [pd.read_csv(f) for f in csv_files]
all_data = pd.concat(dfs, ignore_index=True)

Key Points

  • ✓merge() is SQL JOIN — default is inner join, how= controls left/right/outer
  • ✓Always check the row count before and after merge to detect unexpected fan-out
  • ✓concat() with ignore_index=True resets index to 0,1,2... — almost always what you want
  • ✓validate="one_to_many" in merge() raises an error if the join produces unexpected duplicates
  • ✓suffixes=("_left", "_right") handles columns with the same name in both tables

Practice Question

You have an Orders table and a Customers table. You want ALL orders, with customer details where available (nulls where no customer match). Which merge type?

Related Topics

Pandas DataFramesThe core Pandas data structure — a 2D table with rows and columnsgroupby and Pivot TablesAggregate data by category with groupby — the Pandas equivalent of Excel pivot tablesSQL with Python — pandas and SQLiteQuery databases directly from Python using pandas and run SQL on DataFrames with DuckDB