1
\$\begingroup\$

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.

\$\endgroup\$
  • 1
    \$\begingroup\$ Hey, welcome to Code Review! Can you add some small example input data? It is a bit hard to follow what is happening otherwise. \$\endgroup\$ – Graipher May 2 at 6:49
  • 2
    \$\begingroup\$ 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. \$\endgroup\$ – t3chb0t May 2 at 10:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.