Merging & Joining DataFrames

df_sales = pd.DataFrame({"order_id": [1,2,3], "customer_id": [10,20,10], "amount": [500,300,700]})
df_customers = pd.DataFrame({"customer_id": [10,20], "name": ["Rahul","Priya"]})

result = df_sales.merge(df_customers, on="customer_id", how="inner")

merge() is the primary join function. The on parameter specifies the common key column. how controls the join type — same as SQL JOIN types.

# inner — only matching rows from both
df.merge(df2, on="id", how="inner")

# left — all rows from left, matching from right
df.merge(df2, on="id", how="left")

# right — all rows from right, matching from left
df.merge(df2, on="id", how="right")

# outer — all rows from both, NaN where no match
df.merge(df2, on="id", how="outer")

Left join is the most common — you keep all your main data and enrich it with reference data. Inner join when you only want records that exist in both tables. Outer join to find records missing from either side.

df_sales.merge(
    df_customers,
    left_on="cust_id",    # column name in left df
    right_on="customer_id",  # column name in right df
    how="left"
)

left_on/right_on handles the case where the same key has different column names in each DataFrame — common when combining data from different systems.

# Stack DataFrames on top of each other (same columns):
df_jan = pd.read_csv("jan.csv")
df_feb = pd.read_csv("feb.csv")
df_all = pd.concat([df_jan, df_feb], ignore_index=True)

# Multiple files:
import glob
files = glob.glob("data/*.csv")
df_all = pd.concat([pd.read_csv(f) for f in files], ignore_index=True)

concat() stacks rows — use it when combining monthly/quarterly files with the same structure. ignore_index=True resets the index so you get 0,1,2... instead of duplicated indices.

# Rows in df1 not in df2 (anti-join):
merged = df1.merge(df2, on="id", how="left", indicator=True)
only_in_df1 = merged[merged["_merge"] == "left_only"].drop("_merge", axis=1)

indicator=True adds a _merge column with values "left_only", "right_only", or "both". This is the Pandas anti-join pattern — essential for finding unmatched records, like orders without customers.