Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
like image 204
agf1997 Avatar asked Nov 11 '16 00:11

agf1997


People also ask

How do I merge data frames in Pandas?

Pandas DataFrame merge() MethodThe merge() method updates the content of two DataFrame by merging them together, using the specified method(s). Use the parameters to control which values to keep and which to replace.

How do I convert MultiIndex to single index in Pandas?

To revert the index of the dataframe from multi-index to a single index using the Pandas inbuilt function reset_index(). Returns: (Data Frame or None) DataFrame with the new index or None if inplace=True.


1 Answers

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
like image 143
root Avatar answered Sep 18 '22 12:09

root