Pandas DataFrame merge() Method

Preparation

Before any data manipulation can occur, two (2) new libraries will require installation.

• The Pandas library enables access to/from a DataFrame.
• The NumPy library supports multi-dimensional arrays and matrices in addition to a collection of mathematical functions.

To install these libraries, navigate to an IDE terminal. At the command prompt ($), execute the code below. For the terminal used in this example, the command prompt is a dollar sign ($). Your terminal prompt may be different.

$ pip install pandas

Hit the <Enter> key on the keyboard to start the installation process.

$ pip install numpy

Hit the <Enter> key on the keyboard to start the installation process.

If the installations were successful, a message displays in the terminal indicating the same.

FeFeel free to view the PyCharm installation guide for the required libraries.

• How to Install Pandas on PyCharm
• How to Install NumPy on PyCharm

Add the following code to the top of each code snippet. This snippet will allow the code in this article to run error-free.

import pandas as pd
import numpy

DataFrame merge()

The merge() method joins the DataFrame(s) on columns/indexes.

Points to note:

• If joining columns, the DataFrame indexes ignore.
• If joining indexes, the index passes on.
• If cross-merge, no column specs to merge done.

💡 Note: If the key columns contain rows where the key is NULL (empty), the rows match against each other.

This method does not act like a SQL join.

The syntax for this method is as follows:

DataFrame.merge(right, how='inner', on=None, left_on=None, right_on=None, 
                left_index=False, right_index=False, sort=False, 
                suffixes=('_x', '_y'), copy=True, indicator=False, validate=None)

For these examples, merge using various options appear below (see above for details).

The inner option returns rows df1 and df2 that share a common key.

Code – Inner Join

df1 = pd.DataFrame({'key': ['Germany', 'France', 'Spain'], 'value': [83783942, 67081000, 47431256]})
df2 = pd.DataFrame({'key': ['DE', 'France', 'Spain'], 'value': [49, 33, 34]})

result = df1.merge(df2, on='key', how='inner')
print(result)

• Line [1] creates a DataFrame from a Dictionary of Lists and saves it to df1.
• Line [2] creates a DataFrame from a Dictionary of Lists and saves it to df2.
• Line [3] merges the two (2) DataFrames on the key using the inner option.
• Line [4] outputs the result to the terminal.

Output

The details for Germany (in df1) and DE (in df2) do not display.

Note: To rename the column names, use the rename() method.

The outer option uses the keys from the DataFrames (df1 and df2). A NaN value adds for missing data in both DataFrames.

Code – Outer Join

df1 = pd.DataFrame({'key': ['Germany', 'France', 'Spain'], 'value': [83783942, 67081000, 47431256]})
df2 = pd.DataFrame({'key': ['DE', 'France', 'Spain'], 'value': [49, 33, 34]})

result = df1.merge(df2, on='key', how='outer')
print(result)

• Line [1] creates a DataFrame from a Dictionary of Lists and saves it to df1.
• Line [2] creates a DataFrame from a Dictionary of Lists and saves it to df2.
• Line [3] merges the two (2) DataFrames on the key using the outer option.
• Line [4] outputs the result to the terminal.

Output

Details for Germany (value_y field) and DE (value_x field) display NaN values.

The left option uses the keys from df1. A NaN value adds for missing data in both DataFrames.

Code – Left Join

df1 = pd.DataFrame({'key': ['Germany', 'France', 'Spain'], 'value': [83783942, 67081000, 47431256]})
df2 = pd.DataFrame({'key': ['DE', 'France', 'Spain'], 'value': [49, 33, 34]})

result = df1.merge(df2, on='key', how='left')
print(result)

• Line [1] creates a DataFrame from a Dictionary of Lists and saves it to df1.
• Line [2] creates a DataFrame from a Dictionary of Lists and saves it to df2.
• Line [3] merges the two (2) DataFrames on the key using the left option.
• Line [4] outputs the result to the terminal.

Output:

The row DE does not display. The Germany value_y field displays NaN.

The right option takes keys from the df2. Missing data from df1 changes to NaN value(s).

Code – Right Join

df1 = pd.DataFrame({'key': ['Germany', 'France', 'Spain'], 'value': [83783942, 67081000, 47431256]})
df2 = pd.DataFrame({'key': ['DE', 'France', 'Spain'], 'value': [49, 33, 34]})

result = df1.merge(df2, on='key', how='right')
print(result)

• Line [1] creates a DataFrame from a Dictionary of Lists and saves it to df1.
• Line [2] creates a DataFrame from a Dictionary of Lists and saves it to df2.
• Line [3] merges the two (2) DataFrames on the key using the right option.
• Line [4] outputs the result to the terminal.

Output

The row for Germany does not display. The value_x of DE displays NaN.

More Pandas DataFrame Methods

Feel free to learn more about the previous and next pandas DataFrame methods (alphabetically) here:

Also, check out the full cheat sheet overview of all Pandas DataFrame methods.