LearningSlowly
LearningSlowly

Reputation: 9441

Pandas grouby and flatten based on a column

Consider a df like this:

mode id
car 1_2fgg
car 1_2fgg
car 1_2fgg
car 1_2fgg
bike 2_344jd
car 2_344jd

I wish to flatten the mode column to get a list of all the unique modes, per id, so something like:

id modes
1_2fgg car
2_344jd bike,car

How could I do this in pandas? I presume groupby id

Upvotes: 1

Views: 39

Answers (2)

Ch3steR
Ch3steR

Reputation: 20669

We can use GroupBy.unique with Series.str.join here.

df.groupby('id')['mode'].unique().str.join(',').reset_index()

        id      mode
0   1_2fgg       car
1  2_344jd  bike,car

Upvotes: 3

Quang Hoang
Quang Hoang

Reputation: 150765

Try join the unique

df.groupby('id')['mode'].agg(lambda x: ','.join(x.unique())

Or drop duplicates before groupby (might be faster):

(df.drop_duplicates(['mode', 'id'])
   .groupby('id')['mode'].agg(','.join)
)

Upvotes: 2

Related Questions