Tagging ALL duplicates - Pandas Dataframe - even the first instace without 'NaN's in output

user_13214809 0
Viewed 37

I have this dataframe (example)

employees = [('Mohd', 28, 'NY'),
            ('Anne', 32, 'London'),
            ('Aaditya', 25, 'Mumbai'),
            ('Anne', 32, 'London'),
            ('Anne', 32, 'London'),
            ('Anne', 32, 'Mumbai'),
            ('Aaditya', 40, 'Dubai'),
            ('Link', 32, 'London')]

emp = pd.DataFrame(employees, columns = ['Name', 'Age', 'City'])

I want to find out duplicates in the form of city and store the value in the dataframe itself. If I do emp["duplname"] = emp.Name.duplicated(), I get

      Name  Age    City  duplname
0     Mohd   28      NY     False
1     Anne   32  London     *False*
2  Aaditya   25  Mumbai     *False*
3     Anne   32  London      True
4     Anne   32  London      True
5     Anne   32  Mumbai      True
6  Aaditya   40   Dubai      True
7     Link   32  London     False

However, I want the duplname within ** to be True too - since, technically, it is a duplicate. So I did this instead -

g = emp.groupby(['Name'])
df1 = emp.set_index(['Name'])
emp['dup_index'] = df1.index.map(lambda ind: g.indices[ind][0])
emp['counts'] = emp['dup_index'].value_counts()

But that gives me an output with NaNs

      Name  Age    City  duplname  dup_index  counts
0     Mohd   28      NY     False          0     1.0
1     Anne   32  London     False          1     4.0
2  Aaditya   25  Mumbai     False          2     2.0
3     Anne   32  London      True          1     NaN
4     Anne   32  London      True          1     NaN
5     Anne   32  Mumbai      True          1     NaN
6  Aaditya   40   Dubai      True          2     NaN
7     Link   32  London     False          7     1.0

NaN is not descriptive and sometimes the names are missing, so 'NaNs' is misleading. Is there a way I can tag ALL duplicates?

pandas dataframe duplicates python
1 Answers

Add keep=False to Series.duplicated to mark all duplicates:

emp["duplname"] = emp.Name.duplicated(keep=False)

emp:

      Name  Age    City  duplname
0     Mohd   28      NY     False
1     Anne   32  London      True
2  Aaditya   25  Mumbai      True
3     Anne   32  London      True
4     Anne   32  London      True
5     Anne   32  Mumbai      True
6  Aaditya   40   Dubai      True
7     Link   32  London     False
user_154978880
Related