Pandas merge()

How to Merge Two DataFrame Objects in Pandas?

A DataFrame object in pandas is a two-dimensional, size-mutable, and heterogeneous tabular data structure that allows you to store and manipulate data in a way similar to a spreadsheet or an SQL table.

In pandas, you can merge pandas objects using the merge() function.

Code:

Output:

Pandas DataFrame merge() Method (definition)

The merge() in pandas allows combining two DataFrame objects based on shared columns or indices.

It offers a comprehensive way to integrate data, catering to various merging strategies and complex scenarios.

Hiring Partners:

GoogleAmazonMicrosoftFlipkartAdobe1200+ more

Syntax

Below is the syntax of merge() in python:

Example code:

Output:

Explanation:

The code uses Pandas to merge in pandas two DataFrames ('df1' and 'df2') based on the 'ID' column, performing an outer join, adding '_left' and '_right' suffixes to overlapping columns, and indicating the merge status.

Parameter

Based on the syntax, below are the parameters:

• right: DataFrame to merge with.
• how: Merge type ('inner', 'outer', 'left', 'right').
• on, left_on, right_on: Columns to merge on.
• left_index, right_index: Use indices for merging.
• sort: Sort merged result.
• suffixes: Suffixes for overlapping columns.
• copy: Copy data even if not modified.
• indicator: Add '_merge' column to indicate source.
• validate: Validate merge operation.

Return Value

The return value of the merge() in pandas is a new DataFrame that contains the merged data from the input DataFrames based on the specified merging criteria.

This merged DataFrame consolidates information from both original DataFrames, combining rows that share common values in the specified columns or indices.

Code:

Output:

Explanation:

This code utilizes the Pandas library to merge in pandas, 'df1' and 'df2', based on their 'ID' columns using an outer join.

The result, stored in 'merged_df', contains the combined data from both DataFrames.

The 'suffixes' parameter appends '_left' and '_right' to overlapping column names in the original DataFrames, distinguishing their sources.

The 'indicator' parameter adds a special column '_merge' to the merged DataFrame, indicating the origin of each row (whether it came from the left DataFrame, right DataFrame, or both).

The resulting output displays the merged data, where the 'ID' column serves as the primary key and the 'Name' and 'Age' columns are combined. Rows with IDs present in only one DataFrame are labeled 'left_only' or 'right_only' accordingly.

Pandas DataFrame merge() Examples

Let us consider the below dataframes to perform the operations:

Code:

Output:

1. Default Merging - inner join

   Code:

   Output:

   Explanation:

   The given code employs Pandas to perform an inner join between 'df1' and 'df2' based on their 'ID' column.

   The result, stored in 'merged_inner', includes only the rows with matching 'ID' values in both DataFrames. The output showcases the merged DataFrame, containing columns 'ID', 'City', and 'Population', with information from the rows where 'ID' values are shared between the two DataFrames.

2. Merging DataFrames with Left, Right, and Outer Join

   Code:

   Output:

   Explanation:

   The provided code uses Pandas to demonstrate different types of joins between 'df1' and 'df2' based on their 'ID' column.

   • A left join (merged_left) includes all rows from the left DataFrame ('df1') and matching rows from the right DataFrame ('df2').

   • A right join (merged_right) includes all rows from the right DataFrame and matching rows from the left DataFrame.

   • An outer join (merged_outer) includes all rows from both DataFrames, filling in missing values with NaN where applicable.

3. Merging DataFrame on Specific Columns

   Code:

   Output:

   Explanation:

   This code uses Pandas to perform an inner join on the 'Country' column between 'df1' and 'df2'. The output merged DataFrame, 'merged_country', contains rows where the 'Country' values match in both DataFrames. It displays the 'ID', 'City', 'Country', and 'Population' columns, representing information associated with the matching countries, such as the USA and Canada. This is how we perform the merge in pandas.

4. Specify Left and Right Columns for Merging DataFrame Objects

   Code:

   Output:

   Explanation:

   This code employs Pandas to perform an inner join on specific columns, 'ID' from 'df1' and 'Population' from 'df2'. However, there are no matching values between these columns, resulting in an empty DataFrame as shown in the output. The columns in the output DataFrame are labeled with '_x' and '_y' suffixes to distinguish between the two original DataFrames.

5. Using Index as the Join Keys for Merging DataFrames

   Code:

   Output:

   Explanation:

   This code utilizes Pandas to perform an inner join using the indices of 'df1' and 'df2' as join keys. The output merged DataFrame, 'merged_index', includes rows where the indices of both DataFrames match. The output displays columns 'City', 'Country', and 'Population' representing the merged data from the corresponding indices, such as 'Los Angeles' and 'Chicago', with their associated countries and populations.