Optimizing Slow For Loop Function Issue
-
I am creating a first in first out inventory style function. The input is a dataframe with a DateTimeIndex that is already sorted in ascending order. The two other columns are the price and quantity. Quantity can be either positive (purchases) or negative (sales). The logic evaluates row by row any offsetting transaction in which the buy_price <= sell_price and the position is a non-zero value. The resulting output should show all rows and the remaining quantities which were unable offset with future dated transactions. I started with a for loop which was extremely slow but produces the correct results. So I attempted optimize it and made some good progress but I can't seem to get the results to match. Any help is appreciated.
def fifo(df):
'''
Iterate through chronologically sorted df and determine remaining
buying or selling at each price level based on a first in first out
method
'''
i = 0
while i < len(df) - 1:
if df.iloc[i]['quantity'] == 0:
i += 1
continue
elif df.iloc[i]['quantity'] > 0:
for j in range(i + 1, len(df)):
if df.iloc[j]['quantity'] < 0:
if df.iloc[i]['price'] <= df.iloc[j]['price']:
qty = min(abs(df.iloc[j]['quantity']), df.iloc[i]['quantity'])
df.at[df.index[i], 'quantity'] -= qty
df.at[df.index[j], 'quantity'] += qty
if df.iloc[i]['quantity'] == 0:
break
elif df.iloc[i]['quantity'] < 0:
for j in range(i + 1, len(df)):
if df.iloc[j]['quantity'] > 0:
if df.iloc[j]['price'] <= df.iloc[i]['price']:
qty = min(abs(df.iloc[i]['quantity']), df.iloc[j]['quantity'])
df.at[df.index[i], 'quantity'] += qty
df.at[df.index[j], 'quantity'] -= qty
if df.iloc[i]['quantity'] == 0:
break
i += 1
return dfHere is my optimized version which does produce the net quantity correctly but the row by row price quantity comparison is incorrect.
def fifo_optimized(df):
buy_index = np.where(df.quantity > 0)[0]
sell_index = np.where(df.quantity < 0)[0]
buy_quantity = df.quantity.iloc[buy_index].values
sell_qu