Scaler
Academy
Data Science
AI/ML
DevOps
Neovarsity
New
Skill Test
Free Masterclass
Search for Articles, Topics
Pandas merge()

Pandas merge()

By Ishika Kulkarni
7 mins read
Last updated: 21 Sep 2023
161 views
Learn via video courses
Topics Covered

Overview

In Python data analysis, pandas merging capabilities stand out. Merging involves combining data from separate datasets, by aligning rows using common columns or indices. This consolidation simplifies the exploration of data spread across different tables or files.

The merge method allows you to specify how you want the merging to be done, such as keeping only common rows ('inner'), including all rows from both DataFrames ('outer'), or retaining all rows from one DataFrame ('left' or 'right'). You can also control which columns to merge on using the on, left_on, and right_on parameters.

If you want to merge based on indices instead of columns, you can use the left_index and right_index parameters. Additionally, you can provide suffixes to distinguish overlapping column names.

This process finds significance in situations like merging player scores and player information datasets based on shared player IDs to build a comprehensive analysis-ready dataset.

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.

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.

Conclusion

  • Merge in pandas is essential for consolidating data from different sources by joining rows based on common columns or indices.
  • It streamlines data exploration across various tables, enabling diverse merging strategies.
  • The merge() method combines two DataFrames, with flexibility in choosing merge types, columns, and indices.
  • Merge in pandas is based on indices, offers column suffixes for overlapping names, and even an "_merge" indicator column.
  • This merging process is vital for scenarios such as integrating player scores and information based on shared IDs for robust analysis-ready datasets.