1

I have to dfs that look like:

dfa:

ID   |          Time        |   Mode
B121   2017-10-19 11:00:00     Processed
C232   2017-10-19 12:00:00     Cancelled
B121   2017-10-19 15:00:00     Cancelled
C455   2017-10-19 18:00:00     Processed
N776   2017-10-19 11:00:00     Processed

dfb:

ID   |          Time        
B121   2017-10-19 11:43:32    
C232   2017-10-19 12:00:00    
B121   2017-10-19 15:33:55    
C455   2017-10-19 18:31:33     
N776   2017-10-19 11:08:00

I want to add the Time, and Mode columns from table A to table B based on joining them on ID and Time column.

But the times in table A are based on intervals for each hour and the times in table B are exact times.

How can I join or merge the two dfs based on if the timestamp from df B is in the hour time interval in df A?

Thanks

Chris90
  • 1,868
  • 5
  • 20
  • 42
  • Do these solve your problem https://stackoverflow.com/questions/34880539/pandas-merging-based-on-a-timestamp-which-do-not-match-exactly https://stackoverflow.com/questions/28773342/truncate-timestamp-column-to-hour-precision-in-pandas-dataframe – bbd108 Dec 10 '19 at 06:28
  • 3
    Use `df = pd.merge_asof(dfb.sort_values('Time'), dfa.sort_values('Time'), on='Time', by='ID')` – jezrael Dec 10 '19 at 06:30
  • @jezrael if I have two columns to join on such as two ID columns can I add the 2nd ID column to the by clause? – Chris90 Dec 10 '19 at 22:42

0 Answers0