Admin
1
I want to have a rolling count column that tracks the count of a specific value in a column. I want a rolling count of how many times the horse has finished 1st.
This is an example of what I have
| Horse | Position |
|---|---|
| a | 1 |
| a | 3 |
| a | 1 |
| b | 3 |
| b | 1 |
| b | 3 |
| c | 5 |
| c | 2 |
| c | 1 |
This is what I want
| Horse | Position | Count |
|---|---|---|
| a | 1 | 1 |
| a | 3 | 1 |
| a | 1 | 2 |
| b | 3 | 0 |
| b | 1 | 1 |
| b | 3 | 1 |
| c | 5 | 0 |
| c | 2 | 0 |
| c | 1 | 1 |
You can group-by "Horse" and then .cumsum over the first places in each group:
df["Count"] = df.groupby("Horse")["Position"].apply(lambda x: x.eq(1).cumsum())
print(df)
Prints:
Horse Position Count
0 a 1 1
1 a 3 1
2 a 1 2
3 b 3 0
4 b 1 1
5 b 3 1
6 c 5 0
7 c 2 0
8 c 1 1
First compare 1 and then use GroupBy.cumsum for avoid apply for improve performance:
df["Count"] = df["Position"].eq(1).groupby(df["Horse"]).cumsum()
Or create helper column:
df["Count"] = df.assign(new = df["Position"].eq(1)).groupby("Horse")['new'].cumsum()
print (df)
Horse Position Count
0 a 1 1
1 a 3 1
2 a 1 2
3 b 3 0
4 b 1 1
5 b 3 1
6 c 5 0
7 c 2 0
8 c 1 1
EDIT:
g = df.assign(new = df["Position"].eq(1)).groupby("Horse")['new']
df["Count"] = g.cumsum()
df['perc'] = g.transform('mean').mul(100)
print (df)
Horse Position Count perc
0 a 1 1 66.666667
1 a 3 1 66.666667
2 a 1 2 66.666667
3 b 3 0 33.333333
4 b 1 1 33.333333
5 b 3 1 33.333333
6 c 5 0 33.333333
7 c 2 0 33.333333
8 c 1 1 33.333333