I would like to join two DataFrames. The first one is a multi indexed DataFrame and the second is a simple DataFrame.
import pandas as pd
import numpy
a = pd.DataFrame({'a': {('x', 0) : 1, ('x', 1) : 2, ('y', 0): 3, ('y', 1): 5}, 'b': {('x', 0) : 2, ('x', 1) : 4, ('y', 0): 2, ('y', 1): 7}}).T
print(a)
# x y
# 0 1 0 1
# a 1 2 3 5
# b 2 4 2 7
b = pd.DataFrame({'y': np.arange(10), 'z': np.arange(10, 20)})
magical_merge(left=a, right=b, on='y')
# x y z
# 0 1 0 1 0 1
# a 1 2 3 5 13 15
# b 2 4 2 7 12 17
Right now I am doing this with a loop over the second index like this:
merged = []
for l in [0, 1]:
m = pd.merge(left=a.xs(l, axis=1, level=1),right=b, on='y')
m_indices = pd.MultiIndex.from_product([m.columns, [l]])
m.columns = m_indices
merged.append(m)
result = pd.concat(merged, axis=1).sort_index(axis=1)
Can pandas do this somehow by itself?
You need stack and reset_index on the multiindex df (which is a in your case). Next, merge and set_index back. Finally, use rename_axis to pretty the multiindex names and unstack to put back multiindex columns:
a.stack().reset_index().merge(b, on='y').set_index(['level_0', 'level_1']) \
.rename_axis(index=[None, None]).unstack()
Out[335]:
x y z
0 1 0 1 0 1
a 1 2 3 5 13 15
b 2 4 2 7 12 17