Remove duplicates while keeping the data from their cells

user_16331689 0
Viewed 46

I have a list in excel I need to tidy. I need to remove duplicates from column A while keeping the values from the duplicates in column B and concatenating them. Is there a way to do this in excel or Python?

The reason I need to do this is I am working through a list of part numbers and their alternate numbers. Each alternate is displayed in a cell next to it product code. Products with many alternates have many duplicates. There are 29,883 product codes all up.

Any help would be great as I am new to data cleaning.enter image description here

pandas excel-formula python-3.x python
1 Answers

Probably you need to aggregate the entries of alt-prod-code by grouping of prod-code. You can do it as follows:

df.groupby('prod-code', as_index=False).agg(','.join)

Input

  prod-code alt-prod-code
0    AB0110    22RB.30606
1    AB0110     22RB30606

Output:

  prod-code         alt-prod-code
0    AB0110  22RB.30606,22RB30606
user_150706970
Related