Combine duplicate rows into one row in Pandas data frame

Question

Let's say I have the following data: one person can have multiple Constituency Code

Unique_ID	Name	Constituency Code
404	Mark	Teacher
404	Mark	Staff
404	Mark	Staff
659	Julio	Student
1025	Jasmine	Staff
1025	Jasmine	Student

Question: Using python and pandas, how would I combine them to this: List of unique id and name with all the constituency code combined. Notice that if there are duplicates in constituency code, but it combines it to 1 list of unique constituency code per Unique_ID

Unique_ID	Name	Constituency Code
404	Mark	Teacher, Staff
659	Julio	Student
1025	Jasmine	Staff, Student

I have tried to use groupby and aggregate attributes on pandas, but I am not able to get the result I want. Any suggestions. I know I can do it by iterating through each row, but I rather not do that.

Please see [how to ask Pandas questions](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples). TL;DR, post formatted text examples of your table and any relevant code you have written. — ddejohn, Sep 21 '21 at 14:57
Please provide enough code so others can better understand or reproduce the problem. — Community, Sep 28 '21 at 16:57

score 2 · Accepted Answer · answered Sep 21 '21 at 15:10

Setting up data - very important to provide working examples

test_data = [
    [404, 'Mark', 'Teacher'],
    [404, 'Mark', 'Staff'],
    [404, 'Mark', 'Staff'],
    [659, 'Julio', 'Student'],
    [1025, 'Jasmine', 'Staff'],
    [1025, 'Jasmine', 'Student']
]
cols = ['Unique_ID', 'Name', 'Constinuency Code']

df = pd.DataFrame(test_data, columns=cols)

df.groupby(['Unique_ID', 'Name'])['Constinuency Code'].apply(lambda grp: list(set(grp))).reset_index()

Out

   Unique_ID     Name Constinuency Code
0        404     Mark  [Teacher, Staff]
1        659    Julio         [Student]
2       1025  Jasmine  [Student, Staff]

If you need a string instead:

df.groupby(['Unique_ID', 'Name'])['Constinuency Code'].apply(lambda grp: ', '.join(set(grp))).reset_index()

Out

   Unique_ID     Name Constinuency Code
0        404     Mark    Teacher, Staff
1        659    Julio           Student
2       1025  Jasmine    Student, Staff

Thank you so much! Sorry new to python, and it was my first time posting here. I did not know about the apply attribute. Thanks a million! — Syed Jacob Jilany, Sep 21 '21 at 16:33
Of course, no problem. If the answer helped you, you could consider accepting it :) — JWilliams1, Sep 21 '21 at 16:39

Combine duplicate rows into one row in Pandas data frame

1 Answers1