I have a dataframe that shows the quarterly forecasted amount of an item in one column, and using amount / 13 I can come up with the forecasted weekly consumption. (With there being 13 weeks in a quarter). My question is this: I also have the current quantity on hand of each item in a separate column. I'd like to add an additional column that represents each week in the quarter, and shows the inventory quantity on hand decreasing by the forecasted weekly amount. I understand what the formula would be if I were to manually add these columns, but I can't help but imagine there is a more efficient way to accomplish this.
Current data would look something like this:
item_number | qty | quarter | onhand | weekly
-------------------+---------+---------+---------+---------------------
M17-4R1.0 | 20.79 | 2 | 32.00 | 1.59884615384615
M17-4R1.25 | 22.24 | 2 | -42.02 | 1.71075384615385
M17-4R1.375 | 36.12 | 2 | 77.72 | 2.7782
M17-4R1.5 | 74.29 | 2 | 134.12 | 5.71446923076924
M17-4R1.625 | 46.35 | 2 | 63.10 | 3.56513076923077
M17-4R.369 | 86.79 | 2 | 16.00 | 6.67630769230769
M17-4R.375 | 87.36 | 2 | 702.72 | 6.72026153846154
You can use something like this:
for i in range(13):
if i==0:
df[f'onhand_w{i+1}'] = df.loc[:,"onhand"] - df.loc[:,'weekly']
else:
df[f'onhand_w{i+1}'] = df.loc[:,f'onhand_w{i}'] - df.loc[:,'weekly']