# Flagging out-of-stock items

I wrote a function that iterates over 3 parameters to spot out-of-stock items. To illustrate this I'll draw an example: suppose that the product 10 in store 2 for an offer named "super5" is following this trend

day | qty

1     50
2     70
3     55
4     67
5     13
6     0


Offer finished at day 6, the product was potentially out of stock at day 5. In order to spot this and verify it I find the index of 0 qty and look 2 back i took the mean of that two day (67 + 13) / 2 = 40 if the mean is > first decile then tag "out-of-stock" else "ko"

I tried:

def flag_out_of_stock(dataframe, sku, store, offer) :
ffl = []
sku_store_offer = dataframe[["id_sku", "id_store", "id_offer"]].drop_duplicates(["id_sku", "id_store", "id_offer"])
for sku, store, offer in tqdm(zip(sku_store_offer["id_sku"], sku_store_offer["id_store"], sku_store_offer["id_offer"])):
cond1 = dataframe["id_sku"] == sku
cond2 = dataframe["id_store"] == store
cond3 = dataframe["id_offer"] == offer
timeseries = dataframe[np.logical_and.reduce((cond1, cond2, cond3))][["f_qty_recalc", "id_day"]]\
.set_index("id_day")\
.sort_index()
mu = timeseries.mean()[0]
if mu >= 6 :
sigma = timeseries.std()[0]
q1 = timeseries.quantile(0.1)[0]
# index where qty == 0
likely_out_of_stock_index = np.where(timeseries ==0)[0]
# if there more that one value where qty == 0
if len(likely_out_of_stock_index) > 1 :
# for each index where qty == 0
for i in likely_out_of_stock_index :
# if the day before or day after are superior to the first decile
#then flag out of stock
day_before_2 = timeseries.iloc[i-2:i].mean()[0]
if day_before_2 >= q1 :
ffl.append("out_of_stock")
elif day_before_2 >= mu - sigma  :
ffl.append("likely_out_of_stock")
else :
ffl.append("KO")
else :
try :
day_before_2 = timeseries.iloc[likely_out_of_stock_index-2:likely_out_of_stock_index].mean()[0]
if day_before_2 >= q1 :
ffl.append("out_of_stock")
elif day_before_2 >= mu - sigma  :
ffl.append("out_of_stock")
else :
ffl.append("KO")
except TypeError :
ffl.append("KO")
else :
ffl.append("KO")
return pd.Series(ffl)


The issue there that this code running on ~600k row of parameters and took ~1.3seconds per iteration. Of that, 0.9sec is that operations…

timeseries = dataframe[np.logical_and.reduce((cond1, cond2, cond3))][["f_qty_recalc", "id_day"]]\
.set_index("id_day")\
.sort_index()


so I would like to find a faster way.

• Hey, welcome to Code Review! Can you add some small example input data? It is a bit hard to follow what is happening otherwise. – Graipher May 2 '19 at 6:49
• This question has been flagged for moving to SO but I think it belongs here. It's not looking off-topic but as @Graipher said, some additional example data would be very helpful. – t3chb0t May 2 '19 at 10:23