Look a Value in column and change in a Dataframe

user_11082866 0
Viewed 57

I have a Dataframe like this:

Name

foo
foo
bar
foo
bar
shoo
shoo
foo
bar
bar

I want to add the position of occurrence in the next column, something like Serialized Name

Expected Output:

Name    Serialized Name

foo      foo-1
foo      foo-2
bar      bar-1
foo      foo-3
bar      bar-2
shoo     shoo-1
shoo     shoo-2
foo      foo-4
bar      bar-3
bar      bar-4

My approach was to get the unique values of the column list(df['Name'].unique()) and their count list(df['Name'].value_counts()) but I am not exactly sure how to proceed

pandas python
2 Answers

Use, GroupBy.cumcount

df["Serialized Name"] = (
    df['Name'] + "-" + (df.groupby("Name").cumcount() + 1).astype(str)
)

   Name Serialized Name
0   foo           foo-1
1   foo           foo-2
2   bar           bar-1
3   foo           foo-3
4   bar           bar-2
5  shoo          shoo-1
6  shoo          shoo-2
7   foo           foo-4
8   bar           bar-3
9   bar           bar-4
user_49850990

This code will work

for i in df['Name'].unique():
  exec(f'{i}_count=0')

for i in range(len(df)):
  if df['Name'][i]=='foo':
    foo_count+=1
    df['Serialized Name'][i]=f'foo-{foo_count}'
  elif df['Name'][i]=='bar':
    bar_count+=1
    df['Serialized Name'][i]=f'bar-{bar_count}'
  else:
    shoo_count+=1
    df['Serialized Name'][i]=f'shoo-{shoo_count}'
user_137538460
Related