{"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":"code","source":"import pandas as pd\nimport numpy as np\nfrom sklearn.metrics import mean_squared_error as mse\nimport matplotlib.pyplot as plt\nfrom pylab import rcParams\nimport seaborn as sns\nimport plotly.graph_objects as go\nfrom plotly.offline import plot, iplot, init_notebook_mode\ninit_notebook_mode(connected=True)\nfrom pandas.tseries.offsets import MonthEnd\nfrom pandas import Grouper\nfrom sklearn.preprocessing import LabelEncoder\nimport lightgbm as lgb\nimport warnings\nwarnings.simplefilter(action='ignore', category= FutureWarning)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:34:20.974022Z","iopub.execute_input":"2022-07-13T00:34:20.974416Z","iopub.status.idle":"2022-07-13T00:34:23.636054Z","shell.execute_reply.started":"2022-07-13T00:34:20.974303Z","shell.execute_reply":"2022-07-13T00:34:23.634870Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"item_cat = pd.read_csv('../input/competitive-data-science-predict-future-sales/item_categories.csv',header=0)\nitems = pd.read_csv('../input/competitive-data-science-predict-future-sales/items.csv',header=0)\nsales_train = pd.read_csv('../input/competitive-data-science-predict-future-sales/sales_train.csv',header=0)\nsample_sub = pd.read_csv('../input/competitive-data-science-predict-future-sales/sample_submission.csv',header=0)\nshops = pd.read_csv('../input/competitive-data-science-predict-future-sales/shops.csv',header=0)\ntest = pd.read_csv('../input/competitive-data-science-predict-future-sales/test.csv',header=0)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:35:12.703047Z","iopub.execute_input":"2022-07-13T00:35:12.703392Z","iopub.status.idle":"2022-07-13T00:35:15.961848Z","shell.execute_reply.started":"2022-07-13T00:35:12.703351Z","shell.execute_reply":"2022-07-13T00:35:15.960351Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 1. Exploratory Data Analysis","metadata":{}},{"cell_type":"code","source":"def check_data(data):\n    print('-' * 38+'Head'+'-' * 39)\n    print(data.head(3))\n    print('-' * 38+'Shape'+'-' * 38)\n    print(data.shape)\n    print('-' * 38+'Types'+'-' * 38)\n    print(data.dtypes)\n    print('-' * 38+'Na'+'-' * 41)\n    print(data.isnull().sum())","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:35:34.953375Z","iopub.execute_input":"2022-07-13T00:35:34.953660Z","iopub.status.idle":"2022-07-13T00:35:34.960168Z","shell.execute_reply.started":"2022-07-13T00:35:34.953630Z","shell.execute_reply":"2022-07-13T00:35:34.959278Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 1. 1. Check Data","metadata":{}},{"cell_type":"code","source":"check_data(sales_train)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:35:47.055158Z","iopub.execute_input":"2022-07-13T00:35:47.056075Z","iopub.status.idle":"2022-07-13T00:35:47.418335Z","shell.execute_reply.started":"2022-07-13T00:35:47.056016Z","shell.execute_reply":"2022-07-13T00:35:47.417399Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"check_data(test)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:35:50.263729Z","iopub.execute_input":"2022-07-13T00:35:50.264445Z","iopub.status.idle":"2022-07-13T00:35:50.276954Z","shell.execute_reply.started":"2022-07-13T00:35:50.264401Z","shell.execute_reply":"2022-07-13T00:35:50.276053Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Check if shop_ids & item_ids in the training set are identical to those ids in the testing set\nprint('training set:\\n shop_id:',sorted(list(sales_train.shop_id.unique())),\n      '\\n shop_id size:',sales_train.shop_id.unique().size,\n      '\\n item_id size:',sales_train.item_id.unique().size,\n      '\\n item_id max:',sales_train.item_id.unique().max(),\n     '\\n testing set:\\n shop_id:',sorted(list(test.shop_id.unique())),\n      '\\n shop_id size:',test.shop_id.unique().size,\n      '\\n item_id size:',test.item_id.unique().size,\n      '\\n item_id max:',test.item_id.unique().max())","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:35:52.982187Z","iopub.execute_input":"2022-07-13T00:35:52.983048Z","iopub.status.idle":"2022-07-13T00:35:53.092622Z","shell.execute_reply.started":"2022-07-13T00:35:52.982989Z","shell.execute_reply":"2022-07-13T00:35:53.091608Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sales_train[\"shop_item_id\"] = sales_train[\"shop_id\"] * 100000  + sales_train[\"item_id\"]\ntest[\"shop_item_id\"] = test[\"shop_id\"]* 100000  + test[\"item_id\"] \nprint('\\n\\n shop_item_id in testing set and also in training set:\\n',\n     test[\"shop_item_id\"].isin(sales_train['shop_item_id']).value_counts(),\n      '\\n\\n item_id in testing set and also in training set:\\n',\n     test[\"item_id\"].isin(sales_train['item_id']).value_counts(),\n     '\\n\\n shop_id in testing set and also in training set:\\n',\n     test[\"shop_id\"].isin(sales_train['shop_id']).value_counts())","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:35:55.353218Z","iopub.execute_input":"2022-07-13T00:35:55.354541Z","iopub.status.idle":"2022-07-13T00:35:55.607557Z","shell.execute_reply.started":"2022-07-13T00:35:55.354484Z","shell.execute_reply":"2022-07-13T00:35:55.606503Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"item_id_train = list(sales_train.item_id.unique())\nitem_id_test = list(test.item_id.unique())\nitem_id_new = [item for item in item_id_test if item not in item_id_train]\nprint('The number of new item_id in the testing set:\\n',len(item_id_new))","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:35:58.208413Z","iopub.execute_input":"2022-07-13T00:35:58.208738Z","iopub.status.idle":"2022-07-13T00:36:01.694129Z","shell.execute_reply.started":"2022-07-13T00:35:58.208705Z","shell.execute_reply":"2022-07-13T00:36:01.693258Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We find that: Some shop-item combinations in the testing set are the same as ids in the training set, which means shops will continue to sell those items (scenario 1). Some shop-item combinations in the testing set are not identical to those in the training set:  Those items were sold in some shops in the past and would sell in other shops in the future (scenario 2);  Some new products will be released in the future(scenario 3). \n   \n**Solutions:**\n\nFor scenario 1, we generate some lag features considering the shop-item combination to capture the temporal dynamics and treat the joint multivariate time series forecast as a regression.  \n\nFor scenario 2, the basic idea is the same as scenario 1, but the generated lag features depend on the monthly sales of items. \n\nFor scenario 3, the basic idea is to cluster the products for which we know the sales, calculate average sales per cluster, then map the new products to the nearest category.","metadata":{}},{"cell_type":"markdown","source":"#  1. 2. Group observations and aggregate monthly sales per shop-item combination  ","metadata":{}},{"cell_type":"code","source":"monthly_df = sales_train.groupby(['date_block_num','shop_id','item_id']).agg({'item_cnt_day':'sum'})\nmonthly_df.rename(columns={'item_cnt_day': 'item_cnt_month'}, inplace=True)\nmonthly_df = monthly_df.reset_index()\nmonthly_df.head(3)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:36:04.817933Z","iopub.execute_input":"2022-07-13T00:36:04.818233Z","iopub.status.idle":"2022-07-13T00:36:05.805169Z","shell.execute_reply.started":"2022-07-13T00:36:04.818202Z","shell.execute_reply":"2022-07-13T00:36:05.804393Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del sales_train","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:36:09.329408Z","iopub.execute_input":"2022-07-13T00:36:09.329706Z","iopub.status.idle":"2022-07-13T00:36:09.341679Z","shell.execute_reply.started":"2022-07-13T00:36:09.329676Z","shell.execute_reply":"2022-07-13T00:36:09.340599Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#  1. 3. Reduce memory & Free up Ram\n\nBefore starting to work, we run the 'reduce_mem_usage' function (Reference: [load data (reduce memory usage)](https://www.kaggle.com/code/gemartin/load-data-reduce-memory-usage)) on the sales dataset to save memory and free some RAM because the Kaggle notebook only gives 16GB of free RAM. You can skip it if you have no trouble implementing a large dataset.","metadata":{}},{"cell_type":"code","source":"def reduce_mem_usage(df):\n    \"\"\" iterate through all the columns of a dataframe and modify the data type\n        to reduce memory usage.        \n    \"\"\"\n    start_mem = df.memory_usage().sum() / 1024**2\n    print('Memory usage of dataframe is {:.2f} MB'.format(start_mem))\n    \n    for col in df.columns:\n        col_type = df[col].dtype\n        \n        if col_type != object:\n            c_min = df[col].min()\n            c_max = df[col].max()\n            if str(col_type)[:3] == 'int':\n                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n                    df[col] = df[col].astype(np.int8)\n                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n                    df[col] = df[col].astype(np.int16)\n                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n                    df[col] = df[col].astype(np.int32)\n                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n                    df[col] = df[col].astype(np.int64)  \n            else:\n                if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n                    df[col] = df[col].astype(np.float16)\n                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n                    df[col] = df[col].astype(np.float32)\n                else:\n                    df[col] = df[col].astype(np.float64)\n        else:\n            df[col] = df[col].astype('category')\n\n    end_mem = df.memory_usage().sum() / 1024**2\n    print('Memory usage after optimization is: {:.2f} MB'.format(end_mem))\n    print('Decreased by {:.1f}%'.format(100 * (start_mem - end_mem) / start_mem))\n    \n    return df","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:36:12.017926Z","iopub.execute_input":"2022-07-13T00:36:12.018213Z","iopub.status.idle":"2022-07-13T00:36:12.035105Z","shell.execute_reply.started":"2022-07-13T00:36:12.018183Z","shell.execute_reply":"2022-07-13T00:36:12.034398Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print('-' * 80)\nprint('monthly_df')\nmonthly_df = reduce_mem_usage(monthly_df)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:36:15.536317Z","iopub.execute_input":"2022-07-13T00:36:15.537204Z","iopub.status.idle":"2022-07-13T00:36:15.589715Z","shell.execute_reply.started":"2022-07-13T00:36:15.537158Z","shell.execute_reply":"2022-07-13T00:36:15.588822Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#  1. 4. Impute missing values of the monthly sales per shop-item combination\n\nThe target variable can be used for feature engineering when working on a time series problem. The previous sales per shop-item combination are a critical variable in prediction. If the present value is at time t, then past values are known as lags, so t-1 is lag 1, and t-2 is lag 2. However, not all shop-item combinations have sales every month in this dataset. Therefore, we need to impute the missing values and generate lag features for our series.","metadata":{}},{"cell_type":"code","source":"monthly_df[\"shop_item_id\"] = monthly_df[\"shop_id\"] * 100000  + monthly_df[\"item_id\"]\nmonth_size = monthly_df['date_block_num'].value_counts().size\nshop_item_id = pd.Series(monthly_df['shop_item_id'].unique())\nsize = shop_item_id.size\ndata = pd.DataFrame(columns = ['date_block_num', 'shop_item_id', 'item_cnt_month']) \nfor i in range(month_size):\n    # print(i)\n    impute_data = pd.DataFrame({'date_block_num':[i]*size, 'shop_item_id':shop_item_id, 'item_cnt_month': [0]*size})\n    sub_df = monthly_df.iloc[(monthly_df['date_block_num']==i).tolist()]\n    sub_df = sub_df.append(impute_data, ignore_index=True).drop_duplicates(subset=['date_block_num', 'shop_item_id'])\n    # print(sub_df.shape)\n    data = data.append(sub_df, ignore_index=True)\n    # print(data.shape)\n\ndel monthly_df","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:36:18.409175Z","iopub.execute_input":"2022-07-13T00:36:18.409708Z","iopub.status.idle":"2022-07-13T00:36:53.323262Z","shell.execute_reply.started":"2022-07-13T00:36:18.409662Z","shell.execute_reply":"2022-07-13T00:36:53.322158Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Check if the number of the rows is equal to unique id number size* month_size. \ndata.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:38:10.293551Z","iopub.execute_input":"2022-07-13T00:38:10.293873Z","iopub.status.idle":"2022-07-13T00:38:10.301873Z","shell.execute_reply.started":"2022-07-13T00:38:10.293840Z","shell.execute_reply":"2022-07-13T00:38:10.300988Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data['Date']=data['date_block_num'].apply(lambda x: ((x//12 + 2013)*100+(x % 12)+1))\ndata['Date']=pd.to_datetime(data['Date'],format='%Y%m')+ MonthEnd(1)\ndata['item_id']=data['shop_item_id'].apply(lambda x: x%100000)\ndata['shop_id']=data['shop_item_id'].apply(lambda x: x//100000)\ndata.drop(['date_block_num'], axis=1, inplace=True)\ndata.head(3)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:38:13.019782Z","iopub.execute_input":"2022-07-13T00:38:13.020653Z","iopub.status.idle":"2022-07-13T00:38:47.680385Z","shell.execute_reply.started":"2022-07-13T00:38:13.020607Z","shell.execute_reply":"2022-07-13T00:38:47.679420Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data_pivot = data.pivot('Date',\"shop_item_id\", \"item_cnt_month\")\ndata_pivot.head(3)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:38:54.661840Z","iopub.execute_input":"2022-07-13T00:38:54.662343Z","iopub.status.idle":"2022-07-13T00:39:06.399134Z","shell.execute_reply.started":"2022-07-13T00:38:54.662272Z","shell.execute_reply":"2022-07-13T00:39:06.398065Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#  1. 5. Visualize the top 10 shop-item combinations","metadata":{}},{"cell_type":"code","source":"# Sort according to the best sales shop_item_id\ndf_2id = data.groupby(['shop_item_id']).sum().sort_values(by='item_cnt_month',ascending=False)\n# list the top tenth best sold id\nbest_sold_id = list(df_2id.index[0:10])\nbest_sold_itemid=[abs(best_sold_id[i]%100000) for i in range(len(best_sold_id))]\nbest_sold_shopid=[int(best_sold_id[i]/100000) for i in range(len(best_sold_id))]\nprint('Best sold shop&item id:', best_sold_id, '\\nitem_id:', best_sold_itemid, '\\nshop_id:', best_sold_shopid)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:39:23.821736Z","iopub.execute_input":"2022-07-13T00:39:23.822497Z","iopub.status.idle":"2022-07-13T00:39:30.868968Z","shell.execute_reply.started":"2022-07-13T00:39:23.822449Z","shell.execute_reply":"2022-07-13T00:39:30.868031Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Select the top 10 sales ids\ndata_top10 = data_pivot[best_sold_id]\ndata_top10.head(3)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:39:34.460588Z","iopub.execute_input":"2022-07-13T00:39:34.461467Z","iopub.status.idle":"2022-07-13T00:39:34.486840Z","shell.execute_reply.started":"2022-07-13T00:39:34.461418Z","shell.execute_reply":"2022-07-13T00:39:34.485979Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"rcParams['figure.figsize'] = 12, 6\ndata_top10.plot()\nplt.legend(loc='upper left', fontsize=11)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:39:40.125569Z","iopub.execute_input":"2022-07-13T00:39:40.126068Z","iopub.status.idle":"2022-07-13T00:39:40.831114Z","shell.execute_reply.started":"2022-07-13T00:39:40.126016Z","shell.execute_reply":"2022-07-13T00:39:40.830427Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# The boxplot of the top 10 sales ids per year \nrcParams['figure.figsize'] = 20, 8\ndata_top10.groupby(Grouper(freq='A')).boxplot() # rot=45 xticks rotation 45\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:39:44.471132Z","iopub.execute_input":"2022-07-13T00:39:44.472387Z","iopub.status.idle":"2022-07-13T00:39:45.318690Z","shell.execute_reply.started":"2022-07-13T00:39:44.472307Z","shell.execute_reply":"2022-07-13T00:39:45.317638Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We observe that the monthly sales between different shop-item combinations are very diverse. It is very common for retailers, especially in different departments, such as the sales of milk might be ten thousand times the sales of TV in Walmart. The sales of some shop-item combinnations have obviously seasonly tread.  ","metadata":{}},{"cell_type":"code","source":"del data_pivot\ndel data_top10","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:39:49.539248Z","iopub.execute_input":"2022-07-13T00:39:49.539574Z","iopub.status.idle":"2022-07-13T00:39:49.544606Z","shell.execute_reply.started":"2022-07-13T00:39:49.539540Z","shell.execute_reply":"2022-07-13T00:39:49.543388Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#  1.6. Visualize the best sales item and the worst sales item","metadata":{}},{"cell_type":"code","source":"# Sort according to the best sold item_id\ndf_item = data.groupby(['item_id']).agg({'item_cnt_month':'sum'}).sort_values(by='item_cnt_month',ascending=False)\nitem_t1th = data.loc[data['item_id']==df_item.index[0]]\nitem_l1th = data.loc[data['item_id']==df_item.index[-1]]","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:39:52.402922Z","iopub.execute_input":"2022-07-13T00:39:52.403799Z","iopub.status.idle":"2022-07-13T00:39:52.902685Z","shell.execute_reply.started":"2022-07-13T00:39:52.403742Z","shell.execute_reply":"2022-07-13T00:39:52.901930Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = go.Figure()\nfig.add_trace(go.Box(x=np.array(item_t1th['item_cnt_month']),name=f'The best item id:{df_item.index[0]}'))\nfig.add_trace(go.Box(x=np.array(item_l1th['item_cnt_month']),name=f'The worst item id:{df_item.index[-1]}'))\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:39:56.956981Z","iopub.execute_input":"2022-07-13T00:39:56.957602Z","iopub.status.idle":"2022-07-13T00:39:57.048866Z","shell.execute_reply.started":"2022-07-13T00:39:56.957560Z","shell.execute_reply":"2022-07-13T00:39:57.047671Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del df_item","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:40:01.189357Z","iopub.execute_input":"2022-07-13T00:40:01.189693Z","iopub.status.idle":"2022-07-13T00:40:01.193927Z","shell.execute_reply.started":"2022-07-13T00:40:01.189646Z","shell.execute_reply":"2022-07-13T00:40:01.193065Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#  1. 7. Statistics summary per shop-item combination","metadata":{}},{"cell_type":"code","source":"data_summary = data.groupby(['shop_item_id']).agg({'item_cnt_month': ['sum', 'mean', 'median', 'std']})\ndata_summary.head(3)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:40:06.293344Z","iopub.execute_input":"2022-07-13T00:40:06.294031Z","iopub.status.idle":"2022-07-13T00:40:15.081261Z","shell.execute_reply.started":"2022-07-13T00:40:06.293993Z","shell.execute_reply":"2022-07-13T00:40:15.080303Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del data_summary","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:40:18.589362Z","iopub.execute_input":"2022-07-13T00:40:18.589755Z","iopub.status.idle":"2022-07-13T00:40:18.594801Z","shell.execute_reply.started":"2022-07-13T00:40:18.589723Z","shell.execute_reply":"2022-07-13T00:40:18.593990Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 2. Feature Engineer\n\nReference :\n['6 Powerful Feature Engineering Techniques For Time Series Data (using Python)'](https://www.analyticsvidhya.com/blog/2019/12/6-powerful-feature-engineering-techniques-time-series/#h2_11)\n\n# 2. 1. Lag Features","metadata":{}},{"cell_type":"code","source":"grouped = data.groupby('shop_item_id')['item_cnt_month']\ndata['lag_1'] = grouped.transform(lambda x : x.shift(1))  \ndata['rmean_3'] = grouped.transform(lambda x : x.shift(2).rolling(3).mean())\ndata.dropna(inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:40:23.146493Z","iopub.execute_input":"2022-07-13T00:40:23.147305Z","iopub.status.idle":"2022-07-13T00:45:27.482226Z","shell.execute_reply.started":"2022-07-13T00:40:23.147250Z","shell.execute_reply":"2022-07-13T00:45:27.480812Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 2. 2. Date-Related Features","metadata":{}},{"cell_type":"code","source":"data['Month'] = data['Date'].dt.month.astype('int16') \ndata['Quarter'] = data['Date'].dt.quarter.astype('int16') \ndata['Year'] = data['Date'].dt.year.astype('int16')\ndata.head(1)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:45:45.943308Z","iopub.execute_input":"2022-07-13T00:45:45.943652Z","iopub.status.idle":"2022-07-13T00:45:49.749304Z","shell.execute_reply.started":"2022-07-13T00:45:45.943615Z","shell.execute_reply":"2022-07-13T00:45:49.748351Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 2. 3. encode categorical features","metadata":{}},{"cell_type":"code","source":"cat_feats= ['item_id','shop_id','Year','Quarter','Month']\nfor i in cat_feats:\n    cat_encoder = LabelEncoder()\n    data[i] = cat_encoder.fit_transform(data[i])","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:45:53.709483Z","iopub.execute_input":"2022-07-13T00:45:53.709800Z","iopub.status.idle":"2022-07-13T00:45:59.246134Z","shell.execute_reply.started":"2022-07-13T00:45:53.709762Z","shell.execute_reply":"2022-07-13T00:45:59.244303Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3. LightGBM forecast ","metadata":{}},{"cell_type":"markdown","source":"# 3. 1. Training & validation","metadata":{}},{"cell_type":"code","source":"from dateutil.relativedelta import relativedelta\ncutoff = data.Date.max() - relativedelta(months=3)\nxtrain = data.loc[data.Date < cutoff].copy()\nxvalid = data.loc[data.Date >= cutoff].copy()","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:46:04.212255Z","iopub.execute_input":"2022-07-13T00:46:04.212667Z","iopub.status.idle":"2022-07-13T00:46:07.151391Z","shell.execute_reply.started":"2022-07-13T00:46:04.212629Z","shell.execute_reply":"2022-07-13T00:46:07.150060Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ytrain = xtrain['item_cnt_month']\nyvalid = xvalid['item_cnt_month']\n\nxtrain.drop(['Date', 'item_cnt_month','shop_item_id'], axis = 1, inplace = True)\nxvalid.drop(['Date', 'item_cnt_month','shop_item_id'], axis = 1, inplace = True)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:46:13.461813Z","iopub.execute_input":"2022-07-13T00:46:13.462122Z","iopub.status.idle":"2022-07-13T00:46:13.894816Z","shell.execute_reply.started":"2022-07-13T00:46:13.462091Z","shell.execute_reply":"2022-07-13T00:46:13.893484Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dtrain = lgb.Dataset(xtrain , label = ytrain,  free_raw_data=False)\ndvalid = lgb.Dataset(xvalid, label = yvalid,   free_raw_data=False)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:46:18.886589Z","iopub.execute_input":"2022-07-13T00:46:18.887196Z","iopub.status.idle":"2022-07-13T00:46:18.893753Z","shell.execute_reply.started":"2022-07-13T00:46:18.887147Z","shell.execute_reply":"2022-07-13T00:46:18.892197Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"lgb_params = {'objective':'regression',\n              'metric': 'rmse',\n              'boosting':'goss', # gradient-based one-side sampling\n              'num_leaves': 12,\n              'learning_rate': 0.02,\n              'feature_fraction': 0.8,  # used to speed up training and deal with over-fitting, (0,1]\n              'max_depth': 5,   # used to deal with over-fitting when #data is small\n              'verbosity': 1,\n              'force_row_wise':True,\n              'early_stopping_rounds': 100, #will stop training if one metric of one validation data doesn't improve in last # rounds\n             }\n\n\nmodel = lgb.train(lgb_params, dtrain, valid_sets = [dtrain, dvalid], num_boost_round=1600, verbose_eval=100) ","metadata":{"execution":{"iopub.status.busy":"2022-07-13T00:57:03.834436Z","iopub.execute_input":"2022-07-13T00:57:03.834758Z","iopub.status.idle":"2022-07-13T01:06:58.870622Z","shell.execute_reply.started":"2022-07-13T00:57:03.834726Z","shell.execute_reply":"2022-07-13T01:06:58.869793Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3. 2. Plot important features","metadata":{}},{"cell_type":"code","source":"def plot_lgb_importances(model,plot=True,num=10):\n    gain = model.feature_importance('gain')\n    feat_imp = pd.DataFrame({'feature': model.feature_name(),\n                             'split': model.feature_importance('split'),\n                             'gain': 100 * gain / gain.sum()}).sort_values('gain', ascending=False)\n    if plot:\n        plt.figure(figsize=(10, 4))\n        sns.set(font_scale=1)\n        sns.barplot(x=\"gain\", y=\"feature\", data=feat_imp[0:25])\n        plt.title('feature')\n        plt.tight_layout()\n        plt.show()\n    else:\n        print(feat_imp.head(num))\n    print(feat_imp.head(num))","metadata":{"execution":{"iopub.status.busy":"2022-07-13T01:07:05.524429Z","iopub.execute_input":"2022-07-13T01:07:05.525008Z","iopub.status.idle":"2022-07-13T01:07:05.533960Z","shell.execute_reply.started":"2022-07-13T01:07:05.524959Z","shell.execute_reply":"2022-07-13T01:07:05.533284Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_lgb_importances(model,7)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T01:07:12.843701Z","iopub.execute_input":"2022-07-13T01:07:12.844302Z","iopub.status.idle":"2022-07-13T01:07:13.211637Z","shell.execute_reply.started":"2022-07-13T01:07:12.844262Z","shell.execute_reply":"2022-07-13T01:07:13.210731Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3. 3. Forecast","metadata":{}},{"cell_type":"code","source":"print('-' * 80)\nprint('test')\ntest = reduce_mem_usage(test)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T01:07:16.379058Z","iopub.execute_input":"2022-07-13T01:07:16.379706Z","iopub.status.idle":"2022-07-13T01:07:16.419909Z","shell.execute_reply.started":"2022-07-13T01:07:16.379656Z","shell.execute_reply":"2022-07-13T01:07:16.419030Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Scenario 1:** \n\nShop-item combinations in the testing set and the training set are the same. We can directly use the identical shop-item combination last month, and the monthly sales in last month are the lag1 of the testing set.","metadata":{}},{"cell_type":"code","source":"test1 = data.loc[(data['shop_item_id'].isin(test['shop_item_id']))&(data.Date == data.Date.max()),\n                ['shop_item_id','shop_id','item_id','item_cnt_month','Month','Quarter','Year']] \ntest1.rename(columns={'item_cnt_month':'lag1'}, inplace=True)\ntest1['Month']+=1","metadata":{"execution":{"iopub.status.busy":"2022-07-13T01:07:21.594977Z","iopub.execute_input":"2022-07-13T01:07:21.597879Z","iopub.status.idle":"2022-07-13T01:07:23.901432Z","shell.execute_reply.started":"2022-07-13T01:07:21.597827Z","shell.execute_reply":"2022-07-13T01:07:23.900437Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate rolling mean for last three month\ncutoff2 = data.Date.max() - relativedelta(months=3)\nL3months = data.loc[(data['shop_item_id'].isin(test['shop_item_id']))& (data.Date > cutoff2)]\nrmean_3 = L3months.groupby(['shop_item_id']).agg({'item_cnt_month': 'mean'})\nrmean_3.reset_index(inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T01:07:29.605514Z","iopub.execute_input":"2022-07-13T01:07:29.606461Z","iopub.status.idle":"2022-07-13T01:07:32.501219Z","shell.execute_reply.started":"2022-07-13T01:07:29.606409Z","shell.execute_reply":"2022-07-13T01:07:32.500167Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test1=test1.join(rmean_3.set_index('shop_item_id'), on='shop_item_id')\ntest1.rename(columns={'item_cnt_month':'rmean_3'}, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T01:07:35.868198Z","iopub.execute_input":"2022-07-13T01:07:35.868941Z","iopub.status.idle":"2022-07-13T01:07:35.973147Z","shell.execute_reply.started":"2022-07-13T01:07:35.868895Z","shell.execute_reply":"2022-07-13T01:07:35.972248Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test1.drop(['shop_item_id'], axis = 1, inplace = True)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T01:07:43.275746Z","iopub.execute_input":"2022-07-13T01:07:43.276044Z","iopub.status.idle":"2022-07-13T01:07:43.287733Z","shell.execute_reply.started":"2022-07-13T01:07:43.276012Z","shell.execute_reply":"2022-07-13T01:07:43.287015Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"lgb_params = {'objective':'regression',\n              'metric': 'rmse',\n              'boosting':'goss', # gradient-based one-side sampling\n              'num_leaves': 12,\n              'learning_rate': 0.02,\n              'feature_fraction': 0.8,\n              'max_depth': 5,\n              'force_row_wise':True,\n              'verbosity': 1}","metadata":{"execution":{"iopub.status.busy":"2022-07-13T01:07:47.284352Z","iopub.execute_input":"2022-07-13T01:07:47.284631Z","iopub.status.idle":"2022-07-13T01:07:47.289594Z","shell.execute_reply.started":"2022-07-13T01:07:47.284602Z","shell.execute_reply":"2022-07-13T01:07:47.288878Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Final model, train total data and predict\nX_train = data.drop(['Date', 'item_cnt_month','shop_item_id'], axis = 1)\ny_train = data['item_cnt_month']\nlgbtrain_all = lgb.Dataset(data=X_train, label=y_train)\nfinal_model = lgb.train(lgb_params, lgbtrain_all, num_boost_round=model.best_iteration)\ntest_preds1 = final_model.predict(test1, num_iteration=model.best_iteration)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T01:07:53.484252Z","iopub.execute_input":"2022-07-13T01:07:53.485051Z","iopub.status.idle":"2022-07-13T01:17:53.695392Z","shell.execute_reply.started":"2022-07-13T01:07:53.485011Z","shell.execute_reply":"2022-07-13T01:17:53.694390Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_pre1=pd.DataFrame({'shop_id':test1['shop_id'].values, 'item_id': test1['item_id'].values,'item_cnt_month':test_preds1})\ntest_pre1.head(5)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T01:18:28.164971Z","iopub.execute_input":"2022-07-13T01:18:28.165475Z","iopub.status.idle":"2022-07-13T01:18:28.182614Z","shell.execute_reply.started":"2022-07-13T01:18:28.165414Z","shell.execute_reply":"2022-07-13T01:18:28.181410Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Scenario 2:** \n\nShop-item combinations in the testing set are not identical to those in the training set, but items are the same. So lag features generated only depend on monthly sales of items. ","metadata":{}},{"cell_type":"code","source":"test2 = test[(test['item_id'].isin(data['item_id']))&(~test['shop_item_id'].isin(data['shop_item_id']))]\n# Calculate lag1\nl1month_sales = data.loc[data.Date == data.Date.max()]\nlag1 = l1month_sales.groupby(['item_id']).agg({'item_cnt_month': 'mean'})\nlag1.reset_index(inplace=True)\nlag1.rename(columns={'item_cnt_month':'lag1'}, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T01:18:31.207798Z","iopub.execute_input":"2022-07-13T01:18:31.208086Z","iopub.status.idle":"2022-07-13T01:18:33.121526Z","shell.execute_reply.started":"2022-07-13T01:18:31.208056Z","shell.execute_reply":"2022-07-13T01:18:33.120657Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate rolling mean for last three month\ncutoff2 = data.Date.max() - relativedelta(months=3)\nL3months = data.loc[data.Date > cutoff2]\nrmean_3 = L3months.groupby(['item_id']).agg({'item_cnt_month': 'mean'})\nrmean_3.reset_index(inplace=True)\nrmean_3.rename(columns={'item_cnt_month':'rmean_3'}, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T01:18:35.799724Z","iopub.execute_input":"2022-07-13T01:18:35.800362Z","iopub.status.idle":"2022-07-13T01:18:36.115860Z","shell.execute_reply.started":"2022-07-13T01:18:35.800293Z","shell.execute_reply":"2022-07-13T01:18:36.114772Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test2 = test2.merge(lag1, how='left', on='item_id')\ntest2 = test2.merge(rmean_3, how='left', on='item_id')\ntest2['Month']=[11]*test2.shape[0]\ntest2['Quarter']=[4]*test2.shape[0]\ntest2['Year']=[2015]*test2.shape[0]\ntest2.drop(['ID','shop_item_id'], axis = 1, inplace = True)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T01:18:39.690367Z","iopub.execute_input":"2022-07-13T01:18:39.690706Z","iopub.status.idle":"2022-07-13T01:18:39.863234Z","shell.execute_reply.started":"2022-07-13T01:18:39.690674Z","shell.execute_reply":"2022-07-13T01:18:39.862392Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_preds2 = final_model.predict(test2, num_iteration=model.best_iteration)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T01:18:43.463929Z","iopub.execute_input":"2022-07-13T01:18:43.464541Z","iopub.status.idle":"2022-07-13T01:18:45.300175Z","shell.execute_reply.started":"2022-07-13T01:18:43.464502Z","shell.execute_reply":"2022-07-13T01:18:45.299462Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_pre2=pd.DataFrame({'shop_id':test2['shop_id'].values, 'item_id': test2['item_id'].values,'item_cnt_month':test_preds2})\ntest_pre2.head(5)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T01:18:48.302909Z","iopub.execute_input":"2022-07-13T01:18:48.303213Z","iopub.status.idle":"2022-07-13T01:18:48.317637Z","shell.execute_reply.started":"2022-07-13T01:18:48.303182Z","shell.execute_reply":"2022-07-13T01:18:48.316721Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test = test.merge(test_pre1, how='left', on=['shop_id','item_id'])\ntest = test.merge(test_pre2, how='left', on=['shop_id','item_id'])\ntest['item_cnt_month'] = test['item_cnt_month_x'].fillna(test['item_cnt_month_y'])\nsubmission_df = test.drop(['shop_id','item_id','item_cnt_month_x','item_cnt_month_y'], axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T01:23:32.244521Z","iopub.execute_input":"2022-07-13T01:23:32.244842Z","iopub.status.idle":"2022-07-13T01:23:32.358194Z","shell.execute_reply.started":"2022-07-13T01:23:32.244808Z","shell.execute_reply":"2022-07-13T01:23:32.357419Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Scenario 3:** \n\nWe need to talk with other departments and obtain the basic information about new products, and then use the hybrid method as we discuss at the beginning to predict the sales. ","metadata":{}},{"cell_type":"code","source":"submission=submission_df.fillna(0)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T03:13:55.506820Z","iopub.execute_input":"2022-07-13T03:13:55.507665Z","iopub.status.idle":"2022-07-13T03:13:55.518656Z","shell.execute_reply.started":"2022-07-13T03:13:55.507624Z","shell.execute_reply":"2022-07-13T03:13:55.517665Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission.to_csv('submission.csv', index=False)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T01:34:04.151051Z","iopub.execute_input":"2022-07-13T01:34:04.151739Z","iopub.status.idle":"2022-07-13T01:34:04.765180Z","shell.execute_reply.started":"2022-07-13T01:34:04.151698Z","shell.execute_reply":"2022-07-13T01:34:04.764166Z"},"trusted":true},"execution_count":null,"outputs":[]}]}