Pandas: sum values in some column

Question

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?

jezrael · Accepted Answer · 2017-02-15 07:06:03Z

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.

Graipher · Accepted Answer · 2017-02-15 07:01:10Z

1

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.

answered Feb 15, 2017 at 7:01

Graipher

7,24630 silver badges49 bronze badges

Collectives™ on Stack Overflow

Pandas: sum values in some column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related