Left, right, outer and inner joins on pandas dataframes

Renesh Bedre    1 minute read

Left join

Create dataframes,

import pandas as pd
df1 = pd.DataFrame({'col1':['A', 'B', 'C', 'D', 'E', 'F', 'I'], 'col2':[1,2,3,4,5,6,8] })
df2 = pd.DataFrame({'col1':['A', 'B', 'C', 'D', 'E', 'G'], 'col3':[0.1,0.2,0.3,0.4,0.5,0.7] })
df1.head(2)
  col1  col2
0    A     1
1    B     2

df2.head(2)
  col1  col3
0    A   0.1
1    B   0.2

left join df1 and df2 (join based on all keys from df1),

df_left_joined = pd.merge(df1, df2, how='left', on='col1')
df_left_joined.head(2)
  col1  col2  col3
0    A     1   0.1
1    B     2   0.2

If you want to keep only first match, you can run pandas groupby function after join.

Right join

right join df1 and df2 (join based on all keys from df2),

df_right_joined = pd.merge(df1, df2, how='right', on='col1')
df_right_joined.head(2)
  col1  col2  col3
0    A   1.0   0.1
1    B   2.0   0.2

Outer join

Outer join df1 and df2 (join based on union of keys of df1 and df2),

df_outer_joined = pd.merge(df1, df2, how='outer', on='col1')
df_outer_joined.head(2)
  col1  col2  col3
0    A   1.0   0.1
1    B   2.0   0.2
2    C   3.0   0.3
3    D   4.0   0.4
4    E   5.0   0.5
5    F   6.0   NaN
6    I   8.0   NaN
7    G   NaN   0.7

Inner join

Inner join df1 and df2 (join based on common keys of df1 and df2),

df_inner_joined = pd.merge(df1, df2, how='inner', on='col1')
df_inner_joined
  col1  col2  col3
0    A     1   0.1
1    B     2   0.2
2    C     3   0.3
3    D     4   0.4
4    E     5   0.5

Enhance your skills with courses on Python

Reference

  1. Jeff Reback, Wes McKinney, jbrockmendel, Joris Van den Bossche, Tom Augspurger, Phillip Cloud, … h-vetinari. (2021, April 12). pandas-dev/pandas: Pandas 1.2.4 (Version v1.2.4). Zenodo. http://doi.org/10.5281/zenodo.4681666

Learn more about Python

If you have any questions, comments, corrections, or recommendations, please email me at reneshbe@gmail.com

This work is licensed under a Creative Commons Attribution 4.0 International License

Some of the links on this page may be affiliate links, which means we may get an affiliate commission on a valid purchase. The retailer will pay the commission at no additional cost to you.

You may also enjoy