title: Loop in python for moving averages based on condition
body: <p>i have 5 weeks sales data of product and store combination. Out of the 5 weeks , first three of them can be on promotions. Either 1st  is on promotion, or 2nd or 3rd or all three or none of them are on promotions. so in total 8 conditions will be there. now i want to calculate a moving average kind of thing in python using loops. in simple terms i will calculate the average based on the check if a particular week is on promotion then calculate it differently. <strong>(start and end of loops for each week are as follows</strong> .</p>
  
  <p><strong>Table 1</strong></p>
  
  <pre><code>                week    week    week    week    week
                  1       2       3       4       5
  loop_start_1    1       1       1       2       3
  loop_end_1      3       4       5       5       5
  loop_start_2    1       1       2       3       4
  loop_end_2      2       3       4       5       5
  </code></pre>
  
  <p>for week 1 i will take the average of 1st , 2nd and 3rd week, but for week 2 i will take average of 1,2,3,4 and so on. now this condition will change if a week is on promotion. Input data that i have as:</p>
  
  <p><strong>Table 2</strong></p>
  
  <pre><code>prod_id store_id    sales_week1 sales_week2 sales_week3 sales_week4  sales_week5    promo_1 promo_2 promo_3 promo_4 promo_5
  12345     22222         40          44          50          20            21            0       0       0       0       0
  12346     22222         82          85          51          72            97            1       0       0       0       0
  12347     22222         74          113         31          25            19            0       1       0       0       0
  12348     22222         74          105         195         216           142           0       0       1       0       0
  12349     22222         78          81          23          10            67            1       1       0       0       0
  12243     22222         159         190         223         137           89            0       1       1       0       0
  12240     22222         591         457         556         278           726           1       0       1       0       0
  22240     22222         76          49          84          132           121           1       1       1       0       0
  </code></pre>
  
  <p>At max there can be 8 different combinations. based on which week out of the first three we have promotion. i want the out as follows( moving average based on the loops mentioned below)</p>
  
  <p><strong>Table 3</strong></p>
  
  <pre><code>prod_id store_id    sales_week1 sales_week2 sales_week3 sales_week4  sales_week5    promo_1 promo_2 promo_3 promo_4 promo_5
  12345     22222         44.6667         38.5            35          33.75             30.3333            0       0      0       0       0
  12346     22222         82              69.3333         76.25       76.25             73.3333            1       0      0       0       0
  12347     22222         52.5            113             37.25       25                25                 0       1      0       0       0
  12348     22222         89.5            131.6667        195         154.3333          179                0       0      1       0       0
  12349     22222         79.5            79.5            33.3333     33.3333           33.3333            1       1      0       0       0
  12243     22222         159             206.5           206.5       113               113                0       1      1       0       0
  12240     22222         591             367.5           556         487               502                1       0      1       0       0
  22240     22222         62.5            69.6667         66.5        126.5             126.5              1       1      1       0       0
  </code></pre>
  
  <p>now i have a dummy code(8 conditions) that i want to convert to loops based on promo condition. Dont want to write this many if conditions. Please help thanks in advance.</p>
  
  <pre><code>if promo_1 =1 then:
  sales_new[1]=sales_old[1]
  sales_new[2]=average(sales_old[2],sales_old[3],sales_old[4])
  sales_new[3]=avergae(sales_old[2],sales_old[3],sales_old[4],sales[5])
  sales_new[4]=avergae(sales_old[2],sales_old[3],sales_old[4],sales[5])
  sales_new[5]=avergae(sales_old[3],sales_old[4],sales[5])
  
  if promo_2 =1 then:
  sales_new[1]=average(sales_old[1],sales_old[3])
  sales_new[2]=sales_old[2]
  sales_new[3]=avergae(sales_old[1],sales_old[3],sales_old[4],sales[5])
  sales_new[4]=avergae(sales_old[3],sales_old[4],sales[5])
  sales_new[5]=avergae(sales_old[3],sales_old[4],sales[5])
  
  if promo_3 =1 then:
  sales_new[1]=average(sales_old[1],sales_old[2])
  sales_new[2]=average(sales_old[1],sales[2],sales[4]
  sales_new[3]=sales_old[3]
  sales_new[4]=avergae(sales_old[2],sales_old[4],sales[5])
  sales_new[5]=avergae(sales_old[4],sales[5])
  
  if promo_1 =1 and promo_2=1 then:
  sales_new[1]=average(sales_old[1],sales_old[2])
  sales_new[2]=average(sales_old[1],sales_old[2])
  sales_new[3]=average(sales_old[3],sales_old[4],sales_old[5])
  sales_new[4]=average(sales_old[3],sales_old[4],sales_old[5])
  sales_new[5]=average(sales_old[3],sales_old[4],sales_old[5])
  
  if promo_2 =1 and promo_3=1 then:
  sales_new[1]=sales_old[1]
  sales_new[2]=average(sales_old[2],sales_old[3])
  sales_new[3]=average(sales_old[2],sales_old[3])
  sales_new[4]=average(sales_old[4],sales_old[5])
  sales_new[4]=average(sales_old[4],sales_old[5])
  
  if promo_1 =1 and promo_3=1 then:
  sales_new[1]=sales_old[1]
  sales_new[2]=average(sales_old[2],sales_old[4])
  sales_new[3]=sales_old[3]
  sales_new[4]=average(sales_old[2],sales_old[4],sales_old[5])
  sales_new[4]=average(sales_old[4],sales_old[5])
  
  if promo_1 =1 and promo_2=1 and promo_3=1 then:
  sales_new[1]=average(sales_old[1],sales_old[2])
  sales_new[2]=average(sales_old[1],sales_old[2],sales_old[3])
  sales_new[3]=average(sales_old[2],sales[3])
  sales_new[4]=average(sales_old[4],sales_old[5])
  sales_new[5]=average(sales_old[4],sales_old[5])
  
  if promo_1 =0 and promo_2=0 and promo_3=0 then:
  sales_new[1]=average(sales_old[1],sales_old[2],sales_old[3])
  sales_new[2]=average(sales_old[1],sales_old[2],sales_old[3],sales_old[4])
  sales_new[3]=average(sales_old[1],sales_old[2],sales_old[3],sales_old[4],sales_old[5])
  sales_new[3]=average(,sales_old[2],sales_old[3],sales_old[4],sales_old[5])
  sales_new[5]=average(sales_old[3],sales_old[4],sales_old[5])
  </code></pre>
  
  <p><strong>Business Rules</strong></p>
  
  <p>In this section I provide the business rules to clarify the requirements for my code.</p>
  
  <p>At the highest level, my goal is to calculate moving averages of sales. I do this through a script that calculates new sales data (incorporating these averages) from "old" (raw) data.</p>
  
  <p>For any week that is <strong>not promoted</strong> we will consider <strong>loop_start_1 and loop_end_1</strong>, but if a week is <strong>promoted</strong> then we will consider <strong>loop_start_2 and loop_end_2</strong></p>
  
  <p>One stipulation is that each new week sales data is - <em>by default</em> - derived from a subset of the old week sales data. This is specified in Table 1. For example, the new sales data for week 1 is (by default) calculated from the old sales data for weeks 1-3 if week is not promoted.</p>
  
  <p>However, each week may or may not be a "promotion" week, and this will affect whether that week's old sales data can be used in calculating the new sales data. <em>This overrides whatever Table 1 <strong>loop_start_1 and loop_end_1</strong> specifies</em>. For promotions we will consider <strong>loop_start_2 and loop_end_2</strong> in Table 1 In particular:</p>
  
  <ul>
  <li>if none of the week are on promotion we will consider loop_start_1 and loop_end_1 else </li>
  <li>if any week of out the first three are promoted then we will consider loop_start_2 and loop_end_2</li>
  </ul>
  
  <p>For example, if week 1 is on promotion, then we'd need to do the following:</p>
  
  <ul>
  <li><p>Calculate <code>week_1_new = avg(week_1_old)</code>.</p>
  
  <ul>
  <li>Table 1 specifies we use old weeks 1-2.<strong>(loop_start_2 and loop_end_2)</strong></li>
  <li>Week 1 is on promotion, week 2  isn't - so just take week 1</li>
  </ul></li>
  <li><p>Calculate <code>week_2_new = avg(week_2_old, week_3_old, week_4_old)</code></p>
  
  <ul>
  <li>Table 1 specifies we use old weeks 1-4.(loop_start_1 and loop_end_1)</li>
  <li>Week 1 is on promotion, weeks 2, 3 &amp; 4 aren't - so the majority of weeks aren't on promotion. so promoted sales will be inflated so we would exclude them from calculation of average</li>
  <li>We consider data only from those weeks that aren't on promotion - 2, 3 &amp; 4.</li>
  </ul></li>
  <li>...</li>
  </ul>
  
  <p>On the other hand, if both weeks 1 and 2 were on promotion, then consider how we'd calculate the new sales data for week 1:</p>
  
  <ul>
  <li><code>week_1_new = avg(week_1_old, week_2_old)</code>
  
  <ul>
  <li>Table 1 specifies we use old weeks 1-2.(loop_start_2 and loop_end_2)</li>
  <li>Weeks 1 &amp; 2 are on promotion - so the majority of weeks <em>are</em> on promotion. we take both the weeks for average</li>
  <li>We consider data only from those weeks that are on promotion - 1 &amp; 2.</li>
  </ul></li>
  </ul>
  
  <p>On the other hand, if all week 1 and week 3 were on promotion, then consider how we'd calculate the new sales data for week 1:</p>
  
  <ul>
  <li><code>week_1_new = avg(week_1_old)</code>   <strong>(loop_start_2 and loop_end_2)</strong> out of 1-2 only 1st week is on promotion. we never add the sales when a product is promoted with the week when it is not promoted. coz this would inflate the average.</li>
  <li><code>week_2_new = avg(week_2_old,week_4_old)</code><strong>((loop_start_1 and loop_end_1 as out of 1-4 only 2 and 4 are not promoted)</strong></li>
  <li><p><code>week_3_new = avg(week_3_old)</code></p>
  
  <ul>
  <li>We consider data only from those weeks that are on promotion - 1 &amp; 3 </li>
  </ul></li>
  </ul>
  
  <p>On the other hand, if all weeks 1 to 3 were on promotion, then consider how we'd calculate the new sales data for week 1:</p>
  
  <ul>
  <li><code>week_1_new = avg(week_1_old,week_2_old)</code>as we need (<strong>loop_start_2 and loop_end_2</strong>)</li>
  <li><code>week_2_new = avg(week_1_old,week_2_old,week_4_old)</code>as (<strong>loop_start_2 and loop_end_2</strong>) states week 1-3 to be considered for promoted weeeks for week 2</li>
  <li><code>week_3_new = avg(week_2_old,week_3_old)</code>as (<strong>loop_start_2 and loop_end_2</strong>) states for week 2-4. but out of 2,3,4 only 2 and 3 are promoted.so just take the promoted weeks</li>
  </ul>