How to Merge Pandas DataFrames on Multiple Columns


Often you may want to merge two pandas DataFrames on multiple columns. Fortunately this is easy to do using the pandas merge() function, which uses the following syntax:

pd.merge(df1, df2, left_on=['col1','col2'], right_on = ['col1','col2'])

This tutorial explains how to use this function in practice.

Example 1: Merge on Multiple Columns with Different Names

Suppose we have the following two pandas DataFrames:

import pandas as pd

#create and view first DataFrame
df1 = pd.DataFrame({'a1': [0, 0, 1, 1, 2],
                   'b': [0, 0, 1, 1, 1],
                   'c': [11, 8, 10, 6, 6]})

print(df1)

   a1  b   c
0   0  0  11
1   0  0   8
2   1  1  10
3   1  1   6
4   2  1   6

#create and view second DataFrame 
df2 = pd.DataFrame({'a2': [0, 1, 1, 1, 3],
                   'b': [0, 0, 0, 1, 1],
                   'd': [22, 24, 25, 33, 37]})

print(df2)

   a2  b   d
0   0  0  22
1   1  0  24
2   1  0  25
3   1  1  33
4   3  1  37

The following code shows how to perform a left join using multiple columns from both DataFrames:

pd.merge(df1, df2, how='left', left_on=['a1', 'b'], right_on = ['a2','b'])


        a1	b	c	a2	d
0	0	0	11	0.0	22.0
1	0	0	8	0.0	22.0
2	1	1	10	1.0	33.0
3	1	1	6	1.0	33.0
4	2	1	6	NaN	NaN

Example 2: Merge on Multiple Columns with Same Names

Suppose we have the following two pandas DataFrames with the same column names:

import pandas as pd

#create DataFrames
df1 = pd.DataFrame({'a': [0, 0, 1, 1, 2],
                   'b': [0, 0, 1, 1, 1],
                   'c': [11, 8, 10, 6, 6]})

df2 = pd.DataFrame({'a': [0, 1, 1, 1, 3],
                   'b': [0, 0, 0, 1, 1],
                   'd': [22, 24, 25, 33, 37]})

In this case we can simplify use on = [‘a’, ‘b’] since the column names are the same in both DataFrames:

pd.merge(df1, df2, how='left', on=['a', 'b'])

	a	b	c	d
0	0	0	11	22.0
1	0	0	8	22.0
2	1	1	10	33.0
3	1	1	6	33.0
4	2	1	6	NaN

Additional Resources

How to Merge Two Pandas DataFrames on Index
How to Stack Multiple Pandas DataFrames

Leave a Reply

Your email address will not be published.