I am currently working with a dataset that includes the product sales for different stores over different weeks.
This can be seen in the following dataframe excerpt:
data
Out[44]:
Store_id SKU_id WEEK Units_Sold PRICE Promotion
20462 5 11 166 1 3.79 NaN
20463 5 11 167 3 3.79 NaN
20464 5 11 168 6 3.79 NaN
20465 5 11 169 2 3.79 NaN
20466 5 11 170 1 3.79 NaN
... ... ... ... ... ... ...
3674981 146 14 395 11 4.99 B
3674982 146 14 396 13 4.99 B
3674983 146 14 397 31 4.99 B
3674984 146 14 398 9 5.99 NaN
3674985 146 14 399 9 5.99 NaN
This dataframe includes the product data for 23 different products in 50 stores.
I now want to add a column to each data entry, which includes the price and units sold for the other products in the same store in the same week.
Say for instance i want to look at the first data entry (20462). This data entry looks at the product 11(SKU_id). This data entry includes the PRICE of the product as well as the store it was sold in (Store_id) and the particular WEEK it was sold in.
Now I wish to add a column for every other product (SKU_id) which indicates how many of that product also sold in the SAME STORE in the SAME WEEK.
This data is available in the dataframe. For example refer to the following two examples:
Store_id SKU_id WEEK Units_Sold PRICE Promotion
20462 5 11 166 1 3.79 NaN
21254 5 14 166 12 4.20 NaN
Here i indicate two separate data entries from my dataframe. As one can see, both these products were sold in the same store (Store_id = 5) in the same week (WEEK=166).
I wish to, for each product, add the information of the other products sold in the same week in the same store.
For example, referring back to the previous example, the desired output would look something like this.
Store_id SKU_id WEEK Units_Sold PRICE Promotion Comp1 Units Sold Comp1 PRICE
20462 5 11 166 1 3.79 NaN 12 4.20
21254 5 14 166 12 4.20 NaN 1 3.79
Here one can see that the desired information (PRICE and UNITS SOLD) of the other data entry is added as a column in the current data entry (Comp1 PRICE and Comp1 Units Sold respectively).
This is the type of output I want for every data entry. The problem is I do not know how to automate this process.
Sorry if this is not enough information, Any help is appreciated.
I believe the answer to be in the realm of using some groupby() or stack() functionality. I am however unversed in these data manipulation approaches and am turning to you for help.