Pandas Join DataFrames on Columns

pandas DataFrame join() method doesn’t support joining two DataFrames on columns as join() is used for indices. However, you can convert column to index and used it on join. The best approach would be using merge() method when you wanted to join on columns.

In this article, I will explain how to join pandas DataFrames on columns when columns on the left and right DataFrames are the same and when column names are different by using join() and merge() methods.

1. Quick Examples of Pandas Join DataFrames on Columns

If you are in a hurry, below are some quick examples of how to join pandas DataFrames on multiple columns.


# Below are the quick examples

# Pandas join on columns.
# Use merge() to get efficient results.
df3=df1.set_index('Courses').join(df2.set_index('Courses'), how='inner')

# Merge on common columns
merged_df = pd.merge(df,df1)

# Use pandas.merge() on multiple columns
df2 = pd.merge(df, df1, on=['Courses','Fee'])

# Use pandas.merge() on multiple columns
df2 = pd.merge(df, df1,  how='left', left_on=['Courses','Fee'], right_on = ['Courses','Fee'])

# Merge Pandas DataFrames using left_on and right_on
merged_df = pd.merge(df, df1, left_on="Courses", right_on="Courses")

# Set value of on parameter to specify the key value for merge in pandas
merged_df = pd.merge(df, df1, on="Courses")

Now, let’s create a DataFrame with a few rows and columns, execute these examples and validate results. First DataFrame contains column names CoursesFee, Duration and second DataFrame contains column names Courses,Fee,Percentage.


# Create DataFrame
import pandas as pd
df = pd.DataFrame({'Courses': ["Spark","PySpark","Python","pandas","Java"],
                    'Fee' : [20000,25000,30000,24000,40000],
                    'Duration':['30day','40days','60days','55days','50days']})

df1 = pd.DataFrame({'Courses': ["Java","PySpark","Python","pandas","Hyperion"],
                    'Fee': [20000,25000,30000,24000,40000],
                    'Percentage':['10%','20%','25%','20%','10%']})
 
print(df)
print(df1)

Yields below output.


# Output:
   Courses    Fee Duration
0    Spark  20000    30day
1  PySpark  25000   40days
2   Python  30000   60days
3   pandas  24000   55days
4     Java  40000   50days

    Courses    Fee Percentage
0      Java  20000        10%
1   PySpark  25000        20%
2    Python  30000        25%
3    pandas  24000        20%
4  Hyperion  40000        10%

2. Pandas Join DataFrames on Columns

By default pandas join() method doesn’t support joining DataFrames on columns, but you can do this by converting the column you wish to join to index. In order to join on columns, the better approach would be using merge().


# Pandas join on columns
df3=df.set_index('Courses').join(df2.set_index('Courses'), how='inner')
print(df3)

3. Join on All Common Columns of DataFrame

By default, the merge() method applies join contains on all columns that are present on both DataFrames and uses inner join.


# Merge default pandas DataFrame without any key column
merged_df = pd.merge(df,df1)
print(merged_df)

Yields below output.


# Output:
   Courses    Fee Duration Percentage
0  PySpark  25000   40days        20%
1   Python  30000   60days        25%
2   pandas  24000   55days        20%

4. Join on Multiple Columns using merge()

You can also explicitly specify the column names you wanted to use for joining. To use column names use on param. This also takes a list of names when you wanted to join on multiple columns.


# Use pandas.merge() on multiple columns
df2 = pd.merge(df, df1, on=['Courses','Fee'])
print(df2)

Yields same output as above.

5. When Column Names are Different

When you have column names on left and right are different and want to use these as a join column, use left_on and right_on parameters. This also takes a list of column names as values to join on multiple columns. The left_on will be set to the name of the column in the left DataFrame and right_on will be set to the name of the column in the right DataFrame.


# Use pandas.merge() to on multiple columns
df2 = pd.merge(df, df1,  how='left', left_on=['Courses','Fee'], right_on = ['Courses','Fee'])
print(df2)

Yields below output.


# Output:
   Courses    Fee Duration Percentage
0    Spark  20000    30day        NaN
1  PySpark  25000   40days        20%
2   Python  30000   60days        25%
3   pandas  24000   55days        20%
4     Java  40000   50days        NaN

6. Complete Example of Join DataFrames on Columns


import pandas as pd
df = pd.DataFrame({'Courses': ["Spark","PySpark","Python","pandas","Java"],
                    'Fee' : [20000,25000,30000,24000,40000],
                    'Duration':['30day','40days','60days','55days','50days']})

df1 = pd.DataFrame({'Courses': ["Java","PySpark","Python","pandas","Hyperion"],
                    'Fee': [20000,25000,30000,24000,40000],
                    'Percentage':['10%','20%','25%','20%','10%']})
 
print(df)
print(df1)

# Merge default pandas DataFrame without any key column
merged_df = pd.merge(df,df1)
print(merged_df)

# Use pandas.merge() to on multiple columns
df2 = pd.merge(df, df1,  how='left', left_on=['Courses','Fee'], right_on = ['Courses','Fee'])
print(df2)

# Merge Pandas DataFrames using left_on and right_on
merged_df = pd.merge(df, df1, left_on="Courses", right_on="Courses")
print(merged_df)

# Set value of on parameter to specify the key value for merge in pandas
merged_df = pd.merge(df, df1, on="Courses")
print(merged_df)

Conclusion

In this article, I have explained joining pandas DataFrames on columns with examples. DataFrame join() method doesn’t support joining two DataFrames on columns as join() is used for indices. However, you can convert column to index and used it on join. The best approach would be using merge() method when you wanted to join on columns.

Happy Learning !!

References

Naveen (NNK)

Naveen (NNK) is a Data Engineer with 20+ years of experience in transforming data into actionable insights. Over the years, He has honed his expertise in designing, implementing, and maintaining data pipelines with frameworks like Apache Spark, PySpark, Pandas, R, Hive and Machine Learning. Naveen journey in the field of data engineering has been a continuous learning, innovation, and a strong commitment to data integrity. In this blog, he shares his experiences with the data as he come across. Follow Naveen @ @ LinkedIn

Leave a Reply

You are currently viewing Pandas Join DataFrames on Columns