[FIXED] Group by list multiple columns with conditions

Issue

Having

	car_id	speed	section
0	1	10	a
1	1	10	b
2	1	30	c
3	1	10	d
4	2	10	e
5	2	0	f
6	2	10	g
7	3	0	h

I want a concatenated string in two columns, one containing the sections if speed <=10 (legal_speed) and the others (illegal_speed). The values with speed 0 should be an empty string:

	car_id	legal_speed	illegal_speed
0	1	a, b, d	c
1	2	e, g
2	3

I have been trying

df.groupby('car_id').agg(list)

but not quiet there yet

Solution

Categorize the column speed into legal and illegal labels, then group the dataframe by car_id and labels and aggregate section using join:

s = pd.cut(df['speed'], [0, 10, np.inf], labels=['legal', 'illegal'])
df.groupby(['car_id', s])['section'].agg(', '.join).unstack()

Result

speed     legal illegal
car_id                 
1       a, b, d       c
2          e, g     NaN
3           NaN     NaN

Answered By - Shubham Sharma