Select rows of the multi-level index data frame with index values equal to columns in another data frame in pandas

user_2569042 0
Viewed 36

Having two data frames with the multi-level indices in one present as columns in the other, the idea is to select those rows from the first where the index values are the same as the corresponding column values.

Example

Having the following two data frames df1 and df2:

df1 = pd.DataFrame({'i1': ['a1', 'a2', 'a3'], 'i2': ['b1', 'b2', 'b3'], 'c': [1, 2, 3]})
df1 = df1.set_index(['i1', 'i2'])

       c
i1 i2   
a1 b1  1
a2 b2  2
a3 b3  3


df2 = pd.DataFrame({'i1': ['a1', 'a3'], 'i2': ['b1', 'b3']})

   i1  i2
0  a1  b1
1  a3  b3

Select those rows of df1 which index values i1 and i2 have the same values as the columns i1 and i12 in df2 resulting in:

       c
i1 i2   
a1 b1  1
a3 b3  3
pandas dataframe select python
1 Answers

Use Index.isin with MultiIndex:

df = df1[df1.index.isin(df2.set_index(['i1','i2']).index)]

Alternative:

df = df1[df1.index.isin(pd.MultiIndex.from_arrays([df2['i1'], df2['i2']]))]

Or use merge, join - but necessary reset and set index:

df = df1.reset_index().merge(df2).set_index(['i1', 'i2'])
df = df2.join(df1, on=['i1', 'i2'], how='inner').set_index(['i1', 'i2'])

print (df)
       c
i1 i2   
a1 b1  1
a3 b3  3
user_29010020
Related