14.4 C
London
Monday, July 7, 2025
HomePandas in PythonGeneral Functions in PythonHow to Merge Two Pandas DataFrames on Index

How to Merge Two Pandas DataFrames on Index

Related stories

Learn About Opening an Automobile Repair Shop in India

Starting a car repair shop is quite a good...

Unlocking the Power: Embracing the Benefits of Tax-Free Investing

  Unlocking the Power: Embracing the Benefits of Tax-Free Investing For...

Income Splitting in Canada for 2023

  Income Splitting in Canada for 2023 The federal government’s expanded...

Can I Deduct Home Office Expenses on my Tax Return 2023?

Can I Deduct Home Office Expenses on my Tax...

Canadian Tax – Personal Tax Deadline 2022

  Canadian Tax – Personal Tax Deadline 2022 Resources and Tools...

Often you may want to merge two pandas DataFrames by their indexes. There are three ways to do so in pandas:

1. Use join: By default, this performs a left join.

df1.join(df2)

2. Use merge. By default, this performs an inner join.

pd.merge(df1, df2, left_index=True, right_index=True)

3. Use concat. By default, this performs an outer join.

pd.concat([df1, df2], axis=1)

The following examples illustrate how to use each of these functions on the following two pandas DataFrames:

import pandas as pd

#create first DataFrame
df1 = pd.DataFrame({'rating': [90, 85, 82, 88, 94, 90, 76, 75],
                   'points': [25, 20, 14, 16, 27, 20, 12, 15]},
                   index=list('abcdefgh'))

print(df1)

   rating  points
a      90      25
b      85      20
c      82      14
d      88      16
e      94      27
f      90      20
g      76      12
h      75      15

#create second DataFrame 
df2 = pd.DataFrame({'assists': [5, 7, 7, 8, 5, 7],
                   'rebounds': [11, 8, 10, 6, 6, 9]},
                   index=list('acdgmn'))           

print(df2)

   assists  rebounds
a        5        11
c        7         8
d        7        10
g        8         6
m        5         6
n        7         9

Example 1: Merge DataFrames Using Join

The following code shows how to use join() to merge the two DataFrames:

df1.join(df2)

        rating	points	assists	rebounds
a	90	25	5.0	11.0
b	85	20	NaN	NaN
c	82	14	7.0	8.0
d	88	16	7.0	10.0
e	94	27	NaN	NaN
f	90	20	NaN	NaN
g	76	12	8.0	6.0
h	75	15	NaN	NaN

The join() function performs a left join by default, so each of the indexes in the first DataFrame are kept.

Example 2: Merge DataFrames Using Merge

The following code shows how to use merge() to merge the two DataFrames:

pd.merge(df1, df2, left_index=True, right_index=True)
	rating	points	assists	rebounds
a	90	25	5	11
c	82	14	7	8
d	88	16	7	10
g	76	12	8	6

The merge() function performs an inner join by default, so only the indexes that appear in both DataFrames are kept.

Example 3: Merge DataFrames Using Concat

The following code shows how to use concat() to merge the two DataFrames:

pd.concat([df1, df2], axis=1)

        rating	points	assists	rebounds
a	90.0	25.0	5.0	11.0
b	85.0	20.0	NaN	NaN
c	82.0	14.0	7.0	8.0
d	88.0	16.0	7.0	10.0
e	94.0	27.0	NaN	NaN
f	90.0	20.0	NaN	NaN
g	76.0	12.0	8.0	6.0
h	75.0	15.0	NaN	NaN
m	NaN	NaN	5.0	6.0
n	NaN	NaN	7.0	9.0

The concat() function performs an outer join by default, so each index value from each DataFrame is kept.

Additional Resources

The following tutorials explain how to perform other common operations in pandas:

How to Set Column as Index in Pandas DataFrame
How to Stack Multiple Pandas DataFrames
How to Insert a Column Into a Pandas DataFrame

Subscribe

- Never miss a story with notifications

- Gain full access to our premium content

- Browse free from up to 5 devices at once

Latest stories