making duplicate values into unique

Question

Duplicate values of column needs to be converted to unique values

I have a dataframe with certain r*c. I need to consider one column which is basically the ID column having multiple duplicate IDs. The IDs would need to be made unique. Suppose I have the below mentioned df:

data = [['tom', 10], ['nick', 15], ['juli', 14], ['juli', 14], ['juli', 14]] 

df = pd.DataFrame(data, columns = ['Name', 'Age']) 

df 

Actual Result: 

    Name    Age
0   tom 10
1   nick    15
2   juli    14
3   juli    15
4   juli    16


Expected Result:

    Name    Age
0   tom 10
1   nick    15
2   juli_1  14
3   juli_2  15
4   juli_3  16

This will solve your problem https://stackoverflow.com/questions/30650474/python-rename-duplicates-in-list-with-progressive-numbers-without-sorting-list — Abhi, Oct 30 '19 at 09:50

ExplodingGayFish · Answer 1 · 2019-10-30T10:11:16.700

If you only want unique ID (Name in this case), you can try this:

data = [['tom', 10], ['nick', 15], ['juli', 14], ['juli', 14], ['juli', 14]] 
df = pd.DataFrame(data, columns = ['Name', 'Age']) 
suffix = df.groupby(df.Name)\
      .cumcount()\
      .astype(str)\
      .str.replace('0', '')\
      .values
df.Name = df.Name + suffix

Output:

    Name    Age
0   tom     10
1   nick    15
2   juli    14
3   juli1   14
4   juli2   14

score 0 · Answer 2 · answered Oct 30 '19 at 09:51

0

You can use a Window-function in combination with a Rank-function to make a new unique ID. See also the following post: SQL-like window functions in PANDAS: Row Numbering in Python Pandas Dataframe

answered Oct 30 '19 at 09:51

Joris Penders

11
3

score 0 · Answer 3 · answered Oct 30 '19 at 10:20

try this:

from collections import Counter
keys = [x[0] for x in data]
duplicates = [key for key,value in Counter(keys).items() if value>1]

for i in range(len(duplicates)):
    index = 0
    for j in range(len(data)):
        if data[j][0] == duplicates[i]:
            if index> 0:
                data[j][0] += str(index)
            index +=1

score 0 · Accepted Answer · answered Oct 31 '19 at 09:55

Here is what I tried and it worked for me.... I took help and declared a class for renaming duplicate values.

class renamer(): def init(self): self.d = dict()

def __call__(self, x):
    if x not in self.d:
        self.d[x] = 0
        return x
    else:
        self.d[x] += 1
        return "%s_%d" % (x, self.d[x])

and then I just used apply function to the dataframe column.

df['ID'] = df['ID'].apply(renamer())

making duplicate values into unique

4 Answers4