Merging multi data index in pandas

Question

I have two similar data frames, structured as follows:

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 am trying to combine 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 could get is using df3 = pd.concat([df, df2], axis=1) . Unfortunately, this gives me the following:

  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 1

Do not use axis=1 when using concat , as this means adding by column rather than row. You want axis=0 for the default row, so you don't need to specify it:

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

Result:

  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