[python] Pandas Merging 101

  • How to perform a (INNER| (LEFT|RIGHT|FULL) OUTER) JOIN with pandas?
  • How do I add NaNs for missing rows after merge?
  • How do I get rid of NaNs after merging?
  • Can I merge on the index?
  • How do I merge multiple DataFrames?
  • Cross join with pandas?
  • merge? join? concat? update? Who? What? Why?!

... and more. I've seen these recurring questions asking about various facets of the pandas merge functionality. Most of the information regarding merge and its various use cases today is fragmented across dozens of badly worded, unsearchable posts. The aim here is to collate some of the more important points for posterity.

This QnA is meant to be the next installment in a series of helpful user-guides on common pandas idioms (see this post on pivoting, and this post on concatenation, which I will be touching on, later).

Please note that this post is not meant to be a replacement for the documentation, so please read that as well! Some of the examples are taken from there.



Table of Contents

For ease of access.

This question is related to python pandas join merge concatenation

The answer is


This post will go through the following topics:

  • how to correctly generalize to multiple DataFrames (and why merge has shortcomings here)
  • merging on unique keys
  • merging on non-unqiue keys

BACK TO TOP



Generalizing to multiple DataFrames

Oftentimes, the situation arises when multiple DataFrames are to be merged together. Naively, this can be done by chaining merge calls:

df1.merge(df2, ...).merge(df3, ...)

However, this quickly gets out of hand for many DataFrames. Furthermore, it may be necessary to generalise for an unknown number of DataFrames.

Here I introduce pd.concat for multi-way joins on unique keys, and DataFrame.join for multi-way joins on non-unique keys. First, the setup.

# Setup.
np.random.seed(0)
A = pd.DataFrame({'key': ['A', 'B', 'C', 'D'], 'valueA': np.random.randn(4)})    
B = pd.DataFrame({'key': ['B', 'D', 'E', 'F'], 'valueB': np.random.randn(4)})
C = pd.DataFrame({'key': ['D', 'E', 'J', 'C'], 'valueC': np.ones(4)})
dfs = [A, B, C] 

# Note, the "key" column values are unique, so the index is unique.
A2 = A.set_index('key')
B2 = B.set_index('key')
C2 = C.set_index('key')

dfs2 = [A2, B2, C2]

Multiway merge on unique keys

If your keys (here, the key could either be a column or an index) are unique, then you can use pd.concat. Note that pd.concat joins DataFrames on the index.

# merge on `key` column, you'll need to set the index before concatenating
pd.concat([
    df.set_index('key') for df in dfs], axis=1, join='inner'
).reset_index()

  key    valueA    valueB  valueC
0   D  2.240893 -0.977278     1.0

# merge on `key` index
pd.concat(dfs2, axis=1, sort=False, join='inner')

       valueA    valueB  valueC
key                            
D    2.240893 -0.977278     1.0

Omit join='inner' for a FULL OUTER JOIN. Note that you cannot specify LEFT or RIGHT OUTER joins (if you need these, use join, described below).


Multiway merge on keys with duplicates

concat is fast, but has its shortcomings. It cannot handle duplicates.

A3 = pd.DataFrame({'key': ['A', 'B', 'C', 'D', 'D'], 'valueA': np.random.randn(5)})
pd.concat([df.set_index('key') for df in [A3, B, C]], axis=1, join='inner')
ValueError: Shape of passed values is (3, 4), indices imply (3, 2)

In this situation, we can use join since it can handle non-unique keys (note that join joins DataFrames on their index; it calls merge under the hood and does a LEFT OUTER JOIN unless otherwise specified).

# join on `key` column, set as the index first
# For inner join. For left join, omit the "how" argument.
A.set_index('key').join(
    [df.set_index('key') for df in (B, C)], how='inner').reset_index()

  key    valueA    valueB  valueC
0   D  2.240893 -0.977278     1.0

# join on `key` index
A3.set_index('key').join([B2, C2], how='inner')

       valueA    valueB  valueC
key                            
D    1.454274 -0.977278     1.0
D    0.761038 -0.977278     1.0


Continue Reading

Jump to other topics in Pandas Merging 101 to continue learning:

* you are here


In this answer, I will consider practical examples.

The first one, is of pandas.concat.

The second one, of merging dataframes from the index of one and the column of another one.


1. pandas.concat

Considering the following DataFrames with the same column names:

Preco2018 with size (8784, 5)

DataFrame 1

Preco 2019 with size (8760, 5)

DataFrame 2

That have the same column names.

You can combine them using pandas.concat, by simply

import pandas as pd

frames = [Preco2018, Preco2019]

df_merged = pd.concat(frames)

Which results in a DataFrame with the following size (17544, 5)

DataFrame result of the combination of two dataframes

If you want to visualize, it ends up working like this

How concat works

(Source)


2. Merge by Column and Index

In this part, I will consider a specific case: If one wants to merge the index of one dataframe and the column of another dataframe.

Let's say one has the dataframe Geo with 54 columns, being one of the columns the Date Data, which is of type datetime64[ns].

enter image description here

And the dataframe Price that has one column with the price and the index corresponds to the dates

enter image description here

In this specific case, to merge them, one uses pd.merge

merged = pd.merge(Price, Geo, left_index=True, right_on='Data')

Which results in the following dataframe

enter image description here


A supplemental visual view of pd.concat([df0, df1], kwargs). Notice that, kwarg axis=0 or axis=1 's meaning is not as intuitive as df.mean() or df.apply(func)


on pd.concat([df0, df1])


This post will go through the following topics:

  • Merging with index under different conditions
    • options for index-based joins: merge, join, concat
    • merging on indexes
    • merging on index of one, column of other
  • effectively using named indexes to simplify merging syntax

BACK TO TOP



Index-based joins

TL;DR

There are a few options, some simpler than others depending on the use case.

  1. DataFrame.merge with left_index and right_index (or left_on and right_on using names indexes)
    • supports inner/left/right/full
    • can only join two at a time
    • supports column-column, index-column, index-index joins
  2. DataFrame.join (join on index)
    • supports inner/left (default)/right/full
    • can join multiple DataFrames at a time
    • supports index-index joins
  3. pd.concat (joins on index)
    • supports inner/full (default)
    • can join multiple DataFrames at a time
    • supports index-index joins

Index to index joins

Setup & Basics

import pandas as pd
import numpy as np

np.random.seed([3, 14])
left = pd.DataFrame(data={'value': np.random.randn(4)}, 
                    index=['A', 'B', 'C', 'D'])    
right = pd.DataFrame(data={'value': np.random.randn(4)},  
                     index=['B', 'D', 'E', 'F'])
left.index.name = right.index.name = 'idxkey'

left
           value
idxkey          
A      -0.602923
B      -0.402655
C       0.302329
D      -0.524349

right
 
           value
idxkey          
B       0.543843
D       0.013135
E      -0.326498
F       1.385076

Typically, an inner join on index would look like this:

left.merge(right, left_index=True, right_index=True)

         value_x   value_y
idxkey                    
B      -0.402655  0.543843
D      -0.524349  0.013135

Other joins follow similar syntax.

Notable Alternatives

  1. DataFrame.join defaults to joins on the index. DataFrame.join does a LEFT OUTER JOIN by default, so how='inner' is necessary here.

     left.join(right, how='inner', lsuffix='_x', rsuffix='_y')
    
              value_x   value_y
     idxkey                    
     B      -0.402655  0.543843
     D      -0.524349  0.013135
    

    Note that I needed to specify the lsuffix and rsuffix arguments since join would otherwise error out:

     left.join(right)
     ValueError: columns overlap but no suffix specified: Index(['value'], dtype='object')
    

    Since the column names are the same. This would not be a problem if they were differently named.

     left.rename(columns={'value':'leftvalue'}).join(right, how='inner')
    
             leftvalue     value
     idxkey                     
     B       -0.402655  0.543843
     D       -0.524349  0.013135
    
  2. pd.concat joins on the index and can join two or more DataFrames at once. It does a full outer join by default, so how='inner' is required here..

     pd.concat([left, right], axis=1, sort=False, join='inner')
    
                value     value
     idxkey                    
     B      -0.402655  0.543843
     D      -0.524349  0.013135
    

    For more information on concat, see this post.


Index to Column joins

To perform an inner join using index of left, column of right, you will use DataFrame.merge a combination of left_index=True and right_on=....

right2 = right.reset_index().rename({'idxkey' : 'colkey'}, axis=1)
right2
 
  colkey     value
0      B  0.543843
1      D  0.013135
2      E -0.326498
3      F  1.385076

left.merge(right2, left_index=True, right_on='colkey')

    value_x colkey   value_y
0 -0.402655      B  0.543843
1 -0.524349      D  0.013135

Other joins follow a similar structure. Note that only merge can perform index to column joins. You can join on multiple columns, provided the number of index levels on the left equals the number of columns on the right.

join and concat are not capable of mixed merges. You will need to set the index as a pre-step using DataFrame.set_index.


Effectively using Named Index [pandas >= 0.23]

If your index is named, then from pandas >= 0.23, DataFrame.merge allows you to specify the index name to on (or left_on and right_on as necessary).

left.merge(right, on='idxkey')

         value_x   value_y
idxkey                    
B      -0.402655  0.543843
D      -0.524349  0.013135

For the previous example of merging with the index of left, column of right, you can use left_on with the index name of left:

left.merge(right2, left_on='idxkey', right_on='colkey')

    value_x colkey   value_y
0 -0.402655      B  0.543843
1 -0.524349      D  0.013135


Continue Reading

Jump to other topics in Pandas Merging 101 to continue learning:

* you are here


Examples related to python

programming a servo thru a barometer Is there a way to view two blocks of code from the same file simultaneously in Sublime Text? python variable NameError Why my regexp for hyphenated words doesn't work? Comparing a variable with a string python not working when redirecting from bash script is it possible to add colors to python output? Get Public URL for File - Google Cloud Storage - App Engine (Python) Real time face detection OpenCV, Python xlrd.biffh.XLRDError: Excel xlsx file; not supported Could not load dynamic library 'cudart64_101.dll' on tensorflow CPU-only installation

Examples related to pandas

xlrd.biffh.XLRDError: Excel xlsx file; not supported Pandas Merging 101 How to increase image size of pandas.DataFrame.plot in jupyter notebook? Trying to merge 2 dataframes but get ValueError Python Pandas User Warning: Sorting because non-concatenation axis is not aligned How to show all of columns name on pandas dataframe? Pandas/Python: Set value of one column based on value in another column Python Pandas - Find difference between two data frames Pandas get the most frequent values of a column Python convert object to float

Examples related to join

Pandas Merging 101 pandas: merge (join) two data frames on multiple columns How to use the COLLATE in a JOIN in SQL Server? How to join multiple collections with $lookup in mongodb How to join on multiple columns in Pyspark? Pandas join issue: columns overlap but no suffix specified MySQL select rows where left join is null How to return rows from left table not found in right table? Why do multiple-table joins produce duplicate rows? pandas three-way joining multiple dataframes on columns

Examples related to merge

Pandas Merging 101 Python: pandas merge multiple dataframes Git merge with force overwrite Merge two dataframes by index Visual Studio Code how to resolve merge conflicts with git? merge one local branch into another local branch Merging dataframes on index with pandas Git merge is not possible because I have unmerged files Git merge develop into feature branch outputs "Already up-to-date" while it's not How merge two objects array in angularjs?

Examples related to concatenation

Pandas Merging 101 What does ${} (dollar sign and curly braces) mean in a string in Javascript? Concatenate two NumPy arrays vertically Import multiple csv files into pandas and concatenate into one DataFrame How to concatenate columns in a Postgres SELECT? Concatenate string with field value in MySQL Most efficient way to concatenate strings in JavaScript? How to force a line break on a Javascript concatenated string? How to concatenate two IEnumerable<T> into a new IEnumerable<T>? How to concat two ArrayLists?