Rewriting for loops to execute faster in pandas/python

Question

I have for loop written in pandas, with simple algebric function to create new columns. But they are required to be looped over a large range. How can I create an alternative as right now it takes hours and hours to execute. Please look at the code below

My Data looks like this


Org Oracle Item #   OH  SO  OH-SO   OO-Ven  OO-Internal Available OH    Year    Week    Incoming Inventory  Week End Inv    Sales History   Weekly Fcst Receipt Qty Booked Qty  Date    Wk Start Date   Today   Next 7 Days Next 14 Days    Next 4 Weeks    Next 90 Days    DC  Hub DC  Hub Oracle Item Vendor  Org_Item    Hub_Item    Target Inventory Level  Safety Stock    Stock Upper Limit   Projected Receipt   Closing Inventory   Expected Shortage   Expected Excess
0   811 000033  0.0 0.0 0.0 0.0 0.0 0.0 2022.0  23.0    0.0 0.0 0.0 0.187326    0.0 0.0 2022-06-10  2022-06-06  2022-06-10  2022-06-17  2022-06-24  2022-07-08  2022-09-08  811 812 N   000033  104 811_000033  812_000033  0.995938    0.374652    1.424657    0.000000    0.0 0.187326    0.0
1   811 000033  0.0 0.0 0.0 0.0 0.0 0.0 2022.0  24.0    0.0 0.0 0.0 0.187326    0.0 0.0 2022-06-17  2022-06-13  2022-06-10  2022-06-17  2022-06-24  2022-07-08  2022-09-08  811 812 N   000033  104 811_000033  812_000033  0.931928    0.374652    1.419416    0.000000    0.0 0.187326    0.

# Calculate Projected Inventory, Shortage
for i in range(len(x)):
    if i == 0:
        x.iloc[i]["Projected Receipt"] = 0
        x.iloc[i]["Closing Inventory"] = max(0,x.iloc[i]["Available OH"] + x.iloc[i]["Incoming Inventory"] + x.iloc[i]["Projected Receipt"] - x.iloc[i]["Weekly Fcst"])
        x.iloc[i]["Expected Shortage"] = abs(min(0,x.iloc[i]["Available OH"] + x.iloc[i]["Incoming Inventory"] + x.iloc[i]["Projected Receipt"]- x.iloc[i]["Weekly Fcst"]))
        x.iloc[i]["Expected Excess"] = max(0,(x.iloc[i]["Closing Inventory"] - x.iloc[i]["Stock Upper Limit"]))
    elif x.iloc[i]["Org_Item"] != x.iloc[i-1]["Org_Item"]:
        x.iloc[i]["Projected Receipt"] = 0
        x.iloc[i]["Closing Inventory"] = max(0,x.iloc[i]["Available OH"] + x.iloc[i]["Incoming Inventory"] + x.iloc[i]["Projected Receipt"]- x.iloc[i]["Weekly Fcst"])
        x.iloc[i]["Expected Shortage"] = abs(min(0,x.iloc[i]["Available OH"] + x.iloc[i]["Incoming Inventory"] + x.iloc[i]["Projected Receipt"]- x.iloc[i]["Weekly Fcst"]))
        x.iloc[i]["Expected Excess"] = max(0,(x.iloc[i]["Closing Inventory"] - x.iloc[i]["Stock Upper Limit"]))
    else:
        if x.iloc[i]["Date"]<x.iloc[i]["Next 14 Days"]:
            x.iloc[i]["Projected Receipt"] = 0
        else:
            x.iloc[i]["Projected Receipt"] = max(0,(x.iloc[i]["Target Inventory Level"] + x.iloc[i]["Weekly Fcst"] - x.iloc[i-1]["Closing Inventory"] - x.iloc[i]["Incoming Inventory"]))
        x.iloc[i]["Closing Inventory"] = max(0,x.iloc[i-1]["Closing Inventory"] + x.iloc[i]["Incoming Inventory"] + x.iloc[i]["Projected Receipt"] - x.iloc[i]["Weekly Fcst"])
        x.iloc[i]["Expected Shortage"] = abs(min(0,x.iloc[i-1]["Closing Inventory"] + x.iloc[i]["Incoming Inventory"] + x.iloc[i]["Projected Receipt"]- x.iloc[i]["Weekly Fcst"]))
        x.iloc[i]["Expected Excess"] = max(0,(x.iloc[i]["Closing Inventory"] - x.iloc[i]["Stock Upper Limit"]))

x.head()

There are at least 1001 entries in stackOverflow about how to optimize `for` loops in pandas. I would advice to look into them — Zaero Divide, Jun 11 '22 at 20:09

score 0 · Answer 1 · answered Jun 11 '22 at 19:10

For loops in Pandas are generally super slow, there are many alternatives to it that run much more efficiently. This explanation goes into more detail. I would look into numpy if you aren't familiar with it, it can be used for conditional columns, and to apply functions like abs as well. I don't have the full data, but here are some lines of code I think might be useful here.

Getting the previous row value:

df['prev_Org_Item'] = df['Org_Item'].shift(1)
df['Prev Closing Inventory'] = df['Prev Closing Inventory'].shift(1)

Adding/Subtracting etc. multiple columns together:

df["Closing Inventory"] = df["Available OH"] + df["Incoming Inventory"] + df["Projected Receipt"] - df["Weekly Fcst"]

Conditional Column (The np.where function is used as np.where(condition, value_if_true, value_if_false))

import numpy as np
df["Projected Receipt"] = np.where(df["Date"] < df["Next 14 Days"], 0, df["Target Inventory Level"] + df["Weekly Fcst"] - df["Prev Closing Inventory"] - df["Incoming Inventory"])

And if you want to replace values < or > 0 with 0:

df["Target Inventory Level"] = np.abs(np.where("Target Inventory Level") < 0, 0, df["Target Inventory Level"]))

Hello, Thank you for your comment. In my loop , projected inventory and closing inventory are inter-related. Will my calculation be effected if I simply define previous closing inventory?? — Prasenjit Datta, Jun 11 '22 at 19:25
If you create the projected field first and the closing field (or whichever is dependent) afterwards, it should handle it right — lesk_s, Jun 11 '22 at 20:06

Rewriting for loops to execute faster in pandas/python

1 Answers1