Python Pandas - Merging/Joining

Pandas - Merging and Joining DataFrames

Combining data from different sources is a common task in data analysis. Pandas provides powerful and flexible tools for merging and joining DataFrame objects, offering high-performance in-memory operations similar to relational databases like SQL.

The primary function for combining DataFrames based on common columns or indexes is pd.merge(). Its basic signature is:

pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None,
         left_index=False, right_index=False, sort=True,
         suffixes=('_x', '_y'), ...)

Key parameters explained:

• left: The first DataFrame object.
• right: The second DataFrame object.
• how: Specifies the type of merge to perform. Options are: 'inner' (default), 'outer', 'left', 'right'. See details below.
• on: Column name(s) to join on. These columns must exist in both DataFrames. If None and left_on/right_on are not specified, merges on the intersection of columns.
• left_on: Column name(s) from the left DataFrame to use as join keys.
• right_on: Column name(s) from the right DataFrame to use as join keys. Use left_on/right_on when join keys have different names in the two DataFrames.
• left_index: If True, use the index of the left DataFrame as the join key(s).
• right_index: If True, use the index of the right DataFrame as the join key(s).
• sort: If True (default), sort the join keys lexicographically in the result. Setting to False can significantly improve performance.
• suffixes: A tuple of strings to append to overlapping column names (that are not join keys) in the left and right DataFrames, respectively. Default is ('_x', '_y').

Let’s create two sample DataFrames to illustrate merging:

# Import the pandas library
import pandas as pd

left = pd.DataFrame({
    'id': [1, 2, 3, 4, 5],
    'Student_Name': ['Alex', 'Amy', 'Allen', 'Alice', 'Ayoung'],
    'subject_id': ['sub1', 'sub2', 'sub4', 'sub6', 'sub5']
})

right = pd.DataFrame({
    'id': [1, 2, 3, 4, 5],
    'Teacher_Name': ['Billy', 'Brian', 'Bran', 'Bryce', 'Betty'],
    'subject_id': ['sub2', 'sub4', 'sub3', 'sub6', 'sub5']
})

print("Left DataFrame:")
print(left)
print("\nRight DataFrame:")
print(right)

Output:

Left DataFrame:
   id Student_Name subject_id
0   1         Alex       sub1
1   2          Amy       sub2
2   3        Allen       sub4
3   4        Alice       sub6
4   5       Ayoung       sub5

Right DataFrame:
   id Teacher_Name subject_id
0   1        Billy       sub2
1   2        Brian       sub4
2   3         Bran       sub3
3   4        Bryce       sub6
4   5        Betty       sub5

Merge on a Single Key Column (on)

If both DataFrames share a column with the same name that acts as the key, use the on parameter.

import pandas as pd
# (Use left and right DataFrames defined above)

# Merge based on the 'id' column (present in both)
merged_on_id = pd.merge(left, right, on='id')

print("Merged DataFrame on 'id':")
print(merged_on_id)

Output:

Merged DataFrame on 'id':
   id Student_Name subject_id_x Teacher_Name subject_id_y
0   1         Alex         sub1        Billy         sub2
1   2          Amy         sub2        Brian         sub4
2   3        Allen         sub4         Bran         sub3
3   4        Alice         sub6        Bryce         sub6
4   5       Ayoung         sub5        Betty         sub5

Notice the subject_id column, which existed in both original DataFrames but wasn’t the join key, automatically gets suffixes _x (from left) and _y (from right).

Merge on Multiple Key Columns (on with list)

To merge based on the combination of multiple columns, pass a list of column names to on.

import pandas as pd
# (Use left and right DataFrames defined above)

# Merge only where both 'id' and 'subject_id' match
merged_on_multiple = pd.merge(left, right, on=['id', 'subject_id'])

print("Merged DataFrame on 'id' and 'subject_id':")
print(merged_on_multiple)

Output:

Merged DataFrame on 'id' and 'subject_id':
   id Student_Name subject_id Teacher_Name
0   4        Alice       sub6        Bryce
1   5       Ayoung       sub5        Betty

Only rows where both the id and subject_id were identical in left and right are kept (id=4, subject_id=‘sub6’ and id=5, subject_id=‘sub5’).

Understanding Merge Types (how Argument)

The how argument controls which keys are included in the result and how missing values are handled when a key exists in one DataFrame but not the other.

Merge Method (how)	SQL Equivalent	Description
'inner' (Default)	INNER JOIN	Keeps only keys found in both left and right DataFrames. Intersection of keys.
'left'	LEFT OUTER JOIN	Keeps all keys from the left DataFrame. If a key is missing in the right DataFrame, fills corresponding columns with NaN.
'right'	RIGHT OUTER JOIN	Keeps all keys from the right DataFrame. If a key is missing in the left DataFrame, fills corresponding columns with NaN.
'outer'	FULL OUTER JOIN	Keeps all keys found in either the left or the right DataFrame. Fills with NaN where a key is missing in one of the DataFrames. Union of keys.

Let’s see examples using subject_id as the join key, which has different values in left and right.

Left Join (how='left')

import pandas as pd
# (Use left and right DataFrames defined above)

# Keep all subject_id from 'left', match from 'right'
left_join = pd.merge(left, right, on='subject_id', how='left')

print("Left Join on 'subject_id':")
print(left_join)

Output:

Left Join on 'subject_id':
   id_x Student_Name subject_id  id_y Teacher_Name
0     1         Alex       sub1   NaN          NaN  # sub1 only in left
1     2          Amy       sub2   1.0        Billy
2     3        Allen       sub4   2.0        Brian
3     4        Alice       sub6   4.0        Bryce
4     5       Ayoung       sub5   5.0        Betty

All subject_id values from left (sub1, sub2, sub4, sub6, sub5) are present. Since sub1 doesn’t exist in right, the columns from right (id_y, Teacher_Name) are NaN for that row.

Right Join (how='right')

import pandas as pd
# (Use left and right DataFrames defined above)

# Keep all subject_id from 'right', match from 'left'
right_join = pd.merge(left, right, on='subject_id', how='right')

print("Right Join on 'subject_id':")
print(right_join)

Output:

Right Join on 'subject_id':
   id_x Student_Name subject_id  id_y Teacher_Name
0   2.0          Amy       sub2     1        Billy
1   3.0        Allen       sub4     2        Brian
2   NaN          NaN       sub3     3         Bran  # sub3 only in right
3   4.0        Alice       sub6     4        Bryce
4   5.0       Ayoung       sub5     5        Betty

All subject_id values from right (sub2, sub4, sub3, sub6, sub5) are present. Since sub3 doesn’t exist in left, the columns from left (id_x, Student_Name) are NaN for that row.

Outer Join (how='outer')

import pandas as pd
# (Use left and right DataFrames defined above)

# Keep all subject_id from both 'left' and 'right'
outer_join = pd.merge(left, right, on='subject_id', how='outer')

print("Outer Join on 'subject_id':")
print(outer_join)

Output:

Outer Join on 'subject_id':
   id_x Student_Name subject_id  id_y Teacher_Name
0   1.0         Alex       sub1   NaN          NaN # Only in left
1   2.0          Amy       sub2   1.0        Billy
2   3.0        Allen       sub4   2.0        Brian
3   4.0        Alice       sub6   4.0        Bryce
4   5.0       Ayoung       sub5   5.0        Betty
5   NaN          NaN       sub3   3.0         Bran # Only in right

All unique subject_id values from both DataFrames (sub1, sub2, sub3, sub4, sub5, sub6) are included. NaN values appear where a subject ID exists in one DataFrame but not the other.

Inner Join (how='inner')

The default behavior (how='inner') keeps only the keys that are present in both DataFrames.

import pandas as pd
# (Use left and right DataFrames defined above)

# Keep only subject_id present in both 'left' and 'right'
inner_join = pd.merge(left, right, on='subject_id', how='inner')
# Equivalent to: inner_join = pd.merge(left, right, on='subject_id')

print("Inner Join on 'subject_id':")
print(inner_join)

Output:

Inner Join on 'subject_id':
   id_x Student_Name subject_id  id_y Teacher_Name
0     2          Amy       sub2     1        Billy
1     3        Allen       sub4     2        Brian
2     4        Alice       sub6     4        Bryce
3     5       Ayoung       sub5     5        Betty

Only subject_id values sub2, sub4, sub5, sub6 appear, as these are the only ones found in both the left and right DataFrames.

In addition to pd.merge(), Pandas also offers the DataFrame.join() method, which is convenient for joining DataFrames based on their indexes, or joining a DataFrame’s index with columns from another DataFrame. pd.concat() is another related function used for stacking DataFrames vertically or horizontally. However, pd.merge() is the most general and SQL-like function for combining DataFrames based on common values.