pandas :给定最终值的库存重新计算 [英] Pandas: Inventory recalculation given a final value

问题描述

我正在对Pyhton脚本进行编码，以便在给定实际库存的情况下，对过去365天以来的特定SKU进行库存重新计算.为此，我使用的是Python Pandas Dataframe，如下所示:

I'm coding a Pyhton script to make an inventory recalculation of a specific SKU from today over the past 365 days, given the actual stock. For that I'm using a Python Pandas Dataframe, as it is shown below:

Index   DATE    SUM_IN  SUM_OUT
0      5/12/18   500        0
1      5/13/18    0      -403
2      5/14/18    0       -58
3      5/15/18    0       -39
4      5/16/18   100        0
5      5/17/18    0       -98
6      5/18/18   276        0
7      5/19/18    0      -139
8      5/20/18    0       -59
9      5/21/18    0       -70

数据框显示按日期分组的仓库入库和出库数量的总和.我的意图是添加一个名为"STOCK"的列，该列显示了所代表日期的SKU的库存水平.为此，我拥有的是实际库存水平(指数9).因此，我需要的是通过所有日期系列(从索引9到索引0)逐日重新计算所有级别.

The dataframe presents the sum of quantities IN and OUT of the warehouse, grouped by date. My intention is to add a column named "STOCK" that presents the stock level of the SKU of the represented day. For that, what I have is the actual stock level (index 9). So what I need is to recalculate all the levels day by day through all the dates series (From index 9 until index 0).

在Excel中，这很容易.我可以将实际级别放在最后一行，然后将计算扩展到到达索引0的行.如图所示(E列为公式，G列为所需的输出):

In Excel it's easy. I can put the actual level in the last row and just extend a the calculation until I reach the row of index 0. As presented (Column E is the formula, Column G is the desired Output):

有人可以帮助我达到这个结果吗?

Does someone can help me achieve this result?

我已经有最后一天的库存水平(即5/21/2018等于10).我需要将数字10放在索引9中，并计算从索引8到0的其他几天的库存水平.

I already have the stock level of the last day (i. e. 5/21/2018 is equal to 10). What I need is place the number 10 in index 9 and calculate the stock levels of the other past days, from index 8 until 0.

所需的输出应为:

Index   DATE    TRANSACTION_IN  TRANSACTION_OUT SUM_IN  SUM_OUT STOCK
0      5/12/18         1                 0        500      0     500
1      5/13/18         0                 90        0     -403     97
2      5/14/18         0                 11        0     -58      39
3      5/15/18         0                 11        0     -39      0
4      5/16/18         1                 0        100      0     100
5      5/17/18         0                 17        0     -98      2
6      5/18/18         1                 0        276      0     278
7      5/19/18         0                 12        0    -139     139
8      5/20/18         0                 4         0    -59       80
9      5/21/18         0                 7         0    -70       10

推荐答案

(已更新)

last_stock = 10  # You should try another value
a = (df.SUM_IN + df.SUM_OUT).cumsum()
df["STOCK"] = a - (a.iloc[-1] - last_stock)

这篇关于 pandas :给定最终值的库存重新计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！