How to merge DataFrames using the merge() method in Python
The merge() method in Python is used to combine two or more DataFrames based on their common columns. The merge() method works similar to the JOIN operations in SQL. Merging multiple DataFrames is useful when we want to visualize different chunks of information from different DataFrames all in one place. It enables us to bring information together for further processing and analysis.
Syntax
pd.merge(left, right, how='inner', on=None)
Parameters
The merge() method takes the following parameters:
-
left: This is a DataFrame. -
right: This is another DataFrame. TheleftDataFrame is merged with therightDataFrame and a new DataFrame is returned.
Note: Nothing happens to the
leftandrightDataFrames, i.e., they do not change.
-
how: This parameter specifies the type of merge to be performed. The following are the types of merges available within themerge()method:left: This type of merge returns all the rows from theleftDataFrame. It also returns the rows from therightDataFrame that are in common with those of theleftDataFrame. If there are no rows in therightDataFrame that are in common (rows with similar values) with those of theleftDataFrame,NaNis returned. The merge is performed on a common column or columns.
Note: The number of rows and columns within the merged DataFrame may vary, i.e., it can increase, decrease, or stay the same. The type of merge being performed determines whether the number of rows and columns will change.
right: This type of merge returns all the rows from therightDataFrame. It also returns the rows from theleftDataFrame that are in common with those of therightDataFrame. If there are no rows in theleftDataFrame that are in common with those of therightDataFrame,NaNis returned. The merge is performed on a common column or columns.outer: This type of merge returns all the rows from theleftandrightDataFrames. This also includes the rows from theleftandrightDataFrames that are in common with each other. If no common rows are found,NaNis returned. The merge is performed on a common column or columns.inner: This is the default merge type. It returns only the rows that are common between theleftandrightDataFrames. If there are no common rows, an empty DataFrame is returned. The merge is performed on a common column or columns.cross: This type of merge returns the Cartesian product of the rows from theleftandrightDataFrames, i.e., it combines and returns each row from theleftDataFrame with each row from therightDataFrame. We don’t need to specify any common column or columns when using this merge type.
-
on: This parameter represents the column or columns to perform the merge on. These columns should be present in both theleftandrightDataFrames. Its default value isNone, i.e., no common column or columns are used to perform themerge()operation.
Code example
Let’s look at an example where we will create two DataFrames and then perform the merge() operation on them.
import pandas as pdleft_dataframe = pd.DataFrame({'id': [1, 2, 3, 4],'name': ['Leo', 'Jacob', 'James', 'Mason'],'age': [18, 20, 23, 19],'course': ['english', 'persian', 'arts', 'chemistry']})print('')right_dataframe = pd.DataFrame({'id': [1, 2, 3, 4],'name': ['William', 'Lucas', 'Henry', 'Elio'],'age': [18, 21, 26, 25],'course': ['english', 'persian', 'Literature', 'Physics']})print('')print('Left data frame')print(left_dataframe)print('')print('Right data frame')print(right_dataframe)print('')print('Left merge')print(pd.merge(left_dataframe, right_dataframe, how='left', on='course'))print('')print('Right merge')print(pd.merge(left_dataframe, right_dataframe, how='right', on='course'))print('')print('Outer merge')print(pd.merge(left_dataframe, right_dataframe, how='outer', on='course'))print('')print('Inner merge')print(pd.merge(left_dataframe, right_dataframe, how='inner', on=['age', 'course']))print('')print('Cross merge')print(pd.merge(left_dataframe, right_dataframe, how='cross'))print('')
Code explanation
-
Line 1: We import the required library, i.e.,
pandas. -
Lines 3–8: We create the
leftDataFrame and name itleft_dataframe. This DataFrame has columns namedid,name,age, andcourse. -
Lines 11–16: We create the
rightDataFrame and name itright_dataframe. This DataFrame has columns namedid,name,age, andcourse. -
Line 20: We print the
left_dataframeobject. -
Line 24: We print the
right_dataframeobject. -
Line 28: We merge the
left_dataframewith theright_dataframeusing theleftmerge method. We perform themerge()operation on thecoursecolumn. -
Line 32: We merge the
left_dataframewith theright_dataframeusing therightmerge method. We perform themerge()operation on thecoursecolumn. -
Line 36: We merge the
left_dataframewith theright_dataframeusing theoutermerge method. We perform themerge()operation on thecoursecolumn. -
Line 40: We merge the
left_dataframewith theright_dataframeusing theinnermerge method. We perform themerge()operation on theageandcoursecolumns. -
Line 44: We merge the
left_dataframewith theright_dataframeusing thecrossmerge method. As discussed previously, we do not need to provide any common column or columns when using this merge type.
Test yourself
Let’s test out understanding of the merge() method in Python by solving this multiple-choice question.
Imagine we work for a supermarket with two separate databases—one for customer information and another for orders. The customer database includes customer IDs, names, addresses, and other details, while the order database includes order IDs, customer IDs, product details, and order dates. Which method would we use to get a list of all the customers who have placed orders?
left merge
right merge
inner merge
outer merge
Free Resources
