How to automatically remove part of a stock when it reaches expiration date in Python? (within FIFO method)

Question

I have a pandas dataframe with the structure shown below:

| Contract |Purchase Date|Value|Expiration Date|Demand|Stock
| A        | 2019-01-01  |1000 | 2019-01-04    |700   | 300
| B        | 2019-01-02  |1000 | 2019-01-05    |1000  | 300
| C        | 2019-01-03  |1000 | 2019-01-06    |1200  | 100 
| D        | 2019-01-04  |1000 | 2019-01-07    |1000  | 100
| E        | 2019-01-05  |1000 | 2019-01-08    |1000  | 0
| F        | 2019-01-06  |1000 | 2019-01-09    |800   | 200
| G        | 2019-01-07  |1000 | 2019-01-10    |1000  | 200

The "Demand" column represents demanded daily volumes, "Contract" column represents the daily volumes that were purchased to meet demand. These contracts have an expiration date. The system works in the following way:

• When demand is lower than contracts, the remaining volume goes to the "Stock" column (like in the first row of the example).

• When demand is higher than contracts an there is a positive stock, it is used to meet demand (like in row 3). Here, we use FIFO methodology. That is to say, the first contract purchased is the first going out as long as it has not expired (like in row 5).

So, what I need is to automatically remove from the stock the amounts that are expired at the same time that the stock automatically decreases when demand is higher than contracts following the FIFO methodology.

I´m new in python and I couldn´t find an approach that works for me. I'd appreciate any idea you have.

Just to be clear, I'm not looking for you to code it for me rather I'm looking for the appropriate library that could handle this operations. I know I should use deque objects for the FIFO part (and also there is plenty information about that here) but don't know how to handle expiration dates.

Answer 1

Try:

import pandas as pd
dates = pd.date_range(start='2005-1-1', end='2014-12-31', freq='D')

for each in dates:
    if each.month==expire_month and each.day ==expire_day:
        df.drop(df.columns[[0,1,3]], axis=1, inplace=True) #You can select your own columns such as stock

For FIFO:

df.shift(1)
df.loc[0] = new_row

df.shift(n) will shift the rows n times, filling the first n rows with 'na' and getting rid of last n rows. The number of rows of df will not change with df.shift.