CODEWITHSUNDEEP
python-3.xpandas
babsdoc
babsdoc

Reputation: 749

Pandas Compare Value to Mean of previous rows

Given the following data,

import pandas as pd

data = [['AAA','2019-01-01', 10], ['AAA','2019-01-02', 21],
        ['AAA','2019-02-01', 30], ['AAA','2019-02-02', 45],
        ['BBB','2019-01-01', 50], ['BBB','2019-01-02', 60],
        ['BBB','2019-02-01', 70],['BBB','2019-02-02', 59]]

dfx = pd.DataFrame(data, columns = ['NAME', 'TIMESTAMP','VALUE'])

  NAME   TIMESTAMP  VALUE
0  AAA  2019-01-01     10
1  AAA  2019-01-02     21
2  AAA  2019-02-01     30
3  AAA  2019-02-02     45
4  BBB  2019-01-01     50
5  BBB  2019-01-02     60
6  BBB  2019-02-01     70
7  BBB  2019-02-02     59

Is it possible to compare the last value of each group ('NAME') to the mean of the previous 3 rows, so the expected output would be somewhat like the following,

  NAME   TIMESTAMP  VALUE  RESULT
0  AAA  2019-01-01     10  
1  AAA  2019-01-02     21  
2  AAA  2019-02-01     30   
3  AAA  2019-02-02     45  False
4  BBB  2019-01-01     50
5  BBB  2019-01-02     60  
6  BBB  2019-02-01     70  
7  BBB  2019-02-02     59  True

So the Result is False for Group 'AAA' because the value 45 is 'Greater Than' the mean of the previous 3 values (10+21+30), while the Result is True for Group 'BBB' because the value 59 is 'Lesser Than' the mean of the previous 3 values (50+60+70).

Regards.

Upvotes: 0

Views: 232

Answers (2)

DarkDrassher34
DarkDrassher34

Reputation: 59

This should work:

def compare(a, b):
    if a > b:
        return False
    elif a < b: 
        return True 

dfx['rolling_mean'] = dfx.VALUE.rolling(3, 3).mean()
s = dfx.duplicated('NAME', keep = 'last')
dfx['RESULT'] = dfx[~s].apply(lambda x: compare(x.VALUE, x.rolling_mean), axis = 1)

Upvotes: 2

BENY
BENY

Reputation: 323306

Use duplicated

s=dfx.duplicated('NAME',keep='last')
dfx['RESULT']=dfx[~s].VALUE.le(dfx[s].groupby('NAME')['VALUE'].mean().values)
dfx
  NAME   TIMESTAMP  VALUE RESULT
0  AAA  2019-01-01     10    NaN
1  AAA  2019-01-02     21    NaN
2  AAA  2019-02-01     30    NaN
3  AAA  2019-02-02     45  False
4  BBB  2019-01-01     50    NaN
5  BBB  2019-01-02     60    NaN
6  BBB  2019-02-01     70    NaN
7  BBB  2019-02-02     59   True

Upvotes: 1

Related Questions