CODEWITHSUNDEEP
pythonpandasgroup-bydataframe
everestbaker
everestbaker

Reputation: 73

Pandas - List out data columns in new columns using Groupby

I need to add new columns based on the groupby results. Take the following dataframe for exampe.

 ID      City      
 234x    Lima
 342x    Rica
 234x    Rio
 333x    NYC
 333x    SF

I have been about to use group_by to get the counts:

df_GroupBy = pd.DataFrame({'count':df.groupby([ "ID"]).size()}).reset_index()

This gives an output:

 ID       Count
234x       2
342x       1
333x       2

What I would like to do now is get this output:

 ID       City     City_2
234x       Lima     Rio
342x       Rica      
333x       NYC      SF

I have looked at both Transform and map but without much success. Thanks for your help.

Upvotes: 3

Views: 66

Answers (1)

Stefan
Stefan

Reputation: 42905

You could

cities = df.groupby('ID')['City'].apply(lambda x: pd.Series([city for city in x])).unstack()

         0    1
ID             
234x  Lima  Rio
333x   NYC   SF
342x  Rica  NaN

Upvotes: 2

Related Questions