I have big dataframe df which contains datetime columns as follows
| created | assigned | delivered |
|---|---|---|
| 2022-08-10 12:20:00 | 2022-08-10 12:30:00 | 2022-08-10 12:40:00 |
| 2022-08-10 12:21:00 | 2022-08-10 12:33:00 | 2022-08-10 12:45:00 |
| 2022-08-10 12:25:00 | 2022-08-10 12:36:00 | 2022-08-10 12:49:00 |
| 2022-08-10 12:30:00 | 2022-08-10 12:38:00 | 2022-08-10 12:55:00 |
| 2022-08-10 12:35:00 | 2022-08-10 12:42:00 | 2022-08-10 12:59:00 |
I need help to create a new dataframe df2 which tells me at every created time stamp, how many jobs were assigned and unassigned. I am not able to find the right logic to do this
Unassigned jobs are the jobs not assigned at the created time
Assigned jobs are the jobs between assigned and delivered at the time of the created timestamp.
The resulting dataframe should look like
| created | unassigned_jobs | assigned_jobs |
|---|---|---|
| 2022-08-10 12:20:00 | 1 | 0 |
| 2022-08-10 12:21:00 | 2 | 0 |
| 2022-08-10 12:25:00 | 3 | 0 |
| 2022-08-10 12:30:00 | 3 | 1 |
| 2022-08-10 12:35:00 | 3 | 2 |