How to efficiently assign unique ID to individuals with multiple entries based on name in very large df

by

This approach uses .groupby() and .ngroup() (new in Pandas 0.20.2) to create the id column:

df['id'] = df.groupby(['LastName','FirstName']).ngroup()
>>> df

   First    Second  id
0    Tom     Jones   0
1    Tom     Jones   0
2  David     Smith   1
3   Alex  Thompson   2
4   Alex  Thompson   2

I checked timings and, for the small dataset in this example, Alexander’s answer is faster:

%timeit df.assign(id=(df['LastName'] + '_' + df['FirstName']).astype('category').cat.codes)
1000 loops, best of 3: 848 µs per loop

%timeit df.assign(id=df.groupby(['LastName','FirstName']).ngroup())
1000 loops, best of 3: 1.22 ms per loop

However, for larger dataframes, the groupby() approach appears to be faster. To create a large, representative data set, I used faker to create a dataframe of 5000 names and then concatenated the first 2000 names to this dataframe to make a dataframe with 7000 names, 2000 of which were duplicates. 

import faker
fakenames = faker.Faker()
first = [ fakenames.first_name() for _ in range(5000) ]
last = [ fakenames.last_name() for _ in range(5000) ]
df2 = pd.DataFrame({'FirstName':first, 'LastName':last})
df2 = pd.concat([df2, df2.iloc[:2000]])

Running the timing on this larger data set gives:

%timeit df2.assign(id=(df2['LastName'] + '_' + df2['FirstName']).astype('category').cat.codes)
100 loops, best of 3: 5.22 ms per loop

%timeit df2.assign(id=df2.groupby(['LastName','FirstName']).ngroup())
100 loops, best of 3: 3.1 ms per loop

You may want to test both approaches on your data set to determine which one works best given the size of your data.

More Related Contents:

  1. How are iloc and loc different?
  2. How to convert index of a pandas dataframe into a column
  3. How to reset index in a pandas dataframe? [duplicate]
  4. What rules does Pandas use to generate a view vs a copy?
  5. Pandas split DataFrame by column value
  6. Selecting a row of pandas series/dataframe by integer index
  7. What is the difference between using squared brackets or dot to access a column?
  8. Accessing Pandas column using squared brackets vs using a dot (like an attribute)
  9. Get column index from column name in python pandas
  10. Python Pandas: Get index of rows where column matches certain value
  11. Python: Removing Rows on Count condition
  12. Using replace efficiently in pandas
  13. In Python pandas, start row index from 1 instead of zero without creating additional column
  14. Selecting a range of columns in a dataframe
  15. start index at 1 for Pandas DataFrame
  16. How do I select rows from a DataFrame based on column values?
  17. Aggregation in Pandas
  18. How do I find the closest values in a Pandas series to an input number?
  19. How do I count the NaN values in a column in pandas DataFrame?
  20. getting the index of a row in a pandas apply function
  21. Tilde sign in pandas DataFrame
  22. Drop columns whose name contains a specific string from pandas DataFrame
  23. Python Pandas How to assign groupby operation results back to columns in parent dataframe?
  24. python Pandas DataFrame copy(deep=False) vs copy(deep=True) vs ‘=’
  25. From password-protected Excel file to pandas DataFrame
  26. How can repetitive rows of data be collected in a single row in pandas?
  27. Python pandas: how to remove nan and -inf values
  28. How to summarize on different groupby combinations?
  29. Add column in dataframe from list
  30. Get max value from row of a dataframe in python [duplicate]

Leave a Comment