Groupby and transform another column sequentially

user_5711767 0
Viewed 145

I have this dataframe

df1 = pd.DataFrame(data = {'id':[1,1,1,1,2,2,3],'task':[12,32,12,54,64,21,52]})

I want to group by id and change task values respectively like this

   id  task
0  1   1A   
1  1   2A   
2  1   3A   
3  1   4A   
4  2   1B   
5  2   2B   
6  3   1C

I have done this so far

df1['task']=df1.groupby('id')['task'].transform(lambda x : x.factorize()[0]+1)

Which gives me

   id  task
0  1   1   
1  1   2   
2  1   1   
3  1   3   
4  2   1   
5  2   2   
6  3   1

How can I get the alphabets and secondly why in id 1 the task sequence is 1213 but not 1234?

pandas-groupby pandas python
1 Answers
(df1.groupby('id').cumcount().add(1).astype(str)   # digit
 + df1['id'].add(ord('A') - 1).map(chr))           # letter

0    1A
1    2A
2    3A
3    4A
4    1B
5    2B
6    1C
dtype: object

There are two pieces - the digit, and the letter. Construct each one separately. First, the digits. Your code can be shortened using GroupBy.cumcount. Finally convert this result to a string so we can concatenate it with the letter later.

df1.groupby('id').cumcount().add(1).astype(str)

0    1
1    2
2    3
3    4
4    1
5    2
6    1
dtype: object

This gets the letter for the group.

df1['id'].add(ord('A') - 1).map(chr)

0    A
1    A
2    A
3    A
4    B
5    B
6    C
Name: id, dtype: object

Finally, add the intermediaries to get your final result.

user_49090870
Related