Merging on different column names in Pandas

Introduction

Merging on different column names helps you combine data from two tables even when the columns to join on have different names.

You have two tables with related data but the key columns have different names.

You want to combine customer info from one table with order info from another, but the ID columns are named differently.

You need to join sales data with product data where product IDs are labeled differently in each table.

You want to merge employee records from two sources where employee ID columns have different headers.

Syntax

Pandas

pd.merge(left_df, right_df, left_on='left_column', right_on='right_column')

left_on is the column name in the left DataFrame.

right_on is the column name in the right DataFrame.

Examples

Merges df1 and df2 where df1's 'id' matches df2's 'user_id'.

Pandas

pd.merge(df1, df2, left_on='id', right_on='user_id')

Joins on customer_id from df1 and client_id from df2.

Pandas

pd.merge(df1, df2, left_on='customer_id', right_on='client_id')

Sample Program

This code merges two DataFrames where the employee ID columns have different names: 'emp_id' and 'employee_number'. It combines rows where these IDs match.

Pandas

import pandas as pd

# Create first DataFrame
left_df = pd.DataFrame({
    'emp_id': [1, 2, 3],
    'name': ['Alice', 'Bob', 'Charlie']
})

# Create second DataFrame
right_df = pd.DataFrame({
    'employee_number': [3, 1, 4],
    'department': ['HR', 'IT', 'Finance']
})

# Merge on different column names
merged_df = pd.merge(left_df, right_df, left_on='emp_id', right_on='employee_number')

print(merged_df)

Important Notes

If the columns have the same name, you can use on='column_name' instead.

The result will include both columns used for merging unless you drop one.

Summary

You can merge DataFrames on columns with different names using left_on and right_on.

This helps combine related data even if column names differ.