Pandas - Filtering DataFrame based on unique value and different columns datetime

user_14273582 0
Viewed 56

I have the following datafame:

import numpy as np
import pandas as pd

df = {'ID': ['1','1','2', '2', '3', '3', '4', '4', '4'],
      'USER' : ['A', 'B', 'A', 'B', 'A', 'B', 'A', 'B', 'C'],
      'DATE_VIEW': ['16/05/2019','18/05/2019', '16/03/2020', '18/03/2020', '16/07/2020', '21/07/2020', '13/02/2020', '14/02/2020', '15/02/2020'],
      'DATE_ACCEPT': ['17/05/2019', np.nan, np.nan, '18/03/2020', '16/07/2020', np.nan, np.nan, '14/02/2020', np.nan],
       }

df = pd.DataFrame(df)
df['DATE_VIEW'] = pd.to_datetime(df['DATE_VIEW'], format = '%d/%m/%Y')
df['DATE_ACCEPT'] = pd.to_datetime(df['DATE_ACCEPT'], format = '%d/%m/%Y')
df

enter image description here

I am looking for a way to keep for unique df['ID'] the row if the df['DATE_VIEW'] is smaller than the df['DATE_VIEW'] when the df['DATE_ACCEPT] has been populated and drop the row it if the df['DATE_VIEW'] is grater than the df['DATE_VIEW'] when the df['DATE_ACCEPT] has been populated for that particular df['ID']. Expected output below:

enter image description here

pandas dataframe group-by python
1 Answers

You can groupby the ID column and transform to get DATE_ACCEPT for each row, then compare DATE_VIEW to that date:

df.loc[df['DATE_VIEW'].le(df.groupby('ID')['DATE_ACCEPT'].transform('max'))]

Output:

  ID USER  DATE_VIEW DATE_ACCEPT
0  1    A 2019-05-16  2019-05-17
2  2    A 2020-03-16         NaT
3  2    B 2020-03-18  2020-03-18
4  3    A 2020-07-16  2020-07-16
6  4    A 2020-02-13         NaT
7  4    B 2020-02-14  2020-02-14

P.S. And you can of course reset_index(drop=True) afterwards, if you want to make it look exactly like your expected output

Update If you want to keep rows where both dates are np.nan, you can add another boolean mask and apply it with |:

# the original condition DATE_VIEW <= DATE_ACCEPT
m1 = df['DATE_VIEW'].le(df.groupby('ID')['DATE_ACCEPT'].transform('max'))

# both dates are np.nan
m2 = df[['DATE_VIEW', 'DATE_ACCEPT']].isna().all(axis=1)

df.loc[m1|m2]
user_67927430
Related