Python Pandas Merge Tutorial: Combining DataFrames with the Merge Function

Data manipulation is a fundamental aspect of working with data in any programming language, and Python is no exception. The pandas library is a powerful tool for data manipulation and analysis, providing various functions to perform tasks like merging and joining datasets. One of the most commonly used functions for combining data is the merge function, which allows you to merge two or more DataFrames based on common columns or indices. In this tutorial, we’ll explore the merge function in depth, covering its syntax, parameters, and providing examples to illustrate its usage.

Table of Contents

1. Introduction to the merge function
2. Syntax of the merge function
3. Parameters of the merge function
4. Types of merges
5. Examples of using the merge function

• Example 1: Inner Merge
• Example 2: Outer Merge

1. Conclusion

1. Introduction to the merge function

The merge function in pandas allows you to combine two or more DataFrames into a single DataFrame by aligning rows based on common columns or indices. This operation is analogous to SQL joins, where you can combine tables using specific conditions. Merging is useful when you have data distributed across multiple DataFrames and want to consolidate it for analysis or reporting purposes.

2. Syntax of the merge function

The basic syntax of the merge function is as follows:

result = pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None, left_index=False, right_index=False, sort=False)

Here’s a breakdown of the parameters:

• left and right: The DataFrames you want to merge.
• how: The type of merge to be performed. This can be ‘inner’, ‘outer’, ‘left’, or ‘right’. Default is ‘inner’.
• on: Column name(s) to merge on. If the DataFrames share column names, you can specify this parameter instead of left_on and right_on.
• left_on and right_on: Column name(s) in the left and right DataFrames to use as keys for merging.
• left_index and right_index: Boolean values indicating whether to use the left or right DataFrame’s index as the merge key.
• sort: Boolean value to indicate whether to sort the result by the merge keys.

3. Parameters of the merge function

Let’s delve deeper into the parameters of the merge function:

• how: This parameter specifies the type of merge to be performed. The options are:
• 'inner': Performs an inner join, retaining only the rows with matching keys in both DataFrames.
• 'outer': Performs an outer join, retaining all rows from both DataFrames and filling in missing values with NaN where necessary.
• 'left': Performs a left join, retaining all rows from the left DataFrame and filling in missing values with NaN from the right DataFrame.
• 'right': Performs a right join, retaining all rows from the right DataFrame and filling in missing values with NaN from the left DataFrame.
• on, left_on, right_on: These parameters are used to specify the columns on which to merge the DataFrames. You can either use on if the column names are the same in both DataFrames or use left_on and right_on if the column names differ.
• left_index and right_index: These parameters determine whether to use the index of the left or right DataFrame as the merge key. Set these parameters to True to use the index as the merge key instead of specifying columns.
• sort: Setting this parameter to True will sort the result by the columns used for merging.

4. Types of merges

As mentioned earlier, the how parameter of the merge function defines the type of merge to be performed. Let’s discuss the different types of merges with a brief explanation of each:

• Inner Merge (how='inner'): This type of merge retains only the rows with matching keys in both DataFrames. It essentially performs an intersection of the keys in the specified columns.
• Outer Merge (how='outer'): An outer merge retains all rows from both DataFrames and fills in missing values with NaN where keys don’t match.
• Left Merge (how='left'): In a left merge, all rows from the left DataFrame are retained, while the missing values are filled in with NaN from the right DataFrame.
• Right Merge (how='right'): A right merge is similar to a left merge, but this time, all rows from the right DataFrame are retained.

5. Examples of using the merge function

Now, let’s dive into some examples to illustrate how to use the merge function effectively.

Example 1: Inner Merge

Suppose we have two DataFrames, orders and customers, containing information about orders and customers respectively. We want to merge these DataFrames to get a complete view of which orders were placed by which customers.

import pandas as pd

# Sample data for the orders DataFrame
orders_data = {'order_id': [101, 102, 103, 104],
               'customer_id': ['C101', 'C102', 'C101', 'C103']}
orders = pd.DataFrame(orders_data)

# Sample data for the customers DataFrame
customers_data = {'customer_id': ['C101', 'C102', 'C103', 'C104'],
                  'customer_name': ['Alice', 'Bob', 'Charlie', 'David']}
customers = pd.DataFrame(customers_data)

# Performing an inner merge on 'customer_id'
merged_inner = pd.merge(orders, customers, on='customer_id', how='inner')

print(merged_inner)

In this example, we perform an inner merge on the ‘customer_id’ column, resulting in a DataFrame that contains only the orders with matching customer IDs in both DataFrames.

Example 2: Outer Merge

Continuing from the previous example, let’s perform an outer merge to see how it retains all rows from both DataFrames and fills in missing values with NaN.

# Performing an outer merge on 'customer_id'
merged_outer = pd.merge(orders, customers, on='customer_id', how='outer')

print(merged_outer)

In the outer merge, the resulting DataFrame includes all customer IDs from both DataFrames, and NaN is filled in where there are missing values.

6. Conclusion

The merge function in the pandas library is a powerful tool for combining DataFrames based on common columns or indices. It provides flexibility in choosing the type of merge and the columns on which to merge. By understanding the different types of merges and the function’s parameters, you can effectively consolidate and analyze data from multiple sources. This tutorial covered the syntax, parameters, and examples of using the merge function to help you get started with merging DataFrames in Python. As you become more proficient with pandas, you’ll find that the merge function is an essential tool in your data manipulation toolbox.