Data:
df
:
JavaScript
x
6
1
ts_code
2
2018-01-01 A
3
2018-02-07 A
4
2018-03-11 A
5
2022-07-08 A
6
df_cal
:
JavaScript
1
23
23
1
start_date end_date
2
2018-02-07 2018-03-12
3
2018-10-22 2018-11-16
4
2019-01-07 2019-03-08
5
2019-03-11 2019-04-22
6
2019-05-24 2019-07-02
7
2019-08-06 2019-09-09
8
2019-10-09 2019-11-05
9
2019-11-29 2020-01-14
10
2020-02-03 2020-02-21
11
2020-02-28 2020-03-05
12
2020-03-19 2020-04-28
13
2020-05-06 2020-07-13
14
2020-07-24 2020-08-31
15
2020-11-02 2021-01-13
16
2020-09-11 2020-10-13
17
2021-01-29 2021-02-18
18
2021-03-09 2021-04-30
19
2021-05-06 2021-07-22
20
2021-07-28 2021-09-14
21
2021-10-12 2021-12-13
22
2022-04-27 2022-06-30
23
Expected result:
JavaScript
1
6
1
ts_code col
2
2018-01-01 A 0
3
2018-02-07 A 1
4
2018-03-11 A 1
5
2022-07-08 A 0
6
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.
Advertisement
Answer
You check with numpy
broadcasting
JavaScript
1
10
10
1
df2['new'] = np.any((df1.end_date.values >=df2.index.values[:,None])&
2
(df1.start_date.values <= df2.index.values[:,None]),1).astype(int)
3
df2
4
Out[55]:
5
ts_code col new
6
2018-01-01 A 0 0
7
2018-02-07 A 1 1
8
2018-03-11 A 1 1
9
2022-07-08 A 0 0
10