What is the difference between join and merge in Pandas?

In Pandas, join() combines DataFrames based on their indices and defaults to a left join, while merge() joins on specified columns and defaults to an inner join. Choosing the right method depends on how your data is aligned. To illustrate the difference between join() and merge() visually, Let's understand with help of examples.

Using join() for index- based merging

The join() method is useful when merging DataFrames based on their index values. By default, it performs a left join, meaning all rows from the left DataFrame are retained, and matching rows from the right DataFrame are added based on the index.

Example: In this example, two DataFrames are joined on their indexes. An outer join retains all index values, filling missing ones with NaN.

import pandas as pd

df1 = pd.DataFrame({'A': [1, 2, 3]}, index=['a', 'b', 'c'])
df2 = pd.DataFrame({'B': [4, 5, 6]}, index=['a', 'b', 'd'])

print(df1, "\n")
print(df2, "\n")

# joins on index
res = df1.join(df2, how='outer')
print(res)

Output

Explanation: join() method merges df1 and df2 on their indexes. With how='outer', it retains all unique index values, combining common ones and filling missing values with NaN.

For different types of joins, refer to Different Types of Joins in Pandas to explore alternatives like merge() for column-based joins.

Using merge() for coloumn-based merging

The merge() function is more versatile than join() and is commonly used for column-based merging. By default, merge() performs an inner join, which means it returns only the rows with matching values in both DataFrames based on the specified key columns. Unlike join(), which merges based on index values, merge() allows you to explicitly specify which columns to use as keys for merging.

Example 1 : In this example, we are merging two DataFrames based on the common column 'ID'. This allows us to combine matching rows from both DataFrames where the 'ID' values are the same.

df1 = pd.DataFrame({'ID': [1, 2, 3], 'Name': ['Alice', 'Bob', 'Charlie']})
df2 = pd.DataFrame({'ID': [1, 3, 4], 'Score': [90, 85, 80]})

print(df1, "\n")
print(df2, "\n")

# Performing merge on 'ID'
res = pd.merge(df1, df2, on='ID')
print(res)

Output

Explanation: merge() function combines df1 and df2 using the common column 'ID'. It performs an inner join, so only rows with matching 'ID' values in both DataFrames are included in the result.

Refer to Joining Two Pandas DataFrames Using merge() to understand the various types of joins available in Pandas.

Example 2: In this example, we are merging two DataFrames using their indexes. Since both DataFrames have a column named 'P', suffixes are used to differentiate the overlapping column names in the merged result.

import pandas as pd

df1 = pd.DataFrame([['a', 'b'], ['d', 'f']], index=['X', 'Y'], columns=['P', 'Q'])
df2 = pd.DataFrame([[1, 2], [4, 5]], index=['X', 'Y'], columns=['P', 'R'])

print(df1, "\n")
print(df2, "\n")

res = df1.merge(df2, left_index=True, right_index=True, suffixes=['_', ''])
print(res)

Output

Explanation: merge() function merges df1 and df2 using their shared index labels ('X' and 'Y') by setting left_index=True and right_index=True. Since both have a 'P' column, suffixes=['_', ''] differentiates them as 'P_' (from df1) and 'P' (from df2).

Note: For simple index-based joins, use join(). For more complex merges involving specific columns or multiple keys, use merge().

Difference between join() and merge()

In real-world datasets, combining or enriching data is common. Choosing the right method, join() or merge() ensures accurate relationships, efficient processing and clear code. Understanding their differences helps avoid issues like missing rows, duplicates or incorrect joins.