Retrieving rows by 2 indexes in a 3 MultiIndex pandas dataframe - Python3

user_5513902 0
Viewed 68

I have a multiindex dataframe of 3 indexes, and I need to get all the rows whose first two levels are certain tuples.

My unsuccessful attempt is

index=pd.MultiIndex.from_tuples([(1,2,"A"), (1,2,"B"), (1,3,"A"), (1,3,"B"), (10,20,"A"), (5,3,"B")], names=['first', 'second', "third"])
df=pd.DataFrame(np.random.randn(6, 2), index=index)
## Pairs of first and second level I want to retrieve
pairs=[(1,2), (5,3)]## Actually retrieved from another dataframe, but not important here
pairs=[tuple(list(pair)+[slice(None)]) for pair in pairs]
df.loc[pairs]

I get the following error

TypeError: unhashable type: 'slice'

But If I do df.loc[(1,2, slice(None))] it works, but wont work as df.loc[[(1,2, slice(None)), (5,3, slice(None))]]

I am trying my best to be efficient because the dataframe df always has more than 196560000 rows.

I really don't know what else to do, I have read a lot and am super lost. Thanks!

Edit:

I dont want the combination (1, 3) or (5,2) to be retrieved. The retrieved rows should look like

                           0         1                                                                                                                  
first second third                                                                                                                                      
1     2      A     -0.627749 -1.560681                                                                                                                  
             B      0.053260  0.278046                                                                                                                                                                                                                                   
5     3      B      0.197331 -0.575833
multi-index pandas dataframe python-3.x
2 Answers

What about this trick :

pairs=[(1,2), (5,3)]
df.reset_index(drop=False, level=[2]).loc[pairs].set_index("third", append=True)

Not the most beautiful code, but it will work...

user_69364890

You can filter for the exact rows you need then index:

pairs = [
    (1, 2),
    (5, 3),
]

Search for specific rows in the index with a list comprehension:

specific_rows = [row for row in df.index if row[:2] in pairs]

Index your dataframe:

df.loc[specific_rows]

                            0          1
first   second  third       
   1       2    A       1.764052    0.400157
                B       0.978738    2.240893
   5       3    B       0.144044    1.454274
user_71757130
Related