Be the following python pandas DataFrame:
| num_ID | start_date | end_date | time |
| ------ | ----------- | ---------- | ----------------- |
| 1 | 2022-02-10 | 2022-02-11 | 0 days 09:23:00 |
| 1 | 2022-02-12 | 2022-02-15 | 2 days 12: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 |
| 3 | 2022-02-12 | 2022-02-15 | 2 days 05:21:00 |
| 3 | 2022-02-12 | 2022-02-15 | 2 days 05:15: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-10 | 2022-02-11 | 0 days 09:23:00 | 0 |
| 1 | 2022-02-12 | 2022-02-15 | 2 days 12: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 |
| 3 | 2022-02-12 | 2022-02-15 | 2 days 05:21:00 | 1 |
| 3 | 2022-02-12 | 2022-02-15 | 2 days 05:15:00 | 1 |
Edit: The solution offered by @jezrael adds more days by grouping by previous intervals. Wrong result:
| num_ID | start_date | end_date | time | total_days |
| ------ | ----------- | ---------- | ----------------- | -------------- |
| 1 | 2022-02-10 | 2022-02-11 | 0 days 09:23:00 | 0 |
| 1 | 2022-02-12 | 2022-02-15 | 2 days 12:23:00 | 3 |
| 2 | 2022-02-12 | 2022-02-15 | 2 days 10:23:00 | 3 |
| 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 |
| 3 | 2022-02-12 | 2022-02-15 | 2 days 05:21:00 | 3 |
New Edit: The new solution offered by @jezrael offers another error:
| num_ID | start_date | end_date | time | total_days |
| ------ | ----------- | ---------- | ----------------- | -------------- |
| 1 | 2022-02-10 | 2022-02-11 | 0 days 09:23:00 | 0 |
| 1 | 2022-02-12 | 2022-02-15 | 2 days 12: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 |
| 3 | 2022-02-12 | 2022-02-15 | 2 days 05:21:00 | 2 |
| 3 | 2022-02-12 | 2022-02-15 | 2 days 05:15:00 | 2 |
from Count of the total number of rows meeting a specific condition between two different dates of another DataFrame
No comments:
Post a Comment