Joining Two Pandas DataFrames using merge()

The merge() function is designed to merge two DataFrames based on one or more columns with matching values. The basic idea is to identify columns that contain common data between the DataFrames and use them to align rows.

Let's understand the process of joining two pandas DataFrames using merge(), explaining the key concepts, parameters, and practical examples to make the process clear and accessible.

If the column names are the same in both tables, you just need to use on to specify that column name. For example:

import pandas as pd

# DataFrames to merge
df1 = pd.DataFrame({'ID': [1, 2, 3], 'Name': ['Emily', 'Jack', 'Jennifer']})
df2 = pd.DataFrame({'ID': [1, 2, 4], 'Age': [24, 27, 22]})

# Merge DataFrames on the 'ID' column using an inner join
res = pd.merge(df1, df2, on='ID', how='inner')
print(res)

   ID   Name  Age
0   1  Emily   24
1   2   Jack   27

This example performs an inner join, resulting in a DataFrame that includes only the rows with matching ID values.

How merge() Function Works in Pandas?

The core idea behind merge() is simple: it allows to specify how the rows from two DataFrames should be aligned based on one or more keys (columns or indexes). The result is a new DataFrame that contains data from both original DataFrames.

Syntax:

pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None)

Parameters:

• left: first DataFrame.
• right: second DataFrame.
• how: Specifies the type of join (default is 'inner').
• on: Column(s) to join on. If not specified, Pandas will attempt to merge on columns with the same name in both DataFrames.
• left_on and right_on: Specify different columns from each DataFrame to join on if they don’t share the same column names.

Types of Joins

1. Inner Join

An inner join keeps rows from both DataFrames where there is a match in the specified column(s).

import pandas as pd

df1 = pd.DataFrame({"fruit" : ["apple", "banana", "avocado"], "market_price" : [21, 14, 35]})
print("First DataFrame:")
print(df1)

df2 = pd.DataFrame({"fruit" : ["banana", "apple", "avocado"], "wholesaler_price" : [65, 68, 75]})
print("Second DataFrame:")
print(df2)

print("Merged DataFrame:")
print(pd.merge(df1, df2, on="fruit", how="inner"))

Output

First DataFrame:
fruit market_price
0 apple 21
1 banana 14
2 avocado 35

Second DataFrame:
fruit wholesaler_price
0 banana 65
1 apple 68
2 avocado 75

Merged DataFrame:
fruit market_price wholesaler_price
0 apple 21 68
1 banana 14 65
2 avocado 35 75

2. Outer Join

An outer join includes all rows from both DataFrames so If we use how = "Outer" , it returns all elements in df1 and df2 but if element column are null then its return NaN value.

print(pd.merge(df1, df2, on = "fruit", how = "outer"))

Output

fruit market_price wholesaler_price
0 apple 21 68
1 avocado 35 75
2 banana 14 65

3. Left Join

A left join keeps all rows from the left DataFrame, adding only matching rows from the right.

print(pd.merge(df1, df2, on = "fruit", how = "left"))

Output

fruit market_price wholesaler_price
0 apple 21 68
1 banana 14 65
2 avocado 35 75

4. Right Join

A right join keeps all rows from the right DataFrame, adding only matching rows from the left.

print(pd.merge(df1, df2, on = "fruit", how = "right"))

Output

fruit market_price wholesaler_price
0 banana 14 65
1 apple 21 68
2 avocado 35 75

Key Takeaways

Here are the main points to remember when joining two DataFrames using merge():

• Common Columns: Ensure that the columns you are joining on are correctly identified and named.
• Join Types: Choose the appropriate join type (inner, left, right, outer) based on your data and analysis needs.
• Handling Duplicates: Use suffixes to manage duplicate column names that arise from the merge.
• Index vs Columns: Decide whether to join on columns or indexes using on, left_on, right_on, left_index, and right_index parameters.