Merging two DataFrames with multiple Keys and conditions in Pandas

Last update on April 09 2025 12:52:09 (UTC/GMT +8 hours)

13. Merge Multiple Keys with Conditions

Write a Pandas program to merge two DataFrames using multiple keys and specific join conditions.

This exercise shows how to merge two DataFrames using multiple keys and specific join conditions (inner, left, etc.).

Sample Solution :

Code :

import pandas as pd

# Create two sample DataFrames
df1 = pd.DataFrame({
    'ID': [1, 2, 3, 4],
    'Name': ['Selena', 'Annabel', 'Charlie', 'Caeso'],
    'City': ['NY', 'LA', 'NY', 'LA']
})

df2 = pd.DataFrame({
    'ID': [2, 3, 4, 5],
    'Name': ['Annabel', 'Charlie', 'Caeso', 'Eve'],
    'City': ['LA', 'NY', 'LA', 'NY'],
    'Age': [30, 22, 25, 28]
})

# Merge the DataFrames on both 'ID' and 'City'
merged_df = pd.merge(df1, df2, on=['ID', 'City'], how='inner')

# Output the result
print(merged_df)

Output:

   ID   Name_x City   Name_y  Age
0   2  Annabel   LA  Annabel   30
1   3  Charlie   NY  Charlie   22
2   4    Caeso   LA    Caeso   25   

Explanation:

• Created two DataFrames df1 and df2 with shared columns 'ID' and 'City'.
• Used pd.merge() to merge on both 'ID' and 'City' with an inner join.
• The result includes only rows where both 'ID' and 'City' match in both DataFrames.

For more Practice: Solve these Related Problems:

• Write a Pandas program to merge two DataFrames using multiple keys and apply a conditional filter that excludes rows with null key values.
• Write a Pandas program to merge two DataFrames using multiple keys and then select rows satisfying a custom logical condition post-merge.
• Write a Pandas program to merge two DataFrames on multiple keys with an additional condition that a numeric column exceeds a threshold.
• Write a Pandas program to merge two DataFrames on multiple keys and then create a derived column based on combined key conditions.

Python-Pandas Code Editor:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.