Keeping preferred value of a column and removing the less preferred one

user_14311637 0
Viewed 58

The data-frame, df:

ID status  year
1    0     2000
1    1     2000
2    0     2001
3    1     2002
3    0     2002
4    1     2002

I want to drop '0' status when '1' status is available for the same ID under the same year, such that:

ID status  year
1    1     2000
2    0     2001
3    1     2002
4    1     2002

I have used following code:

df.drop_duplicates(subset=['year', 'id'], keep="first")

But the above code drops out some nonzero rows.

pandas data-cleaning python
2 Answers

Let us try transform with max

m = df.status.eq(df.groupby('ID').status.transform('max'))
df = df[m]
df
   ID  status  year
1   1       1  2000
2   2       0  2001
3   3       1  2002
5   4       1  2002
user_79645270

Please mask

  1. Non duplicated year and
  2. status=1

Code below

df[(~df.duplicated(subset=['year'], keep=False))|(df['status'].eq(1))]



    ID  status  year
1   1       1  2000
2   2       0  2001
3   3       1  2002
5   4       1  2002
user_89869750
Related