Pandas groupby-aggregation not working after shift() operation

user_16890872 0
Viewed 150

I was analyzing a Kaggle project and found below inconsistency:

We have a sales data for store (1, 2, 3) and items (1, 2, 3, 4, 5). The original author, wanted to calculate sum and mean for every combination of the store, item and period (month/year). and since s/he thought we should have visibility of only previous months when predicting the sales for current month, s/he decided to shift the sales vectors by 1. But this causes the issue that last month's Sales for store 1 product 1 becomes part of store 1 product 2.

data.groupby(["store", "item", "period"]).sales.agg(["sum", "mean"]).shift(1)

My initial thought was to maybe move shift operation after grouping but before aggregation.

data.groupby(["store", "item", "period"]).sales.shift(1).agg(["sum", "mean"])

Looking at outputs from the second line of code is very surprising for me. seems like aggregation didnt respect groupby indexation after shift was executed.

Screenshot showing code and output:

Has anybody experienced anything similar? is shifting really removes indexation from groupby operation?

EDIT:

adding the link to Original kaggle workbook: https://www.kaggle.com/ekrembayar/store-item-demand-forecasting-with-lgbm

pandas shift aggregation dataframe python
2 Answers

Try something like:

data['sales'] = data.groupby(["store", "item", "period"]).sales.transform('shift')
print(data.groupby(["store", "item", "period"]).sales.agg(["sum", "mean"]))
user_87083640

You can try using 2 groupby() successively, as follows:

(data.groupby(["store", "item", "period"]).sales.shift(1)
     .groupby([df["store"], df["item"], df["period"]]).agg(["sum", "mean"])
)

This way, we don't need to change the original values of sales and yet we can get the desired result.

user_150706970
Related