0

For the below dataframe df1 I need to get all the datas between the opening and closing time from df2

Eg: Consider 3rd row of df1, in df2 i have the data between the opening and closing time of df which I need to join to df1

df1

closingTime openingTime Name
2022-01-31 11:20:00 2022-01-31 10:41:00 David
2022-04-07 13:49:00 2022-04-07 09:56:00 Alex
2021-11-25 13:49:00 2021-11-25 13:46:00 Hannah
2021-11-16 10:20:00 2021-11-16 10:18:00 Nan
2022-01-03 11:08:00 2022-01-03 10:00:00 John

df2:

time Price
2021-11-25 13:46:00 1.12
2021-11-25 13:47:00 1.10
2021-11-25 13:48:00 1.16
2021-11-25 13:49:00 1.11
2021-11-25 13:50:00 1.21
2021-11-25 13:51:00 0.9
2021-11-25 13:52:00 1.17

Is there anyways to join the df to get the values between two time intervals?

GeekGroot
  • 102
  • 6

1 Answers1

1

You can use the cross product of your 2 dataframes:

>>> df1.merge(df2, how='cross').query('(openingTime<=time) & (time<=closingTime)')

            closingTime          openingTime    Name                 time  Price
14  2021-11-25 13:49:00  2021-11-25 13:46:00  Hannah  2021-11-25 13:46:00   1.12
15  2021-11-25 13:49:00  2021-11-25 13:46:00  Hannah  2021-11-25 13:47:00   1.10
16  2021-11-25 13:49:00  2021-11-25 13:46:00  Hannah  2021-11-25 13:48:00   1.16
17  2021-11-25 13:49:00  2021-11-25 13:46:00  Hannah  2021-11-25 13:49:00   1.11
Corralien
  • 109,409
  • 8
  • 28
  • 52