1st Step of my question which is currently not solved is in Python removing rows with time condition
2nd Step of the Question: After having the filter data from step 1, would like to do a join from DF1 to DF2 based on the condition of same ID and Type and join the nearest Datetime from DF 2 to DF 1 (Date time of DF1 must be after DF2)
E.g. DF1 - EXIT data
DatetimeX ID Type
2020-01-01 02:00:01 12345 C
2020-01-01 02:00:01 13333 D
2020-01-01 02:00:50 13333 E
2020-01-01 16:00:01 12211 C
2020-01-02 21:00:01 12211 C
2020-01-03 17:00:01 12211 C
2020-01-04 17:00:01 12211 C
2020-01-05 21:00:01 12211 C
E.g. DF2 - ENTRY data
DatetimeE ID Type
2020-01-01 01:00:00 12345 C
2020-01-01 00:00:01 77777 C
2020-01-01 00:00:05 12345 C
2020-01-01 00:00:20 12345 C
2020-01-01 02:00:05 13333 D
2020-01-01 04:00:50 13333 E
2020-01-01 07:00:01 12211 C
Endstate after join e.g. DF3
DatetimeX ID Type DatetimeE
2020-01-01 02:00:01 12345 C 2020-01-01 01:00:00
2020-01-01 02:00:01 13333 D
2020-01-01 02:00:50 13333 E
2020-01-01 02:00:01 12211 C 2020-01-01 01:00:30
2020-01-02 21:00:01 12211 C 2020-01-01 07:00:01
I've tried exploring merge and merge_asof but the combined data of DF3 does not match any of the data. Would like to just request for some guidance on how I can start comparing.
Thank you!
Updated with more data sample
question from:
https://stackoverflow.com/questions/65840485/merging-of-2-separate-excel-file-based-3-condition-using-python-pandas 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…