CODEWITHSUNDEEP
pythonpandasdataframejoinmulti-index
S.V
S.V

Reputation: 2793

Join on MultiIndex with different number of levels in pandas

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!

Upvotes: 5

Views: 4916

Answers (5)

S.V
S.V

Reputation: 2793

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)

Upvotes: 3

S.V
S.V

Reputation: 2793

A slow way to do the join in the 2nd example:

for col in t2.columns:
    for i2 in t2.index:
        t1.loc[i2+(slice(None),),col] = t2.loc[i2,col]

The task is to vectorize it and to put slice(None) automatically in the correct locations while creating a t1 index item.

Vectorized version for the 2nd example:

m = list(zip(t1.index.get_level_values('a1'), t1.index.get_level_values('a2')))
t1 = t1.assign(**dict(zip(t2.columns,[np.nan]*len(t2.columns))))
t1[t2.columns] = t2.loc[m,:].values

Vectorized version for the 1st example:

m = t1.index.get_level_values('a1')
t1 = t1.assign(**dict(zip(t2.columns,[np.nan]*len(t2.columns))))
t1[t2.columns] = t2.loc[m,:].values

Upvotes: 0

root
root

Reputation: 33803

Use reindex on t2, setting the level parameter as appropriate, and directly assign to t1:

t1['y'] = t2['y'].reindex(t1.index, level='a1')

         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

To reindex on multiple levels, simply pass a list as the level parameter, e.g. ['a1', 'a2'].

Upvotes: 0

sacuL
sacuL

Reputation: 51395

You could merge t1 and t2 directly on the index level named a1 in t1, and the single index of t2:

t1.merge(t2, left_on = t1.index.get_level_values('a1').values, right_index=True)

         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

Upvotes: 2

jpp
jpp

Reputation: 164773

You can use pd.Index.get_level_values and map a series from t2:

t1['y'] = t1.index.get_level_values(0).map(t2['y'].get)

print(t1)

         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

Upvotes: 2

Related Questions