Tutorial: Merging Two DataFrames using Pandas

Data manipulation and analysis are crucial aspects of any data science or analysis project. In many cases, you’ll have multiple datasets that you need to combine to derive meaningful insights. The pandas library in Python provides powerful tools for handling and merging data, and in this tutorial, we’ll explore how to merge two DataFrames using pandas.

Table of Contents

1. Introduction to Data Merging
2. Types of Joins
3. Using the pd.merge() Function
   • Inner Join
   • Outer Join
4. Using the .join() Function
5. Examples
   • Example 1: Inner Join
   • Example 2: Outer Join
6. Conclusion

1. Introduction to Data Merging

When working with data, it’s common to have information distributed across multiple datasets. Merging allows you to combine these datasets based on common columns or indices, enabling you to create a comprehensive view of the data. The pandas library provides various methods for merging data, and understanding these methods is crucial for effective data analysis.

2. Types of Joins

In database terminology, a “join” combines rows from two or more tables based on a related column between them. There are several types of joins, including:

• Inner Join: Returns only the rows where there is a match in both DataFrames.
• Outer Join: Returns all rows from both DataFrames, filling in missing values with NaN where no match is found.
• Left Join: Returns all rows from the left DataFrame and the matching rows from the right DataFrame. Missing values are filled with NaN.
• Right Join: Returns all rows from the right DataFrame and the matching rows from the left DataFrame. Missing values are filled with NaN.

In this tutorial, we’ll focus on the first two types: Inner Join and Outer Join.

3. Using the pd.merge() Function

The primary method for merging DataFrames in pandas is the pd.merge() function. This function takes two or more DataFrames as arguments and combines them based on common columns.

Inner Join

An inner join returns only the rows where there is a match in both DataFrames. This means that the resulting DataFrame will only contain rows with matching values in the specified columns.

import pandas as pd

# Create two example DataFrames
df1 = pd.DataFrame({'ID': [1, 2, 3],
                    'Name': ['Alice', 'Bob', 'Charlie']})

df2 = pd.DataFrame({'ID': [2, 3, 4],
                    'Age': [25, 30, 22]})

# Perform an inner join
inner_merged = pd.merge(df1, df2, on='ID', how='inner')
print(inner_merged)

Output:

   ID     Name  Age
0   2      Bob   25
1   3  Charlie   30

In this example, an inner join was performed on the ‘ID’ column, resulting in a DataFrame that contains only the rows where ‘ID’ values are common between df1 and df2.

Outer Join

An outer join returns all rows from both DataFrames and fills in missing values with NaN where no match is found.

# Perform an outer join
outer_merged = pd.merge(df1, df2, on='ID', how='outer')
print(outer_merged)

Output:

   ID     Name   Age
0   1    Alice   NaN
1   2      Bob  25.0
2   3  Charlie  30.0
3   4      NaN  22.0

In this example, an outer join was performed on the ‘ID’ column. The resulting DataFrame contains all unique ‘ID’ values from both DataFrames, with corresponding ‘Name’ and ‘Age’ values where available. Where there is no match, the respective columns are filled with NaN.

4. Using the .join() Function

Apart from using the pd.merge() function, pandas also provides the .join() function, which is a more concise way of performing joins when you want to combine DataFrames based on their indices.

# Create example DataFrames
df3 = pd.DataFrame({'Score': [85, 90, 78]}, index=[1, 2, 3])

# Join using the .join() method
df_joined = df1.join(df3, how='left')
print(df_joined)

Output:

   ID     Name  Score
0   1    Alice    NaN
1   2      Bob   85.0
2   3  Charlie   90.0

In this example, the .join() method was used to combine df1 and df3 based on their indices. The resulting DataFrame contains all rows from df1 and fills in the ‘Score’ column with corresponding values from df3 where available.

5. Examples

Example 1: Inner Join

Let’s consider a practical example involving two DataFrames: one containing information about customers and their purchases, and another containing customer demographics.

# Create example DataFrames
customers = pd.DataFrame({'CustomerID': [1, 2, 3, 4],
                          'Name': ['Alice', 'Bob', 'Charlie', 'David']})

purchases = pd.DataFrame({'CustomerID': [2, 3, 1, 4],
                          'Product': ['Laptop', 'Phone', 'Tablet', 'Headphones']})

# Perform an inner join
inner_result = pd.merge(customers, purchases, on='CustomerID', how='inner')
print(inner_result)

Output:

   CustomerID     Name     Product
0           1    Alice      Tablet
1           2      Bob      Laptop
2           3  Charlie       Phone
3           4    David  Headphones

In this example, an inner join was performed on the ‘CustomerID’ column. The resulting DataFrame contains only the rows where ‘CustomerID’ values are common between the customers and purchases DataFrames. The ‘Name’ and ‘Product’ columns are included in the final result.

Example 2: Outer Join

Consider another example where you have information about students and their courses. You want to combine the information to get a complete view of students and the courses they are enrolled in.

# Create example DataFrames
students = pd.DataFrame({'StudentID': [1, 2, 3, 4],
                         'Name': ['Alice', 'Bob', 'Charlie', 'David']})

courses = pd.DataFrame({'StudentID': [2, 3, 5],
                        'Course': ['Math', 'Science', 'History']})

# Perform an outer join
outer_result = pd.merge(students, courses, on='StudentID', how='outer')
print(outer_result)

Output:

   StudentID     Name    Course
0          1    Alice       NaN
1          2      Bob      Math
2          3  Charlie   Science
3          4    David       NaN
4          5      NaN   History

In this

example, an outer join was performed on the ‘StudentID’ column. The resulting DataFrame contains all unique ‘StudentID’ values from both DataFrames, with corresponding ‘Name’ and ‘Course’ values where available. Missing values are filled with NaN.

6. Conclusion

Merging DataFrames is a fundamental operation in data analysis and manipulation. The pandas library provides powerful tools for performing various types of joins, such as inner and outer joins. By understanding these concepts and using functions like pd.merge() and .join(), you can efficiently combine datasets to extract valuable insights from your data. This tutorial has provided you with an overview of the different types of joins and demonstrated their usage through practical examples. With this knowledge, you’re well-equipped to tackle data merging tasks in your own projects.