Map value base on group by result using pandas

Question

How to change this data frame

Index_Value         Value
value_1             1
value_1             2
value_1             3
value_2             33
value_3             111
value_3             222

to

Index_Value         Value       Counter
value_1             1           1
value_1             2           2
value_1             3           3
value_2             33          1
value_3             111         1
value_3             222         2

using pandas. I start to implement this using for loops but for large data set performance can be very poor.

score 2 · Answer 1 · answered Dec 17 '21 at 19:44

You should use cumcount after groupby:

import pandas as pd

df = pd.DataFrame({"Index_Value": ["v1", "v1", "v1", "v2", "v3", "v3"], "Value": list(range(6))})
df.set_index("Index_Value", inplace=True)

df.assign(Counter = df.groupby(df.index).cumcount() + 1)

OUTPUT

             Value  Counter
Index_Value
v1               0        1
v1               1        2
v1               2        3
v2               3        1
v3               4        1
v3               5        2

Map value base on group by result using pandas

1 Answers1