Filter dataframe per ID based on conditional timerange

user_10759216 0
Viewed 23

Hi I will try to explain the issue I am facing.

I have one dataframe (df) with the following:

ID Date (dd-mm-yyyy)
AAA 01-09-2020
AAA 01-11-2020
AAA 18-03-2021
AAA 10-10-2022
BBB 01-01-2019
BBB 01-03-2019
CCC 01-05-2020
CCC 01-07-2020
CCC 01-08-2020
CCC 01-10-2021

I have created another dataframe (df2) with the first date (t) registered per ID and t+3months:

ID T (First Date Occurred) T+3
AAA 01-09-2020 01-12-2020
BBB 01-01-2019 01-03-2020
CCC 01-05-2020 01-08-2020

The desired output where I am struggling is to filter the df based on the two date filters defined in df2("T" & "T+3):

e.g.AAA = AAA > T & AAA < T+3

ID Date (dd-mm-yyyy)
AAA 01-11-2020
BBB 01-03-2019
CCC 01-07-2020
CCC 01-08-2020

What is the best way to approach this? Any help is appreciated!

pandas dataframe date python
1 Answers

IIUC, you can use pandas.merge_asof with allow_exact_matches=False:

(pd.merge_asof(df1.sort_values(by='Date'), df2.sort_values(by='T'),
               allow_exact_matches=False,
               by='ID', left_on='Date', right_on='T')
   .loc[lambda d: d['Date'] <= d['T+3']]
)

NB. the exact condition on the T+3 is unclear as you describe "< T+3" but the shown output has "<= T+3", just chose what you want (< or <=) in the loc

output:

    ID       Date          T        T+3
1  BBB 2019-03-01 2019-01-01 2020-03-01
3  CCC 2020-07-01 2020-05-01 2020-08-01
4  CCC 2020-08-01 2020-05-01 2020-08-01
6  AAA 2020-11-01 2020-09-01 2020-12-01
user_163434640
Related