Thursday, 13 October 2022

Count of the total number of rows meeting a specific condition between two different dates of another DataFrame

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