Pandas Merge

The merge operation in Pandas merges two DataFrames based on their indexes or a specified column.

The merge() in Pandas works similar to JOINs in SQL.

Let's see an example.

 import pandas as pd # create dataframes from the dictionaries data1 = { 'EmployeeID' : ['E001', 'E002', 'E003', 'E004', 'E005'], 'Name' : ['John Doe', 'Jane Smith', 'Peter Brown', 'Tom Johnson', 'Rita Patel'], 'DeptID': ['D001', 'D003', 'D001', 'D002', 'D003'], } employees = pd.DataFrame(data1) data2 = { 'DeptID': ['D001', 'D002', 'D003'], 'DeptName': ['Sales', 'HR', 'Admin'] } departments = pd.DataFrame(data2) 
# merge dataframes employees and departments merged_df = pd.merge(employees, departments)
 # display DataFrames print("Employees:") print(employees) print() print("Departments:") print(departments) print() print("Merged DataFrame:") print(merged_df)

Output

 Employees: EmployeeID Name DeptID 0 E001 John Doe D001 1 E002 Jane Smith D003 2 E003 Peter Brown D001 3 E004 Tom Johnson D002 4 E005 Rita Patel D003 Departments: DeptID DeptName 0 D001 Sales 1 D002 HR 2 D003 Admin Merged DataFrame: EmployeeID Name DeptID DeptName 0 E001 John Doe D001 Sales 1 E003 Peter Brown D001 Sales 2 E002 Jane Smith D003 Admin 3 E005 Rita Patel D003 Admin 4 E004 Tom Johnson D002 HR

In this example, we merged the DataFrames employees and departments using the merge() method.

Notice that the two DataFrames are merged based on the DeptID column as it's common to both the DataFrames.

merge() Syntax in Pandas

The syntax of the merge() method in Pandas is:

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

Here,

• left: specifies the left DataFrame to be merged
• right: specifies the right DataFrame to be merged
• on (optional): specifies column(s) to join on
• how (optional): specifies the type of join to perform
• left_on (optional): specifies column(s) from the left DataFrame to use as key(s) for merging
• right_on (optional): specifies column(s) from the right DataFrame to use as key(s) for merging
• sort (optional): if True, sort the result DataFrame by the join keys

Example: Merge DataFrames Based on Keys

When there are no common columns between two DataFrames, we can merge them by specifying the columns (as keys) in the left_on and right_on arguments. For example,

 import pandas as pd # create dataframes from the dictionaries data1 = { 'EmployeeID': ['E001', 'E002', 'E003', 'E004', 'E005'], 'Name': ['John Doe', 'Jane Smith', 'Peter Brown', 'Tom Johnson', 'Rita Patel'], 'DeptID1': ['D001', 'D003', 'D001', 'D002', 'D006'], } employees = pd.DataFrame(data1) data2 = { 'DeptID2': ['D001', 'D002', 'D003', 'D004'], 'DeptName': ['Sales', 'HR', 'Admin', 'Marketing'] } departments = pd.DataFrame(data2) 
# merge the dataframes df_merge = pd.merge(employees, departments, left_on='DeptID1', right_on = 'DeptID2', sort = True)
 print(df_merge)

Output

  EmployeeID Name DeptID1 DeptID2 DeptName 0 E001 John Doe D001 D001 Sales 1 E003 Peter Brown D001 D001 Sales 2 E004 Tom Johnson D002 D002 HR 3 E002 Jane Smith D003 D003 Admin

In the above example, we performed a merge operation on two DataFrames employees and departments using the merge() method with various arguments.

Here, we used DeptID1 and DeptID2 as the key for merging the DataFrames. Then, we sorted the resulting DataFrame using sort = True.

Types of Join Operations In merge()

So far, we've not defined how to merge the dataframes, thus it defaults to an inner join.

However, we can specify the join type in the how argument. Here are the 5 join types we can use in the merge() method:

• Left Join
• Right Join
• Outer Join
• Inner Join (Default)
• Cross Join

Left Join

A left join combines two DataFrames based on a common key and returns a new DataFrame that contains all rows from the left DataFrame and the matched rows from the right DataFrame.

If values are not found in the right dataframe, it fills the space with NaN. For example,

 import pandas as pd # create dataframes from the dictionaries data1 = { 'EmployeeID': ['E001', 'E002', 'E003', 'E004', 'E005'], 'Name': ['John Doe', 'Jane Smith', 'Peter Brown', 'Tom Johnson', 'Rita Patel'], 'DeptID': ['D001', 'D003', 'D001', 'D002', 'D006'], } employees = pd.DataFrame(data1) data2 = { 'DeptID': ['D001', 'D002', 'D003', 'D004'], 'DeptName': ['Sales', 'HR', 'Admin', 'Marketing'] } departments = pd.DataFrame(data2) 
# left merge the dataframes df_merge = pd.merge(employees, departments, on = 'DeptID', how = 'left', sort = True)
 print(df_merge)

Output

  EmployeeID Name DeptID DeptName 0 E001 John Doe D001 Sales 1 E003 Peter Brown D001 Sales 2 E004 Tom Johnson D002 HR 3 E002 Jane Smith D003 Admin 4 E005 Rita Patel D006 NaN

Right Join

A right join is the opposite of a left join. It returns a new DataFrame that contains all rows from the right DataFrame and the matched rows from the left DataFrame.

If values are not found in the left dataframe, it fills the space with NaN. For example,

 import pandas as pd # create dataframes from the dictionaries data1 = { 'EmployeeID': ['E001', 'E002', 'E003', 'E004', 'E005'], 'Name': ['John Doe', 'Jane Smith', 'Peter Brown', 'Tom Johnson', 'Rita Patel'], 'DeptID': ['D001', 'D003', 'D001', 'D002', 'D006'], } employees = pd.DataFrame(data1) data2 = { 'DeptID': ['D001', 'D002', 'D003', 'D004'], 'DeptName': ['Sales', 'HR', 'Admin', 'Marketing'] } departments = pd.DataFrame(data2) 
# right merge the dataframes df_merge = pd.merge(employees, departments, on = 'DeptID', how = 'right', sort = True)
 print(df_merge)

Output

  EmployeeID Name DeptID DeptName 0 E001 John Doe D001 Sales 1 E003 Peter Brown D001 Sales 2 E004 Tom Johnson D002 HR 3 E002 Jane Smith D003 Admin 4 NaN NaN D004 Marketing

Inner Join

An inner join combines two DataFrames based on a common key and returns a new DataFrame that contains only rows that have matching values in both of the original DataFrames.

For example,

 import pandas as pd # create dataframes from the dictionaries data1 = { 'EmployeeID': ['E001', 'E002', 'E003', 'E004', 'E005'], 'Name': ['John Doe', 'Jane Smith', 'Peter Brown', 'Tom Johnson', 'Rita Patel'], 'DeptID': ['D001', 'D003', 'D001', 'D002', 'D006'], } employees = pd.DataFrame(data1) data2 = { 'DeptID': ['D001', 'D002', 'D003', 'D004'], 'DeptName': ['Sales', 'HR', 'Admin', 'Marketing'] } departments = pd.DataFrame(data2) 
# inner merge the dataframes df_merge = pd.merge(employees, departments, on = 'DeptID', how = 'inner', sort = True)
 print(df_merge)

Output

  EmployeeID Name DeptID DeptName 0 E001 John Doe D001 Sales 1 E003 Peter Brown D001 Sales 2 E004 Tom Johnson D002 HR 3 E002 Jane Smith D003 Admin

Outer Join

An outer join combines two DataFrames based on a common key. Unlike an inner join, an outer join returns a new DataFrame that contains all rows from both original DataFrames.

If values are not found in the DataFrames, it fills the space with NaN.

For example,

 import pandas as pd # create dataframes from the dictionaries data1 = { 'EmployeeID': ['E001', 'E002', 'E003', 'E004', 'E005'], 'Name': ['John Doe', 'Jane Smith', 'Peter Brown', 'Tom Johnson', 'Rita Patel'], 'DeptID': ['D001', 'D003', 'D001', 'D002', 'D006'], } employees = pd.DataFrame(data1) data2 = { 'DeptID': ['D001', 'D002', 'D003', 'D004'], 'DeptName': ['Sales', 'HR', 'Admin', 'Marketing'] } departments = pd.DataFrame(data2) 
# outer merge the dataframes df_merge = pd.merge(employees, departments, on = 'DeptID', how = 'outer', sort = True)
 print(df_merge)

Output

  EmployeeID Name DeptID DeptName 0 E001 John Doe D001 Sales 1 E003 Peter Brown D001 Sales 2 E004 Tom Johnson D002 HR 3 E002 Jane Smith D003 Admin 4 NaN NaN D004 Marketing 5 E005 Rita Patel D006 NaN

Cross Join

A cross join in Pandas creates the cartesian product of both DataFrames while preserving the order of the left DataFrame.

For example,

 import pandas as pd # create dataframes from the dictionaries data1 = { 'EmployeeID': ['E001', 'E002', 'E003', 'E004', 'E005'], 'Name': ['John Doe', 'Jane Smith', 'Peter Brown', 'Tom Johnson', 'Rita Patel'], 'DeptID': ['D001', 'D003', 'D001', 'D002', 'D006'], } employees = pd.DataFrame(data1) data2 = { 'DeptID': ['D001', 'D002', 'D003', 'D004'], 'DeptName': ['Sales', 'HR', 'Admin', 'Marketing'] } departments = pd.DataFrame(data2) 
# merge the dataframes df_merge = pd.merge(employees, departments, how = 'cross')
 print(df_merge)

Output

  EmployeeID Name DeptID_x DeptID_y DeptName 0 E001 John Doe D001 D001 Sales 1 E001 John Doe D001 D002 HR 2 E001 John Doe D001 D003 Admin 3 E001 John Doe D001 D004 Marketing 4 E002 Jane Smith D003 D001 Sales 5 E002 Jane Smith D003 D002 HR 6 E002 Jane Smith D003 D003 Admin 7 E002 Jane Smith D003 D004 Marketing 8 E003 Peter Brown D001 D001 Sales 9 E003 Peter Brown D001 D002 HR 10 E003 Peter Brown D001 D003 Admin 11 E003 Peter Brown D001 D004 Marketing 12 E004 Tom Johnson D002 D001 Sales 13 E004 Tom Johnson D002 D002 HR 14 E004 Tom Johnson D002 D003 Admin 15 E004 Tom Johnson D002 D004 Marketing 16 E005 Rita Patel D006 D001 Sales 17 E005 Rita Patel D006 D002 HR 18 E005 Rita Patel D006 D003 Admin 19 E005 Rita Patel D006 D004 Marketing

Join vs Merge vs Concat

There are three different methods to combine DataFrames in Pandas:

• join(): joins two DataFrames based on their indexes, performs left join by default
• merge(): joins two DataFrames based on any specified columns, performs inner join by default
• concat(): stacks two DataFrames along the vertical or horizontal axis