Pandas left join on multiple keys with different index lengths

user_7015657 0
Viewed 1379

I am trying to join two data tables together with different index lengths - one is a multi index and the other a range index of type int. I need to join another table with a much shorter index, and have the rows repeat where necessary and replace with NAN as would be replicated with a left join in sql.

I have the following production data table:

-------------------------------
Month Plant Product Production 
-------------------------------
1     A     AFS     11,212
            TF1     9,005
            AA1     21,656
      B     AA1     11,512
            POD     6,550
2     A     AFS     12,550
            TF1     12,121
            AA1     15,091
      B     AA1     16,212
            POD     7,890

and the following price forecast data, monthly:

-------------------------------
Month Product Forecast Price
-------------------------------
1     AFS     0.91
      AA1     6.66
      TF1     11.90
      POD     21.80
      ZBR     0.61
      TPO     0.88
2     AFS     1.12
      AA1     7.42
      TF1     12.56

I would like to have the following final table: 

----------------------------------------------
Month Plant Product Production Forecast Price
----------------------------------------------
1     A     AFS     11,212     0.91
            TF1     9,005      11.90
            AA1     21,656     6.66
      B     AA1     11,512     1.12
            POD     6,550      etc
2     A     AFS     12,550
            TF1     12,121
            AA1     15,091
      B     AA1     16,212
            POD     7,890

I have tried using

pd.concat([df, fcast_df], join='inner', axis=0)

and df.merge(fcast_df, left_index=True, right_on='Product')

The first option yields nothing and the second unfortunately is not the result I am after as it does not account for the multi-indexing of the first dataframe i.e. join on both Month and Product.

Any help greatly appreciated

pandas python
1 Answers

You can convert MultiIndex to columns and use merge with left join:

df1 = df.reset_index().merge(fcast_df, on=['Month','Product'], how='left')
print (df1)
   Month Plant Product Production  Forecast Price
0      1     A     AFS     11,212            0.91
1      1     A     TF1      9,005           11.90
2      1     A     AA1     21,656            6.66
3      1     B     AA1     11,512            6.66
4      1     B     POD      6,550           21.80
5      2     A     AFS     12,550            1.12
6      2     A     TF1     12,121           12.56
7      2     A     AA1     15,091            7.42
8      2     B     AA1     16,212            7.42
9      2     B     POD      7,890             NaN

Setup:

print (df)
                    Production
Month Plant Product           
1     A     AFS         11,212
            TF1          9,005
            AA1         21,656
      B     AA1         11,512
            POD          6,550
2     A     AFS         12,550
            TF1         12,121
            AA1         15,091
      B     AA1         16,212
            POD          7,890

print (fcast_df)
   Month Product  Forecast Price
0      1     AFS            0.91
1      1     AA1            6.66
2      1     TF1           11.90
3      1     POD           21.80
4      1     ZBR            0.61
5      1     TPO            0.88
6      2     AFS            1.12
7      2     AA1            7.42
8      2     TF1           12.56

If MultiIndex in both DataFrames use:

df1 = df.reset_index().merge(fcast_df.reset_index(), on=['Month','Product'], how='left')
user_29010020
Related