Count how may dates within a list are between two dates in a dataframe-CodePudding

I have a dataframe, df with two date columns and a list of dates and I'm trying to determine how many times dates within the list fall between dates in the two dataframe columns.

df:

Date1       Date2
2022-09-01  2022-09-07
2022-09-10  2022-09-22
2022-09-06  2022-09-21
2022-09-17  2022-09-25
2022-06-30  2022-09-21

list = [2022-09-16, 2022-07-01]

Here's what I would like!

df2:

Date1       Date2        Count
2022-09-01  2022-09-07   0
2022-09-10  2022-09-22   1
2022-09-06  2022-09-21   1
2022-09-17  2022-09-25   0
2022-06-30  2022-09-21   2

CodePudding user response：

Create series out of the date list, then for each row in the dataframe, call between on the series passing unpacked row values, then finally call sum for axis=1:

>>> list_ = pd.to_datetime(['2022-09-16', '2022-07-01']).to_series()
>>> df['Count'] = df.apply(lambda x: list_.between(*x), axis=1).sum(1)

OUTPUT

       Date1      Date2  Count
0 2022-09-01 2022-09-07      0
1 2022-09-10 2022-09-22      1
2 2022-09-06 2022-09-21      1
3 2022-09-17 2022-09-25      0
4 2022-06-30 2022-09-21      2