Drop rows in DataFrame where two date columns match, otherwise, change value in another column

user_10059652 0
Viewed 283

I have a df that looks the following,

df

  id   date      rating_date  rating 
  1  1993-05-20   1993-05-20     3     
  2  1987-03-12   1988-03-12     4    
  3  1994-01-19   1994-10-19     3     
  4  2004-08-03   2004-09-17     2    
  5  2005-10-12   2005-10-12     2

I wish to remove the rows where date equals rating_date, and change rating to NR if rating_date is > date. Would be awesome for some guidance!

  id   date      rating_date  rating 
  2  1987-03-12   1988-03-12    NR    
  3  1994-01-19   1994-10-19    NR     
  4  2004-08-03   2004-09-17    NR

Thanks!

pandas datetime python
3 Answers

If rating_date has no chance smaller than date, then

df = df[df['date'] < df['rating_date']]
df['rating'] = 'NR'
user_163536620

Another way

m=df['date'].ne(df['rating_date'])
df1=df[m].assign(rating='NR')

Or simply put

df1=df[df['date'].ne(df['rating_date'])].assign(rating='NR')
user_89869750

  1. For removing the rows where date equals rating_date.

    • Get the list of index where date equals to rating_date, then drop the rows
    rows = df[df['date']==df['rating_date']].index.to_list()
df.drop(rows)
    • In short
    df.drop(df[df['date']==df['rating_date']].index.to_list())

  2. For changing rating to NR if rating_date is > date:

    • Get the list of index where 'rating_date' > 'date', then change the value of rows under rating to NR
    rows = df[df['rating_date']>df['date']].index.to_list()
df.loc[rows,'rating'] = 'NR'
user_86529500
Related