Merging DataFrames
Combine two tables on a shared key, like a SQL JOIN.
Syntax
pd.merge(left, right, on='key', how='inner')pd.merge joins two DataFrames on a common column (the key), matching rows across them.
Join types
- inner — keep only keys in both tables (default).
- left — keep all rows from the left table.
- outer — keep everything, filling gaps with NaN.
To stack tables on top of each other instead, use pd.concat.
Example
import pandas as pd
users = pd.DataFrame({'id': [1, 2, 3], 'name': ['Ann', 'Bob', 'Cara']})
orders = pd.DataFrame({'id': [1, 1, 3], 'total': [50, 20, 99]})
merged = pd.merge(users, orders, on='id', how='inner')
print(merged)
# id name total
# 0 1 Ann 50
# 1 1 Ann 20
# 2 3 Cara 99When to use it
- A data engineer joins a customer demographics table to a transactions table on customer_id to build a unified feature table for an ML model.
- An analyst performs a left join to keep all orders while attaching product metadata, preserving orders even if no matching product record exists.
- A data scientist merges model predictions back onto the original test DataFrame by index to compare predicted versus actual labels side by side.
More examples
Inner join on a shared key
Performs an inner join on customer_id, returning only orders that have a matching customer record.
import pandas as pd
orders = pd.DataFrame({'order_id': [1,2,3], 'customer_id': [10,11,10], 'amount': [200,350,180]})
customers = pd.DataFrame({'customer_id': [10,11,12], 'name': ['Alice','Bob','Carol']})
merged = pd.merge(orders, customers, on='customer_id')
print(merged)Left join to preserve all rows
Left-joins product metadata onto orders, keeping every order row and inserting NaN where no matching product exists.
import pandas as pd
df_orders = pd.read_csv('orders.csv')
df_products = pd.read_csv('products.csv')
result = pd.merge(df_orders, df_products,
on='product_id', how='left')
print(result.isnull().sum()) # NaN where no product matchMerge predictions onto test set
Attaches model predictions as a new column on the original test DataFrame using assign, enabling easy comparison with ground-truth labels.
import pandas as pd
from sklearn.ensemble import RandomForestClassifier
test_df = pd.read_csv('test.csv')
X_test = test_df.drop(columns=['id','label'])
# model already trained
# preds = model.predict(X_test)
preds = [0, 1, 1, 0, 1] # example
test_df = test_df.assign(predicted=preds)
print(test_df[['id', 'label', 'predicted']].head())
Discussion