Create new column based on values from another one-CodePudding

My df looks like below:

column1  column2 
x        X22
x        X26        
x        X287
y        X26
y        X22
y        X287
y        X26
z        X27
c        X29
c        X22

I would like to create a new column and output should be like this:

column1  column2 column3
x        X22      1
x        X26      2 
x        X287     3
y        X26      1
y        X22      2
y        X287     3
y        X26      4
z        X27      1
c        X29      1
c        X22      2

So as you can see I need a column with values from 1 to x based on column1. So if in column1 we have value "x" I would like to create a column 3 with values from 1 to 3. I can't use groupby because I have to keep my column2. Do you have any idea?

Regards Tomasz

CodePudding user response：

You can use groupby cumcount:

df['column3'] = df.groupby('column1').cumcount().add(1)

output:

  column1 column2  column3
0       x     X22        1
1       x     X26        2
2       x    X287        3
3       y     X26        1
4       y     X22        2
5       y    X287        3
6       y     X26        4
7       z     X27        1
8       c     X29        1
9       c     X22        2