For each date – is it between any of the provided date bounds?

August 30, 2022

Data:

df:

            ts_code
2018-01-01    A   
2018-02-07    A
2018-03-11    A
2022-07-08    A

df_cal:

start_date  end_date
2018-02-07  2018-03-12
2018-10-22  2018-11-16
2019-01-07  2019-03-08
2019-03-11  2019-04-22
2019-05-24  2019-07-02
2019-08-06  2019-09-09
2019-10-09  2019-11-05
2019-11-29  2020-01-14
2020-02-03  2020-02-21
2020-02-28  2020-03-05
2020-03-19  2020-04-28
2020-05-06  2020-07-13
2020-07-24  2020-08-31
2020-11-02  2021-01-13
2020-09-11  2020-10-13
2021-01-29  2021-02-18
2021-03-09  2021-04-30
2021-05-06  2021-07-22
2021-07-28  2021-09-14
2021-10-12  2021-12-13
2022-04-27  2022-06-30

Expected result:

            ts_code col
2018-01-01    A      0
2018-02-07    A      1
2018-03-11    A      1
2022-07-08    A      0

Goal:

I want to assign values to a new column col: to 1 if df.index is between any of df_cal date ranges, and to 0 otherwise.

Reference:

I refer this post. But it just works for one condition and mine is lots of date ranges. And I don’t want to use dataframe join method to achieve it because it will break index order.

>Solution :

You check with numpy broadcasting

df2['new'] = np.any((df1.end_date.values >=df2.index.values[:,None])&
                    (df1.start_date.values <= df2.index.values[:,None]),1).astype(int)
df2
Out[55]: 
           ts_code  col  new
2018-01-01       A    0    0
2018-02-07       A    1    1
2018-03-11       A    1    1
2022-07-08       A    0    0