I have a DataFrame such that:
Amt
Date
01/01/2000 10
01/02/2000 10
01/03/2000 10
01/04/2000 10
01/05/2000 10
01/06/2000 10
01/07/2000 10
Now suppose I have two storage facilities to store the Amt of product that I purchase; Storage 1 which has a cap of 22.5, and Storage 2 which has a capacity of 30. I would like to add both of these as columns and have them each sum cumulatively at a SPLIT quantity of Amt (for every 10, 5 goes into each). Once Storage 1 reaches capacity, I would like the remainder to go into Storage 2 until it becomes full, at which point the remainder would go into a third column, Sell. After this, the Amt can continue to accumulate in the Sell column for the remainder of the DataFrame, such that the output would look like:
Amt | Storage 1 | Storage 2 | Sell |
Date
01/01/2000 10 5 5 0
01/02/2000 10 10 10 0
01/03/2000 10 15 15 0
01/04/2000 10 20 20 0
01/05/2000 10 22.5 27.5 0
01/06/2000 10 22.5 30 7.5
01/07/2000 10 22.5 30 17.5
I am aware of cumsum but I am not sure how to set conditions on it, nor do I know how to retrieve the remainder value in the case the storage fills up.
I apologize if this is unclear. If I am missing any necessary information, please let me know. Thanks in advance.
Since all values in Amt are the same, You may create each column by using cumsum and clip as follows
s = df.Amt.cumsum()
df['Storage 1'] = (s/2).clip(upper=22.5)
df['Storage 2'] = (s - df['Storage 1']).clip(upper=30)
df['sell'] = s - df['Storage 1'] - df['Storage 2']
Out[556]:
Amt Storage 1 Storage 2 sell
Date
01/01/2000 10 5.0 5.0 0.0
01/02/2000 10 10.0 10.0 0.0
01/03/2000 10 15.0 15.0 0.0
01/04/2000 10 20.0 20.0 0.0
01/05/2000 10 22.5 27.5 0.0
01/06/2000 10 22.5 30.0 7.5
01/07/2000 10 22.5 30.0 17.5