大熊猫仓库中的累积库存量(pandas cumulative sum of stock in warehouse)

大熊猫仓库中的累积库存量(pandas cumulative sum of stock in warehouse)

考虑不同日期的仓库库存

day action quantity symbol 0 1 40 a 1 1 53 b 2 -1 21 a 3 1 21 b 4 -1 2 a 5 1 42 b

这里， day表示时间序列， action表示特定产品（ symbol ）和quantity buy/sell 。 对于此数据框，如何计算每种产品的每日累计总和。 基本上，结果数据帧如下：

days a b 0 40 0 1 40 53 2 19 53 3 19 64 4 17 64 5 17 106

我用groupby尝试了cumsum（）并且没有成功

Consider the warehouse stocks on different days

day action quantity symbol 0 1 40 a 1 1 53 b 2 -1 21 a 3 1 21 b 4 -1 2 a 5 1 42 b

Here, day represents time series, action represents buy/sell for specific product (symbol) and of quantity. For this dataframe, How do I calculate the cumulative sum daily, for each product. Basically, a resultant dataframe as below:

days a b 0 40 0 1 40 53 2 19 53 3 19 64 4 17 64 5 17 106

I have tried cumsum() with groupby and was unsuccessful with it

最满意答案

使用pivot_table

In [920]: dff = df.pivot_table( index=['day', 'action'], columns='symbol', values='quantity').reset_index() In [921]: dff Out[921]: symbol day action a b 0 0 1 40.0 NaN 1 1 1 NaN 53.0 2 2 -1 21.0 NaN 3 3 1 NaN 21.0 4 4 -1 2.0 NaN 5 5 1 NaN 42.0

然后，执行操作，使用cumsum ，向前填充缺失值，最后用0替换NaN

In [922]: dff[['a', 'b']].mul(df.action, 0).cumsum().ffill().fillna(0) Out[922]: symbol a b 0 40.0 0.0 1 40.0 53.0 2 19.0 53.0 3 19.0 74.0 4 17.0 74.0 5 17.0 116.0

最后结果

In [926]: dff[['a', 'b']].mul(df.action, 0).cumsum().ffill().fillna(0).join(df.day) Out[926]: a b day 0 40.0 0.0 0 1 40.0 53.0 1 2 19.0 53.0 2 3 19.0 74.0 3 4 17.0 74.0 4 5 17.0 116.0 5

Using pivot_table

In [920]: dff = df.pivot_table( index=['day', 'action'], columns='symbol', values='quantity').reset_index() In [921]: dff Out[921]: symbol day action a b 0 0 1 40.0 NaN 1 1 1 NaN 53.0 2 2 -1 21.0 NaN 3 3 1 NaN 21.0 4 4 -1 2.0 NaN 5 5 1 NaN 42.0

Then, mul the action, take cumsum, forward fill missing values, and finally replace NaNs with 0

In [922]: dff[['a', 'b']].mul(df.action, 0).cumsum().ffill().fillna(0) Out[922]: symbol a b 0 40.0 0.0 1 40.0 53.0 2 19.0 53.0 3 19.0 74.0 4 17.0 74.0 5 17.0 116.0

Final result

In [926]: dff[['a', 'b']].mul(df.action, 0).cumsum().ffill().fillna(0).join(df.day) Out[926]: a b day 0 40.0 0.0 0 1 40.0 53.0 1 2 19.0 53.0 2 3 19.0 74.0 3 4 17.0 74.0 4 5 17.0 116.0 5