Pandas rolling sum with an unevenly distributed index

Question

I have a data block containing weekly sales for different products (a, b, c). If during the week (for example, week 4) there was a zero number of sales, this week there is no record:

In[1]
df = pd.DataFrame({'product': list('aaaabbbbcccc'),
               'week': [1, 2, 3, 5, 1, 2, 3, 5, 1, 2, 3, 4],
               'sales': np.power(2, range(12))})
Out[1]
   product  sales  week
0        a      1     1
1        a      2     2
2        a      4     3
3        a      8     5
4        b     16     1
5        b     32     2
6        b     64     3
7        b    128     5
8        c    256     1
9        c    512     2
10       c   1024     3
11       c   2048     4

I would like to create a new column containing cumulative sales for the previous n weeks, grouped by product. For example. for n = 2 it should be like last_2_weeks:

   product  sales  week  last_2_weeks
0        a      1     1             0
1        a      2     2             1
2        a      4     3             3
3        a      8     5             4
4        b     16     1             0
5        b     32     2            16
6        b     64     3            48
7        b    128     5            64
8        c    256     1             0
9        c    512     2           256
10       c   1024     3           768
11       c   2048     4          1536

If there was a record for every week, I could just use rolling_sumit as described in this question .

Is there a way to set the "week" as an index and only calculate the amount by this index? Or can I redo the "week" and set the sales to zero for all missing lines?

Answer 1

Resample DatetimeIndex, TimedeltaIndex PeriodIndex.
reindex .

week . df product reindex . 0.

import pandas as pd
import numpy as np

df = pd.DataFrame({'product': list('aaaabbbbcccc'),
               'week': [1, 2, 3, 5, 1, 2, 3, 5, 1, 2, 3, 4],
               'sales': np.power(2, range(12))})

df = df.set_index('week')   

def reindex_by_max_index_of_group(df):
    index = range(1, max(df.index) + 1)
    return df.reindex(index, fill_value=0)

df = df.groupby('product').apply(reindex_by_max_index_of_group)

df.drop(['product'], inplace=True, axis=1)
print df.reset_index()

#   product  week  sales
#0        a     1      1
#1        a     2      2
#2        a     3      4
#3        a     4      0
#4        a     5      8
#5        b     1     16
#6        b     2     32
#7        b     3     64
#8        b     4      0
#9        b     5    128
#10       c     1    256
#11       c     2    512
#12       c     3   1024
#13       c     4   2048

Answer 2

pivot, , . , , reindex , , .

rolling_sum:

import pandas, numpy
df = pandas.DataFrame({'product': list('aaaabbbbcccc'),
              'week': [1, 2, 3, 5, 1, 2, 3, 5, 1, 2, 3, 4],
               'sales': numpy.power(2, range(12))})
sales = df.pivot(index='week', columns='product')
# Cope with weeks when there were no sales at all
sales = sales.reindex(range(min(sales.index), 1+max(sales.index))).fillna(0)
# Calculate the sum for the preceding two weeks
pandas.rolling_sum(sales, 3, min_periods=1)-sales

, ( , ):

product     a   b     c
week                   
1           0   0     0
2           1  16   256
3           3  48   768
4           6  96  1536
5           4  64  3072