CODEWITHSUNDEEP
python-3.xpandas
Shijith
Shijith

Reputation: 4872

How to filter values from a multi index Pandas data frame

I have a multi index pandas data frame df as below:

                Count
Letter Direction           
A       -1          3
         1          0
B       -1          2
         1          4
C       -1          4
         1          10
D       -1          8
         1          1
E       -1          4
         1          5
F       -1          1
         1          1

I want to filter out Letters which has Count < 2 in both or either one of the direction.
Tried df[df.Count < 2], but its giving the below output:

                Count
Letter Direction           
A        1          0
D        1          1
F       -1          1
         1          1

Desired output is as below,

                Count
Letter Direction           
A       -1          3
         1          0
D       -1          8
         1          1
F       -1          1
         1          1

what should i do to get the above?

Upvotes: 1

Views: 52

Answers (1)

jezrael
jezrael

Reputation: 862661

Use GroupBy.transform with boolean mask and GroupBy.any - any check if at least one True per first level of MultiIndex and transform return mask with same size like original DataFrame, so possible filter by boolean indexing:

df = df[(df.Count < 2).groupby(level=0).transform('any')]
print (df)
                  Count
Letter Direction       
A      -1             3
        1             0
D      -1             8
        1             1
F      -1             1
        1             1

Another solution is use MultiIndex.get_level_values for get values of Letter by condition and select by DataFrame.loc:

df = df.loc[df.index.get_level_values(0)[df.Count < 2]]
print (df)
                  Count
Letter Direction       
A      -1             3
        1             0
D      -1             8
        1             1
F      -1             1
        1             1

Upvotes: 1

Related Questions