Combining dataframes based on datetime values

Question

I would like to combine two panda datasets based on a logic that compares time. I have the following two datasets.

df1

df1 = pd.DataFrame({'Timestamp': ['2022-03-20 08:25:01', '2022-03-20 08:25:02', '2022-03-20 08:25:03', '2022-03-20 08:25:04', '2022-03-20 08:25:05', '2022-03-20 08:25:06'],
                   'Temperature': ['650', '720', '40', '30', '500', '130']})

df2

df2 = pd.DataFrame({'Testphase': ['A1', 'A2', 'A3'],
                    'Begin_time': ['2022-03-20 08:25:01', '2022-03-20 08:25:04', '2022-03-20 08:25:30'],
                   'End_time': ['2022-03-20 08:25:03', '2022-03-20 08:25:05' , '2022-03-20 08:25:35']})

Desired df

Now I would like to add the Testphase to df1 based on the 'Begin_time' and 'End_time' of df2. If the time is between or on these times I would like to add the value of 'Testphase'. This is the desired result:

df_desired = pd.DataFrame({'Timestamp': ['2022-03-20 08:25:01', '2022-03-20 08:25:02', '2022-03-20 08:25:03', '2022-03-20 08:25:04', '2022-03-20 08:25:05', '2022-03-20 08:25:06'],
                   'Testphase': ['A1', 'A1', 'A1', 'A2', 'A2', 'NAN'],
                    'Temperature': ['650', '720', '40', '30', '500', '130']})

I had two ideas of doing this

Iterate a logic Begin_time<Timestamp<End_time over the rows of df1 and add the 'Testphase' when True
Create a new dataframe that is an exploded version of df2 with rows for every second and then merge the new dateframe to df1 with pandas.DataFrame.join using the timestamp.

But I couldn't figure out how to actually code it.

BENY · Accepted Answer · 2022-06-02 15:53:48Z

1

You can try with pd.IntervalIndex

#df2.Begin_time = pd.to_datetime(df2.Begin_time)
#df2.End_time = pd.to_datetime(df2.End_time)
df2.index = pd.IntervalIndex.from_arrays(left = df2.Begin_time,right = df2.End_time,closed='both')

df1['new'] = df2.Testphase.reindex(pd.to_datetime(df1.Timestamp)).tolist()
df1
Out[209]: 
             Timestamp Temperature  new
0  2022-03-20 08:25:01         650   A1
1  2022-03-20 08:25:02         720   A1
2  2022-03-20 08:25:03          40   A1
3  2022-03-20 08:25:04          30   A2
4  2022-03-20 08:25:05         500   A2
5  2022-03-20 08:25:06         130  NaN

answered Jun 2, 2022 at 15:53

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Combining dataframes based on datetime values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related