{"cells":[{"metadata":{},"cell_type":"markdown","source":"# 1. Importing Packages and Collecting Data, Defining Evaluation "},{"metadata":{},"cell_type":"markdown","source":"## 1.1 Importing Packages"},{"metadata":{"trusted":false},"cell_type":"code","source":"'''Importing Data Manipulattion Modules'''\nimport re\nimport numpy as np\nimport pandas as pd\nfrom pandas.core.dtypes.dtypes import CategoricalDtype\nfrom scipy.stats import norm, skew\nfrom scipy.special import boxcox1p\npd.set_option('display.float_format', lambda x: '{:.3f}'.format(x))\npd.set_option(\"display.max_columns\", 81)\npd.set_option(\"display.max_rows\", 101)\npd.set_option(\"display.max_colwidth\", 100)\n\n'''Seaborn and Matplotlib Visualization'''\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nplt.style.use('bmh')                    \nsns.set_style({'axes.grid':False}) \nsns.set_style('whitegrid')\n%matplotlib inline\n\n'''Validation'''\nfrom sklearn.model_selection import KFold, cross_val_score\n\n'''Ignore deprecation and future, and user warnings.'''\nimport warnings as wrn\nwrn.filterwarnings('ignore', category = DeprecationWarning) \nwrn.filterwarnings('ignore', category = FutureWarning) \nwrn.filterwarnings('ignore', category = UserWarning) ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 1.2 Collecting Data"},{"metadata":{"trusted":false},"cell_type":"code","source":"'''Check the files'''\nfrom subprocess import check_output\nprint(check_output([\"ls\", \"../input/grupo-bimbo-inventory-demand/\"]).decode(\"utf8\"))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### town_state"},{"metadata":{"trusted":false},"cell_type":"code","source":"dtype = {'Agencia_ID': 'int16'}\ntown_state_df = pd.read_csv(\"../input/grupo-bimbo-inventory-demand/town_state.csv\", dtype=dtype)\n\ntown_state_df['Town_ID'] = town_state_df.Town.apply(lambda x: x.split(' ')[0]).astype('int16')\ntown_state_df['Town_name'] = town_state_df.Town.apply(lambda x: ' '.join(x.split(' ')[1:]))\n\nprint(f'town_state\\'s shape: {town_state_df.shape}')\ntown_state_df.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"town_state_df.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### product"},{"metadata":{"trusted":false},"cell_type":"code","source":"dtype = {'Producto_ID': 'int32'}\nproduct_df = pd.read_csv(\"../input/grupo-bimbo-inventory-demand/producto_tabla.csv\", dtype=dtype)\n\nproduct_df['popular_name'] = product_df.NombreProducto.str.extract(r'^(.*?)(\\d*\\s\\d+(kg|Kg|g|G|ml| ml|p|Reb)\\s)', expand=False)[0]\nproduct_df['property'] = product_df.NombreProducto.str.extract(r'^.*\\d+(kg|Kg|g|G|ml| ml|p|Reb)\\s(.*?)\\s\\d+$', expand=False)[1]\nproduct_df['unit'] = product_df.NombreProducto.str.extract(r'(\\d*\\s\\d+(kg|Kg|g|G|ml| ml))', expand=False)[0]\nproduct_df['pieces'] =  product_df.NombreProducto.str.extract('(\\d+(p|Reb)) ', expand=False)[0]\n\nprint(f'product\\'s shape: {product_df.shape}')\nproduct_df.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"product_df.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### client"},{"metadata":{"trusted":false},"cell_type":"code","source":"dtype = {'Cliente_ID': 'int32'}\nclient_df = pd.read_csv(\"../input/grupo-bimbo-inventory-demand/cliente_tabla.csv\", dtype=dtype)\n\ndup_sr = client_df.groupby('Cliente_ID')['Cliente_ID'].count().astype('int8')\ndup_sr.name = 'dup_num'\nclient_df = pd.merge(client_df, pd.DataFrame(dup_sr).reset_index())\ndel dup_sr\n\nprint(f'client\\'s shape: {client_df.shape}')\nclient_df.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"client_df.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### test"},{"metadata":{"trusted":false},"cell_type":"code","source":"dtype = {\n    'id': 'int32',\n    'Semana': 'int8',\n    'Agencia_ID': 'int16',\n    'Canal_ID': 'int8',\n    'Ruta_SAK': 'int16',\n    'Cliente_ID': 'int32',\n    'Producto_ID': 'int32',\n}\ntest_df = pd.read_csv(\"../input/grupo-bimbo-inventory-demand/test.csv\", dtype=dtype)\n\nprint(f'test\\'s shape: {test_df.shape}')\ntest_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"test_df.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### train"},{"metadata":{"trusted":false},"cell_type":"code","source":"dtype = {\n    'Semana': 'int8',\n    'Agencia_ID': 'int16',\n    'Canal_ID': 'int8',\n    'Ruta_SAK': 'int16',\n    'Cliente_ID': 'int32',\n    'Producto_ID': 'int32',\n    'Venta_uni_hoy': 'int16',\n    'Venta_hoy': 'float32',\n    'Dev_uni_proxima': 'int32',\n    'Dev_proxima': 'float32',\n    'Demanda_uni_equil': 'int16',\n}\ntrain_df = pd.read_csv(\"../input/grupo-bimbo-inventory-demand/train.csv\", dtype=dtype)\n\nprint(f'train\\'s shape: {train_df.shape}')\ntrain_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"train_df.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### product_price"},{"metadata":{"trusted":false},"cell_type":"code","source":"sale_price_sr = (train_df.Venta_hoy / train_df.Venta_uni_hoy)\nreturn_price_sr = (train_df.Dev_proxima / train_df.Dev_uni_proxima)\nproduct_price_df = pd.DataFrame({'Producto_ID': train_df.Producto_ID, 'sale_price': sale_price_sr, 'return_price': return_price_sr})\n\ndel sale_price_sr\ndel return_price_sr\n\nprint(f'product price\\'s shape: {product_price_df.shape}')\nproduct_price_df.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"product_price_df.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 1.3 Defining Evaluation"},{"metadata":{"trusted":false},"cell_type":"code","source":"'''KFold for cross validation'''\nkf = KFold(n_splits=3, shuffle=True, random_state=2)\n\n'''Define the validation function'''\ndef rmsle_cv(model, X, y, cv=kf):\n    rmsle = np.sqrt(\n        -cross_val_score(\n            model,\n            X, y,\n            scoring=\"neg_mean_squared_log_error\",\n            cv=cv,\n        )\n    )\n    return(rmsle)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# 2. Adjusting Data"},{"metadata":{},"cell_type":"markdown","source":"## 2.1 Cleansing"},{"metadata":{},"cell_type":"markdown","source":"### town_state"},{"metadata":{"trusted":false},"cell_type":"code","source":"town_state_df['Town'] = town_state_df['Town'].str.upper()\ntown_state_df['Town_name'] = town_state_df['Town_name'].str.upper()\ntown_state_df['State'] = town_state_df['State'].str.upper()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"town_state_df.groupby(['Town_name'])['Town_ID'].nunique().sort_values(ascending=False)[:6]","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"town_state_df[(town_state_df['Town_name'].isin(['LOS MOCHIS', 'PINOTEPA']))].sort_values(by='Town_name')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"town_state_df.loc[498, 'Town_ID'] = 2561","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"town_state_df.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"town_state_df.groupby('Town_ID')['Town'].nunique().sort_values(ascending=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"town_state_df[town_state_df.Town_ID.isin([2561, 2169, 2152])].sort_values(by='Town_ID')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"town_state_df['Town_ID'].max()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"town_state_df.loc[199, 'Town_ID'] = 3217\ntown_state_df.loc[311, 'Town_ID'] = 3218","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"town_state_df['Town_ID'].nunique()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### product_price"},{"metadata":{"trusted":false},"cell_type":"code","source":"sale_prices_df = product_price_df.drop('return_price', axis=1).dropna().rename(columns={'sale_price': 'price'})\nreturn_prices_df = product_price_df.drop('sale_price', axis=1).dropna().rename(columns={'return_price': 'price'})\nprices_df = pd.concat([sale_prices_df, return_prices_df])\nprices_df = prices_df.groupby('Producto_ID')['price'].median().reset_index()\nprices_df.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"prices_df.shape","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### product"},{"metadata":{"trusted":false},"cell_type":"code","source":"product_df['in_train'] = 0\nproduct_df['in_test'] = 0\nproduct_df.loc[product_df['Producto_ID'].isin(test_df['Producto_ID'].unique()), 'in_test'] = 1\nproduct_df.loc[product_df['Producto_ID'].isin(train_df['Producto_ID'].unique()), 'in_train'] = 1\nproduct_df = product_df[(product_df['in_test'] == 1) | (product_df['in_train'] == 1)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"product_df[product_df['property'].isnull()]","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"product_df.loc[117, 'popular_name'] = 'Donas'\nproduct_df.loc[117, 'property'] = 'Prom BIM'\nproduct_df.loc[117, 'unit'] = None\nproduct_df.loc[117, 'pieces'] = '6p'\n\nproduct_df.loc[190, 'popular_name'] = 'Paletina para Cafe'\nproduct_df.loc[190, 'property'] = 'NES'\nproduct_df.loc[190, 'unit'] = None\nproduct_df.loc[190, 'pieces'] = None\n\nproduct_df.loc[381, 'popular_name'] = 'Camioncitos Bimbo'\nproduct_df.loc[381, 'property'] = 'BIM'\nproduct_df.loc[381, 'unit'] = None\nproduct_df.loc[381, 'pieces'] = None\n\nproduct_df.loc[1152, 'popular_name'] = 'Burrito Vaporero FrijolChorizo'\nproduct_df.loc[1152, 'property'] = 'CU LON'\nproduct_df.loc[1152, 'unit'] = '90g'\nproduct_df.loc[1152, 'pieces'] = None\n\nproduct_df.loc[1677, 'popular_name'] = 'Tarima Twin Pack Thins Multig'\nproduct_df.loc[1677, 'property'] = 'CU ORO'\nproduct_df.loc[1677, 'unit'] = None\nproduct_df.loc[1677, 'pieces'] = None\n\nproduct_df.loc[1888, 'popular_name'] = 'Deliciosas Chochochispas'\nproduct_df.loc[1888, 'property'] = 'Prom MTA LAR'\nproduct_df.loc[1888, 'unit'] = '204g'\nproduct_df.loc[1888, 'pieces'] = None\n\nproduct_df.loc[1889, 'popular_name'] = 'Deliciosas Chochochispas'\nproduct_df.loc[1889, 'property'] = 'Prom LAR'\nproduct_df.loc[1889, 'unit'] = '204g'\nproduct_df.loc[1889, 'pieces'] = None\n\nproduct_df.loc[2449, 'popular_name'] = 'Galleta Granel Classics Chocolate'\nproduct_df.loc[2449, 'property'] = 'GBI'\nproduct_df.loc[2449, 'unit'] = None\nproduct_df.loc[2449, 'pieces'] = None","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"product_df[product_df['popular_name'].isnull()]","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"product_df.loc[877, 'popular_name'] = 'Tortilla Hna Chihuahua'\nproduct_df.loc[877, 'unit'] = '535g'\nproduct_df.loc[877, 'pieces'] = '10p'\n\nproduct_df.loc[1585, 'popular_name'] = 'Principe Cho Bco MG'\nproduct_df.loc[1585, 'unit'] = '110g'\nproduct_df.loc[1585, 'pieces'] = '10p'\n\nproduct_df.loc[1748, 'popular_name'] = 'Combo Salma mas Levite'\nproduct_df.loc[1748, 'unit'] = '1360g'\nproduct_df.loc[1748, 'pieces'] = None","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"product_df['pieces'] = product_df['pieces'].str.extract(r'(\\d+)(p|Reb)')[0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"product_df['weight'] = product_df['unit'].str.strip()\nproduct_df['weight'] = product_df['weight'].str.replace(' ', '.')\nproduct_df['weight'] = product_df['weight'].str.upper()\nw = product_df['weight'].str.extract('(.+?)(KG|G|ML)', expand=True)\nproduct_df['weight'] = w[0].astype('float') * w[1].map({'KG':1000, 'G':1, 'ML':1})","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"product_df = pd.merge(product_df, prices_df, how='left')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 2.2 Imputing Missing Data"},{"metadata":{},"cell_type":"markdown","source":"### product"},{"metadata":{"trusted":false},"cell_type":"code","source":"product_df['pieces'] = product_df['pieces'].fillna(1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"product_df[product_df['weight'].isnull()]['price'].max()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"product_df[product_df['price'].isnull()]['weight'].max()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"df = product_df.dropna()\ndf = df[(df['price'] <= 311) & (df['weight'] <= 1880)]\nplt.figure(figsize=(16,8))\nsns.scatterplot(x='weight', y='price', data=df)\ndel df","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"from sklearn.linear_model import LinearRegression\n\ndf = product_df.dropna()\ndf = df[(df['price'] <= 100) & (df['weight'] <= 1880)]\n\n# predict missing prices\nlf = LinearRegression()\nlf.fit(df['weight'].values.reshape(-1, 1), df['price'])\n\nprices = lf.predict(product_df[product_df['price'].isnull()]['weight'].values.reshape(-1, 1))\n\nproduct_df.loc[product_df['price'].isnull(), 'price'] = prices\n\n# predict missing weights\nlf = LinearRegression()\nlf.fit(df['price'].values.reshape(-1, 1), df['weight'])\n\nweights = lf.predict(product_df[product_df['weight'].isnull()]['price'].values.reshape(-1, 1))\n\nproduct_df.loc[product_df['weight'].isnull(), 'weight'] = weights\n\ndel df\ndel prices\ndel weights","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"(product_df.drop(['unit'], axis=1).isnull().sum() == 0).all()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 2.3 Transforming Data Type"},{"metadata":{"trusted":false},"cell_type":"code","source":"product_df['pieces'] = product_df['pieces'].astype('int16')\nproduct_df['in_train'] = product_df['in_train'].astype('bool')\nproduct_df['in_test'] = product_df['in_test'].astype('bool')\nproduct_df['weight'] = product_df['weight'].astype('float32')\nproduct_df['price'] = product_df['price'].astype('float32')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"town_state_df['State'] = town_state_df['State'].astype('category')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"train_df['Canal_ID'] = train_df['Canal_ID'].astype('category')\ntest_df['Canal_ID'] = test_df['Canal_ID'].astype('category')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 2.4 Dropping Features"},{"metadata":{"trusted":false},"cell_type":"code","source":"train_df.drop(['Venta_uni_hoy', 'Venta_hoy', 'Dev_uni_proxima', 'Dev_proxima'], axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 2.5 Merging Data"},{"metadata":{},"cell_type":"markdown","source":"### town_state"},{"metadata":{"trusted":false},"cell_type":"code","source":"train_df = pd.merge(train_df, town_state_df[['Agencia_ID', 'Town_ID']], how='left')\ntest_df = pd.merge(test_df, town_state_df[['Agencia_ID', 'Town_ID']], how='left')\ntrain_df.drop('Agencia_ID', axis=1, inplace=True)\ntest_df.drop('Agencia_ID', axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### product"},{"metadata":{"trusted":false},"cell_type":"code","source":"train_df = pd.merge(\n    train_df,\n    product_df[[\n        'Producto_ID', 'popular_name', 'property',\n        'pieces', 'weight', 'price'\n    ]], how='left')\ntest_df = pd.merge(\n    test_df,\n    product_df[[\n        'Producto_ID', 'popular_name', 'property',\n        'pieces', 'weight', 'price'\n    ]], how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"train_df.head(5)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 2.6 Bin-Counting"},{"metadata":{},"cell_type":"markdown","source":"### Semana"},{"metadata":{"trusted":false},"cell_type":"code","source":"semana_med_s = train_df.groupby('Semana')['Demanda_uni_equil'].median()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"semana_med_s","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"train_df.drop('Semana', axis=1, inplace=True)\ntest_df.drop('Semana', axis=1, inplace=True)\ndel semana_med_s","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Cliente_ID"},{"metadata":{"trusted":false},"cell_type":"code","source":"client_med_s = train_df.groupby('Cliente_ID')['Demanda_uni_equil'].median().astype('int16')\nclient_med_s.name = 'client_med'","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### popular_name"},{"metadata":{"trusted":false},"cell_type":"code","source":"popular_name_med_s = train_df.groupby('popular_name')['Demanda_uni_equil'].median().astype('int16')\npopular_name_med_s.name = 'popular_name_med'","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Town_ID"},{"metadata":{"trusted":false},"cell_type":"code","source":"town_id_med_s = train_df.groupby('Town_ID')['Demanda_uni_equil'].median().astype('int16')\ntown_id_med_s.name = 'town_id_med'","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Ruta_SAK"},{"metadata":{"trusted":false},"cell_type":"code","source":"ruta_id_med_s = train_df.groupby('Ruta_SAK')['Demanda_uni_equil'].median().astype('int16')\nruta_id_med_s.name = 'ruta_id_med'","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 2.7 Merging Bin-Counting Data"},{"metadata":{"trusted":false},"cell_type":"code","source":"test_df = pd.merge(test_df, client_med_s.reset_index(), how='left')\ntest_df = pd.merge(test_df, popular_name_med_s.reset_index(), how='left')\ntest_df = pd.merge(test_df, town_id_med_s.reset_index(), how='left')\ntest_df = pd.merge(test_df, ruta_id_med_s.reset_index(), how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"train_df = pd.merge(train_df, client_med_s.reset_index(), how='left')\ntrain_df = pd.merge(train_df, popular_name_med_s.reset_index(), how='left')\ntrain_df = pd.merge(train_df, town_id_med_s.reset_index(), how='left')\ntrain_df = pd.merge(train_df, ruta_id_med_s.reset_index(), how='left')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 2.8 Imputing Test Missing Data"},{"metadata":{"trusted":false},"cell_type":"code","source":"test_df['popular_name_med'] = test_df['popular_name_med'].fillna(test_df['popular_name_med'].mean())\ntest_df['client_med'] = test_df['client_med'].fillna(test_df['client_med'].mean())\ntest_df['ruta_id_med'] = test_df['ruta_id_med'].fillna(test_df['ruta_id_med'].mean())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 2.9 Transforming Data Type"},{"metadata":{"trusted":false},"cell_type":"code","source":"train_df['client_med'] = train_df['client_med'].astype('int16')\ntrain_df['popular_name_med'] = train_df['popular_name_med'].astype('int16')\ntrain_df['town_id_med'] = train_df['town_id_med'].astype('int16')\ntrain_df['ruta_id_med'] = train_df['ruta_id_med'].astype('int16')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"test_df['client_med'] = test_df['client_med'].astype('int16')\ntest_df['popular_name_med'] = test_df['popular_name_med'].astype('int16')\ntest_df['town_id_med'] = test_df['town_id_med'].astype('int16')\ntest_df['ruta_id_med'] = test_df['ruta_id_med'].astype('int16')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 2.10 Dropping Features"},{"metadata":{"trusted":false},"cell_type":"code","source":"train_df.drop(\n    ['Ruta_SAK', 'Cliente_ID', 'Producto_ID', 'Town_ID', 'popular_name', 'property', 'pieces'],\n    axis=1, inplace=True)\ntest_df.drop(\n    ['Ruta_SAK', 'Cliente_ID', 'Producto_ID', 'Town_ID', 'popular_name', 'property', 'pieces'],\n    axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"train_df.head(3)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"test_df.head(3)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# 3. Data Preprocessing"},{"metadata":{},"cell_type":"markdown","source":"## 3.1 Take a glance at all variables"},{"metadata":{"trusted":false},"cell_type":"code","source":"train_df.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"test_df.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"train_df.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"test_df.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"'''Plot histogram of numerical variables to validate pandas intuition.'''\ndef draw_histograms(df, variables, n_rows, n_cols, size):\n    fig=plt.figure()\n    for i, var_name in enumerate(variables):\n        ax=fig.add_subplot(n_rows, n_cols, i+1)\n        df[var_name].hist(bins=40, ax=ax, color='skyblue', alpha=0.8, figsize=size)\n        ax.set_title(var_name, fontsize=43)\n        ax.tick_params(axis='both', which='major', labelsize=35)\n        ax.tick_params(axis='both', which='minor', labelsize=35)\n        ax.set_xlabel('')\n    fig.tight_layout(rect=[0, 0.03, 1, 0.95])\n    plt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"skewness = train_df.select_dtypes(include=['int8', 'int16', 'int32', 'int64', 'float32', 'float64']).apply(lambda x: skew(x))\nskew_index = skewness[abs(skewness) >= 0.75].index\nskewness[skew_index].sort_values(ascending=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"skewness = test_df.select_dtypes(include=['int8', 'int16', 'int32', 'int64', 'float32', 'float64']).apply(lambda x: skew(x))\nskew_index = skewness[abs(skewness) >= 0.75].index\nskewness[skew_index].sort_values(ascending=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 3.2 BoxCox Transform"},{"metadata":{"trusted":false},"cell_type":"code","source":"'''BoxCox Transform'''\nlam = 0.01\nfor column in skew_index:\n    train_df[column] = boxcox1p(train_df[column], lam)\n    test_df[column] = boxcox1p(test_df[column], lam)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"train_df.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"test_df.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 3.3 Transforming Data Type"},{"metadata":{"trusted":false},"cell_type":"code","source":"train_df['client_med'] = train_df['client_med'].astype('float32')\ntrain_df['popular_name_med'] = train_df['popular_name_med'].astype('float32')\ntrain_df['town_id_med'] = train_df['town_id_med'].astype('float32')\ntrain_df['ruta_id_med'] = train_df['ruta_id_med'].astype('float32')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"test_df['client_med'] = test_df['client_med'].astype('float32')\ntest_df['popular_name_med'] = test_df['popular_name_med'].astype('float32')\ntest_df['town_id_med'] = test_df['town_id_med'].astype('float32')\ntest_df['ruta_id_med'] = test_df['ruta_id_med'].astype('float32')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# 4. Exploratory Data Analysis "},{"metadata":{},"cell_type":"markdown","source":"## 4.1 Analyzing Target "},{"metadata":{"trusted":false},"cell_type":"code","source":"sample_train_df = train_df.sample(n=10000)\nsample_train_df['log_target'] = np.log1p(sample_train_df['Demanda_uni_equil'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"sample_train_df.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"'''correlation matrix'''\nplt.subplots(figsize=(20, 16))\nk = 20 #number of variables for heatmap\ncorrmat = sample_train_df.corr()\ncols = corrmat.nlargest(k, 'log_target')['log_target'].index\n\ncm = np.corrcoef(sample_train_df[cols].values.T)\nhm = sns.heatmap(cm, cbar=True, annot=True, square=True,\n                 fmt='.2f', annot_kws={'size': 10}, cmap='Blues',\n                 yticklabels=cols.values, xticklabels=cols.values)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"'''Check feature inportance by applying LightGBM'''\nimport lightgbm as lgb\nmodel_lgb = lgb.LGBMRegressor(num_leaves=1000,\n                              max_depth=5,\n                              learning_rate=0.1,\n                              random_state=2)\nmodel_lgb.fit(sample_train_df.drop(['Demanda_uni_equil', 'log_target'], axis=1), sample_train_df['log_target'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"df = pd.DataFrame(model_lgb.feature_importances_,\n             index=sample_train_df.drop(['Demanda_uni_equil', 'log_target'], axis=1).columns,\n             columns=['importance']).sort_values('importance', ascending=False)\ndf[df.importance > 10]","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# weight\nplt.figure(figsize=(16, 8))\nsns.scatterplot(x='weight', y='log_target', data=sample_train_df, palette='Blues_d')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# price\nplt.figure(figsize=(16, 8))\nsns.scatterplot(x='price', y='log_target', data=sample_train_df, palette='Blues_d')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# client_med\nplt.figure(figsize=(16, 8))\nsns.scatterplot(x='client_med', y='log_target', data=sample_train_df, palette='Blues_d')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# popular_name_med\nplt.figure(figsize=(16, 8))\nsns.scatterplot(x='popular_name_med', y='log_target', data=sample_train_df, palette='Blues_d')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# town_id_med\nplt.figure(figsize=(16, 8))\nsns.scatterplot(x='town_id_med', y='log_target', data=sample_train_df, palette='Blues_d')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# ruta_id_med\nplt.figure(figsize=(16, 8))\nsns.scatterplot(x='ruta_id_med', y='log_target', data=sample_train_df, palette='Blues_d')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# 5. Model Building and Evaluation"},{"metadata":{},"cell_type":"markdown","source":"## 5.1 Importing Packages"},{"metadata":{"trusted":false},"cell_type":"code","source":"'''Importing Modeling Interested Modules'''\nfrom sklearn.base import BaseEstimator\nfrom sklearn.pipeline import make_pipeline\nfrom sklearn.preprocessing import StandardScaler, RobustScaler\nfrom sklearn.model_selection import GridSearchCV\nfrom sklearn.linear_model import LinearRegression, LassoCV, RidgeCV, ElasticNetCV\nfrom sklearn.svm import SVR\nfrom sklearn.kernel_ridge import KernelRidge\nfrom lightgbm import LGBMRegressor","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 5.2 Preparation before Building Models "},{"metadata":{"trusted":false},"cell_type":"code","source":"'''Adjust dataframe for modeling'''\ntrain_y = train_df['Demanda_uni_equil']\ntrain_df.drop(['Demanda_uni_equil'], axis=1, inplace=True)\ntrain_X = train_df\ntest_X = test_df.drop('id', axis=1)\n\n'''Transform categorical features to dummy variables'''\ntrain_X = pd.get_dummies(train_X)\ntest_X = pd.get_dummies(test_X)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"sample_train_df['Demanda_uni_equil'] = np.expm1(sample_train_df['log_target']).astype('int32')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"sample_train_df.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"'''Prepare sample train for the fast training'''\nsample_train_y = sample_train_df['Demanda_uni_equil']\nsample_train_df.drop(['Demanda_uni_equil', 'log_target'], axis=1, inplace=True)\nsample_train_X = sample_train_df\n\nsample_train_X = pd.get_dummies(sample_train_X)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"'''We should use the log transform of the target value'''\nclass MyEstimator(BaseEstimator):\n    def __init__(self, model):\n        self.model = model\n        \n    def fit(self, X, y):\n        self.model.fit(X, np.log1p(y))\n        return self \n\n    def predict(self, X):\n        predicts = np.expm1(self.model.predict(X))\n        mask = (predicts <= 0)\n        predicts[mask] = 0\n        return predicts","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"'''Define evaluation function for Convienience'''\ndef evaluation_model(model, train_X, train_y, test_X):\n    cv = rmsle_cv(model, train_X, train_y)\n    cv_mean = np.round(cv.mean(), 5)\n    cv_std = np.round(cv.std(), 5)\n    sample_prediction = model.predict(test_X.loc[:3, :])\n    return {'cv_mean': cv_mean, 'cv_std': cv_std, 'sample_prediction': sample_prediction}","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"'''Define Hyperparameters Tuning Function'''\ndef tune_hyperparameters(model, param_grid, train_X, train_y):\n    grid = GridSearchCV(\n        model, param_grid, \n        scoring='neg_mean_squared_log_error',\n        cv=3, n_jobs=-1,\n    )\n    grid.fit(train_X, train_y)\n    best_params = grid.best_params_ \n    best_score = np.round(np.sqrt(-1 * grid.best_score_), 5)\n    return best_params, best_score","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 5.3 Building Models"},{"metadata":{},"cell_type":"markdown","source":"### LinearRegression"},{"metadata":{"trusted":false},"cell_type":"code","source":"model = make_pipeline(\n    RobustScaler(),\n    LinearRegression(),\n)\nlr_model = MyEstimator(model)\nlr_model.fit(sample_train_X, sample_train_y)\nlr_eval = evaluation_model(lr_model, sample_train_X, sample_train_y, test_X)\nprint(lr_eval)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### LassoCV"},{"metadata":{"trusted":false},"cell_type":"code","source":"model = make_pipeline(\n    RobustScaler(),\n    LassoCV(\n        alphas=(0.00001, 0.0001, 0.0005, 0.001, 0.01, 0.05, 0.1, 0.3, 1, 3, 5, 10),\n    ),\n)\nlasso_cv_model = MyEstimator(model)\nlasso_cv_model.fit(sample_train_X, sample_train_y)\nlasso_cv_eval = evaluation_model(lasso_cv_model, sample_train_X, sample_train_y, test_X)\nprint(lasso_cv_eval)\n\nopt_alpha = lasso_cv_model.model.steps[1][1].alpha_\nprint(f'\\nopt_alpha: {opt_alpha}')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### RidgeCV"},{"metadata":{"trusted":false},"cell_type":"code","source":"model = make_pipeline(\n    RobustScaler(),\n    RidgeCV(\n        alphas=(0.0001, 0.0005, 0.001, 0.01, 0.05, 0.1, 0.3, 1, 3, 5, 10),\n    ),\n)\nridge_cv_model = MyEstimator(model)\nridge_cv_model.fit(sample_train_X, sample_train_y)\nridge_cv_eval = evaluation_model(ridge_cv_model, sample_train_X, sample_train_y, test_X)\nprint(ridge_cv_eval)\n\nopt_alpha = ridge_cv_model.model.steps[1][1].alpha_\nprint(f'\\nopt_alpha: {opt_alpha}')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### ElasticNetCV"},{"metadata":{"trusted":false},"cell_type":"code","source":"model = make_pipeline(\n    RobustScaler(),\n    ElasticNetCV(\n        alphas=(0.00001, 0.0001, 0.0002, 0.0003), \n        l1_ratio=(0.001, 0.01, 0.1, 0.2, 0.3, 0.4, 0.5),\n    ),\n)\nelsnt_cv_model = MyEstimator(model)\nelsnt_cv_model.fit(sample_train_X, sample_train_y)\nelsnt_cv_eval = evaluation_model(elsnt_cv_model, sample_train_X, sample_train_y, test_X)\nprint(elsnt_cv_eval)\n\nopt_alpha = elsnt_cv_model.model.steps[1][1].alpha_\nopt_l1_ratio = elsnt_cv_model.model.steps[1][1].l1_ratio_\nprint(f'\\nopt_alpha: {opt_alpha} opt_l1_ratio: {opt_l1_ratio}')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### SVR"},{"metadata":{"trusted":false},"cell_type":"code","source":"# {'cv_mean': 0.57025, 'cv_std': 0.00775, 'sample_prediction': array([3.85650165, 1.24517075, 3.45866592])}\n\n# grid best_params: {'model__svr__C': 10, 'model__svr__epsilon': 0.1, 'model__svr__gamma': 0.01}\n\n# ### build basemodel\n# model = make_pipeline(\n#     RobustScaler(),\n#     SVR(),\n# )\n# svr_model = MyEstimator(model)\n# \n# ### optimize hyperparameters\n# param_grid = {'model__svr__C': [1, 10, 20],\n#               'model__svr__epsilon': [0.001, 0.01, 0.1],\n#               'model__svr__gamma': [0.0001, 0.001, 0.01]}\n# best_params, best_score = \\\n#     tune_hyperparameters(svr_model, param_grid, sample_train_X, sample_train_y)\n# \n# ### fit using best_params\n# svr_model.set_params(**best_params)\n# svr_model.fit(sample_train_X, sample_train_y)\n# svr_eval = evaluation_model(svr_model, sample_train_X, sample_train_y, test_X)\n# print(svr_eval)\n# \n# print(f'\\ngrid best_params: {best_params}')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### KernelRidge"},{"metadata":{"trusted":false},"cell_type":"code","source":"# {'cv_mean': 0.56925, 'cv_std': 0.00546, 'sample_prediction': array([4.15061605, 1.21544493, 3.65678685, 1.81862787])}\n\n# grid best_params: {'model__kernelridge__alpha': 0.5, 'model__kernelridge__coef0': 3, 'model__kernelridge__degree': 2, 'model__kernelridge__kernel': 'polynomial'}\n\n# ### build basemodel\n# model = make_pipeline(\n#     RobustScaler(),\n#     KernelRidge(),\n# )\n# kr_model = MyEstimator(model)\n# \n# ### optimize hyperparameters\n# param_grid = {'model__kernelridge__alpha': [0.01, 0.1, 0.5, 1],\n#               'model__kernelridge__kernel': ['linear', 'polynomial'],\n#               'model__kernelridge__degree': [1, 1.5, 2, 3],\n#               'model__kernelridge__coef0': [3, 4, 5]}\n# best_params, best_score = \\\n#     tune_hyperparameters(kr_model, param_grid, sample_train_X, sample_train_y)\n# \n# ### fit using best_params\n# kr_model.set_params(**best_params)\n# kr_model.fit(sample_train_X, sample_train_y)\n# kr_eval = evaluation_model(kr_model, sample_train_X, sample_train_y, test_X)\n# print(kr_eval)\n# \n# print(f'\\ngrid best_params: {best_params}')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### LightGBM"},{"metadata":{"trusted":false},"cell_type":"code","source":"model = LGBMRegressor(learning_rate=0.01, n_estimators=3000,\n                      num_leaves=5,\n                      objective='regression',\n                      max_bin=55, bagging_fraction=0.8,\n                      bagging_freq=5, feature_fraction=0.2319,\n                      feature_fraction_seed=9, bagging_seed=9,\n                      min_data_in_leaf=6, min_sum_hessian_in_leaf=11)\nlgb_model = MyEstimator(model)\nlgb_model.fit(sample_train_X, sample_train_y)\nlgb_eval = evaluation_model(lgb_model, sample_train_X, sample_train_y, test_X)\nprint(lgb_eval)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 5.4 Submission"},{"metadata":{"trusted":false},"cell_type":"code","source":"def output_submission_file(model, test_X, filename='submission.csv'):\n    prediction = model.predict(test_X)\n    df = pd.DataFrame({'id': test_df['id'], 'Demanda_uni_equil': prediction})\n    print(f'{df.shape}')\n    print(f'{df.head(5)}')\n    df.to_csv(filename, index=False)\n    df.to_csv(filename + '.gz', index=False, compression='gzip')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"'''Submission'''\noutput_submission_file(lgb_model, test_X)","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.7.4"},"toc-autonumbering":false,"toc-showcode":false,"toc-showmarkdowntxt":false,"toc-showtags":false},"nbformat":4,"nbformat_minor":1}