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

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

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

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  quot key quot  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  quot how quot  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   Merging basics - basic types of joins  Index-based joins  Generalizing to multiple DataFrames    Cross join     you are here

User · Answer

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   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   DataFrame join  join on index   supports inner left  default  right full can join multiple DataFrames at a time supports index-index joins   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  amp  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  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   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  gt   0 23  If your index is named  then from pandas  gt   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   Merging basics - basic types of joins  Index-based joins   Generalizing to multiple DataFrames  Cross join     you are here

[python] Pandas Merging 101

Examples related to python

Examples related to pandas

Examples related to join

Examples related to merge

Examples related to concatenation