Merging multiindex dataframe in pandas

Question

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

root · Accepted Answer · 2016-11-11 00:45:20Z

9

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

answered Nov 11, 2016 at 0:45

root

34.1k6 gold badges77 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merging multiindex dataframe in pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related