Is there a way to roll Pandas Grouper backwards over a dataframe?

user_14633053 0
Viewed 446

The first option below isn't very elegant, but I think gets to the right place. The second option using pd.Grouper, but chops off values during the group and produces different results and groups to the beginning of the period. Using grouper begins the grouping of the Timestamp column from the earliest date to the latest. I would want to do the opposite. Being able to choose the direction of the groupby using a Grouper would be useful.

Any suggestions? Is there another function to try? Thanks!

data = pd.DataFrame({
    'Timestamp': pd.date_range('8-11-2020', '10-12-20', freq='D'),
    'Qty': [4, 8, 5, 0, 6, 1, 0, 5, 4, 8, 4, 4, 8, 2, 6, 5, 3, 2, 4, 8, 0, 4,
       0, 9, 5, 3, 8, 2, 2, 4, 5, 7, 6, 7, 6, 5, 1, 6, 0, 1, 0, 5, 0, 2,
       3, 6, 5, 2, 5, 8, 3, 7, 8, 6, 4, 5, 8, 7, 7, 5, 9, 6, 6]
})
df = data.groupby(pd.Grouper(key='Timestamp', freq='D')).sum()[['Qty']].sort_index()
df = df.rolling('30D').sum().pct_change(1, freq='30D')
df.loc['10-12-2020']['Qty']

# or...

data \
    .groupby(pd.Grouper(key='Timestamp', freq='30D', label='right', closed='right')) \
    .sum()[['Qty']].sort_index().pct_change()
pandas-groupby pandas dataframe python
1 Answers

I think what you really want to do is just get minimum number of elements to reproduce the result of the most recent date.

I doubled-ish your dataset to illustrate. For 30-day rolling averages you only need 60 days worth of data to get the same result.

data = pd.DataFrame({
    'Timestamp': pd.date_range('8-11-2020', '12-18-20', freq='D'),
    'Qty': [4, 8, 5, 0, 6, 1, 0, 5, 4, 8, 4, 4, 8, 2, 6, 5, 3, 2, 4, 8, 0, 4,
       0, 9, 5, 3, 8, 2, 2, 4, 5, 7, 6, 7, 6, 5, 1, 6, 0, 1, 0, 5, 0, 2,
       3, 6, 5, 2, 5, 8, 3, 7, 8, 6, 4, 5, 8, 7, 7, 5, 9, 6, 6,2,4,4, 8, 5, 0, 6, 
       1, 0, 5, 4, 8, 4, 4, 8, 2, 6, 5, 3, 2, 4, 8, 0, 4,
          0, 9, 5, 3, 8, 2, 2, 4, 5, 7, 6, 7, 6, 5, 1, 6, 0, 1, 0, 5, 0, 2,
          3, 6, 5, 2, 5, 8, 3, 7, 8, 6, 4, 5, 8, 7, 7, 5, 9, 6, 6,2,4]})


# your code
df = data.groupby(pd.Grouper(key='Timestamp', freq='D')).sum()[['Qty']].sort_index()
df = df.rolling('30D').sum().pct_change(1, freq='30D')
print(df.shape)
print(df.loc['12-18-20']['Qty'])

# updated code: note the slice data[-60:]
df = data[-60:].groupby(pd.Grouper(key='Timestamp', freq='D')).sum()[['Qty']].sort_index()
df['sum'] = df.rolling('30D').sum()
df['pct'] = df['sum'].pct_change(1, freq='30D')
print(df.shape)
print(df.loc['12-18-20']['pct'])

Output: No matter how large your dataset, you'll only need the most recent 60 datapoints

(130, 1)
0.030303030303030276
(60, 3)
0.030303030303030276

here's the dataframe using the slice and expanding the code see the columns. I think, but not sure, you should be using df.rolling(30).sum() instead of df.rolling('30D').sum(), although you get to the same answer.

In [175]: df.tail(35)
Out[175]:
            Qty    sum         pct
Timestamp
2020-11-14    5  106.0         NaN
2020-11-15    7  113.0         NaN
2020-11-16    6  119.0         NaN
2020-11-17    7  126.0         NaN
2020-11-18    6  132.0         NaN
2020-11-19    5  136.0  135.000000
2020-11-20    1  137.0  136.000000
2020-11-21    6  138.0   22.000000
2020-11-22    0  134.0   12.400000
2020-11-23    1  127.0    6.055556
2020-11-24    0  123.0    4.590909
2020-11-25    5  124.0    3.769231
2020-11-26    0  116.0    2.411765
2020-11-27    2  116.0    2.222222
2020-11-28    3  113.0    1.690476
2020-11-29    6  114.0    1.425532
2020-11-30    5  116.0    1.320000
2020-12-01    2  116.0    1.230769
2020-12-02    5  117.0    1.089286
2020-12-03    8  117.0    0.828125
2020-12-04    3  120.0    0.875000
2020-12-05    7  123.0    0.808824
2020-12-06    8  131.0    0.926471
2020-12-07    6  128.0    0.662338
2020-12-08    4  127.0    0.548780
2020-12-09    5  129.0    0.517647
2020-12-10    8  129.0    0.387097
2020-12-11    7  134.0    0.410526
2020-12-12    7  139.0    0.432990
2020-12-13    5  140.0    0.386139
2020-12-14    9  144.0    0.358491
2020-12-15    6  143.0    0.265487
2020-12-16    6  143.0    0.201681
2020-12-17    2  138.0    0.095238
2020-12-18    4  136.0    0.030303
user_121334340
Related