{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"From transaction, we can extract effective information as out_of_stock item, or find out some campaigns in retail marketing:\n* 'Online channel sale': item which its price is lower in online channel\n* 'Offline channel sale': item which its price is lower in offline channel(store)\n* 'Current month sale': item which its price in September 2020 is lower than other months\n\nIn this notebook i will show you how we can get hidden informative item features from features. In practice, 'Campaign' features is very useful, and 'on-campaign' items is ussually enhancement by weight in recommendation. In this notebook, we will extract 4 campaign:\n\n* out-of-stock items\n* 'Current month sale'\n* 'Offline channel sale'\n* 'Online channel sale'\n\nIf you want to use directly campaign data for your recommendation model, you can use it directly here:\nhttps://www.kaggle.com/astrung/hm-article-capaign","metadata":{}},{"cell_type":"markdown","source":"# 1. Find out out_of_stock_items","metadata":{}},{"cell_type":"code","source":"import pandas as pd\ndf = pd.read_csv(r\"/kaggle/input/h-and-m-personalized-fashion-recommendations/transactions_train.csv\", \n                 dtype={'article_id': 'str'})\ndf.head()","metadata":{"execution":{"iopub.status.busy":"2022-03-06T09:03:43.306206Z","iopub.execute_input":"2022-03-06T09:03:43.306808Z","iopub.status.idle":"2022-03-06T09:04:53.298962Z","shell.execute_reply.started":"2022-03-06T09:03:43.306778Z","shell.execute_reply":"2022-03-06T09:04:53.298003Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df['t_dat'] = pd.to_datetime(df['t_dat'], format=\"%Y-%m-%d\")\ndf['month'] = df['t_dat'].dt.strftime('%m')\ndf['year'] = df['t_dat'].dt.strftime('%Y')\ndf.head()","metadata":{"execution":{"iopub.status.busy":"2022-03-06T09:05:27.806740Z","iopub.execute_input":"2022-03-06T09:05:27.807630Z","iopub.status.idle":"2022-03-06T09:10:38.210406Z","shell.execute_reply.started":"2022-03-06T09:05:27.807584Z","shell.execute_reply":"2022-03-06T09:10:38.209286Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"we will mask item which isn't sold in 2020 July, August and September is 'out_of_stock'.\nWe will remove out of stock item in recommendation item candidates.","metadata":{}},{"cell_type":"code","source":"df_month_price = df[df['year'] == '2020'][['article_id', 'price', 'month', 'year']].drop_duplicates(\n    ['article_id', 'price', 'month', 'year']).copy()\ndf_month_price.head()","metadata":{"execution":{"iopub.status.busy":"2022-03-06T09:14:23.978635Z","iopub.execute_input":"2022-03-06T09:14:23.979795Z","iopub.status.idle":"2022-03-06T09:14:34.430684Z","shell.execute_reply.started":"2022-03-06T09:14:23.979739Z","shell.execute_reply":"2022-03-06T09:14:34.429454Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_month_avg_price = df_month_price.groupby(['article_id', 'month'])['price'].mean().unstack().reset_index()\ndf_month_avg_price","metadata":{"execution":{"iopub.status.busy":"2022-03-06T09:14:51.169137Z","iopub.execute_input":"2022-03-06T09:14:51.169404Z","iopub.status.idle":"2022-03-06T09:14:52.282063Z","shell.execute_reply.started":"2022-03-06T09:14:51.169375Z","shell.execute_reply":"2022-03-06T09:14:52.281513Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"In above tables, item which isn't sold in month will be masked as `NaN`. We can see that so many items which isn't sold in many months. Let find out item whichs isn't sold in last 3 months, and mask it as out-of-stock","metadata":{}},{"cell_type":"code","source":"df_out_of_stock = df_month_avg_price[df_month_avg_price['07'].isna() & \n                                     df_month_avg_price['08'].isna() & df_month_avg_price['09'].isna()]\ndf_out_of_stock","metadata":{"execution":{"iopub.status.busy":"2022-03-06T09:18:30.617992Z","iopub.execute_input":"2022-03-06T09:18:30.618268Z","iopub.status.idle":"2022-03-06T09:18:30.642240Z","shell.execute_reply.started":"2022-03-06T09:18:30.618240Z","shell.execute_reply":"2022-03-06T09:18:30.641610Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_out_of_stock = pd.DataFrame({'article_id': df_out_of_stock.article_id.values})\ndf_out_of_stock['out_of_stock'] = 1\ndf_out_of_stock","metadata":{"execution":{"iopub.status.busy":"2022-03-06T09:51:25.097346Z","iopub.execute_input":"2022-03-06T09:51:25.097934Z","iopub.status.idle":"2022-03-06T09:51:25.112335Z","shell.execute_reply.started":"2022-03-06T09:51:25.097899Z","shell.execute_reply":"2022-03-06T09:51:25.111482Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 2. Find out items which is 'Current month sale' campaign","metadata":{}},{"cell_type":"markdown","source":"we calculate average price in 2020 for each items, then compare average price with price in Sep 2020. If its price is lower than 10%, we will mask it as 'current_month_sale' campaign","metadata":{}},{"cell_type":"code","source":"df_year_avg_price = df_month_price.groupby(['article_id'])['price'].mean().reset_index()\ndf_year_avg_price.head()","metadata":{"execution":{"iopub.status.busy":"2022-03-06T09:25:29.150739Z","iopub.execute_input":"2022-03-06T09:25:29.151105Z","iopub.status.idle":"2022-03-06T09:25:29.804369Z","shell.execute_reply.started":"2022-03-06T09:25:29.151066Z","shell.execute_reply":"2022-03-06T09:25:29.803474Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_on_sale = pd.merge(df_month_avg_price, df_year_avg_price, on='article_id')\ndf_on_sale = df_on_sale.fillna(-1)\ndf_on_sale","metadata":{"execution":{"iopub.status.busy":"2022-03-06T09:27:21.489218Z","iopub.execute_input":"2022-03-06T09:27:21.489532Z","iopub.status.idle":"2022-03-06T09:27:21.604930Z","shell.execute_reply.started":"2022-03-06T09:27:21.489503Z","shell.execute_reply":"2022-03-06T09:27:21.603746Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_on_sale['on_sale'] = df_on_sale.apply(\n    lambda x: 1 if x['09'] != -1 and abs(x['price']-x['09'])/x['price'] > 0.1 else 0, axis=1)\ndf_on_sale","metadata":{"execution":{"iopub.status.busy":"2022-03-06T09:28:00.370899Z","iopub.execute_input":"2022-03-06T09:28:00.371175Z","iopub.status.idle":"2022-03-06T09:28:01.277035Z","shell.execute_reply.started":"2022-03-06T09:28:00.371145Z","shell.execute_reply":"2022-03-06T09:28:01.276094Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_on_sale = df_on_sale[['article_id', 'on_sale']].copy()\ndf_on_sale.head()","metadata":{"execution":{"iopub.status.busy":"2022-03-06T09:29:09.391731Z","iopub.execute_input":"2022-03-06T09:29:09.392533Z","iopub.status.idle":"2022-03-06T09:29:09.411185Z","shell.execute_reply.started":"2022-03-06T09:29:09.392491Z","shell.execute_reply":"2022-03-06T09:29:09.409819Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3. Find out items which is 'Offline/Online channel sale' campaign","metadata":{}},{"cell_type":"markdown","source":"As you see in below plot, some items have different price for each channel. Item in online channel may be higher, or lower more than 50%, so it may be in a campaign for attention in a channel. We will extract campaign information, then use it as a item features","metadata":{}},{"cell_type":"code","source":"import matplotlib.pyplot as plt\ndef plot_comparing_price_channel(article_id):\n    test2 = df[df.article_id == article_id][['t_dat', 'price', 'sales_channel_id']].drop_duplicates().copy()\n    fig, ax = plt.subplots()\n    test2[test2.sales_channel_id == 1].set_index(\"t_dat\")['price'].plot(label='store')\n    test2[test2.sales_channel_id == 2].set_index(\"t_dat\")['price'].plot(label='online')\n    ax.legend()\n    plt.show()\n    plt.close()","metadata":{"execution":{"iopub.status.busy":"2022-03-06T09:32:37.208337Z","iopub.execute_input":"2022-03-06T09:32:37.208923Z","iopub.status.idle":"2022-03-06T09:32:37.214434Z","shell.execute_reply.started":"2022-03-06T09:32:37.208883Z","shell.execute_reply":"2022-03-06T09:32:37.213731Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_comparing_price_channel('0562245001')","metadata":{"execution":{"iopub.status.busy":"2022-03-06T09:32:37.876757Z","iopub.execute_input":"2022-03-06T09:32:37.877038Z","iopub.status.idle":"2022-03-06T09:32:41.369760Z","shell.execute_reply.started":"2022-03-06T09:32:37.876992Z","shell.execute_reply":"2022-03-06T09:32:41.368968Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Online channel has higher price than store. It may be in a campaign. Let find out it fromt transactions**","metadata":{}},{"cell_type":"code","source":"df_2020 = df[df['year'] == '2020'][\n    ['article_id', 'price', 'sales_channel_id', 'month']].drop_duplicates().copy().reset_index(drop=True)\ndf_2020","metadata":{"execution":{"iopub.status.busy":"2022-03-06T09:38:41.708492Z","iopub.execute_input":"2022-03-06T09:38:41.708897Z","iopub.status.idle":"2022-03-06T09:38:50.221445Z","shell.execute_reply.started":"2022-03-06T09:38:41.708865Z","shell.execute_reply":"2022-03-06T09:38:50.220576Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_2020['month'] = df_2020['month'].astype(int)\ndf_2020","metadata":{"execution":{"iopub.status.busy":"2022-03-06T09:39:29.286475Z","iopub.execute_input":"2022-03-06T09:39:29.286754Z","iopub.status.idle":"2022-03-06T09:39:29.617860Z","shell.execute_reply.started":"2022-03-06T09:39:29.286722Z","shell.execute_reply":"2022-03-06T09:39:29.617264Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# findout avg price for each channel in last 2 months\ndf_chanel_1 = df_2020[(df_2020.sales_channel_id == 1) & (df_2020['month'] >= 8)].groupby(\n    'article_id')['price'].mean().reset_index()\ndf_chanel_2 = df_2020[(df_2020.sales_channel_id == 2) & (df_2020['month'] >= 8)].groupby(\n    'article_id')['price'].mean().reset_index()\ndf_compare = pd.merge(df_chanel_2, df_chanel_1, on='article_id', suffixes=('_online', '_store'))\ndf_compare['price_diff_ratio'] = abs(df_compare.price_online / df_compare.price_store)\ndf_compare","metadata":{"execution":{"iopub.status.busy":"2022-03-06T09:41:20.547967Z","iopub.execute_input":"2022-03-06T09:41:20.548278Z","iopub.status.idle":"2022-03-06T09:41:20.733480Z","shell.execute_reply.started":"2022-03-06T09:41:20.548243Z","shell.execute_reply":"2022-03-06T09:41:20.732656Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* We will mask item with price_online / price_store < 0.8(lower than 20%) as 'online channel sale' items\n* We will mask item with price_online / price_store > 1.2(higher than 20%) as 'offline channel sale' items","metadata":{}},{"cell_type":"code","source":"df_online_sale = df_compare[df_compare.price_diff_ratio <= 0.8].copy()\ndf_online_sale['online_channel_sale'] = 1\ndf_online_sale","metadata":{"execution":{"iopub.status.busy":"2022-03-06T09:57:56.222577Z","iopub.execute_input":"2022-03-06T09:57:56.223108Z","iopub.status.idle":"2022-03-06T09:57:56.238411Z","shell.execute_reply.started":"2022-03-06T09:57:56.223072Z","shell.execute_reply":"2022-03-06T09:57:56.237777Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_offline_sale = df_compare[df_compare.price_diff_ratio >= 1.2].copy()\ndf_offline_sale['offline_channel_sale'] = 1\ndf_offline_sale","metadata":{"execution":{"iopub.status.busy":"2022-03-06T09:57:48.124512Z","iopub.execute_input":"2022-03-06T09:57:48.125055Z","iopub.status.idle":"2022-03-06T09:57:48.141225Z","shell.execute_reply.started":"2022-03-06T09:57:48.125004Z","shell.execute_reply":"2022-03-06T09:57:48.140531Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# finnaly, let make a data with all of campaign we extracted","metadata":{}},{"cell_type":"code","source":"df_article = pd.read_csv(r\"../input/h-and-m-personalized-fashion-recommendations/articles.csv\", \n                         dtype={'article_id': 'str'})\ndf_article.head()","metadata":{"execution":{"iopub.status.busy":"2022-03-06T09:52:10.149064Z","iopub.execute_input":"2022-03-06T09:52:10.149346Z","iopub.status.idle":"2022-03-06T09:52:11.223015Z","shell.execute_reply.started":"2022-03-06T09:52:10.149317Z","shell.execute_reply":"2022-03-06T09:52:11.222081Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"result = pd.merge(df_article[['article_id']], df_out_of_stock, on='article_id', how='outer')\nresult = pd.merge(result, df_on_sale, on='article_id', how='outer')\nresult = pd.merge(result, df_online_sale[['article_id', 'online_channel_sale']], on='article_id', how='outer')\nresult = pd.merge(result, df_offline_sale[['article_id', 'offline_channel_sale']], on='article_id', how='outer')\nresult","metadata":{"execution":{"iopub.status.busy":"2022-03-06T09:58:08.231730Z","iopub.execute_input":"2022-03-06T09:58:08.232220Z","iopub.status.idle":"2022-03-06T09:58:08.460964Z","shell.execute_reply.started":"2022-03-06T09:58:08.232168Z","shell.execute_reply":"2022-03-06T09:58:08.460118Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"result = result.fillna(0)\nresult.to_csv('article_campaign.csv', index=False)","metadata":{"execution":{"iopub.status.busy":"2022-03-06T10:00:05.458692Z","iopub.execute_input":"2022-03-06T10:00:05.459008Z","iopub.status.idle":"2022-03-06T10:00:05.729993Z","shell.execute_reply.started":"2022-03-06T10:00:05.458976Z","shell.execute_reply":"2022-03-06T10:00:05.729094Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}