Previous group mean shift in pandas

user_14242308 0
Viewed 37

I have a table that looks like this:

date         revenue
2021-01-01   20
2021-01-02   30
...
2021-01-31   50
2021-02-01   35
2021-02-02   67

I want to calculate for each row maximum revenue for the previous month.

I can calculate maximum revenue for the current month:

df['max']=df.groupby(df['date'].dt.month)[revenue'].transform(max)

And it will look like this:

  date         revenue  max
2021-01-01   20         50
2021-01-02   30         50
...
2021-01-31   50         50
2021-02-01   35         67
2021-02-02   67         67

But I want it to be:

  date         revenue  max
2021-01-01   20         nan
2021-01-02   30         nan
...
2021-01-31   50         nan
2021-02-01   35         50
2021-02-02   67         50

I tried to put .shift() in the end but it will only lag/lead max by rows, yet I need it by group.

Please help

pandas python
1 Answers

You can do groupby with shift map

s = df.date.dt.strftime('%Y%m')
df['new'] = s.map(df.groupby(s).revenue.max().shift())
df
Out[62]: 
        date  revenue   new
0 2021-01-01       20   NaN
1 2021-01-02       30   NaN
2 2021-01-31       50   NaN
3 2021-02-01       35  50.0
4 2021-02-02       67  50.0
user_79645270
Related