Calculate the mean of a nested group if the condition holds

user_11016584 0
Viewed 63

I own a dataset (df) which contains the historical daily (date) demand (demand) of different supermarkets (id_store). Each id_store offers different products (id_product), but the assortment varies daily, so the same id_product is not offered every day.

My goal is to find the mean demand of the last four identical weekdays (t-7, t-14, t-21, t-28) for each id_product of the respective id_store, if the id_product was offered in the id_store on more than one identical weekday. If the id_product was not offered on any or only one identical weekday, NaN should be returned.

Question:
The following code works, but takes about 6h for my df, which contains two million observations. Do any of you have an idea how I can make the code more efficient (e.g. with groupby)?

import numpy as np
import pandas as pd
from pandas import Timestamp

def mean_weekday_4w(df):

    df.loc[:, "weekday"] = df["date"].dt.day_name()
    df_group = df.groupby(["id_store", "id_product", "weekday"], as_index=False)["demand"].sum()
    query_4w = "date == '%s' | date == '%s' | date == '%s' | date == '%s'"

    for ids, idp, wdy in df_group[["id_store", "id_product", "weekday"]].values:
        df_query_group = df.query("id_store == @ids & id_product == @idp & weekday == @wdy")
    
        for i, row in df_query_group.iterrows():
            df_query_4w = df_query_group.query(query_4w % (row["date"] - pd.Timedelta(days=7),
                                                           row["date"] - pd.Timedelta(days=14),
                                                           row["date"] - pd.Timedelta(days=21),
                                                           row["date"] - pd.Timedelta(days=28)))

            if df_query_4w.shape[0] >= 2:
                df.at[i, "mean_weekday_4w"] = df_query_4w["demand"].mean()
            else:
                df.at[i, "mean_weekday_4w"] = np.nan


    return df

Here is a small sample of my dataset:

df = pd.DataFrame({'date': {0: Timestamp('2020-03-21 00:00:00'), 1: Timestamp('2020-03-28 00:00:00'), 2: Timestamp('2020-04-04 00:00:00'), 3: Timestamp('2020-04-11 00:00:00'), 4: Timestamp('2020-04-18 00:00:00'), 5: Timestamp('2020-03-21 00:00:00'), 6: Timestamp('2020-03-28 00:00:00'), 7: Timestamp('2020-04-04 00:00:00'), 8: Timestamp('2020-03-21 00:00:00'), 9: Timestamp('2020-03-25 00:00:00'), 10: Timestamp('2020-04-04 00:00:00'), 11: Timestamp('2020-03-21 00:00:00'), 12: Timestamp('2020-03-28 00:00:00'), 13: Timestamp('2020-04-18 00:00:00'), 14: Timestamp('2020-04-25 00:00:00'), 15: Timestamp('2020-05-02 00:00:00'), 16: Timestamp('2020-05-09 00:00:00'), 17: Timestamp('2020-05-16 00:00:00'), 18: Timestamp('2020-03-21 00:00:00')}, 'id_store': {0: 0, 1: 0, 2: 0, 3: 0, 4: 0, 5: 0, 6: 0, 7: 0, 8: 1, 9: 1, 10: 1, 11: 1, 12: 1, 13: 1, 14: 1, 15: 1, 16: 1, 17: 1, 18: 1}, 'id_product': {0: 1, 1: 1, 2: 1, 3: 1, 4: 1, 5: 2, 6: 2, 7: 2, 8: 1, 9: 1, 10: 1, 11: 2, 12: 2, 13: 3, 14: 3, 15: 3, 16: 3, 17: 3, 18: 4}, 'demand': {0: 97.0, 1: 156.0, 2: 130.0, 3: 105.0, 4: 229.0, 5: 60.0, 6: 50.0, 7: 47.0, 8: 69.0, 9: 95.0, 10: 175.0, 11: 66.0, 12: 63.0, 13: 82.0, 14: 7.0, 15: 78.0, 16: 34.0, 17: 36.0, 18: 81.0}})

The desired output looks like this:

date id_store id_product demand mean_weekday_4w
0 2020-03-21 0 1 97.00 nan
1 2020-03-28 0 1 156.00 nan
2 2020-04-04 0 1 130.00 126.50
3 2020-04-11 0 1 105.00 127.67
4 2020-04-18 0 1 229.00 122.00
5 2020-03-21 0 2 60.00 nan
6 2020-03-28 0 2 50.00 nan
7 2020-04-04 0 2 47.00 55.00
8 2020-03-21 1 1 69.00 nan
9 2020-03-25 1 1 95.00 nan
10 2020-04-04 1 1 175.00 nan
11 2020-03-21 1 2 66.00 nan
12 2020-03-28 1 2 63.00 nan
13 2020-04-18 1 3 82.00 nan
14 2020-04-25 1 3 7.00 nan
15 2020-05-02 1 3 78.00 44.50
16 2020-05-09 1 3 34.00 55.67
17 2020-05-16 1 3 36.00 50.25
18 2020-03-21 1 4 81.00 nan
pandas dataframe python
2 Answers

Try:

out = []
timedeltas = [pd.Timedelta(days=d) for d in (7, 14, 21, 28)]

for _, g in df.groupby(["id_store", "id_product"]):

    for d in g["date"]:
        mask = g["date"].isin([d - t for t in timedeltas])
        if mask.sum() < 2:
            out.append(np.nan)
        else:
            out.append(g.loc[mask, "demand"].mean())

df["mean_weekday_4w"] = out
print(df)

Prints:

         date  id_store  id_product  demand  mean_weekday_4w
0  2020-03-21         0           1    97.0              NaN
1  2020-03-28         0           1   156.0              NaN
2  2020-04-04         0           1   130.0       126.500000
3  2020-04-11         0           1   105.0       127.666667
4  2020-04-18         0           1   229.0       122.000000
5  2020-03-21         0           2    60.0              NaN
6  2020-03-28         0           2    50.0              NaN
7  2020-04-04         0           2    47.0        55.000000
8  2020-03-21         1           1    69.0              NaN
9  2020-03-25         1           1    95.0              NaN
10 2020-04-04         1           1   175.0              NaN
11 2020-03-21         1           2    66.0              NaN
12 2020-03-28         1           2    63.0              NaN
13 2020-04-18         1           3    82.0              NaN
14 2020-04-25         1           3     7.0              NaN
15 2020-05-02         1           3    78.0        44.500000
16 2020-05-09         1           3    34.0        55.666667
17 2020-05-16         1           3    36.0        50.250000
18 2020-03-21         1           4    81.0              NaN
user_100359850

Group by week day and roll over 4 weeks with a minimum of 2 observations (and the current) and exclude the current observation (close only the left interval):

df['mean_weekday_4w'] = df.groupby(['id_store', 'id_product', df['date'].dt.weekday]) \
                          .rolling(4, 2, closed='left') \
                          .mean().values

>>> df
         date  id_store  id_product  demand  mean_weekday_4w
0  2020-03-21         0           1    97.0              NaN
1  2020-03-28         0           1   156.0              NaN
2  2020-04-04         0           1   130.0       126.500000
3  2020-04-11         0           1   105.0       127.666667
4  2020-04-18         0           1   229.0       122.000000
5  2020-03-21         0           2    60.0              NaN
6  2020-03-28         0           2    50.0              NaN
7  2020-04-04         0           2    47.0        55.000000
8  2020-03-21         1           1    69.0              NaN
9  2020-03-25         1           1    95.0              NaN
10 2020-04-04         1           1   175.0              NaN
11 2020-03-21         1           2    66.0              NaN
12 2020-03-28         1           2    63.0              NaN
13 2020-04-18         1           3    82.0              NaN
14 2020-04-25         1           3     7.0              NaN
15 2020-05-02         1           3    78.0        44.500000
16 2020-05-09         1           3    34.0        55.666667
17 2020-05-16         1           3    36.0        50.250000
18 2020-03-21         1           4    81.0              NaN

Performance

# create 1,999,997 records
df1 = pd.concat([df]*105263)

# change just the store id to create new groups
df1['id_store'] = pd.concat([df['id_store']+i for i in range(N)]).values  

%timeit df1.groupby(['id_store', 'id_product', df1['date'].dt.weekday]).rolling(4, 2, closed='left').mean().values
57.9 s ± 1.58 s per loop (mean ± std. dev. of 7 runs, 1 loop each)
user_152399510
Related