Drop all rows of higher level multi-index if certain lower level index doesn't exist

user_12533570 0
Viewed 44

Suppose I have a DataFrame with two MultiIndex levels, such as:

     col1
A x     1
  y     2
B x     3
  y     4
C x     5

Now, if an index of the level 0 lacks the lower level index 'x' or 'y', I want to drop all rows associated with this index. So in this example I want to drop all rows of 'C' because there is no 'y' under 'C'. So the result should be:

     col1
A x     1
  y     2
B x     3
  y     4

Is there a nice/clean way to do this?

multi-index pandas python
2 Answers

Try:

mask=df.unstack().isna().any(1)       
#created a mask to check if an index has a missing value or not

Finally:

df=df.loc[~df.index.get_level_values(0).isin(mask[mask].index)]
                                               #^getting index of where value is missing
                      #^excluding that value from the level 0

output of df:

        col1
A   x   1
    y   2
B   x   3
    y   4
user_142898920

Try with transform

out = df[df.groupby(level=0)['col1'].transform('count')==2]
user_79645270
Related