Produce Unique value for duplicates in column using Pandas/Python

Question

I have a dataset, df, where I would like to create unique ids for the values in the type column by placing numbers on the end.

Data

type    total   free  use
a       10      5     5
a       10      4     6
a       10      1     9
a       10      8     2
a       10      3     7
b       20      5     5
b       20      3     7
b       20      2     8
b       20      6     4
b       20      2     8

Desired

type    total   free  use
a       10      5     5
a1      10      4     6
a2      10      1     9
a3      10      8     2
a4      10      3     7
b       20      5     5
b1      20      3     7
b2      20      2     8
b3      20      6     4
b4      20      2     8

Doing

I was able to do this in R by doing, but unsure of how to do this in Python:

library(data.table)
setDT(DT)

DT[ , run_id := rleid(ID)]
DT[DT[ , .SD[1L], by = run_id][duplicated(ID), ID := paste0('list', .I)],
   on = 'run_id', ID := i.ID][]

I am researching this, any input is appreciated

Quang Hoang · Accepted Answer · 2020-12-30 01:15:36Z

5

You can use groupby.cumcount:

df['type'] += np.where(df['type'].duplicated(),
                       df.groupby('type').cumcount().astype(str), 
                       '')

Or similarly with loc update:

df.loc[df['type'].duplicated(), 'type'] += df.groupby('type').cumcount().astype(str)

Output:

  type  total  free  use
0    a     10     5    5
1   a1     10     4    6
2   a2     10     1    9
3   a3     10     8    2
4   a4     10     3    7
5    b     20     5    5
6   b1     20     3    7
7   b2     20     2    8
8   b3     20     6    4
9   b4     20     2    8

answered Dec 30, 2020 at 1:15

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lynn Over a year ago

Thank you Quang, so the groupby.cumcount() function is what creates the consecutive values? Could you tell me what is the += . Is this shorthand for df['type'] = df['type'] + np.where....

Collectives™ on Stack Overflow

Produce Unique value for duplicates in column using Pandas/Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related