Assign id to item over groups with certain rules

user5t3Q 0
Viewed 25

I have a dataframe looks like this

df = pd.DataFrame({'type': ['A', 'A', 'A', 'B', 'B', 'B', 'A', 'A','C','D','C','D','D','A', 'A'],
                  })

I wanna create a unique id based on the group of the type column, but it will still cumsum when the type equals to 'A'

Eventually this output dataframe will look like this

df = pd.DataFrame({'type': ['A', 'A', 'A', 'B', 'B', 'B', 'A', 'A','C','D','C','D','D','A', 'A'],
                   'id': [1, 2, 3, 4, 4, 4, 5,6, 7, 8, 9, 10, 10, 11, 12],
                  })

Any help would be much appreciated

cumsum pandas python
1 Answers

You can try with shift with cumsum create the key , then assign the A with unique key

s = df.groupby(df.type.ne(df.type.shift()).cumsum()).cumcount().astype(str)
df['new'] = df['type']
df.loc[df.new.eq('A'),'new'] += s
df['new'] = df['new'].ne(df['new'].shift()).cumsum()
df
Out[58]: 
   type  new
0     A    1
1     A    2
2     A    3
3     B    4
4     B    4
5     B    4
6     A    5
7     A    6
8     C    7
9     D    8
10    C    9
11    D   10
12    D   10
13    A   11
14    A   12
BENY0
Related