Pandas group by but keep another column

user_10342711 0
Viewed 55

Say that I have a dataframe that looks something like this

            date                                      location  year
 0    1908-09-17                           Fort Myer, Virginia  1908
 1    1909-09-07                       Juvisy-sur-Orge, France  1909
 2    1912-07-12                     Atlantic City, New Jersey  1912
 3    1913-08-06            Victoria, British Columbia, Canada  1912

I want to use pandas groupby function to create an output that shows the total number of incidents by year but also keep the location column that will display one of the locations that year. Any which one works. So it would look something like this:

       total  location
 year                
 1908     1    Fort Myer, Virginia
 1909     1    Juvisy-sur-Orge, France
 1912     2    Atlantic City, New Jersey

Can this be done without doing funky joining? The furthest I can get is using the normal groupby

df = df.groupby(['year']).count()

But that only gives me something like this

               location
year                
1908     1         1
1909     1         1
1912     2         2

How can I display one of the locations in this dataframe?

pandas-groupby pandas python
1 Answers

You can use groupby.agg and use 'first' to extract the first location in each group:

res = df.groupby('year')['location'].agg(['first', 'count'])

print(res)
#                           first  count
# year                                  
# 1908        Fort Myer, Virginia      1
# 1909    Juvisy-sur-Orge, France      1
# 1912  Atlantic City, New Jersey      2
user_92095460
Related