a running count of based on multiple grouping and reset

user_14654691 0
Viewed 63
df = pd.DataFrame({"Date":['7.6.2021','7.6.2021','7.6.2021','8.6.2021','8.6.2021','8.6.2021','9.6.2021','9.6.2021','9.6.2021','10.6.2021','10.6.2021','10.6.2021'],
                  "WH": ['k','a','b','k','k','a','k','b','c','b','k','k']})

A running count based on the value of column "WH", in that a value of "k' is counted as a running total until the date changes. Thereafter count goes to 0 and the process repeats as the date changes.

Desired output:


        Date    WH  count
0   7.6.2021    k   1
1   7.6.2021    a   1
2   7.6.2021    b   1

3   8.6.2021    k   1
4   8.6.2021    k   2
5   8.6.2021    a   2

6   9.6.2021    k   1
7   9.6.2021    b   1
8   9.6.2021    c   1

9   10.6.2021   b   0
10  10.6.2021   k   1
11  10.6.2021   k   2
pandas numpy python
2 Answers
df["count"] = df.groupby("Date")["WH"].transform(lambda x: x.eq("k").cumsum())
print(df)

Prints:

         Date WH  count
0    7.6.2021  k      1
1    7.6.2021  a      1
2    7.6.2021  b      1
3    8.6.2021  k      1
4    8.6.2021  k      2
5    8.6.2021  a      2
6    9.6.2021  k      1
7    9.6.2021  b      1
8    9.6.2021  c      1
9   10.6.2021  b      0
10  10.6.2021  k      1
11  10.6.2021  k      2
user_100359850

Try this:

df['count'] = df['WH'].eq('k').groupby(df['Date']).cumsum()
df

Output:

         Date WH  count
0    7.6.2021  k      1
1    7.6.2021  a      1
2    7.6.2021  b      1
3    8.6.2021  k      1
4    8.6.2021  k      2
5    8.6.2021  a      2
6    9.6.2021  k      1
7    9.6.2021  b      1
8    9.6.2021  c      1
9   10.6.2021  b      0
10  10.6.2021  k      1
11  10.6.2021  k      2

Timings:

%timeit df['count'] = df['WH'].eq('k').groupby(df['Date']).cumsum()

818 µs ± 18.9 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

%timeit df["count"] = df.groupby("Date")["WH"].transform(lambda x: x.eq("k").cumsum())

2.45 ms ± 47.3 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

%%timeit
m = df['Date'] != df['Date'].shift()
df['count'] = df['WH'].eq('k').groupby(m.cumsum()).cumsum()

1.08 ms ± 37.8 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

user_63615310
Related