CODEWITHSUNDEEP
pythonpandasgroup-bysumaggregate
Petr Petrov
Petr Petrov

Reputation: 4432

Pandas: sum values in some column

I need to group elements and sum it with one column.

   member_id    event_path  event_duration
0        111        vk.com               1
1        111   twitter.com               4
2        111  facebook.com              56
3        111        vk.com              23
4        222      vesti.ru               6
5        222  facebook.com              23
6        222        vk.com              56
7        333      avito.ru               8
8        333       avito.ru               4
9        444       mail.ru               7
10       444        vk.com              20
11       444     yandex.ru              40
12       111     vk.com                 10
13       222     vk.com                 20

And I want no unify member_id and event_path and sum event_duration. Desire output

       member_id    event_path  event_duration
0        111        vk.com              34
1        111   twitter.com               4
2        111  facebook.com              56
4        222      vesti.ru               6
5        222  facebook.com              23
6        222        vk.com              76
7        333      avito.ru               12
9        444       mail.ru               7
10       444        vk.com              20
11       444     yandex.ru              40

I use 

df['event_duration'] = df.groupby(['member_id', 'event_path'])['event_duration'].transform('sum')

but I get

   member_id    event_path  event_duration
0        111        vk.com              34
1        111   twitter.com               4
2        111  facebook.com              56
3        111        vk.com              34
4        222      vesti.ru               6
5        222  facebook.com              23
6        222        vk.com              76
7        333      avito.ru              12
8        333      avito.ru              12
9        444       mail.ru               7
10       444        vk.com              20
11       444     yandex.ru              40
12       111        vk.com              34
13       222        vk.com              76

What I do wrong?

Upvotes: 1

Views: 702

Answers (2)

jezrael
jezrael

Reputation: 862406

You need groupby with parameters sort=False and as_index=False with aggregation sum:

df = df.groupby(['member_id','event_path'],sort=False,as_index=False)['event_duration'].sum()
print (df)
   member_id    event_path  event_duration
0        111        vk.com              34
1        111   twitter.com               4
2        111  facebook.com              56
3        222      vesti.ru               6
4        222  facebook.com              23
5        222        vk.com              76
6        333      avito.ru              12
7        444       mail.ru               7
8        444        vk.com              20
9        444     yandex.ru              40

Another possible solution is add reset_index:

df = df.groupby(['member_id', 'event_path'],sort=False)['event_duration'].sum().reset_index()
print (df)
   member_id    event_path  event_duration
0        111        vk.com              34
1        111   twitter.com               4
2        111  facebook.com              56
3        222      vesti.ru               6
4        222  facebook.com              23
5        222        vk.com              76
6        333      avito.ru              12
7        444       mail.ru               7
8        444        vk.com              20
9        444     yandex.ru              40

Function transform is used to add an aggregated calculation back to the original df as a new column.

Upvotes: 3

Graipher
Graipher

Reputation: 7186

What you are doing wrong is that you try to assign it to a column in the original dataframe. And since your new column has less rows than the original dataframe, it gets repeated at the end.

Upvotes: 1

Related Questions