CODEWITHSUNDEEP
pythonpandasdataframe
kumar_m_kiran
kumar_m_kiran

Reputation: 4022

pandas cumulative sum of stock in warehouse

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

Upvotes: 1

Views: 590

Answers (2)

Zero
Zero

Reputation: 76977

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

Upvotes: 3

Oliver Ni
Oliver Ni

Reputation: 2664

Nevermind, didn't see tag. This is just plain Python.

Try this:

sums = []

currentsums = {'a': 0, 'b': 0}

for i in data:
    currentsums[i['symbol']] += i['action'] * i['quantity']
    sums.append({'a': currentsums['a'], 'b': currentsums['b']})

Try it online!

Note that it gives a different result than you posted because you calculated wrong.

Upvotes: 0

Related Questions