Drop row based on condition

Question

I have this DataFrame:

pd.DataFrame(
    {'name': ['Adam', 'Adam', 'Adam', 'Bill', 'Bill', 'Charlie', 'Charlie', 'Charlie', 'Charlie'],
     'message': ['start', 'stuck', 'finish', 'start', 'stuck', 'start', 'stuck', 'finish', 'finish']}
)

and I want to drop all rows with message "stuck" from all rows that don't have a message "finish":

pd.DataFrame(
    {'name': ['Adam', 'Adam', 'Bill', 'Bill', 'Charlie', 'Charlie', 'Charlie'],
     'message': ['start', 'finish', 'start', 'stuck', 'start', 'finish', 'finish']}
)

So Bill never "finished", so his message will remain "stuck".

Cimbali · Accepted Answer · 2021-08-16 14:44:13Z

1

To get if any student has finished, group by student and use any, here we want it back in the original shape of the dataframe so we use groupby.transform:

>>> sf = df['message'].eq('finish').groupby(df['name']).transform('any')
>>> sf
0     True
1     True
2     True
3    False
4    False
5     True
6     True
7     True
8     True
Name: message, dtype: bool

From there it’s easy to remove messages that are stuck from students that have not finished yet:

>>> df[~sf | df['message'].ne('stuck'))]
      name message
0     Adam   start
2     Adam  finish
3     Bill   start
4     Bill   stuck
5  Charlie   start
7  Charlie  finish
8  Charlie  finish

answered Aug 16, 2021 at 14:44

Cimbali

11.5k1 gold badge44 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

j__carlson · Accepted Answer · 2021-08-16 15:37:37Z

1

This will work:

df[~((df.name.isin(df[df.message=="finish"]['name'])) & (df.message=='stuck'))]

Output:

name	message
Adam	start
Adam	finish
Bill	start
Bill	stuck
Charlie	start
Charlie	finish
Charlie	finish

edited Aug 16, 2021 at 15:37

answered Aug 16, 2021 at 15:31

j__carlson

1,3583 gold badges14 silver badges22 bronze badges

Collectives™ on Stack Overflow

Drop row based on condition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related