Python extract rows from dataframe where data is between two rows of anothe dataframe

Question

Dataset

df_one = ['2017-07-27 04:00:00', '2017-08-07 04:00:00', '2017-08-11 20:00:00', '2017-08-15 16:00:00', '2017-08-21 20:00:00', '2017-08-23 08:00:00', '2017-08-23 16:00:00', '2017-08-31 20:00:00', '2017-09-01 08:00:00', '2017-09-01 16:00:00', '2017-09-01 20:00:00', '2017-09-04 00:00:00', '2017-09-04 20:00:00', '2017-09-05 00:00:00', '2017-09-05 04:00:00', '2017-09-12 12:00:00', '2017-09-13 12:00:00', '2017-09-14 00:00:00', '2017-09-18 04:00:00', '2017-09-21 08:00:00', '2017-09-22 16:00:00', '2017-09-25 08:00:00', '2017-10-10 12:00:00', '2017-10-16 16:00:00', '2017-10-19 12:00:00', '2017-10-23 04:00:00', '2017-10-26 00:00:00', '2017-10-27 00:00:00', '2017-11-10 04:00:00', '2017-11-21 08:00:00', '2017-11-22 16:00:00', '2017-11-30 00:00:00', '2017-11-30 08:00:00', '2017-11-30 16:00:00', '2017-12-01 00:00:00', '2017-12-04 20:00:00', '2017-12-14 08:00:00', '2017-12-15 12:00:00', '2017-12-15 16:00:00', '2017-12-18 00:00:00', '2017-12-19 12:00:00', '2018-01-08 20:00:00', '2018-01-11 20:00:00', '2018-02-06 04:00:00', '2018-02-13 20:00:00', '2018-02-20 08:00:00', '2018-03-02 20:00:00', '2018-03-09 08:00:00', '2018-03-13 20:00:00', '2018-03-16 00:00:00', '2018-03-20 08:00:00', '2018-03-20 16:00:00', '2018-03-22 08:00:00', '2018-03-29 04:00:00', '2018-04-09 20:00:00', '2018-04-13 20:00:00', '2018-04-16 00:00:00', '2018-04-20 08:00:00', '2018-05-11 20:00:00', '2018-05-15 16:00:00', '2018-05-31 16:00:00', '2018-06-13 12:00:00', '2018-06-14 00:00:00', '2018-06-14 20:00:00', '2018-06-22 16:00:00', '2018-06-27 20:00:00', '2018-06-29 20:00:00', '2018-07-03 00:00:00', '2018-07-03 04:00:00', '2018-07-12 04:00:00', '2018-07-16 20:00:00', '2018-07-18 00:00:00', '2018-07-20 20:00:00', '2018-07-27 04:00:00', '2018-07-31 00:00:00', '2018-08-02 00:00:00', '2018-08-20 04:00:00', '2018-09-03 00:00:00', '2018-09-06 08:00:00', '2018-09-07 20:00:00', '2018-09-13 00:00:00', '2018-09-27 16:00:00', '2018-10-11 08:00:00', '2018-10-17 20:00:00', '2018-11-02 00:00:00', '2018-11-05 20:00:00', '2018-11-06 00:00:00', '2018-11-09 04:00:00', '2018-11-16 08:00:00', '2018-11-23 20:00:00', '2018-11-29 12:00:00', '2018-12-03 00:00:00', '2018-12-03 16:00:00', '2018-12-03 20:00:00', '2018-12-04 08:00:00', '2018-12-05 08:00:00', '2018-12-07 00:00:00', '2018-12-12 00:00:00', '2018-12-13 12:00:00', '2018-12-13 20:00:00', '2018-12-18 08:00:00', '2018-12-27 00:00:00', '2018-12-28 00:00:00', '2019-01-03 00:00:00', '2019-01-07 08:00:00', '2019-01-14 20:00:00', '2019-01-15 08:00:00', '2019-01-15 16:00:00', '2019-01-28 04:00:00', '2019-02-05 12:00:00', '2019-02-18 20:00:00', '2019-02-19 12:00:00', '2019-02-20 00:00:00', '2019-03-04 16:00:00', '2019-03-13 00:00:00', '2019-03-22 20:00:00', '2019-04-08 20:00:00', '2019-04-18 16:00:00', '2019-04-30 16:00:00', '2019-05-03 00:00:00', '2019-05-07 04:00:00', '2019-05-08 00:00:00', '2019-05-08 12:00:00', '2019-05-09 08:00:00', '2019-05-09 12:00:00', '2019-05-09 16:00:00', '2019-05-09 20:00:00', '2019-05-15 04:00:00', '2019-05-24 08:00:00', '2019-05-29 00:00:00', '2019-06-03 08:00:00', '2019-06-14 00:00:00', '2019-06-20 12:00:00', '2019-07-01 16:00:00', '2019-07-11 12:00:00', '2019-07-16 16:00:00', '2019-07-19 04:00:00', '2019-07-22 00:00:00', '2019-08-05 16:00:00', '2019-08-14 04:00:00', '2019-08-26 04:00:00', '2019-08-27 12:00:00', '2019-08-27 16:00:00', '2019-08-28 00:00:00', '2019-09-05 08:00:00', '2019-09-11 20:00:00', '2019-09-13 04:00:00', '2019-09-17 00:00:00', '2019-09-18 04:00:00', '2019-09-19 08:00:00', '2019-09-19 16:00:00', '2019-09-20 20:00:00', '2019-10-03 04:00:00', '2019-10-09 08:00:00', '2019-10-09 16:00:00', '2019-10-25 08:00:00', '2019-10-30 08:00:00', '2019-11-05 12:00:00', '2019-11-18 00:00:00', '2019-11-25 00:00:00', '2019-12-02 20:00:00', '2019-12-09 08:00:00', '2019-12-10 16:00:00', '2019-12-19 00:00:00', '2019-12-19 16:00:00', '2019-12-19 20:00:00', '2019-12-27 08:00:00', '2020-01-03 16:00:00', '2020-01-06 16:00:00', '2020-01-08 00:00:00', '2020-01-14 12:00:00', '2020-01-14 20:00:00', '2020-01-15 20:00:00', '2020-01-17 16:00:00', '2020-01-31 20:00:00', '2020-02-05 04:00:00', '2020-02-24 04:00:00', '2020-02-24 12:00:00', '2020-02-25 00:00:00', '2020-03-12 20:00:00', '2020-03-26 04:00:00', '2020-04-01 20:00:00', '2020-04-08 04:00:00', '2020-04-08 08:00:00', '2020-04-09 20:00:00', '2020-04-16 04:00:00', '2020-04-27 20:00:00', '2020-04-28 12:00:00', '2020-04-28 16:00:00', '2020-05-05 16:00:00', '2020-05-13 04:00:00', '2020-05-14 04:00:00', '2020-05-19 00:00:00', '2020-05-25 00:00:00', '2020-05-26 16:00:00', '2020-06-15 00:00:00', '2020-06-16 08:00:00', '2020-06-17 04:00:00', '2020-06-23 08:00:00', '2020-06-25 12:00:00', '2020-06-29 20:00:00', '2020-06-30 00:00:00', '2020-07-02 04:00:00', '2020-07-03 12:00:00', '2020-07-06 08:00:00', '2020-07-10 16:00:00', '2020-07-10 20:00:00', '2020-08-10 04:00:00', '2020-08-13 08:00:00', '2020-08-20 16:00:00', '2020-08-21 08:00:00', '2020-08-21 16:00:00', '2020-08-27 12:00:00', '2020-08-28 00:00:00', '2020-08-28 12:00:00', '2020-09-02 20:00:00', '2020-09-10 20:00:00', '2020-09-17 04:00:00', '2020-09-18 12:00:00', '2020-09-21 16:00:00', '2020-09-30 00:00:00', '2020-10-14 00:00:00', '2020-10-20 00:00:00', '2020-10-28 04:00:00', '2020-11-05 04:00:00', '2020-11-11 20:00:00', '2020-11-13 00:00:00', '2020-11-24 08:00:00', '2020-11-24 16:00:00', '2020-12-10 08:00:00', '2020-12-10 16:00:00', '2020-12-23 04:00:00', '2020-12-24 12:00:00', '2020-12-24 16:00:00', '2020-12-28 12:00:00', '2021-01-08 08:00:00', '2021-01-21 20:00:00', '2021-01-26 12:00:00', '2021-01-27 00:00:00', '2021-01-27 16:00:00', '2021-02-09 04:00:00', '2021-02-17 08:00:00', '2021-02-19 16:00:00', '2021-02-26 20:00:00', '2021-03-11 20:00:00', '2021-03-12 20:00:00', '2021-03-15 04:00:00', '2021-03-15 12:00:00', '2021-03-18 04:00:00', '2021-03-19 04:00:00', '2021-03-23 04:00:00', '2021-03-23 16:00:00', '2021-04-02 16:00:00', '2021-04-05 00:00:00', '2021-04-06 00:00:00', '2021-05-03 00:00:00', '2021-05-07 04:00:00', '2021-05-13 04:00:00', '2021-05-14 20:00:00', '2021-05-27 08:00:00', '2021-06-01 00:00:00', '2021-06-02 16:00:00', '2021-06-03 08:00:00', '2021-06-03 12:00:00']
df_two = ['2017-08-11 23:59', '2017-09-14 23:59', '2017-10-10 23:59', '2017-10-12 23:59', '2017-10-16 23:59', '2017-10-25 23:59', '2018-04-23 23:59', '2018-07-09 23:59', '2018-07-31 23:59', '2018-08-30 23:59', '2018-09-05 23:59', '2018-09-28 23:59', '2018-11-20 23:59', '2019-01-03 23:59', '2019-01-16 23:59', '2019-01-29 23:59', '2019-02-06 23:59', '2019-04-18 23:59', '2019-05-10 23:59', '2019-06-04 23:59', '2019-06-05 23:59', '2019-07-03 23:59', '2019-07-10 23:59', '2019-07-16 23:59', '2019-08-05 23:59', '2019-10-15 23:59', '2019-10-29 23:59', '2019-12-10 23:59', '2019-12-26 23:59', '2020-01-08 23:59', '2020-01-14 23:59', '2020-01-20 23:59', '2020-02-03 23:59', '2020-03-30 23:59', '2020-05-01 23:59', '2020-05-19 23:59', '2020-10-02 23:59', '2020-10-05 23:59', '2020-10-14 23:59', '2020-11-11 23:59', '2021-01-19 23:59', '2021-01-20 23:59', '2021-02-02 23:59', '2021-02-12 23:59', '2021-02-19 23:59', '2021-02-22 23:59', '2021-03-02 23:59', '2021-04-14 23:59', '2021-04-16 23:59', '2021-05-05 23:59', '2021-05-06 23:59']

I'm looking to find those previous & current rows from df_one where df_two date is in-between 2 consecutive rows of df_one

The sort of logic I'm looking to write is something on the lines below

for each row in df_two:
  for each row in df_one:
    if df_two > df_one_previous_row & df_two < df_one_current_row:
      print(df_one_previous_row & df_one_current_row)

Expected Output

2017-08-11 20:00:00 -  2017-08-11 23:59 - 2017-08-15 16:00:00
Found
2017-09-14 00:00:00 -  2017-09-14 23:59 - 2017-09-18 04:00:00
Found
2017-10-10 12:00:00 -  2017-10-10 23:59 - 2017-10-16 16:00:00
Found
2017-10-10 12:00:00 -  2017-10-12 23:59 - 2017-10-16 16:00:00
Found
2017-10-16 16:00:00 -  2017-10-16 23:59 - 2017-10-19 12:00:00
Found
2017-10-23 04:00:00 -  2017-10-25 23:59 - 2017-10-26 00:00:00
Found
2018-04-20 08:00:00 -  2018-04-23 23:59 - 2018-05-11 20:00:00
Found
2018-07-03 04:00:00 -  2018-07-09 23:59 - 2018-07-12 04:00:00
Found
2018-07-31 00:00:00 -  2018-07-31 23:59 - 2018-08-02 00:00:00
Found
2018-08-20 04:00:00 -  2018-08-30 23:59 - 2018-09-03 00:00:00
Found
2018-09-03 00:00:00 -  2018-09-05 23:59 - 2018-09-06 08:00:00
Found
2018-09-27 16:00:00 -  2018-09-28 23:59 - 2018-10-11 08:00:00
Found
2018-11-16 08:00:00 -  2018-11-20 23:59 - 2018-11-23 20:00:00
Found
2019-01-03 00:00:00 -  2019-01-03 23:59 - 2019-01-07 08:00:00
Found
2019-01-15 16:00:00 -  2019-01-16 23:59 - 2019-01-28 04:00:00
Found
2019-01-28 04:00:00 -  2019-01-29 23:59 - 2019-02-05 12:00:00
Found
2019-02-05 12:00:00 -  2019-02-06 23:59 - 2019-02-18 20:00:00
Found
2019-04-18 16:00:00 -  2019-04-18 23:59 - 2019-04-30 16:00:00
Found
2019-05-09 20:00:00 -  2019-05-10 23:59 - 2019-05-15 04:00:00
Found
2019-06-03 08:00:00 -  2019-06-04 23:59 - 2019-06-14 00:00:00
Found
2019-06-03 08:00:00 -  2019-06-05 23:59 - 2019-06-14 00:00:00
Found
2019-07-01 16:00:00 -  2019-07-03 23:59 - 2019-07-11 12:00:00
Found
2019-07-01 16:00:00 -  2019-07-10 23:59 - 2019-07-11 12:00:00
Found
2019-07-16 16:00:00 -  2019-07-16 23:59 - 2019-07-19 04:00:00
Found
2019-08-05 16:00:00 -  2019-08-05 23:59 - 2019-08-14 04:00:00
Found
2019-10-09 16:00:00 -  2019-10-15 23:59 - 2019-10-25 08:00:00
Found
2019-10-25 08:00:00 -  2019-10-29 23:59 - 2019-10-30 08:00:00
Found
2019-12-10 16:00:00 -  2019-12-10 23:59 - 2019-12-19 00:00:00
Found
2019-12-19 20:00:00 -  2019-12-26 23:59 - 2019-12-27 08:00:00
Found
2020-01-08 00:00:00 -  2020-01-08 23:59 - 2020-01-14 12:00:00
Found
2020-01-14 20:00:00 -  2020-01-14 23:59 - 2020-01-15 20:00:00
Found
2020-01-17 16:00:00 -  2020-01-20 23:59 - 2020-01-31 20:00:00
Found
2020-01-31 20:00:00 -  2020-02-03 23:59 - 2020-02-05 04:00:00
Found
2020-03-26 04:00:00 -  2020-03-30 23:59 - 2020-04-01 20:00:00
Found
2020-04-28 16:00:00 -  2020-05-01 23:59 - 2020-05-05 16:00:00
Found
2020-05-19 00:00:00 -  2020-05-19 23:59 - 2020-05-25 00:00:00
Found
2020-09-30 00:00:00 -  2020-10-02 23:59 - 2020-10-14 00:00:00
Found
2020-09-30 00:00:00 -  2020-10-05 23:59 - 2020-10-14 00:00:00
Found
2020-10-14 00:00:00 -  2020-10-14 23:59 - 2020-10-20 00:00:00
Found
2020-11-11 20:00:00 -  2020-11-11 23:59 - 2020-11-13 00:00:00
Found
2021-01-08 08:00:00 -  2021-01-19 23:59 - 2021-01-21 20:00:00
Found
2021-01-08 08:00:00 -  2021-01-20 23:59 - 2021-01-21 20:00:00
Found
2021-01-27 16:00:00 -  2021-02-02 23:59 - 2021-02-09 04:00:00
Found
2021-02-09 04:00:00 -  2021-02-12 23:59 - 2021-02-17 08:00:00
Found
2021-02-19 16:00:00 -  2021-02-19 23:59 - 2021-02-26 20:00:00
Found
2021-02-19 16:00:00 -  2021-02-22 23:59 - 2021-02-26 20:00:00
Found
2021-02-26 20:00:00 -  2021-03-02 23:59 - 2021-03-11 20:00:00
Found
2021-04-06 00:00:00 -  2021-04-14 23:59 - 2021-05-03 00:00:00
Found
2021-04-06 00:00:00 -  2021-04-16 23:59 - 2021-05-03 00:00:00
Found
2021-05-03 00:00:00 -  2021-05-05 23:59 - 2021-05-07 04:00:00
Found
2021-05-03 00:00:00 -  2021-05-06 23:59 - 2021-05-07 04:00:00
Found

Looping with a for or while is not so performance effective from the looks of it. Please could I get help to write a piece of code for this?

user_12833166 · Answer

We can use np.searchsorted to find the indices in df_one for the corresponding timestamps in df_two which satisfy the condition of inclusion. Note: The timestamps in df_one must be sorted in order for searchsorted to work properly

one = pd.to_datetime(df_one)
two = pd.to_datetime(df_two)

i = np.searchsorted(one, two)
m = ~np.isin(i, [0, len(one)])

df = pd.DataFrame({'df_two': two})
df.loc[m, 'df_one_prev'] = one[i[m] - 1]
df.loc[m, 'df_one_curr'] = one[i[m]]
df['found'] = np.where(m, 'found', 'not found')

                df_two         df_one_prev         df_one_curr  found
0  2017-08-11 23:59:00 2017-08-11 20:00:00 2017-08-15 16:00:00  found
1  2017-09-14 23:59:00 2017-09-14 00:00:00 2017-09-18 04:00:00  found
2  2017-10-10 23:59:00 2017-10-10 12:00:00 2017-10-16 16:00:00  found
3  2017-10-12 23:59:00 2017-10-10 12:00:00 2017-10-16 16:00:00  found
4  2017-10-16 23:59:00 2017-10-16 16:00:00 2017-10-19 12:00:00  found
5  2017-10-25 23:59:00 2017-10-23 04:00:00 2017-10-26 00:00:00  found
6  2018-04-23 23:59:00 2018-04-20 08:00:00 2018-05-11 20:00:00  found
7  2018-07-09 23:59:00 2018-07-03 04:00:00 2018-07-12 04:00:00  found
8  2018-07-31 23:59:00 2018-07-31 00:00:00 2018-08-02 00:00:00  found
9  2018-08-30 23:59:00 2018-08-20 04:00:00 2018-09-03 00:00:00  found
10 2018-09-05 23:59:00 2018-09-03 00:00:00 2018-09-06 08:00:00  found
11 2018-09-28 23:59:00 2018-09-27 16:00:00 2018-10-11 08:00:00  found
12 2018-11-20 23:59:00 2018-11-16 08:00:00 2018-11-23 20:00:00  found
13 2019-01-03 23:59:00 2019-01-03 00:00:00 2019-01-07 08:00:00  found
14 2019-01-16 23:59:00 2019-01-15 16:00:00 2019-01-28 04:00:00  found
15 2019-01-29 23:59:00 2019-01-28 04:00:00 2019-02-05 12:00:00  found
16 2019-02-06 23:59:00 2019-02-05 12:00:00 2019-02-18 20:00:00  found
17 2019-04-18 23:59:00 2019-04-18 16:00:00 2019-04-30 16:00:00  found
18 2019-05-10 23:59:00 2019-05-09 20:00:00 2019-05-15 04:00:00  found
19 2019-06-04 23:59:00 2019-06-03 08:00:00 2019-06-14 00:00:00  found
20 2019-06-05 23:59:00 2019-06-03 08:00:00 2019-06-14 00:00:00  found
21 2019-07-03 23:59:00 2019-07-01 16:00:00 2019-07-11 12:00:00  found
22 2019-07-10 23:59:00 2019-07-01 16:00:00 2019-07-11 12:00:00  found
23 2019-07-16 23:59:00 2019-07-16 16:00:00 2019-07-19 04:00:00  found
24 2019-08-05 23:59:00 2019-08-05 16:00:00 2019-08-14 04:00:00  found
25 2019-10-15 23:59:00 2019-10-09 16:00:00 2019-10-25 08:00:00  found
26 2019-10-29 23:59:00 2019-10-25 08:00:00 2019-10-30 08:00:00  found
27 2019-12-10 23:59:00 2019-12-10 16:00:00 2019-12-19 00:00:00  found
28 2019-12-26 23:59:00 2019-12-19 20:00:00 2019-12-27 08:00:00  found
29 2020-01-08 23:59:00 2020-01-08 00:00:00 2020-01-14 12:00:00  found
30 2020-01-14 23:59:00 2020-01-14 20:00:00 2020-01-15 20:00:00  found
31 2020-01-20 23:59:00 2020-01-17 16:00:00 2020-01-31 20:00:00  found
32 2020-02-03 23:59:00 2020-01-31 20:00:00 2020-02-05 04:00:00  found
33 2020-03-30 23:59:00 2020-03-26 04:00:00 2020-04-01 20:00:00  found
34 2020-05-01 23:59:00 2020-04-28 16:00:00 2020-05-05 16:00:00  found
35 2020-05-19 23:59:00 2020-05-19 00:00:00 2020-05-25 00:00:00  found
36 2020-10-02 23:59:00 2020-09-30 00:00:00 2020-10-14 00:00:00  found
37 2020-10-05 23:59:00 2020-09-30 00:00:00 2020-10-14 00:00:00  found
38 2020-10-14 23:59:00 2020-10-14 00:00:00 2020-10-20 00:00:00  found
39 2020-11-11 23:59:00 2020-11-11 20:00:00 2020-11-13 00:00:00  found
40 2021-01-19 23:59:00 2021-01-08 08:00:00 2021-01-21 20:00:00  found
41 2021-01-20 23:59:00 2021-01-08 08:00:00 2021-01-21 20:00:00  found
42 2021-02-02 23:59:00 2021-01-27 16:00:00 2021-02-09 04:00:00  found
43 2021-02-12 23:59:00 2021-02-09 04:00:00 2021-02-17 08:00:00  found
44 2021-02-19 23:59:00 2021-02-19 16:00:00 2021-02-26 20:00:00  found
45 2021-02-22 23:59:00 2021-02-19 16:00:00 2021-02-26 20:00:00  found
46 2021-03-02 23:59:00 2021-02-26 20:00:00 2021-03-11 20:00:00  found
47 2021-04-14 23:59:00 2021-04-06 00:00:00 2021-05-03 00:00:00  found
48 2021-04-16 23:59:00 2021-04-06 00:00:00 2021-05-03 00:00:00  found
49 2021-05-05 23:59:00 2021-05-03 00:00:00 2021-05-07 04:00:00  found
50 2021-05-06 23:59:00 2021-05-03 00:00:00 2021-05-07 04:00:00  found

Python extract rows from dataframe where data is between two rows of anothe dataframe

1 Answers