Fairly new to this so hopefully my query makes sense!
I have a dataset which covers numerous drivers and dates they have worked, example below:
| Driver | Date |
|---|---|
| Steve | 11/02/2022 |
| Steve | 14/02/2022 |
| Steve | 15/02/2022 |
| Steve | 16/02/2022 |
| Steve | 17/02/2022 |
| Steve | 18/02/2022 |
| Steve | 20/02/2022 |
| Graham | 11/02/2022 |
| Graham | 12/02/2022 |
| Graham | 14/02/2022 |
| Graham | 15/02/2022 |
| Graham | 16/02/2022 |
| Graham | 18/02/2022 |
| Graham | 19/02/2022 |
| Graham | 20/02/2022 |
I am trying to calculate the consecutive days each has worked, to come out as below:
| Driver | Date | Days |
|---|---|---|
| Steve | 11/02/2022 | 1 |
| Steve | 14/02/2022 | 1 |
| Steve | 15/02/2022 | 2 |
| Steve | 16/02/2022 | 3 |
| Steve | 17/02/2022 | 4 |
| Steve | 18/02/2022 | 5 |
| Steve | 20/02/2022 | 1 |
| Graham | 11/02/2022 | 1 |
| Graham | 12/02/2022 | 2 |
| Graham | 14/02/2022 | 1 |
| Graham | 15/02/2022 | 2 |
| Graham | 16/02/2022 | 3 |
| Graham | 18/02/2022 | 1 |
| Graham | 19/02/2022 | 2 |
| Graham | 20/02/2022 | 3 |
So far i have managed to find the code below (by Grzegorz Skibinski on here) which seems to work in general. However i am getting some negative values, which seem to be calculated where it resets to 0 more than once. As i say i am fairly new to this and am not totally familiar with what the code is doing. I am just wondering if anything obvious stands out, or if this is not suitable for what i need.
df3["Date"]=pd.to_datetime(df3["Date"])
df3=df3.sort_values(["Driver", "Date"])
df["Days"]=df.groupby("Driver")["Date"].diff()
mask=df["Days"].isna()
df["Days"]=df["Days"].eq(pd.to_timedelta("1 days"))
df["Days"]=np.where(~df["Days"]&~mask, -df.groupby("Driver")["Days"].cumsum(), df["Days"])
df["Days"]=df.groupby("Driver")["Days"].cumsum().add(1).astype(int)
Many thanks