Pandas: Join Dataframes Based On Time Interval

I have a data frame with a datetime column every 10 minutes and a numerical value: df1 = pd.DataFrame({'time' : pd.date_range('1/1/2018', periods=20, freq='10min'), 'value' : np.ra

Solution 1:

Setup (Only using a few entries from df1 for brevity):

df1 = pd.DataFrame({'time' : pd.date_range('1/1/2018', periods=20, freq='10min'), 'value' : np.random.randint(2, 20, size=20)})
df2 = pd.DataFrame({'start_time' : ['2018-01-01 00:00:00', '2018-01-01 00:00:00','2018-01-01 01:00:00', '2018-01-01 01:00:00', '2018-01-01 01:00:00', '2018-01-01 02:00:00' ], 'end_time' : ['2018-01-01 01:00:00', '2018-01-01 01:00:00', '2018-01-01 02:00:00','2018-01-01 02:00:00', '2018-01-01 02:00:00', '2018-01-01 03:00:00'], 'event' : ['A', 'B', 'C', 'D', 'E', 'F'] })

df1 = df1.sample(5)
df2[['start_time', 'end_time']] = df2.iloc[:,0:2].apply(pd.to_datetime)

You can use a couple of straightfoward list comprehensions to achieve your result. This answer assumes that all date columns are in fact, of type datetime in your DataFrame:

Step 1 Find all events that occur within a particular time range using a list comprehension and simple interval checking:

packed=list(zip(df2.start_time,df2.end_time,df2.event))df1['event']= [[evforstrt, end, evinpackedifstrt<=el<=end] forelindf1.time]

                  timevalueevent22018-01-01 00:20:00      8     [A, B]
142018-01-01 02:20:00     14        [F]
82018-01-01 01:20:00      6  [C, D, E]
192018-01-01 03:10:00     16         []
42018-01-01 00:40:00      7     [A, B]

Step 2:

Finally, explode each list from the last result to a new row using another list comprehension:

pd.DataFrame(
    [[t, val, e] for t, val, eventinzip(df1.time, df1.value, df1.event)
    for e inevent
    ], columns=df1.columns
)

Output:

timevalueevent02018-01-01 00:20:00      8A12018-01-01 00:20:00      8B22018-01-01 02:20:00     14F32018-01-01 01:20:00      6C42018-01-01 01:20:00      6D52018-01-01 01:20:00      6E62018-01-01 00:40:00      7A72018-01-01 00:40:00      7B

Solution 2:

I'm not entirely sure of your question, but if you are trying to join on "events that fall inside the start and end times," then sounds like you need something akin to a "between" operator from SQL. You're data doesn't make it particularly clear.

Pandas doesn't have this natively, but Pandasql does. It allows you to run sqlite against you're dataframe. I think something like this is what you need:

import pandasql as ps

sqlcode = '''
select *
from df1
inner join df2 on df1.event=df2.event
where df2.time >= d1.start_time and df2.fdate <= d1.stop_time
'''

newdf = ps.sqldf(sqlcode,locals())

Relevant Question: Merge pandas dataframes where one value is between two others

Solution 3:

You can work on df2 to create a column with all the time with a resampling '10min' (like in df1) for each event, and then use merge. It's a lot of manipulation so probably not the most efficient.

df2_manip = (df2.set_index('event').stack().reset_index().set_index(0)
                .groupby('event').resample('10T').ffill().reset_index(1))

and df2_manip looks like:

0eventlevel_1eventA2018-01-0100:00:00Astart_timeA2018-01-0100:10:00Astart_timeA2018-01-0100:20:00Astart_timeA2018-01-0100:30:00Astart_timeA2018-01-0100:40:00Astart_timeA2018-01-0100:50:00Astart_timeA2018-01-0101:00:00Aend_timeB2018-01-0100:00:00Bstart_timeB2018-01-0100:10:00Bstart_timeB2018-01-0100:20:00Bstart_timeB2018-01-0100:30:00Bstart_time
...

Now you can merge:

df1 = df1.merge(df2_manip[[0, 'event']].rename(columns={0:'time'}))

and you get df1:

timevalueevent02018-01-01 00:00:00      9A12018-01-01 00:00:00      9B22018-01-01 00:10:00     16A32018-01-01 00:10:00     16B...332018-01-01 02:00:00      6D342018-01-01 02:00:00      6E352018-01-01 02:00:00      6F362018-01-01 02:10:00      2F372018-01-01 02:20:00     18F382018-01-01 02:30:00     14F392018-01-01 02:40:00      5F402018-01-01 02:50:00      3F412018-01-01 03:00:00      9F