Count consecutive days by product pandas

Question

I need know the number off days each product had sold by row. Exemple "In the day 1 de product AX1 had sold 3, and the product AX2 had sold 2 on the day 1"

I have this:

Product | Date       | Sales

AX1     |2019-01-01  | 3
AX1     |2019-01-02  | 2
AX2     |2019-01-01  | 2
AX2     |2019-01-02  | 1

But i need this:

Product | Date       | Sales | Day

AX1     |2019-01-01  | 3     | 1
AX1     |2019-01-02  | 2     | 2
AX2     |2019-01-01  | 2     | 1
AX2     |2019-01-02  | 1     | 2

Do you need `df['Day'] = df.groupby(['Product']).cumcount() + 1`? — cs95, Jun 24 '20 at 18:06

score 3 · Accepted Answer · answered Jun 24 '20 at 18:24

Try:

import numpy as np

# if not done already:
df["Date"]=pd.to_datetime(df["Date"])
df=df.sort_values(["Product", "Date"])

df["Days"]=df.groupby("Product")["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("Product")["Days"].cumsum(), df["Days"])

df["Days"]=df.groupby("Product")["Days"].cumsum().add(1).astype(int)

Outputs:

  Product       Date  Sales  Days
0     AX1 2019-01-01      3     1
1     AX1 2019-01-02      2     2
2     AX2 2019-01-01      2     1
3     AX2 2019-01-02      1     2

Count consecutive days by product pandas

1 Answers1