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.
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