Pandas Merging 101

Question

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   Merging basics - basic types of joins  read this first   Index-based joins  Generalizing to multiple DataFrames  Cross join

User · Answer

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}

User · Answer

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}

User · Answer

This post aims to give readers a primer on SQL-flavored merging with pandas  how to use it  and when not to use it  In particular  here s what this post will go through   The basics - types of joins  LEFT  RIGHT  OUTER  INNER   merging with different column names merging with multiple columns avoiding duplicate merge key column in output    What this post  and other posts by me on this thread  will not go through   Performance-related discussions and timings  for now   Mostly notable mentions of better alternatives  wherever appropriate  Handling suffixes  removing extra columns  renaming outputs  and other specific use cases  There are other  read  better  posts that deal with that  so figure it out    Note Most examples default to INNER JOIN operations while demonstrating various features  unless otherwise specified  Furthermore  all the DataFrames here can be copied and replicated so you can play with them  Also  see this post on how to read DataFrames from your clipboard  Lastly  all visual representation of JOIN operations have been hand-drawn using Google Drawings  Inspiration from here     Enough Talk  just show me how to use merge  Setup  amp  Basics np random seed 0  left   pd DataFrame   key     A    B    C    D     value   np random randn 4        right   pd DataFrame   key     B    D    E    F     value   np random randn 4       left    key     value 0   A  1 764052 1   B  0 400157 2   C  0 978738 3   D  2 240893  right    key     value 0   B  1 867558 1   D -0 977278 2   E  0 950088 3   F -0 151357  For the sake of simplicity  the key column has the same name  for now   An INNER JOIN is represented by   Note This  along with the forthcoming figures all follow this convention   blue indicates rows that are present in the merge result red indicates rows that are excluded from the result  i e   removed  green indicates missing values that are replaced with NaNs in the result   To perform an INNER JOIN  call merge on the left DataFrame  specifying the right DataFrame and the join key  at the very least  as arguments  left merge right  on  key     Or  if you want to be explicit   left merge right  on  key   how  inner      key   value x   value y 0   B  0 400157  1 867558 1   D  2 240893 -0 977278  This returns only rows from left and right which share a common key  in this example   quot B quot  and  quot D   A LEFT OUTER JOIN  or LEFT JOIN  is represented by  This can be performed by specifying how  left   left merge right  on  key   how  left      key   value x   value y 0   A  1 764052       NaN 1   B  0 400157  1 867558 2   C  0 978738       NaN 3   D  2 240893 -0 977278  Carefully note the placement of NaNs here  If you specify how  left   then only keys from left are used  and missing data from right is replaced by NaN  And similarly  for a RIGHT OUTER JOIN  or RIGHT JOIN which is        specify how  right   left merge right  on  key   how  right      key   value x   value y 0   B  0 400157  1 867558 1   D  2 240893 -0 977278 2   E       NaN  0 950088 3   F       NaN -0 151357  Here  keys from right are used  and missing data from left is replaced by NaN  Finally  for the FULL OUTER JOIN  given by  specify how  outer   left merge right  on  key   how  outer      key   value x   value y 0   A  1 764052       NaN 1   B  0 400157  1 867558 2   C  0 978738       NaN 3   D  2 240893 -0 977278 4   E       NaN  0 950088 5   F       NaN -0 151357  This uses the keys from both frames  and NaNs are inserted for missing rows in both  The documentation summarizes these various merges nicely    Other JOINs - LEFT-Excluding  RIGHT-Excluding  and FULL-Excluding ANTI JOINs If you need LEFT-Excluding JOINs and RIGHT-Excluding JOINs in two steps  For LEFT-Excluding JOIN  represented as  Start by performing a LEFT OUTER JOIN and then filtering  excluding   rows coming from left only   left merge right  on  key   how  left   indicator True        query   merge     quot left only quot          drop   merge   1      key   value x  value y 0   A  1 764052      NaN 2   C  0 978738      NaN  Where  left merge right  on  key   how  left   indicator True     key   value x   value y      merge 0   A  1 764052       NaN  left only 1   B  0 400157  1 867558       both 2   C  0 978738       NaN  left only 3   D  2 240893 -0 977278       both And similarly  for a RIGHT-Excluding JOIN    left merge right  on  key   how  right   indicator True        query   merge     right only          drop   merge   1      key  value x   value y 2   E      NaN  0 950088 3   F      NaN -0 151357 Lastly  if you are required to do a merge that only retains keys from the left or right  but not both  IOW  performing an ANTI-JOIN    You can do this in similar fashion     left merge right  on  key   how  outer   indicator True        query   merge     quot both quot          drop   merge   1      key   value x   value y 0   A  1 764052       NaN 2   C  0 978738       NaN 4   E       NaN  0 950088 5   F       NaN -0 151357   Different names for key columns If the key columns are named differently   for example  left has keyLeft  and right has keyRight instead of key   then you will have to specify left on and right on as arguments instead of on  left2   left rename   key   keyLeft    axis 1  right2   right rename   key   keyRight    axis 1   left2     keyLeft     value 0       A  1 764052 1       B  0 400157 2       C  0 978738 3       D  2 240893  right2    keyRight     value 0        B  1 867558 1        D -0 977278 2        E  0 950088 3        F -0 151357   left2 merge right2  left on  keyLeft   right on  keyRight   how  inner      keyLeft   value x keyRight   value y 0       B  0 400157        B  1 867558 1       D  2 240893        D -0 977278   Avoiding duplicate key column in output When merging on keyLeft from left and keyRight from right  if you only want either of the keyLeft or keyRight  but not both  in the output  you can start by setting the index as a preliminary step  left3   left2 set index  keyLeft   left3 merge right2  left index True  right on  keyRight            value x keyRight   value y 0  0 400157        B  1 867558 1  2 240893        D -0 977278  Contrast this with the output of the command just before  that is  the output of left2 merge right2  left on  keyLeft   right on  keyRight   how  inner     you ll notice keyLeft is missing  You can figure out what column to keep based on which frame s index is set as the key  This may matter when  say  performing some OUTER JOIN operation   Merging only a single column from one of the DataFrames For example  consider right3   right assign newcol np arange len right    right3   key     value  newcol 0   B  1 867558       0 1   D -0 977278       1 2   E  0 950088       2 3   F -0 151357       3  If you are required to merge only  quot new val quot   without any of the other columns   you can usually just subset columns before merging  left merge right3   key    newcol     on  key      key     value  newcol 0   B  0 400157       0 1   D  2 240893       1  If you re doing a LEFT OUTER JOIN  a more performant solution would involve map    left  newcol     left  key   map right3 set index  key    newcol     left assign newcol left  key   map right3 set index  key    newcol        key     value  newcol 0   A  1 764052     NaN 1   B  0 400157     0 0 2   C  0 978738     NaN 3   D  2 240893     1 0  As mentioned  this is similar to  but faster than left merge right3   key    newcol     on  key   how  left      key     value  newcol 0   A  1 764052     NaN 1   B  0 400157     0 0 2   C  0 978738     NaN 3   D  2 240893     1 0   Merging on multiple columns To join on more than one column  specify a list for on  or left on and right on  as appropriate   left merge right  on   key1    key2         Or  in the event the names are different  left merge right  left on   lkey1    lkey2    right on   rkey1    rkey2      Other useful merge  operations and functions  Merging a DataFrame with Series on index  See this answer   Besides merge  DataFrame update and DataFrame combine first are also used in certain cases to update one DataFrame with another   pd merge ordered is a useful function for ordered JOINs   pd merge asof  read  merge asOf  is useful for approximate joins    This section only covers the very basics  and is designed to only whet your appetite  For more examples and cases  see the documentation on merge  join  and concat as well as the links to the function specs    Continue Reading Jump to other topics in Pandas Merging 101 to continue learning   Merging basics - basic types of joins    Index-based joins  Generalizing to multiple DataFrames  Cross join     you are here

User · Answer

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   Preco 2019 with size  8760  5   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   If you want to visualize  it ends up working like this   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    And the dataframe Price that has one column with the price and the index corresponds to the dates  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

User · Answer

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

[python] Pandas Merging 101

Generalizing to multiple DataFrames

Multiway merge on unique keys

Multiway merge on keys with duplicates

Continue Reading

Examples related to python

Examples related to pandas

Examples related to join

Examples related to merge

Examples related to concatenation