How to do a merge with conditions in python

Question

I have 2 dataframe df1 and df2.

df1 has 4000 records and df2 has 160 records.

I need to merge the 7th column of df2 with df1 based on Date and Time(which is a common column in both).

Condition:

If date and time are same in df1 and df2 then a normal merge will happen

If date is same but the time in df1 is 14:00 and df2 has a time of 13:59 and after that if it has only 14:03, then the merge should happen with 13:59 time(which is the time before 14:00).

I tried:

Extracting only the Date, time and 7th column from df1.

then i did a pd.merge(left merge)

pd.merge(df1,df2,on['Date,Time],how=left)

but it misses many values where the time is not matching. Even if the exact time is not available i wanted the merge to happen with whatever time available before the required time.

https://pandas.pydata.org/docs/reference/api/pandas.merge_asof.html — Chris, Sep 24 '20 at 01:02

score 0 · Answer 1 · answered Sep 24 '20 at 01:03

0

try

pd.merge_asof(df1, df2, on=['Date','Time'], how='left', direction='backward')

answered Sep 24 '20 at 01:03

Chris

15,819
3
24
37

1

Please explain your solution! – Quality Catalyst Sep 24 '20 at 02:01

How to do a merge with conditions in python

1 Answers1