python - Using python to solve FIFO inventory problem

Question

I'm trying to use Python to create a FIFO notional (stock) ageing solution. The idea is that when the sign of the 'day trade' changes it offsets the earliest 'day trade' with the opposite sign. And when the sign of 'Notional' changes, the post-offsetting value will just be the current day notional value, like a reset.

I have tried to use for loops with this but it almost feels like the calculation needs to break at some point, offset the earlies inventory and resume the calculation again.

Could someone point out tips/functions/libraries which I can use to make this happen?

Logic

import pandas as pd

df = pd.DataFrame()
df['Cusip'] = [123,123,123,123,123,123,123,123,123,123]
df['Day'] = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
df['Notional'] = [50, 200, 230, 130, -30, -40, -20, 100, -10, 30]
df['In'] = [50, 150, 30, 0, 0, 0, 20, 120, 0, 40]
df['Out'] = [0, 0, 0, -100, -160, -10, 0, 0, -110, 0]


z = 0
        

df['day trade'] = df.apply(lambda row: row['Out'] + row['In'] , axis=1)
df['previous day trade'] = df['day trade'].shift(1).fillna(0)


def FiFo(dfg):
    if dfg[dfg['CS'] < 0]['day trade'].count():
        subT = dfg[dfg['CS'] < 0]['CS'].iloc[-1]
        dfg['day trade'] = np.where((dfg['CS'] + subT) <= 0, 0, dfg['day trade'])
        dfg = dfg[dfg['day trade'] > 0]
        if (len(dfg) > 0):
            dfg['day trade'].iloc[0] = dfg['CS'].iloc[0] + subT
    return dfg

df['PN'] = np.where(df['day trade'] > 0, 'P', 'N')
df['CS'] = df['day trade'].cumsum()
dfR = df.groupby(['Day'], as_index=False)\
    .apply(FiFo)  \
    .reset_index(drop=True)

dfR

标签： pythonpython-3.xpandasfor-loop