Skip to content
Advertisement

Filter dataframe per ID based on conditional timerange

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!

Advertisement

Answer

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

JavaScript

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:

JavaScript
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement