1

I'm adding the counter to the end of the unique rows but unable to do.I have 4 columns namely "ID", "Name","Amount".The problem I'm facing is that i want to add counter at the end of unique row "Id" column but make sure that i am also considering other unique rows as well.

This is the data frame i am using.

   Amount    ID Name
0     110  c121  abc
1     120  c121  abc
2     120  c123  sdd
3     140  c124  eet

df = {'ID':['c121', 'c121', 'c123', 'c124'], 'Name':['abc', 'abc','sdd','eet'],'Amount':[110,120,120,140]} 
df = pd.DataFrame(df)

current df

 Amount    ID Name
0     110  c121  abc
1     120  c121  abc
2     120  c123  sdd
3     140  c124  eet

Expected result:

 Amount    ID    Name
0     110  c121_1  abc
1     120  c121_2  abc
2     120  c123    sdd
3     140  c124    eet
bharatk
  • 4,202
  • 5
  • 16
  • 30
Maddy6
  • 367
  • 1
  • 3
  • 14

1 Answers1

1

First filter only duplicated values by Series.duplicated, filter them with boolean indexing and add counter by GroupBy.cumcount, use Series.add for starting by 1, convert values to strings and use Series.radd for add _ with right side. Then assign to filtered ID column with +=:

#for test duplicates in ID column
m = df['ID'].duplicated(keep=False)
#for test duplicates in Name and ID columns
#m = df.duplicated(['Name', 'ID'], keep=False)
df.loc[m, 'ID'] += df[m].groupby('ID').cumcount().add(1).astype(str).radd('_')

Alternative:

df.loc[m, 'ID'] = df.loc[m, 'ID'] + '_' + df[m].groupby('ID').cumcount().add(1).astype(str)

print (df)
       ID Name  Amount
0  c121_1  abc     110
1  c121_2  abc     120
2    c123  sdd     120
3    c124  eet     140
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252