Count of the total number of rows meeting a specific condition between two different dates of anothe-CodePudding

Be the following python pandas DataFrame:

| num_ID | start_date  | end_date   | time              |
| ------ | ----------- | ---------- | ----------------- |
| 1      | 2022-02-14  | 2022-02-15 | 0 days 09:23:00   |
| 2      | 2022-02-12  | 2022-02-15 | 2 days 10:23:00   |
| 2      | 2022-02-05  | 2022-02-27 | 22 days 02:35:00  |
| 3      | 2022-02-04  | 2022-02-06 | 1 days 19:55:00   |

And the following DataFrame containing consecutive dates with their respective holiday values in the is_holiday column.

| date       | is_holiday | name | other |
| ---------- | ---------- | ---- | ----- |
| 2022-01-01 | True       | ABC  | red   |
| 2022-01-02 | False      | CNA  | blue  |
...
# we assume in this case that the omitted rows have the value False in column 
| 2022-02-15 | True       | OOO  | red   |
| 2022-02-16 | True       | POO  | red   |
| 2022-02-17 | False      | KTY  | blue  |
...
| 2023-12-30 | False      | TTE  | white |
| 2023-12-31 | True       | VVV  | red   |

I want to add a new column total_days to the initial DataFrame that indicates the total holidays marked True in second DataFrame that each row passes between the two dates (start_date and end_date).

Output result example:

| num_ID | start_date  | end_date   | time              | total_days     |
| ------ | ----------- | ---------- | ----------------- | -------------- |
| 1      | 2022-02-14  | 2022-02-15 | 0 days 09:23:00   | 1              |
| 2      | 2022-02-12  | 2022-02-15 | 2 days 10:23:00   | 1              |
| 2      | 2022-02-05  | 2022-02-27 | 22 days 02:35:00  | 2              |
| 3      | 2022-02-04  | 2022-02-06 | 1 days 19:55:00   | 0              |

CodePudding user response：

Use DataFrame.merge with cross join by rows with only Trues filtering by column holiday, filter by Series.between and count by GroupBy.size, last add new column with DataFrame.join:

df2 = df.merge(df1.loc[df1['holiday'], ['date']], how='cross')
s = (df2[df2['date'].between(df2["start_date"],df2["end_date"])]
       .groupby(['start_date','end_date']).size())

df = df.join(s.rename('total_holidays'), on=['start_date','end_date'])
df['total_holidays'] = df['total_holidays'].fillna(0, downcast='int')
print (df)
   num_ID start_date   end_date        total_time  total_holidays
0       1 2022-02-14 2022-02-15   0 days 09:23:00               1
1       2 2022-02-12 2022-02-15   2 days 10:23:00               1
2       2 2022-02-05 2022-02-27  22 days 02:35:00               2
3       3 2022-02-04 2022-02-06   1 days 19:55:00               0

CodePudding user response：

If your data is small, a cartesian join is fine; as your data increases, it becomes inefficient, as you are comparing every row between both dataframes. A better way is to use some form of binary search, to get your matches - conditional_join from pyjanitor offers an efficient way for non-equi joins:

# pip install pyjanitor
# you can install the dev version for latest improvements
# pip install git   https://github.com/pyjanitor-devs/pyjanitor.git
import pandas as pd
import janitor

df.start_date = pd.to_datetime(df.start_date)
df.end_date = pd.to_datetime(df.end_date)
df2.date = pd.to_datetime(df2.date)
# relevant columns
cols = [*df.columns, 'is_holiday']

out = (df
       .conditional_join(
            df2.loc[df2.is_holiday == "True"], 
            ('start_date', 'date', '<='), 
            ('end_date', 'date', '>='), 
            how = 'inner')
       .loc(axis = 1)[cols]
       .groupby(cols[:-1])
       .size()
       .rename('total_days')
       )

Merge back to the original dataframe to get the final output

(df
.merge(out, how = 'left', on = cols[:-1])
# fillna is faster on a Series
.assign(total_days = lambda df: df.total_days.fillna(0, downcast = 'infer'))
) 
   num_ID start_date   end_date              time  total_days
0       1 2022-02-14 2022-02-15   0 days 09:23:00           1
1       2 2022-02-12 2022-02-15   2 days 10:23:00           1
2       2 2022-02-05 2022-02-27  22 days 02:35:00           2
3       3 2022-02-04 2022-02-06   1 days 19:55:00           0

With the dev version, you could preselect columns and also possibly avoid the merge back to the original dataframe. At any rate, for performance, if you can, avoid a cross join.