Pandas: Merge all values in two datasets, with matching records from both sides where available

Last update on April 03 2025 12:54:51 (UTC/GMT +8 hours)

7. Join with Matching Records (Inner Join)

Write a Pandas program to join the two dataframes with matching records from both sides where available.

Test Data:

student_data1:
  student_id              name  marks
0         S1  Danniella Fenton    200
1         S2      Ryder Storey    210
2         S3      Bryce Jensen    190
3         S4         Ed Bernal    222
4         S5       Kwame Morin    199

student_data2:
  student_id              name  marks
0         S4  Scarlette Fisher    201
1         S5  Carla Williamson    200
2         S6       Dante Morse    198
3         S7    Kaiser William    219
4         S8   Madeeha Preston    201

Sample Solution:

Python Code :

import pandas as pd
student_data1 = pd.DataFrame({
        'student_id': ['S1', 'S2', 'S3', 'S4', 'S5'],
         'name': ['Danniella Fenton', 'Ryder Storey', 'Bryce Jensen', 'Ed Bernal', 'Kwame Morin'], 
        'marks': [200, 210, 190, 222, 199]})

student_data2 = pd.DataFrame({
        'student_id': ['S4', 'S5', 'S6', 'S7', 'S8'],
        'name': ['Scarlette Fisher', 'Carla Williamson', 'Dante Morse', 'Kaiser William', 'Madeeha Preston'], 
        'marks': [201, 200, 198, 219, 201]})

print("Original DataFrames:")
print(student_data1)
print(student_data2)
merged_data = pd.merge(student_data1, student_data2, on='student_id', how='outer')
print("Merged data (outer join):")
print(merged_data)

Sample Output:

Original DataFrames:
  student_id              name  marks
0         S1  Danniella Fenton    200
1         S2      Ryder Storey    210
2         S3      Bryce Jensen    190
3         S4         Ed Bernal    222
4         S5       Kwame Morin    199
  student_id              name  marks
0         S4  Scarlette Fisher    201
1         S5  Carla Williamson    200
2         S6       Dante Morse    198
3         S7    Kaiser William    219
4         S8   Madeeha Preston    201
Merged data (outer join):
  student_id            name_x  marks_x            name_y  marks_y
0         S1  Danniella Fenton    200.0               NaN      NaN
1         S2      Ryder Storey    210.0               NaN      NaN
2         S3      Bryce Jensen    190.0               NaN      NaN
3         S4         Ed Bernal    222.0  Scarlette Fisher    201.0
4         S5       Kwame Morin    199.0  Carla Williamson    200.0
5         S6               NaN      NaN       Dante Morse    198.0
6         S7               NaN      NaN    Kaiser William    219.0
7         S8               NaN      NaN   Madeeha Preston    201.0        

For more Practice: Solve these Related Problems:

• Write a Pandas program to perform an inner join on two DataFrames and then display only the rows with matching keys.
• Write a Pandas program to merge two DataFrames and then filter out rows that do not have matching records on both sides.
• Write a Pandas program to join two DataFrames with an inner join and then count the number of matching records.
• Write a Pandas program to combine two DataFrames using an inner join and then sort the merged data by a common column.

Python Code Editor:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a Pandas program to join the two dataframes using the common column of both dataframes.
Next: Write a Pandas program to join (left join) the two dataframes using keys from left dataframe only.