Search code examples
pythonpandasdataframeconcatenation

Join 2 identical pandas dataframe into multi level row key


I have 2 dataframe with identical index and column. I need to join or concatenate them into one dataframe. The code to generate the data is as such:

import pandas as pd
sites = pd.Index(['AAA', 'BBB','CCC', 'DDD'], name='SITELIST')
vvv = pd.DataFrame({'KK':[1,2,3,4],'GG':[2,3,4,5], 'RR':[6,5,4,3]}, index = sites)
ttt = pd.DataFrame({'KK':[11,22,33,34],'GG':[23,33,34,35], 'RR':[26,25,24,23]}, index = sites)

The resulting dataframe is

print(vvv)

          KK  GG  RR
SITELIST            
AAA        1   2   6
BBB        2   3   5
CCC        3   4   4
DDD        4   5   3

print(ttt)

          KK  GG  RR
SITELIST            
AAA       11  23  26
BBB       22  33  25
CCC       33  34  24
DDD       34  35  23

When I use concat to join those two dataframes with the following code:

res2 = pd.concat([vvv, ttt], axis=0, keys=["VVV", "TTT"], names=['Sensor', 'Site'])

I get this as a result:

print(res2)

             KK  GG  RR
Sensor Site            
VVV    AAA    1   2   6
       BBB    2   3   5
       CCC    3   4   4
       DDD    4   5   3
TTT    AAA   11  23  26
       BBB   22  33  25
       CCC   33  34  24
       DDD   34  35  23

I need to join them so to get the result such as:

              KK  GG  RR
Site  Sensor
AAA   VVV      1   2   6
      TTT     11  23  26
BBB   VVV      2   3   5
      TTT     22  33  25
CCC   VVV      3   4   4
      TTT     33  34  24
DDD   VVV      4   5   3
      TTT     34  35  23

Thank you.


Solution

  • You can use df.swaplevel with df.sort_index and reindex:

    print(res2.swaplevel().sort_index(level=0).reindex(['VVV','TTT'],level=1))
    

                 KK  GG  RR
    Site Sensor            
    AAA  VVV      1   2   6
         TTT     11  23  26
    BBB  VVV      2   3   5
         TTT     22  33  25
    CCC  VVV      3   4   4
         TTT     33  34  24
    DDD  VVV      4   5   3
         TTT     34  35  23