CODEWITHSUNDEEP
pythonpandaspandas-groupby
rahul
rahul

Reputation: 1243

Pandas cumulative count on new value

I have a data frame like the below one.

df = pd.DataFrame()
df['col_1'] = [1, 1, 1, 2, 2, 2, 3, 3, 3]
df['col_2'] = ['A', 'B', 'B', 'A', 'B', 'C', 'A', 'A', 'B']
df

  col_1 col_2
0   1   A
1   1   B
2   1   B
3   2   A
4   2   B
5   2   C
6   3   A
7   3   A
8   3   B

I need to group by on col_1 and within each group, I need to update cumulative count whenever there is a new value in col_2. Something like below data frame.

  col_1 col_2 col_3
0   1    A      1
1   1    B      2
2   1    B      2
3   2    A      1
4   2    B      2
5   2    C      3
6   3    A      1
7   3    A      1
8   3    B      2

I could do this using lists and dictionary. But couldn't find a way using pandas in built functions.

Upvotes: 1

Views: 88

Answers (1)

jezrael
jezrael

Reputation: 862641

Use factorize with lambda function in GroupBy.transform:

df['col_3'] = df.groupby('col_1')['col_2'].transform(lambda x: pd.factorize(x)[0]+1)
print (df)
   col_1 col_2  col_3
0      1     A      1
1      1     B      2
2      1     B      2
3      2     A      1
4      2     B      2
5      2     C      3
6      3     A      1
7      3     A      1
8      3     B      2

Upvotes: 2

Related Questions