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
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With