How to roll up duplicate observation in pydatatable?

user_9479925 0
Viewed 39

I have a data frame as-

my_dt = dt.Frame({'last_name':['mallesh','bhavik','jagarini','mallesh','jagarini'],
                  'first_name':['yamulla','vemulla','yegurla','yamulla','yegurla'],
                  'ssn':['1234','7847','0648','4567','0648']})

enter image description here

Here I would like to find out duplicates considering last_name and firs_name columns and if any duplicates found their respective ssn needs to be rolled up with semicolon(;) if SSN are not different. if SSN are also same only one SSN needs to be present.

the expected output as:

enter image description here

Here since mallesh yamulla is duplicated and has different SSN's they are rolled up with ';'

and in case of jagarini yegurla it has a unique SSN hence one SSN is only taken.

py-datatable python
1 Answers

Use DataFrame.drop_duplicates by all columns first and then aggregate join:

df = (my_dt.drop_duplicates()
           .groupby(['last_name','first_name'], sort=False)['ssn']
           .agg(';'.join)
           .reset_index())
print (df)
  last_name first_name        ssn
0   mallesh    yamulla  1234;4567
1    bhavik    vemulla       7847
2  jagarini    yegurla       0648
user_29010020
Related