Write a program in Python to merge two dataframes based on matching data in a column

Assume, you have two dataframe,

first dataframe is
 id country
0 1 India
1 2 UK
2 3 US
3 4 China

second dataframe is

 id City
0 1 Chennai
1 11 Cambridge
2 22 Chicago
3 4 Chengdu

And the result for merging based on same column is,

Merging data based on same column - id
 id    country    City
0 1    India    Chennai
1 4    China    Chengdu

Solution

To solve this, we will follow the steps given below −

• Define a two dataframes

• Merge two dataframes based on the same column id is defined below,

pd.merge(first_df,second_df,how='inner',on='id')

Example

Let us see the following implementation to get a better understanding −

import pandas as pd
import numpy as np
first_df = pd.DataFrame({'id':[1,2,3,4], 'country':['India','UK','US','China']
                        })
print("first dataframe is\n", first_df)
second_df = pd.DataFrame({'id':[1,11,22,4], 'City' : ['Chennai','Cambridge','Chicago','Chengdu']
                        })
print("second dataframe is\n", second_df)
print("Merging data based on same column - id")
print(pd.merge(first_df,second_df,how='inner',on='id'))

Output

first dataframe is
 id country
0 1 India
1 2 UK
2 3 US
3 4 China

second dataframe is
  id  City
0 1 Chennai
1 11 Cambridge
2 22 Chicago
3 4 Chengdu

Merging data based on same column - id
 id country City
0 1 India Chennai
1 4 China Chengdu