I need to collapse a single column using a unique ID which contains duplicates and concatenate strings of text in that column only

Question

I need to combine strings in the text column for matching IDs with duplicats in the ID column. Column A through Column X will have matching data and I want to preserve that in the new dataframe.

ID	Text	column A	Column B
1	apple	five	22
1	banana	five	22
2	pancake	three	8
3	peach	two	5
3	mango	two	5

I tried this: aggregate(Text ~ ID, data = df, toString)

I am expecting

ID	text	Column A	Column B
1	apple, banana	five	22
2	pancake	three	8
3	peach, mango	two	5

Please check [How to make pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples). — MagnusO_O, Nov 02 '22 at 19:44

score 0 · Answer 1 · answered Nov 02 '22 at 19:55

import pandas as pd

df = pd.DataFrame({"ID": [1, 1, 2, 3, 3], "Text": ["apple", "banana", "pancake", "peach", "mango"], \
                   "column_A": ["five", "five", "three", "two", "two"], "column_B": [22, 22, 8, 5, 5]})

df = df.groupby(["column_A","column_B", "ID"])["Text"].apply(list).reset_index()

I need to collapse a single column using a unique ID which contains duplicates and concatenate strings of text in that column only

1 Answers1