CODEWITHSUNDEEP
pythonpandas
juanjo
juanjo

Reputation: 57

Mean of the previous n rows

I have the following Data Frame:

date = pd.date_range('2021-01-01', periods = 21, freq = '60S')
df = pd.DataFrame({ 'Date': date,
'Type':'DM','DM','DM','DS','DS','DS','DS','DM','DS','DS','DM','DM','DM','DM','DM','DM','DM','DS','DS','DS','DM'],
'Value': [105,130,104,205,206,208,222,160,105,130,104,205,206,208,222,160,158,176,120,200,75]})

So i want to obtain the mean value in df['mean'], but only for the condition where Type is 'DS' and the mean value must be the previous 3 rows of df['Value] where Type is DM. I should obtain something like this

Output

       Type Value mean   
    0    DM  105  NaN
    1    DM  130  NaN
    2    DM  104  NaN
    3    DS  205  113
    4    DS  206  113
    5    DS  208  113
    6    DS  222  113
    7    DM  160  NaN
    8    DS  105  131,33
    9    DS  130  131,33
    10   DM  104  NaN
    11   DM  205  NaN
    12   DM  206  NaN
    13   DM  208  NaN
    14   DM  222  NaN
    15   DS  160  212
    16   DM  158  NaN
    17   DS  176  196
    18   DS  120  196
    19   DS  200  196
    20   DM  75   NaN

I tried with grouby, but i manage to obtain a result only for a equal df['Type'] of value (if the value is 'DM' the mean value i obtain is with the previous 'DM' Values).

The code i tried: df['Avg_Back'] = ((df.groupby('Type')['Value'].rolling(window=4).mean().reset_index(level=0,drop=True))*4 - df['NOx'])/3

But, i don't know how to use it to calculate the mean for a different Type value.

Upvotes: 1

Views: 101

Answers (1)

Andrej Kesely
Andrej Kesely

Reputation: 195438

df["mean"] = df[df.Type == "DM"].rolling(3)["Value"].mean()
df["mean"] = df["mean"].ffill()
df.loc[df.Type == "DM", "mean"] = np.nan
print(df)

Prints:

                  Date Type  Value        mean
0  2021-01-01 00:00:00   DM    105         NaN
1  2021-01-01 00:01:00   DM    130         NaN
2  2021-01-01 00:02:00   DM    104         NaN
3  2021-01-01 00:03:00   DS    205  113.000000
4  2021-01-01 00:04:00   DS    206  113.000000
5  2021-01-01 00:05:00   DS    208  113.000000
6  2021-01-01 00:06:00   DS    222  113.000000
7  2021-01-01 00:07:00   DM    160         NaN
8  2021-01-01 00:08:00   DS    105  131.333333
9  2021-01-01 00:09:00   DS    130  131.333333
10 2021-01-01 00:10:00   DM    104         NaN
11 2021-01-01 00:11:00   DM    205         NaN
12 2021-01-01 00:12:00   DM    206         NaN
13 2021-01-01 00:13:00   DM    208         NaN
14 2021-01-01 00:14:00   DM    222         NaN
15 2021-01-01 00:15:00   DS    160  212.000000
16 2021-01-01 00:16:00   DM    158         NaN
17 2021-01-01 00:17:00   DS    176  196.000000
18 2021-01-01 00:18:00   DS    120  196.000000
19 2021-01-01 00:19:00   DS    200  196.000000
20 2021-01-01 00:20:00   DM     75         NaN

Upvotes: 2

Related Questions