When you merge two or more DataFrames in pandas, and you want to have an indicator column to tell the information on the source of each row, then you can make use of the parameter indicator=True along with the pandas.merge() function.
Example: Pandas: Merge Indicator (Left, Right, and Both)
import pandas as pd
data_city_NYC = {
'City': ['NYC', 'NYC', 'NYC', 'NYC'],
'Date': ['2023-07-01', '2023-07-02', '2023-07-03', '2023-07-04'],
'Temp_NYC': [25, 30, 27, 0]
}
data_city_Chicago = {
'City': ['Chicago', 'Chicago', 'Chicago', 'Chicago'],
'Date': ['2023-07-01', '2023-07-02', '2023-07-03', '2023-07-06'],
'Temp_Chicago': [28, 32, 29, 0]
}
df_city_NYC = pd.DataFrame(data_city_NYC).set_index('Date')
df_city_Chicago = pd.DataFrame(data_city_Chicago).set_index('Date')
print("DataFrame: NYC City")
print(df_city_NYC)
print("\nDataFrame: Chicago City")
print(df_city_Chicago)
# Merge both DataFrames on the common column 'Date' and add an indicator
merged_cities_both_df = pd.merge(df_city_NYC, df_city_Chicago, on='Date', how='outer', suffixes=('_NYC', '_Chicago'), indicator=True)
# Merge left DataFrames on the common column 'Date' and add an indicator
merged_cities_left_df = pd.merge(df_city_NYC, df_city_Chicago, on='Date', how='left', suffixes=('_NYC', '_Chicago'), indicator=True)
# Merge right DataFrames on the common column 'Date' and add an indicator
merged_cities_right_df = pd.merge(df_city_NYC, df_city_Chicago, on='Date', how='right', suffixes=('_NYC', '_Chicago'), indicator=True)
print("\nMerged DataFrame Data (Merge Both):")
print(merged_cities_both_df)
print("\nMerged DataFrame Data (Merge Left):")
print(merged_cities_left_df)
print("\nMerged DataFrame Data (Merge Right):")
print(merged_cities_right_df)
Output:
DataFrame: NYC City
City Temp_NYC
Date
2023-07-01 NYC 25
2023-07-02 NYC 30
2023-07-03 NYC 27
2023-07-04 NYC 0
DataFrame: Chicago City
City Temp_Chicago
Date
2023-07-01 Chicago 28
2023-07-02 Chicago 32
2023-07-03 Chicago 29
2023-07-06 Chicago 0
Merged DataFrame Data (Merge Both):
City_NYC Temp_NYC City_Chicago Temp_Chicago _merge
Date
2023-07-01 NYC 25.0 Chicago 28.0 both
2023-07-02 NYC 30.0 Chicago 32.0 both
2023-07-03 NYC 27.0 Chicago 29.0 both
2023-07-04 NYC 0.0 NaN NaN left_only
2023-07-06 NaN NaN Chicago 0.0 right_only
Merged DataFrame Data (Merge Left):
City_NYC Temp_NYC City_Chicago Temp_Chicago _merge
Date
2023-07-01 NYC 25 Chicago 28.0 both
2023-07-02 NYC 30 Chicago 32.0 both
2023-07-03 NYC 27 Chicago 29.0 both
2023-07-04 NYC 0 NaN NaN left_only
Merged DataFrame Data (Merge Right):
City_NYC Temp_NYC City_Chicago Temp_Chicago _merge
Date
2023-07-01 NYC 25.0 Chicago 28 both
2023-07-02 NYC 30.0 Chicago 32 both
2023-07-03 NYC 27.0 Chicago 29 both
2023-07-06 NaN NaN Chicago 0 right_only
Merge Indicator Values
_merge | Merge Indicator Description |
---|---|
both | The row is present in both left and right DataFrames after the merge. |
left_only | The row is present only in the left DataFrame after the merge. |
right_only | The row is present only in the right DataFrame after the merge. |
Facing issues? Have Questions? Post them here! I am happy to answer!
Author Info:
Rakesh (He/Him) has over 14+ years of experience in Web and Application development. He is the author of insightful How-To articles for Code2care.
Follow him on: X
You can also reach out to him via e-mail: rakesh@code2care.org
More Posts related to Python,
- How to convert int to ASCII in Python
- How to make use of SQLite Module in Python?
- Split a String into Sub-string and Parse in Python
- Python: Pandas Rename Columns with List Example
- How to run Python file from Mac Terminal
- How to Exit a Loop in Python Code
- Python: How to Plot a Histogram using Matplotlib and data as list
- MD5 Hashing in Python
- Jupyter: Safari Cant Connect to the Server localhost:8888/tree
- Fix: AttributeError: str object has no attribute decode. Did you mean: encode?[Python]
- How to Read a binary File with Python
- How to add two float numbers in Python
- Python: How to install YAML Package
- Python: How to Save Image from URL
- What is Markdown in Jupyter Notebook with Examples
- How to change the Python Default version
- 33: Python Program to send an email vid GMail
- How to comment code in Python
- How to Fix AttributeError in Python
- Fix: error: Jupyter command `jupyter-nbconvert` not found [VSCode]
- How to comment out a block of code in Python
- List of All 35 Reserved Keywords in Python Programming Language 3.11
- Import Other Python Files Examples
- Python: How to add Progress Bar in Console with Examples
- 3 Ways to convert bytes to String in Python
More Posts:
- Error: Can not find the tag library descriptor for - Java
- Fix: Eclipse Cant Connect to any repository not Authorized Error GitHub - Eclipse
- Command to know the Available Memory on Linux System - Linux
- How to Change Eclipse Default Web Browser - Eclipse
- REST API to get all Jira projects - Postman, Basic Auth - Jira
- How to create SharePoint Document Library - SharePoint
- Examples: Convert String to int in JavaScript - JavaScript
- [Python] Fix: ValueError: All arrays must be of the same length - Python