Group by several columns and add values from last column to list

Question

I have this table:

id	type	text
1	inv_num	123
1	company	ASD
1	item	fruit
1	item	vegetable
2	inv_num	123
2	company	FOO
2	item	computer
2	item	mouse
2	item	headphones

I would like to group the same types in one row in a list format:

id	type	text
1	inv_num	123
1	company	ASD
1	item	['fruit', 'vegetable']
2	inv_num	123
2	company	FOO
2	item	['computer', 'mouse', 'headphones']

Is it possible to do it using 'groupby'?

If you want a list only for groups of more than one item: use `lambda x: list(x) if len(x)>1 else x` as aggregation function — mozway, Jan 26 '23 at 09:47

score 1 · Accepted Answer · answered Jan 26 '23 at 09:47

If need lists only if length of values greater like 1 use custom lambd function in GroupBy.agg:

f = lambda x: x.tolist() if len(x) > 1 else x
df = df.groupby(['id','type'])['text'].agg(f).reset_index()
print (df)
   id     type                           text
0   1  company                            ASD
1   1  inv_num                            123
2   1     item             [fruit, vegetable]
3   2  company                            FOO
4   2  inv_num                            123
5   2     item  [computer, mouse, headphones]

Group by several columns and add values from last column to list

1 Answers1