title: How do I create a while loop for this df that has moving average in every stage?
body: <p>So I want to spread the shipments per ID in the group one by one by looking at avg sales to determine who to give it to.</p>
  <p>Here's my dataframe:</p>
  <pre><code>ID   STOREID    BAL       SALES   SHIP
  1     STR1      50        5       18
  1     STR2      6         7       18
  1     STR3      74        4       18
  2     STR1      35        3       500
  2     STR2      5         4       500
  2     STR3      54        7       500
  </code></pre>
  <p>While SHIP (grouped by ID) is greater than 0, calculate AVG (BAL/SALES) and the lowest AVG per group give +1 to its column BAL and +1 to its column final. And then repeat the process until SHIP is 0. The AVG would be different every stage  which is why I wanted it to be a while loop.</p>
  <p>Sample output of first round is below. So do this until SHIP is 0 and SUM of Final per ID is = to SHIP:</p>
  <pre><code>ID   STOREID   BAL   SALES  SHIP   AVG        Final
  1    STR1      50    5      18     10         0
  1    STR2      6     4      18     1.5        1
  1    STR3      8     4      18     2          0
  2    STR1      35    3      500    11.67      0
  2    STR2      5     4      500    1.25       1
  2    STR3      54    7      500    7.71       0
  </code></pre>
  <p>I've tried a couple of ways in SQL, I thought it would be better to do it in python but I haven't been doing a great job with my loop. Here's what I tried so far:</p>
  <pre><code>df['AVG'] = 0
  df['FINAL'] = 0
  
  for i in df.groupby([&quot;ID&quot;])['SHIP']:
      if i &gt; 0:
            df['AVG'] = df['BAL'] / df['SALES']
            df['SHIP'] = df.groupby([&quot;ID&quot;])['SHIP']-1
            total = df.groupby([&quot;ID&quot;])[&quot;FINAL&quot;].transform(&quot;cumsum&quot;)
            df['FINAL'] = + 1
            df['A'] = + 1
      else:
           df['FINAL'] = 0
  </code></pre>