Merging two dataframes with hierarchical columns
this is my first time using the multi-indexing in pandas and I need some help to merge two dataframes with hierarchical columns. Here are my two dataframes:
col_index = pd.MultiIndex.from_product([['a', 'b', 'c'], ['w', 'x']])
df1 = pd.DataFrame(np.ones([4,6]),columns=col_index, index=range(4))
a b c
w x w x w x
0 1.0 1.0 1.0 1.0 1.0 1.0
1 1.0 1.0 1.0 1.0 1.0 1.0
2 1.0 1.0 1.0 1.0 1.0 1.0
3 1.0 1.0 1.0 1.0 1.0 1.0
df2 = pd.DataFrame(np.zeros([2,6]),columns=col_index, index=range(2))
a b c
w x w x w x
0 0.0 0.0 0.0 0.0 0.0 0.0
1 0.0 0.0 0.0 0.0 0.0 0.0
When I use the merge method, I get the following result:
pd.merge(df1,df2, how='left', suffixes=('', '_2'), left_index = True, right_index= True ))
a b c a_2 b_2 c_2
w x w x w x w x w x w x
0 1.0 1.0 1.0 1.0 1.0 1.0 0.0 0.0 0.0 0.0 0.0 0.0
1 1.0 1.0 1.0 1.0 1.0 1.0 0.0 0.0 0.0 0.0 0.0 0.0
2 1.0 1.0 1.0 1.0 1.0 1.0 NaN NaN NaN NaN NaN NaN
3 1.0 1.0 1.0 1.0 1.0 1.0 NaN NaN NaN NaN NaN NaN
But I would like to merge the two dataframes on a lower level with the suffixes taking effect on ['w', 'x'] like in the following :
a b c
w w_2 x x_2 w w_2 x x_2 w w_2 x x_2
0 1.0 0.0 1.0 0.0 1.0 0.0 1.0 0.0 1.0 0.0 1.0 0.0
1 1.0 0.0 1.0 0.0 1.0 0.0 1.0 0.0 1.0 0.0 1.0 0.0
2 1.0 NaN 1.0 NaN 1.0 NaN 1.0 NaN 1.0 NaN 1.0 NaN
3 1.0 NaN 1.0 NaN 1.0 NaN 1.0 NaN 1.0 NaN 1.0 NaN
from Recent Questions - Stack Overflow https://ift.tt/3i1FkDQ
https://ift.tt/eA8V8J
Comments
Post a Comment