Tutorial: Pandas Merge on Multiple Columns

Introduction to Pandas Merge

Pandas is a popular Python library for data manipulation and analysis. One of the fundamental operations in data analysis is combining datasets, and Pandas provides the merge() function for this purpose. The merge() function allows you to combine two or more DataFrames based on common columns, which can be especially powerful when working with datasets that share multiple key columns. In this tutorial, we’ll explore how to use the merge() function on multiple columns to effectively combine datasets.

Table of Contents

1. Basic Syntax of merge()
2. Inner Merge on Multiple Columns
3. Outer Merge on Multiple Columns
4. Left and Right Merge on Multiple Columns
5. Merging on Non-Identical Column Names
6. Handling Duplicate Column Names
7. Example 1: Sales Data
8. Example 2: Student Records
9. Conclusion

1. Basic Syntax of merge()

The basic syntax of the merge() function is as follows:

merged_dataframe = pd.merge(left_dataframe, right_dataframe, on=['column1', 'column2'])

Here, left_dataframe and right_dataframe are the DataFrames you want to merge, and ['column1', 'column2'] represents the list of columns on which you want to merge.

2. Inner Merge on Multiple Columns

An inner merge returns only the rows that have matching values in both DataFrames based on the specified columns. This is the most common type of merge and is useful when you want to retain only the overlapping data.

inner_merged = pd.merge(df1, df2, on=['column1', 'column2'], how='inner')

3. Outer Merge on Multiple Columns

An outer merge returns all rows from both DataFrames, filling in missing values with NaN where there is no match.

outer_merged = pd.merge(df1, df2, on=['column1', 'column2'], how='outer')

4. Left and Right Merge on Multiple Columns

Left merge keeps all the rows from the left DataFrame and includes the matching rows from the right DataFrame. Right merge works similarly, keeping all rows from the right DataFrame and matching rows from the left DataFrame.

left_merged = pd.merge(df1, df2, on=['column1', 'column2'], how='left')
right_merged = pd.merge(df1, df2, on=['column1', 'column2'], how='right')

5. Merging on Non-Identical Column Names

Sometimes, the columns you want to merge on have different names in the two DataFrames. In such cases, you can use the left_on and right_on parameters to specify the columns to merge on from each DataFrame.

merged_diff_names = pd.merge(df1, df2, left_on=['left_column1', 'left_column2'], right_on=['right_column1', 'right_column2'])

6. Handling Duplicate Column Names

When merging DataFrames that have the same column names, Pandas appends _x to the column names of the left DataFrame and _y to the column names of the right DataFrame to avoid naming conflicts.

7. Example 1: Sales Data

Let’s walk through an example to understand how to use the merge() function on multiple columns. Consider two DataFrames: orders and customers.

import pandas as pd

# Sample data for orders DataFrame
orders_data = {'order_id': [1, 2, 3, 4, 5],
               'customer_id': [101, 102, 101, 103, 102],
               'order_date': ['2023-01-15', '2023-02-20', '2023-02-25', '2023-03-10', '2023-04-05']}
orders = pd.DataFrame(orders_data)

# Sample data for customers DataFrame
customers_data = {'customer_id': [101, 102, 103],
                  'customer_name': ['Alice', 'Bob', 'Charlie'],
                  'customer_city': ['New York', 'San Francisco', 'Los Angeles']}
customers = pd.DataFrame(customers_data)

Now, let’s perform an inner merge on the orders and customers DataFrames based on the customer_id and order_date columns.

inner_merged = pd.merge(orders, customers, on=['customer_id', 'order_date'], how='inner')
print(inner_merged)

The resulting inner_merged DataFrame will contain only the rows where both customer_id and order_date match in both DataFrames.

8. Example 2: Student Records

Consider another example where you have two DataFrames: students and grades.

# Sample data for students DataFrame
students_data = {'student_id': [1, 2, 3, 4, 5],
                  'first_name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],
                  'last_name': ['Johnson', 'Smith', 'Brown', 'Davis', 'Adams']}
students = pd.DataFrame(students_data)

# Sample data for grades DataFrame
grades_data = {'student_id': [1, 2, 3, 4, 5],
                'subject': ['Math', 'Science', 'Math', 'History', 'Science'],
                'grade': ['A', 'B', 'A', 'C', 'B']}
grades = pd.DataFrame(grades_data)

Now, let’s perform a left merge on the students and grades DataFrames based on the student_id and subject columns.

left_merged = pd.merge(students, grades, on=['student_id', 'subject'], how='left')
print(left_merged)

In this case, the left_merged DataFrame will include all the rows from the students DataFrame and the matching rows from the grades DataFrame.

9. Conclusion

The Pandas merge() function is a powerful tool for combining datasets based on common columns. In this tutorial, we explored the various options for merging on multiple columns, including inner, outer, left, and right merges. We also covered scenarios where columns have different names and how to handle duplicate column names. By mastering these techniques, you’ll be well-equipped to efficiently merge and analyze complex datasets using Pandas.

Remember that data preparation is a crucial step in any data analysis project, and understanding how to effectively merge DataFrames is an essential skill for any data scientist or analyst. Experiment with different merge types and explore real-world datasets to gain hands-on experience with the Pandas merge() function and its capabilities.