agf1997 agf1997 - 1 month ago 17
Python Question

Merging multiindex dataframe in pandas

I have 2 similar data frames structured like this :

ind = pd.MultiIndex.from_product([['Day 1','Day 2'],['D1','D2'],['Mean','StDev','StErr']], names = ['interval','device','stats'])
df = pd.DataFrame({'col1':[1,2,3,4,5,6,7,8,9,10,11,12]}, index = ind)
print(df)

col1
interval device stats
Day 1 D1 Mean 1
StDev 2
StErr 3
D2 Mean 4
StDev 5
StErr 6
Day 2 D1 Mean 7
StDev 8
StErr 9
D2 Mean 10
StDev 11
StErr 12

ind2 = pd.MultiIndex.from_product([['Day 1','Day 2'],['D1','D2'],['Ratio']], names = ['interval','device','stats'])
df2 = pd.DataFrame({'col1':[100,200,300,400]}, index = ind2)
print(df2)

col1
interval device stats
Day 1 D1 Ratio 100
D2 Ratio 200
Day 2 D1 Ratio 300
D2 Ratio 400


I'm trying to merge them to get this :

col1
interval device stats
Day 1 D1 Mean 1
StDev 2
StErr 3
Ratio 100
D2 Mean 4
StDev 5
StErr 6
Ratio 200
Day 2 D1 Mean 7
StDev 8
StErr 9
Ratio 300
D2 Mean 10
StDev 11
StErr 12
Ratio 400


I tried a bunch of different things using
join
,
concat
, and
merge
but the closest I've been able to get is using
df3 = pd.concat([df, df2], axis=1)
. Unfortunately that gives me this :

col1 col1
interval device stats
Day 1 D1 Mean 1 NaN
Ratio NaN 100
StDev 2 NaN
StErr 3 NaN
D2 Mean 4 NaN
Ratio NaN 200
StDev 5 NaN
StErr 6 NaN
Day 2 D1 Mean 7 NaN
Ratio NaN 300
StDev 8 NaN
StErr 9 NaN
D2 Mean 10 NaN
Ratio NaN 400
StDev 11 NaN
StErr 12 NaN

Answer

Don't use axis=1 when using concat, as it means appending column-wise, not row-wise. You want axis=0 for row-wise, which happens to be the default, so you don't need to specify it:

df3 = pd.concat([df, df2]).sort_index()

The resulting output:

                       col1
interval device stats      
Day 1    D1     Mean      1
                Ratio   100
                StDev     2
                StErr     3
         D2     Mean      4
                Ratio   200
                StDev     5
                StErr     6
Day 2    D1     Mean      7
                Ratio   300
                StDev     8
                StErr     9
         D2     Mean     10
                Ratio   400
                StDev    11
                StErr    12