{"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":"# Predict Future Sales Competition\n## Top 3.5% Solution with Feature Engineering\n\n- [Competition Link](https://www.kaggle.com/c/competitive-data-science-predict-future-sales)\n- [Reference modeling link](https://www.kaggle.com/dkomyagin/predict-future-sales-lightgbm-framework)","metadata":{}},{"cell_type":"markdown","source":"## This is Top 3.5% modeling code with feature engineering. I made a total of 30 featrues. If this notebook is helpful, please upvote my code!! ๐Ÿ‘€ ๐Ÿ‘","metadata":{}},{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport warnings\n\nwarnings.filterwarnings(action='ignore') # Ignore warning message\n\n# date path\ndata_path = '/kaggle/input/competitive-data-science-predict-future-sales/'\n\nsales_train = pd.read_csv(data_path + 'sales_train.csv')\nshops = pd.read_csv(data_path + 'shops.csv')\nitems = pd.read_csv(data_path + 'items.csv')\nitem_categories = pd.read_csv(data_path + 'item_categories.csv')\ntest = pd.read_csv(data_path + 'test.csv')\nsubmission = pd.read_csv(data_path + 'sample_submission.csv')","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:29.418983Z","iopub.execute_input":"2022-08-13T15:16:29.419381Z","iopub.status.idle":"2022-08-13T15:16:32.378746Z","shell.execute_reply.started":"2022-08-13T15:16:29.419297Z","shell.execute_reply":"2022-08-13T15:16:32.377964Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Data Downcasting","metadata":{}},{"cell_type":"code","source":"def downcast(df, verbose=True):\n start_mem = df.memory_usage().sum() / 1024**2\n for col in df.columns:\n dtype_name = df[col].dtype.name\n if dtype_name == 'object':\n pass\n elif dtype_name == 'bool':\n df[col] = df[col].astype('int8')\n elif dtype_name.startswith('int') or (df[col].round() == df[col]).all():\n df[col] = pd.to_numeric(df[col], downcast='integer')\n else:\n df[col] = pd.to_numeric(df[col], downcast='float')\n end_mem = df.memory_usage().sum() / 1024**2\n if verbose:\n print('{:.1f}% compressed'.format(100 * (start_mem - end_mem) / start_mem))\n \n return df\n\nall_df = [sales_train, shops, items, item_categories, test]\nfor df in all_df:\n df = downcast(df)","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:32.382479Z","iopub.execute_input":"2022-08-13T15:16:32.382800Z","iopub.status.idle":"2022-08-13T15:16:32.745818Z","shell.execute_reply.started":"2022-08-13T15:16:32.382773Z","shell.execute_reply":"2022-08-13T15:16:32.745017Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Feature Engineering I - handling sales_train, shops, items, item_categories","metadata":{}},{"cell_type":"markdown","source":"### sales_train: remove outliers and preprocess","metadata":{}},{"cell_type":"code","source":"# Extract data with a item_price greater than 0\nsales_train = sales_train[sales_train['item_price'] > 0]\n# Extract data with a item_priceof less than 50,000\nsales_train = sales_train[sales_train['item_price'] < 50000]\n# Extract data with item_cnt_day greater than 0\nsales_train = sales_train[sales_train['item_cnt_day'] > 0]\n# Extract data with item_cnt_day less than 1,000\nsales_train = sales_train[sales_train['item_cnt_day'] < 1000]","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:32.747272Z","iopub.execute_input":"2022-08-13T15:16:32.747570Z","iopub.status.idle":"2022-08-13T15:16:33.335950Z","shell.execute_reply.started":"2022-08-13T15:16:32.747540Z","shell.execute_reply":"2022-08-13T15:16:33.335053Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(shops['shop_name'][0], '||', shops['shop_name'][57])\nprint(shops['shop_name'][1], '||', shops['shop_name'][58])\nprint(shops['shop_name'][10], '||', shops['shop_name'][11])\nprint(shops['shop_name'][39], '||', shops['shop_name'][40])","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:33.337139Z","iopub.execute_input":"2022-08-13T15:16:33.337408Z","iopub.status.idle":"2022-08-13T15:16:33.344580Z","shell.execute_reply.started":"2022-08-13T15:16:33.337382Z","shell.execute_reply":"2022-08-13T15:16:33.343741Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Modify shop_id in sales_train data\nsales_train.loc[sales_train['shop_id'] == 0, 'shop_id'] = 57\nsales_train.loc[sales_train['shop_id'] == 1, 'shop_id'] = 58\nsales_train.loc[sales_train['shop_id'] == 10, 'shop_id'] = 11\nsales_train.loc[sales_train['shop_id'] == 39, 'shop_id'] = 40\n\n# Modify shop_id in test data\ntest.loc[test['shop_id'] == 0, 'shop_id'] = 57\ntest.loc[test['shop_id'] == 1, 'shop_id'] = 58\ntest.loc[test['shop_id'] == 10, 'shop_id'] = 11\ntest.loc[test['shop_id'] == 39, 'shop_id'] = 40","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:33.349144Z","iopub.execute_input":"2022-08-13T15:16:33.349415Z","iopub.status.idle":"2022-08-13T15:16:33.374858Z","shell.execute_reply.started":"2022-08-13T15:16:33.349390Z","shell.execute_reply":"2022-08-13T15:16:33.374173Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Leaking to imporve performance\nunique_test_shop_id = test['shop_id'].unique()\nsales_train = sales_train[sales_train['shop_id'].isin(unique_test_shop_id)]","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:33.376844Z","iopub.execute_input":"2022-08-13T15:16:33.377226Z","iopub.status.idle":"2022-08-13T15:16:33.504927Z","shell.execute_reply.started":"2022-08-13T15:16:33.377195Z","shell.execute_reply":"2022-08-13T15:16:33.503997Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Shops: create derived features and encode","metadata":{}},{"cell_type":"code","source":"shops['city'] = shops['shop_name'].apply(lambda x: x.split()[0])","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:33.506104Z","iopub.execute_input":"2022-08-13T15:16:33.506370Z","iopub.status.idle":"2022-08-13T15:16:33.512577Z","shell.execute_reply.started":"2022-08-13T15:16:33.506344Z","shell.execute_reply":"2022-08-13T15:16:33.510883Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"shops['city'].unique()","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:33.514018Z","iopub.execute_input":"2022-08-13T15:16:33.514673Z","iopub.status.idle":"2022-08-13T15:16:33.529455Z","shell.execute_reply.started":"2022-08-13T15:16:33.514635Z","shell.execute_reply":"2022-08-13T15:16:33.528824Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"shops.loc[shops['city'] =='!ะฏะบัƒั‚ัะบ', 'city'] = 'ะฏะบัƒั‚ัะบ'","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:33.530774Z","iopub.execute_input":"2022-08-13T15:16:33.531125Z","iopub.status.idle":"2022-08-13T15:16:33.540364Z","shell.execute_reply.started":"2022-08-13T15:16:33.531097Z","shell.execute_reply":"2022-08-13T15:16:33.539677Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from sklearn.preprocessing import LabelEncoder\n\n# Create Label Encoder\nlabel_encoder = LabelEncoder()\n# City Feature Label Encoding \nshops['city'] = label_encoder.fit_transform(shops['city'])","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:33.542588Z","iopub.execute_input":"2022-08-13T15:16:33.543118Z","iopub.status.idle":"2022-08-13T15:16:34.416384Z","shell.execute_reply.started":"2022-08-13T15:16:33.543078Z","shell.execute_reply":"2022-08-13T15:16:34.415287Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Remove shop_name feature\nshops = shops.drop('shop_name', axis=1)\n\nshops.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:34.417823Z","iopub.execute_input":"2022-08-13T15:16:34.418111Z","iopub.status.idle":"2022-08-13T15:16:34.431657Z","shell.execute_reply.started":"2022-08-13T15:16:34.418084Z","shell.execute_reply":"2022-08-13T15:16:34.430644Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### itmes: Create derived features","metadata":{}},{"cell_type":"code","source":"# Remove item_name feature\nitems = items.drop(['item_name'], axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:34.433060Z","iopub.execute_input":"2022-08-13T15:16:34.433311Z","iopub.status.idle":"2022-08-13T15:16:34.438789Z","shell.execute_reply.started":"2022-08-13T15:16:34.433287Z","shell.execute_reply":"2022-08-13T15:16:34.438001Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create the date the product was first sold as a feature\nitems['first_sale_date'] = sales_train.groupby('item_id').agg({'date_block_num': 'min'})['date_block_num']\n\nitems.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:34.439912Z","iopub.execute_input":"2022-08-13T15:16:34.440179Z","iopub.status.idle":"2022-08-13T15:16:34.545034Z","shell.execute_reply.started":"2022-08-13T15:16:34.440154Z","shell.execute_reply":"2022-08-13T15:16:34.543984Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"items[items['first_sale_date'].isna()]","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:34.546771Z","iopub.execute_input":"2022-08-13T15:16:34.547150Z","iopub.status.idle":"2022-08-13T15:16:34.564270Z","shell.execute_reply.started":"2022-08-13T15:16:34.547101Z","shell.execute_reply":"2022-08-13T15:16:34.563305Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Replace NaN of first_sale_date with 34\nitems['first_sale_date'] = items['first_sale_date'].fillna(34)","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:34.566015Z","iopub.execute_input":"2022-08-13T15:16:34.566645Z","iopub.status.idle":"2022-08-13T15:16:34.573931Z","shell.execute_reply.started":"2022-08-13T15:16:34.566608Z","shell.execute_reply":"2022-08-13T15:16:34.573286Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Create item_categories derived feature and encode","metadata":{}},{"cell_type":"code","source":"# Extract the first word of the item_categories_name into category\nitem_categories['category'] = item_categories['item_category_name'].apply(lambda x: x.split()[0]) ","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:34.574934Z","iopub.execute_input":"2022-08-13T15:16:34.575355Z","iopub.status.idle":"2022-08-13T15:16:34.586944Z","shell.execute_reply.started":"2022-08-13T15:16:34.575316Z","shell.execute_reply":"2022-08-13T15:16:34.586340Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"item_categories['category'].value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:34.587933Z","iopub.execute_input":"2022-08-13T15:16:34.588350Z","iopub.status.idle":"2022-08-13T15:16:34.602862Z","shell.execute_reply.started":"2022-08-13T15:16:34.588309Z","shell.execute_reply":"2022-08-13T15:16:34.602000Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def make_etc(x):\n if len(item_categories[item_categories['category']==x]) >= 5:\n return x\n else:\n return 'etc'\n\n# Replace with 'etc' if category count is less than 5\nitem_categories['category'] = item_categories['category'].apply(make_etc)","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:34.604247Z","iopub.execute_input":"2022-08-13T15:16:34.604672Z","iopub.status.idle":"2022-08-13T15:16:34.651645Z","shell.execute_reply.started":"2022-08-13T15:16:34.604626Z","shell.execute_reply":"2022-08-13T15:16:34.650650Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"item_categories.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:34.652721Z","iopub.execute_input":"2022-08-13T15:16:34.653002Z","iopub.status.idle":"2022-08-13T15:16:34.671610Z","shell.execute_reply.started":"2022-08-13T15:16:34.652975Z","shell.execute_reply":"2022-08-13T15:16:34.670925Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create Label Encoder\nlabel_encoder = LabelEncoder()\n# Category Feature Label Encoding \nitem_categories['category'] = label_encoder.fit_transform(item_categories['category'])\n\n# Remove item_category_name feature\nitem_categories = item_categories.drop('item_category_name', axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:34.673411Z","iopub.execute_input":"2022-08-13T15:16:34.673832Z","iopub.status.idle":"2022-08-13T15:16:34.687410Z","shell.execute_reply.started":"2022-08-13T15:16:34.673794Z","shell.execute_reply":"2022-08-13T15:16:34.686556Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Generate data combinations and derived features","metadata":{}},{"cell_type":"code","source":"from itertools import product\n\ntrain = []\n# Create date_block_num, sop_id, item_id combination\nfor i in sales_train['date_block_num'].unique():\n all_shop = sales_train.loc[sales_train['date_block_num']==i, 'shop_id'].unique()\n all_item = sales_train.loc[sales_train['date_block_num']==i, 'item_id'].unique()\n train.append(np.array(list(product([i], all_shop, all_item))))\n\nidx_features = ['date_block_num', 'shop_id', 'item_id'] # base features\ntrain = pd.DataFrame(np.vstack(train), columns=idx_features)","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:34.690556Z","iopub.execute_input":"2022-08-13T15:16:34.691026Z","iopub.status.idle":"2022-08-13T15:16:44.412054Z","shell.execute_reply.started":"2022-08-13T15:16:34.690996Z","shell.execute_reply":"2022-08-13T15:16:44.411242Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"group = sales_train.groupby(idx_features).agg({'item_cnt_day': 'sum',\n 'item_price': 'mean'})\ngroup = group.reset_index()\ngroup = group.rename(columns={'item_cnt_day': 'item_cnt_month', 'item_price': 'item_price_mean'})\n\ntrain = train.merge(group, on=idx_features, how='left')\n\ntrain.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:44.413094Z","iopub.execute_input":"2022-08-13T15:16:44.413387Z","iopub.status.idle":"2022-08-13T15:16:48.878565Z","shell.execute_reply.started":"2022-08-13T15:16:44.413342Z","shell.execute_reply":"2022-08-13T15:16:48.877695Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import gc\n\n# group variable garbage collection\ndel group\ngc.collect();","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:48.879732Z","iopub.execute_input":"2022-08-13T15:16:48.879987Z","iopub.status.idle":"2022-08-13T15:16:48.960181Z","shell.execute_reply.started":"2022-08-13T15:16:48.879962Z","shell.execute_reply":"2022-08-13T15:16:48.958904Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Add a feature for the number of items sold\ngroup = sales_train.groupby(idx_features).agg({'item_cnt_day': 'count'})\ngroup = group.reset_index()\ngroup = group.rename(columns={'item_cnt_day': 'item_count'})\n\ntrain = train.merge(group, on=idx_features, how='left')\n\n# Garbage collection\ndel group, sales_train\ngc.collect()\n\ntrain.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:48.961747Z","iopub.execute_input":"2022-08-13T15:16:48.962196Z","iopub.status.idle":"2022-08-13T15:16:53.231219Z","shell.execute_reply.started":"2022-08-13T15:16:48.962154Z","shell.execute_reply":"2022-08-13T15:16:53.230299Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Concatenate test data, Merge remaining data","metadata":{}},{"cell_type":"code","source":"# Set test data date_block_num to 34\ntest['date_block_num'] = 34\n\n# Concatenate train and test\nall_data = pd.concat([train, test.drop('ID', axis=1)],\n ignore_index=True,\n keys=idx_features)\n# Replace NaN with 0\nall_data = all_data.fillna(0)\n\nall_data.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:53.232910Z","iopub.execute_input":"2022-08-13T15:16:53.233308Z","iopub.status.idle":"2022-08-13T15:16:53.798604Z","shell.execute_reply.started":"2022-08-13T15:16:53.233267Z","shell.execute_reply":"2022-08-13T15:16:53.797574Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Merge other data\nall_data = all_data.merge(shops, on='shop_id', how='left')\nall_data = all_data.merge(items, on='item_id', how='left')\nall_data = all_data.merge(item_categories, on='item_category_id', how='left')\n\n# Data downcasting\nall_data = downcast(all_data)\n\n# Garbage collection\ndel shops, items, item_categories\ngc.collect();","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:53.799768Z","iopub.execute_input":"2022-08-13T15:16:53.800059Z","iopub.status.idle":"2022-08-13T15:16:59.492231Z","shell.execute_reply.started":"2022-08-13T15:16:53.800030Z","shell.execute_reply":"2022-08-13T15:16:59.491326Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Feature summary","metadata":{}},{"cell_type":"code","source":"def resumetable(df):\n print(f'Data Shape: {df.shape}')\n summary = pd.DataFrame(df.dtypes, columns=['Dtypes'])\n summary['Null'] = df.isnull().sum().values\n summary['Uniques'] = df.nunique().values\n summary['First_values'] = df.loc[0].values\n summary['Second_values'] = df.loc[1].values\n summary['Third_values'] = df.loc[2].values\n \n return summary","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:59.493609Z","iopub.execute_input":"2022-08-13T15:16:59.494147Z","iopub.status.idle":"2022-08-13T15:16:59.499318Z","shell.execute_reply.started":"2022-08-13T15:16:59.494110Z","shell.execute_reply":"2022-08-13T15:16:59.498339Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"resumetable(all_data)","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:16:59.500407Z","iopub.execute_input":"2022-08-13T15:16:59.500696Z","iopub.status.idle":"2022-08-13T15:17:00.896500Z","shell.execute_reply.started":"2022-08-13T15:16:59.500670Z","shell.execute_reply":"2022-08-13T15:17:00.895541Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Visualization","metadata":{}},{"cell_type":"code","source":"import seaborn as sns\nimport matplotlib as mpl\nimport matplotlib.pyplot as plt\n%matplotlib inline\n\nmpl.rc('font', size=13)\nfigure, ax = plt.subplots() \nfigure.set_size_inches(11, 5)\n\n# total montly item sales\ngroup_month_sum = all_data.groupby('date_block_num').agg({'item_cnt_month': 'sum'})\ngroup_month_sum = group_month_sum.reset_index()\n\nsns.barplot(x='date_block_num', y='item_cnt_month', data=group_month_sum)\nax.set(title='Distribution of monthly item counts by date block number',\n xlabel='Date block number', \n ylabel='Monthly item counts');","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:17:00.897701Z","iopub.execute_input":"2022-08-13T15:17:00.897943Z","iopub.status.idle":"2022-08-13T15:17:01.605548Z","shell.execute_reply.started":"2022-08-13T15:17:00.897918Z","shell.execute_reply":"2022-08-13T15:17:01.604867Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"figure, ax= plt.subplots() \nfigure.set_size_inches(11, 5)\n\n# Total item sales by item_category_id\ngroup_cat_sum = all_data.groupby('item_category_id').agg({'item_cnt_month': 'sum'})\ngroup_cat_sum = group_cat_sum.reset_index()\n\n# Extract only item categories with total sales > 10,000\ngroup_cat_sum = group_cat_sum[group_cat_sum['item_cnt_month'] > 10000]\n\nsns.barplot(x='item_category_id', y='item_cnt_month', data=group_cat_sum)\nax.set(title='Distribution of total item counts by item category id',\n xlabel='Item category ID', \n ylabel='Total item counts')\nax.tick_params(axis='x', labelrotation=90) # Rotate X label","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:17:01.606548Z","iopub.execute_input":"2022-08-13T15:17:01.606798Z","iopub.status.idle":"2022-08-13T15:17:02.257519Z","shell.execute_reply.started":"2022-08-13T15:17:01.606773Z","shell.execute_reply":"2022-08-13T15:17:02.256671Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"figure, ax= plt.subplots() \nfigure.set_size_inches(11, 5)\n\n# Total item sales by shop_id\ngroup_shop_sum = all_data.groupby('shop_id').agg({'item_cnt_month': 'sum'})\ngroup_shop_sum = group_shop_sum.reset_index()\n\ngroup_shop_sum = group_shop_sum[group_shop_sum['item_cnt_month'] > 10000]\n\nsns.barplot(x='shop_id', y='item_cnt_month', data=group_shop_sum)\nax.set(title='Distribution of total item counts by shop id',\n xlabel='Shop ID', \n ylabel='Total item counts')\nax.tick_params(axis='x', labelrotation=90)","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:17:02.259054Z","iopub.execute_input":"2022-08-13T15:17:02.259404Z","iopub.status.idle":"2022-08-13T15:17:02.925270Z","shell.execute_reply.started":"2022-08-13T15:17:02.259368Z","shell.execute_reply":"2022-08-13T15:17:02.924435Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Feature Engineering II - Create Lag features ","metadata":{}},{"cell_type":"markdown","source":"### Create Average Monthly Sales Derived Feature by Base Feature","metadata":{}},{"cell_type":"code","source":"def add_mean_features(df, mean_features, idx_features):\n # Check base features\n assert (idx_features[0] == 'date_block_num') and \\\n len(idx_features) in [2, 3]\n \n # Set derived feature name \n if len(idx_features) == 2:\n feature_name = idx_features[1] + '_mean_sales'\n else:\n feature_name = idx_features[1] + '_' + idx_features[2] + '_mean_sales'\n \n # Get average monthly sales by grouping based on base features\n group = df.groupby(idx_features).agg({'item_cnt_month': 'mean'})\n group = group.reset_index()\n group = group.rename(columns={'item_cnt_month': feature_name})\n \n # Merge df with group based on idx_features\n df = df.merge(group, on=idx_features, how='left')\n # Date downcasting\n df = downcast(df, False)\n # Append newly created mean_feature_name features to the mean_features list\n mean_features.append(feature_name)\n \n # Garbage collection\n del group\n gc.collect()\n \n return df, mean_features","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:17:02.931066Z","iopub.execute_input":"2022-08-13T15:17:02.931345Z","iopub.status.idle":"2022-08-13T15:17:02.938002Z","shell.execute_reply.started":"2022-08-13T15:17:02.931317Z","shell.execute_reply":"2022-08-13T15:17:02.937152Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# List of derived features containing 'item_id' in the grouping base features\nitem_mean_features = []\n\n\n# Create monthly average sales derived features grouped by ['date_block_num', 'item_id']\nall_data, item_mean_features = add_mean_features(df=all_data,\n mean_features=item_mean_features,\n idx_features=['date_block_num', 'item_id'])\n\n# Create monthly average sales derived features grouped by ['date_block_num', 'item_id', 'city']\nall_data, item_mean_features = add_mean_features(df=all_data,\n mean_features=item_mean_features,\n idx_features=['date_block_num', 'item_id', 'city'])","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:17:02.939639Z","iopub.execute_input":"2022-08-13T15:17:02.939985Z","iopub.status.idle":"2022-08-13T15:17:14.245009Z","shell.execute_reply.started":"2022-08-13T15:17:02.939949Z","shell.execute_reply":"2022-08-13T15:17:14.244103Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"item_mean_features","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:17:14.246323Z","iopub.execute_input":"2022-08-13T15:17:14.246770Z","iopub.status.idle":"2022-08-13T15:17:14.251937Z","shell.execute_reply.started":"2022-08-13T15:17:14.246734Z","shell.execute_reply":"2022-08-13T15:17:14.251104Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# List of derived features containing 'shop_id' in the grouping base features\nshop_mean_features = []\n\n# Create monthly average sales derived features grouped by ['date_block_num', 'shop_id', 'item_category_id']\nall_data, shop_mean_features = add_mean_features(df=all_data, \n mean_features=shop_mean_features,\n idx_features=['date_block_num', 'shop_id', 'item_category_id'])","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:17:14.253186Z","iopub.execute_input":"2022-08-13T15:17:14.253533Z","iopub.status.idle":"2022-08-13T15:17:17.036977Z","shell.execute_reply.started":"2022-08-13T15:17:14.253481Z","shell.execute_reply":"2022-08-13T15:17:17.036261Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"shop_mean_features","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:17:17.038079Z","iopub.execute_input":"2022-08-13T15:17:17.038341Z","iopub.status.idle":"2022-08-13T15:17:17.043638Z","shell.execute_reply.started":"2022-08-13T15:17:17.038317Z","shell.execute_reply":"2022-08-13T15:17:17.042719Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Create Lag Features","metadata":{}},{"cell_type":"code","source":"def add_lag_features(df, lag_features_to_clip, idx_features, \n lag_feature, nlags=3, clip=False):\n # Copy only the part of the DataFrame needed to create the lag features\n df_temp = df[idx_features + [lag_feature]].copy() \n\n # Create lag features\n for i in range(1, nlags+1):\n # Lag featrue name\n lag_feature_name = lag_feature +'_lag' + str(i)\n # Set df_temp column name\n df_temp.columns = idx_features + [lag_feature_name]\n # Add 1 to date_block_num feature in df_temp\n df_temp['date_block_num'] += 1\n # Merge df with df_temp based on idx_feature\n df = df.merge(df_temp.drop_duplicates(), \n on=idx_features, \n how='left')\n # Replace NaN with 0\n df[lag_feature_name] = df[lag_feature_name].fillna(0)\n # Add lag features to lag_features_to_clip to clip between 0 and 20\n if clip: \n lag_features_to_clip.append(lag_feature_name)\n \n # Date downcasting\n df = downcast(df, False)\n # Garbage collection\n del df_temp\n gc.collect()\n \n return df, lag_features_to_clip","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:17:17.045020Z","iopub.execute_input":"2022-08-13T15:17:17.045401Z","iopub.status.idle":"2022-08-13T15:17:17.056536Z","shell.execute_reply.started":"2022-08-13T15:17:17.045359Z","shell.execute_reply":"2022-08-13T15:17:17.055691Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"lag_features_to_clip = [] # list of lag features to be clipped to between 0 to 20 \nidx_features = ['date_block_num', 'shop_id', 'item_id'] # base features\n\n# Create 3 month lag features of item_cnt_month based on idx_features\nall_data, lag_features_to_clip = add_lag_features(df=all_data, \n lag_features_to_clip=lag_features_to_clip,\n idx_features=idx_features,\n lag_feature='item_cnt_month', \n nlags=3,\n clip=True)","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:17:17.057493Z","iopub.execute_input":"2022-08-13T15:17:17.057754Z","iopub.status.idle":"2022-08-13T15:17:41.965910Z","shell.execute_reply.started":"2022-08-13T15:17:17.057731Z","shell.execute_reply":"2022-08-13T15:17:41.965202Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"all_data.head().T","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:17:41.966932Z","iopub.execute_input":"2022-08-13T15:17:41.967213Z","iopub.status.idle":"2022-08-13T15:17:41.983087Z","shell.execute_reply.started":"2022-08-13T15:17:41.967187Z","shell.execute_reply":"2022-08-13T15:17:41.982016Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"lag_features_to_clip","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:17:41.985283Z","iopub.execute_input":"2022-08-13T15:17:41.985883Z","iopub.status.idle":"2022-08-13T15:17:41.991700Z","shell.execute_reply.started":"2022-08-13T15:17:41.985845Z","shell.execute_reply":"2022-08-13T15:17:41.990774Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create 3 month lag features of item_count feature based on idx_features\nall_data, lag_features_to_clip = add_lag_features(df=all_data, \n lag_features_to_clip=lag_features_to_clip,\n idx_features=idx_features,\n lag_feature='item_count', \n nlags=3)\n\n# Create 3 month lag features of item_price_mean feature based on idx_features\nall_data, lag_features_to_clip = add_lag_features(df=all_data, \n lag_features_to_clip=lag_features_to_clip,\n idx_features=idx_features,\n lag_feature='item_price_mean', \n nlags=3)","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:17:41.993043Z","iopub.execute_input":"2022-08-13T15:17:41.993714Z","iopub.status.idle":"2022-08-13T15:18:41.914368Z","shell.execute_reply.started":"2022-08-13T15:17:41.993676Z","shell.execute_reply":"2022-08-13T15:18:41.912863Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_test_temp = all_data[all_data['date_block_num'] == 34]\nX_test_temp[item_mean_features].sum()","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:18:41.916077Z","iopub.execute_input":"2022-08-13T15:18:41.916440Z","iopub.status.idle":"2022-08-13T15:18:42.228219Z","shell.execute_reply.started":"2022-08-13T15:18:41.916404Z","shell.execute_reply":"2022-08-13T15:18:42.227263Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create lag features by item_mean_features element based on dx_features\nfor item_mean_feature in item_mean_features:\n all_data, lag_features_to_clip = add_lag_features(df=all_data, \n lag_features_to_clip=lag_features_to_clip, \n idx_features=idx_features, \n lag_feature=item_mean_feature, \n nlags=3)\n# Remove features in item_mean_features\nall_data = all_data.drop(item_mean_features, axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:18:42.229636Z","iopub.execute_input":"2022-08-13T15:18:42.229907Z","iopub.status.idle":"2022-08-13T15:19:42.037131Z","shell.execute_reply.started":"2022-08-13T15:18:42.229881Z","shell.execute_reply":"2022-08-13T15:19:42.036208Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"shop_mean_features","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:19:42.038409Z","iopub.execute_input":"2022-08-13T15:19:42.038886Z","iopub.status.idle":"2022-08-13T15:19:42.043925Z","shell.execute_reply.started":"2022-08-13T15:19:42.038834Z","shell.execute_reply":"2022-08-13T15:19:42.043048Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create lag features by shop_mean_features element based on ['date_block_num', 'shop_id', 'item_category_id']\nfor shop_mean_feature in shop_mean_features:\n all_data, lag_features_to_clip = add_lag_features(df=all_data,\n lag_features_to_clip=lag_features_to_clip, \n idx_features=['date_block_num', 'shop_id', 'item_category_id'], \n lag_feature=shop_mean_feature, \n nlags=3)\n# Remove features in shop_mean_features\nall_data = all_data.drop(shop_mean_features, axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:19:42.044890Z","iopub.execute_input":"2022-08-13T15:19:42.045152Z","iopub.status.idle":"2022-08-13T15:19:54.303866Z","shell.execute_reply.started":"2022-08-13T15:19:42.045127Z","shell.execute_reply":"2022-08-13T15:19:54.302957Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Remove data less than date ID 3\nall_data = all_data.drop(all_data[all_data['date_block_num'] < 3].index)","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:19:54.305111Z","iopub.execute_input":"2022-08-13T15:19:54.305493Z","iopub.status.idle":"2022-08-13T15:19:56.682954Z","shell.execute_reply.started":"2022-08-13T15:19:54.305436Z","shell.execute_reply":"2022-08-13T15:19:56.682225Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Other Features Engineering","metadata":{}},{"cell_type":"code","source":"all_data['item_cnt_month_lag_mean'] = all_data[['item_cnt_month_lag1',\n 'item_cnt_month_lag2', \n 'item_cnt_month_lag3']].mean(axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:19:56.684147Z","iopub.execute_input":"2022-08-13T15:19:56.684531Z","iopub.status.idle":"2022-08-13T15:19:56.813146Z","shell.execute_reply.started":"2022-08-13T15:19:56.684493Z","shell.execute_reply":"2022-08-13T15:19:56.812405Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Clip 0~20\nall_data[lag_features_to_clip + ['item_cnt_month', 'item_cnt_month_lag_mean']] = all_data[lag_features_to_clip +['item_cnt_month', 'item_cnt_month_lag_mean']].clip(0, 20)","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:19:56.814385Z","iopub.execute_input":"2022-08-13T15:19:56.814759Z","iopub.status.idle":"2022-08-13T15:19:57.992101Z","shell.execute_reply.started":"2022-08-13T15:19:56.814720Z","shell.execute_reply":"2022-08-13T15:19:57.991287Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"all_data['lag_grad1'] = all_data['item_cnt_month_lag1']/all_data['item_cnt_month_lag2']\nall_data['lag_grad1'] = all_data['lag_grad1'].replace([np.inf, -np.inf], \n np.nan).fillna(0)\n\nall_data['lag_grad2'] = all_data['item_cnt_month_lag2']/all_data['item_cnt_month_lag3']\nall_data['lag_grad2'] = all_data['lag_grad2'].replace([np.inf, -np.inf], \n np.nan).fillna(0)","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:19:57.993376Z","iopub.execute_input":"2022-08-13T15:19:57.993736Z","iopub.status.idle":"2022-08-13T15:19:58.450752Z","shell.execute_reply.started":"2022-08-13T15:19:57.993701Z","shell.execute_reply":"2022-08-13T15:19:58.450024Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"all_data['brand_new'] = all_data['first_sale_date'] == all_data['date_block_num']","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:19:58.451840Z","iopub.execute_input":"2022-08-13T15:19:58.452084Z","iopub.status.idle":"2022-08-13T15:19:58.460265Z","shell.execute_reply.started":"2022-08-13T15:19:58.452060Z","shell.execute_reply":"2022-08-13T15:19:58.459446Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"all_data['duration_after_first_sale'] = all_data['date_block_num'] - all_data['first_sale_date']\nall_data = all_data.drop('first_sale_date', axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:19:58.461426Z","iopub.execute_input":"2022-08-13T15:19:58.461924Z","iopub.status.idle":"2022-08-13T15:19:59.057132Z","shell.execute_reply.started":"2022-08-13T15:19:58.461893Z","shell.execute_reply":"2022-08-13T15:19:59.056321Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"all_data['month'] = all_data['date_block_num']%12","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:19:59.058277Z","iopub.execute_input":"2022-08-13T15:19:59.058557Z","iopub.status.idle":"2022-08-13T15:19:59.105704Z","shell.execute_reply.started":"2022-08-13T15:19:59.058530Z","shell.execute_reply":"2022-08-13T15:19:59.105049Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Remove item_price_mean, item_count features\nall_data = all_data.drop(['item_price_mean', 'item_count'], axis=1)\nall_data = downcast(all_data, False) # Data downcasting\nall_data.info()","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:19:59.106665Z","iopub.execute_input":"2022-08-13T15:19:59.106904Z","iopub.status.idle":"2022-08-13T15:20:00.735957Z","shell.execute_reply.started":"2022-08-13T15:19:59.106881Z","shell.execute_reply":"2022-08-13T15:20:00.735135Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Train data (Features)\nX_train = all_data[all_data['date_block_num'] < 33]\nX_train = X_train.drop(['item_cnt_month'], axis=1)\n# Valid data (Features)\nX_valid = all_data[all_data['date_block_num'] == 33]\nX_valid = X_valid.drop(['item_cnt_month'], axis=1)\n# Test data (Features)\nX_test = all_data[all_data['date_block_num'] == 34]\nX_test = X_test.drop(['item_cnt_month'], axis=1)\n\n# Train data (Target values)\ny_train = all_data[all_data['date_block_num'] < 33]['item_cnt_month']\n# Valid data (Target values)\ny_valid = all_data[all_data['date_block_num'] == 33]['item_cnt_month']\n\n# Garbage collection\ndel all_data\ngc.collect();","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:20:00.737324Z","iopub.execute_input":"2022-08-13T15:20:00.737645Z","iopub.status.idle":"2022-08-13T15:20:03.539766Z","shell.execute_reply.started":"2022-08-13T15:20:00.737616Z","shell.execute_reply":"2022-08-13T15:20:03.538845Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Train model and Submit","metadata":{}},{"cell_type":"code","source":"import lightgbm as lgb\n\n# lgb hyper-parameters\nparams = {'metric': 'rmse',\n 'num_leaves': 256,\n 'learning_rate': 0.005,\n 'feature_fraction': 0.75,\n 'bagging_fraction': 0.75,\n 'bagging_freq': 5,\n 'force_col_wise' : True,\n 'random_state': 10}\n\ncat_features = ['shop_id', 'city', 'item_category_id', 'category', 'month']\n\n# lgb train and valid dataset\ndtrain = lgb.Dataset(X_train, y_train)\ndvalid = lgb.Dataset(X_valid, y_valid)\n \n# Train LightGBM model\nlgb_model = lgb.train(params=params,\n train_set=dtrain,\n num_boost_round=2400,\n valid_sets=(dtrain, dvalid),\n early_stopping_rounds=200,\n categorical_feature=cat_features,\n verbose_eval=100) ","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:20:03.541249Z","iopub.execute_input":"2022-08-13T15:20:03.541640Z","iopub.status.idle":"2022-08-13T15:40:40.375346Z","shell.execute_reply.started":"2022-08-13T15:20:03.541605Z","shell.execute_reply":"2022-08-13T15:40:40.374276Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- [1210]\ttraining's rmse: 0.756134\tvalid_1's rmse: 0.7926\n- [1209]\ttraining's rmse: 0.755821\tvalid_1's rmse: 0.792047","metadata":{}},{"cell_type":"code","source":"preds = lgb_model.predict(X_test).clip(0,20)\n\nsubmission['item_cnt_month'] = preds\nsubmission.to_csv('submission.csv', index=False)","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:40:40.377093Z","iopub.execute_input":"2022-08-13T15:40:40.377516Z","iopub.status.idle":"2022-08-13T15:40:57.986834Z","shell.execute_reply.started":"2022-08-13T15:40:40.377453Z","shell.execute_reply":"2022-08-13T15:40:57.985791Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del X_train, y_train, X_valid, y_valid, X_test, lgb_model, dtrain, dvalid\ngc.collect();","metadata":{"execution":{"iopub.status.busy":"2022-08-13T15:40:57.988218Z","iopub.execute_input":"2022-08-13T15:40:57.988526Z","iopub.status.idle":"2022-08-13T15:40:58.095404Z","shell.execute_reply.started":"2022-08-13T15:40:57.988489Z","shell.execute_reply":"2022-08-13T15:40:58.094631Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Thank you ๐Ÿ™‚ Upvote is free ๐Ÿ‘","metadata":{}}]}