Conditional sum from rows into a new column in pandas

Question

I am looking to create a new column in panda based on the value in the row. My sample data:

df=pd.DataFrame({"A":['a','a','a','a','a','a','b','b','b'],
         "Sales":[2,3,7,1,4,3,5,6,9,10,11,8,7,13,14],
         "Week":[1,2,3,4,5,11,1,2,3,4])

I want a new column "Last3WeekSales" corresponding to each week, having the sum of sales for the previous 3 weeks.

NOTE: Shift() won't work here as data for some weeks is missing.

Logic which I thought: Checking the week no. in each row, then summing up the data from w-1, w-2, w-3.

Output required:

   A    Week  Last3WeekSales
0  a      1       0
1  a      2       2 
2  a      3       5
3  a      4       12 
4  a      5       11
5  a     11       0
6  b      1       0
7  b      2       5
8  b      3       11
9  b      4       20

score 1 · Answer 1 · answered May 22 '18 at 15:05

1

Use groupby, shift and rolling:

df['Last3WeekSales'] = df.groupby('A')['Sales']\
                         .apply(lambda x: x.shift(1)
                                           .rolling(3, min_periods=1)
                                           .sum())\
                         .fillna(0)

Output:

   A  Sales  Week  Last3WeekSales
0  a      2     1             0.0
1  a      3     2             2.0
2  a      7     3             5.0
3  a      1     4            12.0
4  a      4     5            11.0
5  a      3     6            12.0
6  b      5     1             0.0
7  b      6     2             5.0
8  b      9     3            11.0

answered May 22 '18 at 15:05

Scott Boston

147,308
15
139
187

Shift() won't be really useful here as the week no is not continuous and we can't simply refer to previous 3 rows – Chetanya Saxena May 22 '18 at 15:57
Sorting won't help as the weeks are not in series with some weeks remaining. Updated dataset will be helpful – Chetanya Saxena May 22 '18 at 16:01

score 0 · Answer 2 · answered May 22 '18 at 15:11

0

you can use pandas.rolling_sum to sum over 3 last values, and shift(n) to shift your column by n times (1 in your case).

if we suppose you a column 'sales' with the sales of each week, the code would be :

df["Last3WeekSales"] = df.groupby("A")["sales"].apply(lambda x: pd.rolling_sum(x.shoft(1),3))

answered May 22 '18 at 15:11

Ahmed Lahlou Mimi

1,913
2
12
21

1

what is shoft :-) – BENY May 22 '18 at 15:15
rolling_sum is deprecated? – Scott Boston May 22 '18 at 15:22
Shift() or Rolling_sum can't be used here as the weeks are not continuous. Edited the question accordingly. – Chetanya Saxena May 22 '18 at 15:58

Conditional sum from rows into a new column in pandas

2 Answers2