How can one join 2 pandas DataFrames on MultiIndex with different number of levels?
import pandas as pd
t1 = pd.DataFrame(data={'a1':[0,0,1,1,2,2],
'a2':[0,1,0,1,0,1],
'x':[1.,2.,3.,4.,5.,6.]})
t1.set_index(['a1','a2'], inplace=True)
t1.sort_index(inplace=True)
t2 = pd.DataFrame(data={'b1':[0,1,2],
'y':[20.,40.,60.]})
t2.set_index(['b1'], inplace=True)
t2.sort_index(inplace=True)
>>> t1
x
a1 a2
0 0 1.0
1 2.0
1 0 3.0
1 4.0
2 0 5.0
1 6.0
>>> t2
y
b1
0 20.0
1 40.0
2 60.0
Expected result for joining on 'a1' => 'b1':
x y
a1 a2
0 0 1.0 20.0
1 2.0 20.0
1 0 3.0 40.0
1 4.0 40.0
2 0 5.0 60.0
1 6.0 60.0
Another example: joining on ['a1','a2'] => ['b1','b2']:
import pandas as pd, numpy as np
t1 = pd.DataFrame(data={'a1':[0,0,0,0,1,1,1,1,2,2,2,2],
'a2':[3,3,4,4,3,3,4,4,3,3,4,4],
'a3':[7,8,7,8,7,8,7,8,7,8,7,8],
'x':[1.,2.,3.,4.,5.,6.,7.,8.,9.,10.,11.,12.]})
t1.set_index(['a1','a2','a3'], inplace=True)
t1.sort_index(inplace=True)
t2 = pd.DataFrame(data={'b1':[0,0,1,1,2,2],
'b2':[3,4,3,4,3,4],
'y':[10.,20.,30.,40.,50.,60.]})
t2.set_index(['b1','b2'], inplace=True)
t2.sort_index(inplace=True)
>>> t1
x
a1 a2 a3
0 3 7 1.0
8 2.0
4 7 3.0
8 4.0
1 3 7 5.0
8 6.0
4 7 7.0
8 8.0
2 3 7 9.0
8 10.0
4 7 11.0
8 12.0
>>> t2
y
b1 b2
0 3 10.0
4 20.0
1 3 30.0
4 40.0
2 3 50.0
4 60.0
Expected result for joining on ['a1','a2'] => ['b1','b2']:
x y
a1 a2 a3
0 3 7 1.0 10.0
8 2.0 10.0
4 7 3.0 20.0
8 4.0 20.0
1 3 7 5.0 30.0
8 6.0 30.0
4 7 7.0 40.0
8 8.0 40.0
2 3 7 9.0 50.0
8 10.0 50.0
4 7 11.0 60.0
8 12.0 60.0
The solution should work joining on multiple index levels.
Thank you for your help!
Solution to the 1st example:
t1.reset_index('a2', drop=False).join(t2
).rename_axis('a1').set_index('a2', append=True)
Solution to the 2nd example:
t1.reset_index('a3', drop=False).join(
t2.rename_axis(index={'b1':'a1', 'b2':'a2'})
).set_index('a3', append=True)