Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to create a new column with the rolling mean of another column - Python

I have a dataframe:

import pandas as pd
import numpy as np
d1 = {'id': [11, 11,11,11,11,24,24,24,24,24,24], 
     'PT': [3, 3,6,0,9,4,2,3,4,5,0], 
      "date":["2010-10-10","2010-10-12","2010-10-16","2010-10-18","2010-10-22","2010-10-10","2010-10-11","2010-10-14","2010-10-16","2010-10-19","2010-10-22"], 
        }

df1 = pd.DataFrame(data=d1)

    id  PT  date
0   11  3   2010-10-10
1   11  3   2010-10-12
2   11  6   2010-10-16
3   11  0   2010-10-18
4   11  9   2010-10-22
5   24  4   2010-10-10
6   24  2   2010-10-11
7   24  3   2010-10-14
8   24  4   2010-10-16
9   24  5   2010-10-19
10  24  0   2010-10-22

and i would like to calculate the rolling mean of the column PTfor each id on a moving window of the last 3 entries for that id. Moreover, if there is not yet 3 entries for that id I would like to obtain the average of the last 2 entries or the current entry. The result should look like this:

id  PT  date    Rolling mean last 3
0   11  3   2010-10-10  3
1   11  3   2010-10-12  3
2   11  6   2010-10-16  4
3   11  0   2010-10-18  3
4   11  9   2010-10-22  5
5   24  4   2010-10-10  4
6   24  2   2010-10-11  3
7   24  3   2010-10-14  3
8   24  4   2010-10-16  3
9   24  5   2010-10-19  4
10  24  0   2010-10-22  3

I try and obtained :

df1["rolling"]=df1.groupby('id')['PT'].rolling(3).mean().reset_index(0,drop=True)
    id  PT  date    rolling
0   11  3   2010-10-10  NaN
1   11  3   2010-10-12  NaN
2   11  6   2010-10-16  4.0
3   11  0   2010-10-18  3.0
4   11  9   2010-10-22  5.0
5   24  4   2010-10-10  NaN
6   24  2   2010-10-11  NaN
7   24  3   2010-10-14  3.0
8   24  4   2010-10-16  3.0
9   24  5   2010-10-19  4.0
10  24  0   2010-10-22  3.0

Therefore, my problem is when there is not 3 entries... I have NaN instead of what the 2 previous or current entries.

like image 741
Jagr Avatar asked Oct 16 '22 09:10

Jagr


1 Answers

You might be looking for the min_periods argument:

df1['rolling'] = df1.groupby('id')['PT'].rolling(window=3, min_periods=1).mean().reset_index(0, drop=True)



   id  PT        date  rolling
0   11   3  2010-10-10      3.0
1   11   3  2010-10-12      3.0
2   11   6  2010-10-16      4.0
3   11   0  2010-10-18      3.0
4   11   9  2010-10-22      5.0
5   24   4  2010-10-10      4.0
6   24   2  2010-10-11      3.0
7   24   3  2010-10-14      3.0
8   24   4  2010-10-16      3.0
9   24   5  2010-10-19      4.0
10  24   0  2010-10-22      3.0
like image 180
sacuL Avatar answered Oct 20 '22 09:10

sacuL