python - How do I check if a date has consecutive rows in pandas?

Question

This is my dataframe,

date            id        name           score

2020-10-19      1         Peter          0
2020-10-19      2         Betty          50
2020-10-19      3         Susie          45
2020-10-18      1         Peter          0
2020-10-18      2         Betty          50
2020-10-18      3         Susie          45
2020-10-17      1         Peter          60
2020-10-17      2         Betty          0
2020-10-17      3         Susie          45

How can I check if there was a score of 0 on two consecutive days? The following table should be returned. (Betty did not have 0 on two consecutive dates)

date            id        name           score

2020-10-19      1         Peter          0
2020-10-18      1         Peter          0

I have tried:

df['score'] = (df.score.diff(1) == 0).astype('int').cumsum()

Note: the datetimes are always sorted in descending order.

question from:https://stackoverflow.com/questions/65843153/how-do-i-check-if-a-date-has-consecutive-rows-in-pandas

Answer 1

If datetimes are sorted you can test if 2 consecutive values per groups are 0:

m1 = df['score'].eq(0) & df.groupby('id')['score'].shift(-1).eq(0)
m2 = df['score'].eq(0) & df.groupby('id')['score'].shift().eq(0)

df = df[m1 | m2]
print (df)
        date  id   name  score
0 2020-10-19   1  Peter      0
3 2020-10-18   1  Peter      0