Compute row average by another column in Python/Pandas

Question

I have a data frame as below:

user | profit
-------------
Anna |    1.0
Bell |    2.0
Anna |    2.0
Chad |    5.0
Bell |    4.0
Anna |    3.0

that I need to compute each row's mean value on the users' level, that is, each time I see the same user I compute his/her profit mean thus far.

For instance, Anna's first profit mean is 1.0 and her second profit mean becomes 1.5, and so on.

The desired result looks like:

user | profit | mean
--------------------
Anna |    1.0 |  1.0
Bell |    2.0 |  2.0
Anna |    2.0 |  1.5
Chad |    5.0 |  5.0
Bell |    4.0 |  3.0
Anna |    3.0 |  2.0

Any suggestions to do so in Python/Pandas?

import pandas as pd

record = pd.DataFrame({
    "user": ("Anna", "Bell", "Anna", "Chad", "Bell", "Anna"), 
    "profit": (1.0, 2.0, 2.0, 5.0, 4.0, 3.0)
})

Thanks!

jezrael · Accepted Answer · 2018-07-09 11:19:24Z

2

Use GroupBy.transform with rolling and mean:

df['mean'] = (df.groupby('user')['profit']
                .transform(lambda x: x.rolling(len(x), min_periods=1).mean()))
print (df)
   user  profit  mean
0  Anna     1.0   1.0
1  Bell     2.0   2.0
2  Anna     2.0   1.5
3  Chad     5.0   5.0
4  Bell     4.0   3.0
5  Anna     3.0   2.0

answered Jul 9, 2018 at 11:19

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Anton vBR Over a year ago

I'm thinking something like dividing cumsum and cumcount?

Rock Over a year ago

Great one liner solution!

Anton vBR Over a year ago

@Rock I'm allergic to one-liners but the idea is smart! And I think this is readable! (one-liners can sometimes become very hard to read)

Anton vBR Over a year ago

@jezrael Hey, I figured we could use cumsum() and divide with cumcount +1? What do you think? I think it is more readalbe at least. Speed-wise they look the same.

Anton vBR · Accepted Answer · 2018-07-11 17:59:47Z

1

I think we can use cumsum() and divide with the count so far.

g = df.groupby('user')['profit']
df['mean'] = g.cumsum() / (g.cumcount() + 1)

Full example

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "user": ("Anna", "Bell", "Anna", "Chad", "Bell", "Anna"), 
    "profit": (1.0, 2.0, 2.0, 5.0, 4.0, 3.0)
})

g = df.groupby('user')['profit']
df['mean'] = g.cumsum() / (g.cumcount() + 1)

print(df)

Returns:

   user  profit  mean
0  Anna     1.0   1.0
1  Bell     2.0   2.0
2  Anna     2.0   1.5
3  Chad     5.0   5.0
4  Bell     4.0   3.0
5  Anna     3.0   2.0

edited Jul 11, 2018 at 17:59

answered Jul 9, 2018 at 11:26

Anton vBR

19k6 gold badges47 silver badges47 bronze badges

Collectives™ on Stack Overflow

Compute row average by another column in Python/Pandas

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related