Home > Software engineering >  Assign id to item over groups with certain rules
Assign id to item over groups with certain rules

Time:09-21

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

CodePudding user response:

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
  • Related