CODEWITHSUNDEEP
pythonpandasdataframepandas-groupby
olivier dadoun
olivier dadoun

Reputation: 743

Pandas rolling over days and getting sum

This is my dataframe

d= {'dates': ['2020-07-16','2020-07-15','2020-07-14','2020-07-13','2020-07-16','2020-07-15','2020-07-14','2020-07-13'], 
    'location':['Paris','Paris','Paris','Paris','NY','NY','NY','NY'],'T':[100,200,300,400,10,20,30,40]} 
df = pandas.DataFrame(data=d)
df['dates']=pandas.to_datetime(df['dates'])
df

    dates   location    T
0   2020-07-16  Paris   100
1   2020-07-15  Paris   200
2   2020-07-14  Paris   300
3   2020-07-13  Paris   400
4   2020-07-16  NY       10
5   2020-07-15  NY       20
6   2020-07-14  NY       30
7   2020-07-13  NY       40

I want to some T value for a given location rolling over the past 2 days (including the current date). This the the panda I would like:

    dates   location    T     SUM2D
0   2020-07-16  Paris   100     300
1   2020-07-15  Paris   200     500
2   2020-07-14  Paris   300     700
3   2020-07-13  Paris   400     NaN
4   2020-07-16  NY       10      30
5   2020-07-15  NY       20      50
6   2020-07-14  NY       30      70
7   2020-07-13  NY       4      NaN

I have tried to play with this sentence without success:

df['SUM2D'] = df.set_index('dates').groupby('location').rolling(window=2, freq='D').sum()['T'].values

Upvotes: 1

Views: 99

Answers (1)

ipj
ipj

Reputation: 3598

Try just sorting dataframe before indexing:

df = df.sort_values(['location','dates']).set_index('dates')
df['SUM2D'] = df.groupby('location')['T'].rolling(window=2, freq='D').sum().values

df[::-1]

result set:

           location    T  SUM2D
dates                          
2020-07-16    Paris  100  300.0
2020-07-15    Paris  200  500.0
2020-07-14    Paris  300  700.0
2020-07-13    Paris  400    NaN
2020-07-16       NY   10   30.0
2020-07-15       NY   20   50.0
2020-07-14       NY   30   70.0
2020-07-13       NY   40    NaN

More compact and elegant solution is to use transform:

df['SUM2D'] = df.sort_values(['dates']).groupby('location')['T'].transform(lambda x: x.rolling(2, 2).sum())

result is now:

       dates location    T  SUM2D
0 2020-07-16    Paris  100  300.0
1 2020-07-15    Paris  200  500.0
2 2020-07-14    Paris  300  700.0
3 2020-07-13    Paris  400    NaN
4 2020-07-16       NY   10   30.0
5 2020-07-15       NY   20   50.0
6 2020-07-14       NY   30   70.0
7 2020-07-13       NY   40    NaN

Upvotes: 1

Related Questions