{"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":"'''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) \n\n'''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","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2023-05-30T04:59:49.613299Z","iopub.execute_input":"2023-05-30T04:59:49.613759Z","iopub.status.idle":"2023-05-30T04:59:52.439376Z","shell.execute_reply.started":"2023-05-30T04:59:49.613726Z","shell.execute_reply":"2023-05-30T04:59:52.437893Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"'Create function for checking memory usage of dataframes'\ndef check_memory_usage(data):\n    \"\"\" Takes DataFrame as input and returns memory usage statistics. \n    \"\"\"\n    print ('')\n    print ('Checking memory usage statistics... ')\n    print(data.info(memory_usage=True))  ","metadata":{"execution":{"iopub.status.busy":"2023-05-03T02:12:40.676933Z","iopub.execute_input":"2023-05-03T02:12:40.678761Z","iopub.status.idle":"2023-05-03T02:12:40.686450Z","shell.execute_reply.started":"2023-05-03T02:12:40.678720Z","shell.execute_reply":"2023-05-03T02:12:40.684238Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"'''Check the files'''\nfrom subprocess import check_output\nprint(check_output([\"ls\", \"../input/grupo-bimbo-inventory-demand/\"]).decode(\"utf8\"))","metadata":{"execution":{"iopub.status.busy":"2023-05-03T02:12:41.561871Z","iopub.execute_input":"2023-05-03T02:12:41.562684Z","iopub.status.idle":"2023-05-03T02:12:41.599732Z","shell.execute_reply.started":"2023-05-03T02:12:41.562640Z","shell.execute_reply":"2023-05-03T02:12:41.598392Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## **Import in files as dataframe**","metadata":{}},{"cell_type":"code","source":"#town_table\ndtype = {'Agencia_ID': 'int16'} \ntown_state_df = pd.read_csv(\"../input/grupo-bimbo-inventory-demand/town_state.csv.zip\", dtype=dtype)\ntown_state_df['Town'] = town_state_df['Town'].str.upper()\ntown_state_df['State'] = town_state_df['State'].str.upper()\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:])).str.upper()\n\n\n#Product_table\ndtype = {'Producto_ID': 'int32'}\nproduct_df = pd.read_csv(\"../input/grupo-bimbo-inventory-demand/producto_tabla.csv.zip\", dtype=dtype)\nproduct_df['popular_name'] = product_df.NombreProducto.str.extract(r'^(.*?)(\\d*\\s\\d+(kg|Kg|g|G|ml| ml|p|pct|Granos|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['brand'] = product_df.NombreProducto.str.extract('^.+\\s(\\D+) \\d+$', expand=False)\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|pct|Granos|Reb)) ', expand=False)[0]\nproduct_df['Prom1'] =  product_df.property.str.extract(r'(Prom)', expand=False)\nproduct_df.loc[product_df['Prom1'].isnull(), 'Prom'] = 0\nproduct_df['Prom']=product_df['Prom'].fillna(1).astype(\"int8\")\nproduct_df=product_df.drop([\"Prom1\"],axis=1)\n\n\n# Cliente_table\ndtype = {'Cliente_ID': 'int32'}\nclient_df = pd.read_csv(\"../input/grupo-bimbo-inventory-demand/cliente_tabla.csv.zip\", dtype=dtype)\nclient_df=client_df.drop_duplicates(subset=['Cliente_ID'])\nclient_df=client_df.reset_index()\nclient_df=client_df.drop(['index'],axis=1)\nclient_df['NombreCliente'] = client_df['NombreCliente'].str.upper()\n\n#test\ndtype = {\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.zip\", dtype=dtype)\n\n\n#train\ndtype = {\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.zip\", dtype=dtype)\n\nsale_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\n","metadata":{"execution":{"iopub.status.busy":"2023-05-30T04:59:52.443444Z","iopub.execute_input":"2023-05-30T04:59:52.443933Z","iopub.status.idle":"2023-05-30T05:00:41.690206Z","shell.execute_reply.started":"2023-05-30T04:59:52.443894Z","shell.execute_reply":"2023-05-30T05:00:41.688948Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"product_df.head(100)","metadata":{"execution":{"iopub.status.busy":"2023-05-30T05:01:07.168434Z","iopub.execute_input":"2023-05-30T05:01:07.168819Z","iopub.status.idle":"2023-05-30T05:01:07.199550Z","shell.execute_reply.started":"2023-05-30T05:01:07.168783Z","shell.execute_reply":"2023-05-30T05:01:07.198083Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"'https://www.kaggle.com/code/abbysobh/classifying-client-type-using-client-names'\n\ndef filter_specific(vf2):\n    \n    # Known Large Company / Special Group Types\n    vf2['NombreCliente'] = vf2['NombreCliente'].str.replace('.*REMISION.*','Consignment')\n    vf2['NombreCliente'] = vf2['NombreCliente'].replace(['.*WAL MART.*','.*SAMS CLUB.*'],'Walmart', regex=True)\n    vf2['NombreCliente'] = vf2['NombreCliente'].str.replace('.*OXXO.*','Oxxo Store')\n    vf2['NombreCliente'] = vf2['NombreCliente'].str.replace('.*CONASUPO.*','Govt Store')\n    vf2['NombreCliente'] = vf2['NombreCliente'].str.replace('.*BIMBO.*','Bimbo Store')\n\n    \n\n    # General term search for a random assortment of words I picked from looking at\n    # their frequency of appearance in the data and common spanish words for these categories\n    vf2['NombreCliente'] = vf2['NombreCliente'].replace(['.*COLEG.*','.*UNIV.*','.*ESCU.*','.*INSTI.*',\\\n                                                        '.*PREPAR.*'],'School', regex=True)\n    vf2['NombreCliente'] = vf2['NombreCliente'].str.replace('.*PUESTO.*','Post')\n    vf2['NombreCliente'] = vf2['NombreCliente'].replace(['.*FARMA.*','.*HOSPITAL.*','.*CLINI.*'],'Hospital/Pharmacy', regex=True)\n    vf2['NombreCliente'] = vf2['NombreCliente'].replace(['.*CAFE.*','.*CREMERIA.*','.*DULCERIA.*',\\\n                                                        '.*REST.*','.*BURGER.*','.*TACO.*', '.*TORTA.*',\\\n                                                        '.*TAQUER.*','.*HOT DOG.*',\\\n                                                        '.*COMEDOR.*', '.*ERIA.*','.*BURGU.*'],'Eatery', regex=True)\n    vf2['NombreCliente'] = vf2['NombreCliente'].str.replace('.*SUPER.*','Supermarket')\n    vf2['NombreCliente'] = vf2['NombreCliente'].replace(['.*COMERCIAL.*','.*BODEGA.*','.*DEPOSITO.*',\\\n                                                            '.*ABARROTES.*','.*MERCADO.*','.*CAMBIO.*',\\\n                                                        '.*MARKET.*','.*MART .*','.*MINI .*',\\\n                                                        '.*PLAZA.*','.*MISC.*','.*ELEVEN.*','.*EXP.*',\\\n                                                         '.*SNACK.*', '.*PAPELERIA.*', '.*CARNICERIA.*',\\\n                                                         '.*LOCAL.*','.*COMODIN.*','.*PROVIDENCIA.*'\n                                                        ],'General Market/Mart'\\\n                                                       , regex=True)\n\n    vf2['NombreCliente'] = vf2['NombreCliente'].replace(['.*VERDU.*','.*FRUT.*'],'Fresh Market', regex=True)\n    vf2['NombreCliente'] = vf2['NombreCliente'].replace(['.*HOTEL.*','.*MOTEL.*'],'Hotel', regex=True)\n    \ndef filter_participle(vf2):\n    vf2['NombreCliente'] = vf2['NombreCliente'].replace([\n            '.*LA .*','.*EL .*','.*DE .*','.*LOS .*','.*DEL .*','.*Y .*', '.*SAN .*', '.*SANTA .*',\\\n            '.*AG .*','.*LAS .*','.*MI .*','.*MA .*', '.*II.*', '.*[0-9]+.*'\\\n    ],'Small Franchise', regex=True)\n    \ndef filter_remaining(vf2):\n    def function_word(data):\n        # Avoid the single-words created so far by checking for upper-case\n        if (data.isupper()) and (data != \"NO IDENTIFICADO\"): \n            return 'Individual'\n        else:\n            return data\n    vf2['NombreCliente'] = vf2['NombreCliente'].map(function_word)\n\nfilter_specific(client_df)\nfilter_participle(client_df)\nfilter_remaining(client_df)","metadata":{"execution":{"iopub.status.busy":"2023-05-30T05:01:21.338882Z","iopub.execute_input":"2023-05-30T05:01:21.339237Z","iopub.status.idle":"2023-05-30T05:03:01.898430Z","shell.execute_reply.started":"2023-05-30T05:01:21.339204Z","shell.execute_reply":"2023-05-30T05:03:01.897396Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**select top 10 best seller( 27.3% of all sales)**","metadata":{}},{"cell_type":"code","source":"client_df","metadata":{"execution":{"iopub.status.busy":"2023-05-30T05:03:21.611541Z","iopub.execute_input":"2023-05-30T05:03:21.611963Z","iopub.status.idle":"2023-05-30T05:03:21.624815Z","shell.execute_reply.started":"2023-05-30T05:03:21.611929Z","shell.execute_reply":"2023-05-30T05:03:21.623257Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# top10product=[2425,1284,1278,43285,36610,1240,1250,2233,1242,45143]\n# filt=test_df[\"Producto_ID\"].isin(top10product)\n# test_df=test_df.loc[filt,['id','Semana','Agencia_ID','Canal_ID','Ruta_SAK','Cliente_ID','Producto_ID']]\n# filt2=train_df[\"Producto_ID\"].isin(top10product)\n# train_df=train_df.loc[filt2,['Semana','Agencia_ID','Canal_ID','Ruta_SAK','Cliente_ID','Producto_ID','Venta_uni_hoy','Venta_hoy','Dev_uni_proxima','Dev_proxima','Demanda_uni_equil']]\n# filt3=product_df[\"Producto_ID\"].isin(top10product)\n# product_df=product_df.loc[filt3,['Producto_ID','NombreProducto','popular_name','property','brand','unit','pieces','Prom']]","metadata":{"execution":{"iopub.status.busy":"2023-04-25T08:41:40.168964Z","iopub.execute_input":"2023-04-25T08:41:40.169377Z","iopub.status.idle":"2023-04-25T08:41:41.864054Z","shell.execute_reply.started":"2023-04-25T08:41:40.169337Z","shell.execute_reply":"2023-04-25T08:41:41.863018Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Cleansing","metadata":{}},{"cell_type":"code","source":"#town\ntown_state_df.loc[498, 'Town_ID'] = 2561\ntown_state_df.loc[199, 'Town_ID'] = 3217\ntown_state_df.loc[311, 'Town_ID'] = 3218\n\n#product price\nsale_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()\n\ndel sale_prices_df\ndel return_prices_df\n\n#product\nproduct_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)]\n\n\nproduct_df.loc[117, 'popular_name'] = 'Donas'\nproduct_df.loc[117, 'property'] = 'Prom BIM'\nproduct_df.loc[117, 'brand'] = 'BIM'\nproduct_df.loc[117, 'unit'] = None\nproduct_df.loc[117, 'pieces'] = '6p'\nproduct_df.loc[117, 'Prom'] = 1\n\nproduct_df.loc[190, 'popular_name'] = 'Paletina para Cafe'\nproduct_df.loc[190, 'property'] = 'NES'\nproduct_df.loc[190, 'brand'] = 'NES'\nproduct_df.loc[190, 'unit'] = None\nproduct_df.loc[190, 'pieces'] = None\nproduct_df.loc[190, 'Prom'] = 0\n\nproduct_df.loc[381, 'popular_name'] = 'Camioncitos Bimbo'\nproduct_df.loc[381, 'property'] = 'BIM'\nproduct_df.loc[381, 'brand'] = 'BIM'\nproduct_df.loc[381, 'unit'] = None\nproduct_df.loc[381, 'pieces'] = None\nproduct_df.loc[381, 'Prom'] = 0\n\nproduct_df.loc[1152, 'popular_name'] = 'Burrito Vaporero FrijolChorizo'\nproduct_df.loc[1152, 'property'] = 'CU LON'\nproduct_df.loc[1152, 'brand'] = 'LON'\nproduct_df.loc[1152, 'unit'] = '90g'\nproduct_df.loc[1152, 'pieces'] = None\nproduct_df.loc[1152, 'Prom'] = 0\n\nproduct_df.loc[1677, 'popular_name'] = 'Tarima Twin Pack Thins Multig'\nproduct_df.loc[1677, 'property'] = 'CU ORO'\nproduct_df.loc[1677, 'brand'] = 'ORO'\nproduct_df.loc[1677, 'unit'] = None\nproduct_df.loc[1677, 'pieces'] = None\nproduct_df.loc[1677, 'Prom'] = 0\n\nproduct_df.loc[1888, 'popular_name'] = 'Deliciosas Chochochispas'\nproduct_df.loc[1888, 'property'] = 'Prom MTA LAR'\nproduct_df.loc[1888, 'brand'] = 'LAR'\nproduct_df.loc[1888, 'unit'] = '204g'\nproduct_df.loc[1888, 'pieces'] = None\nproduct_df.loc[1888, 'Prom'] = 1\n\nproduct_df.loc[1889, 'popular_name'] = 'Deliciosas Chochochispas'\nproduct_df.loc[1889, 'property'] = 'Prom LAR'\nproduct_df.loc[1889, 'brand'] = 'LAR'\nproduct_df.loc[1889, 'unit'] = '204g'\nproduct_df.loc[1889, 'pieces'] = None\nproduct_df.loc[1889, 'Prom'] = 1\n\nproduct_df.loc[2449, 'popular_name'] = 'Galleta Granel Classics Chocolate'\nproduct_df.loc[2449, 'property'] = 'GBI'\nproduct_df.loc[2449, 'brand'] = 'GBI'\nproduct_df.loc[2449, 'unit'] = None\nproduct_df.loc[2449, 'pieces'] = None\nproduct_df.loc[2449, 'Prom'] = 0\n\nproduct_df.loc[877, 'popular_name'] = 'Tortilla Hna Chihuahua'\nproduct_df.loc[877, 'brand'] = 'DH'\nproduct_df.loc[877, 'unit'] = '535g'\nproduct_df.loc[877, 'pieces'] = '10p'\nproduct_df.loc[877, 'Prom'] = 0\n\nproduct_df.loc[1585, 'popular_name'] = 'Principe Cho Bco MG'\nproduct_df.loc[1585, 'brand'] = 'MLA'\nproduct_df.loc[1585, 'unit'] = '110g'\nproduct_df.loc[1585, 'pieces'] = '10p'\nproduct_df.loc[1585, 'Prom'] = 1\n\nproduct_df.loc[1748, 'popular_name'] = 'Combo Salma mas Levite'\nproduct_df.loc[1748, 'brand'] = 'SAN'\nproduct_df.loc[1748, 'unit'] = '1360g'\nproduct_df.loc[1748, 'pieces'] = None\nproduct_df.loc[1748, 'Prom'] = 1\n\n#pieces\nproduct_df['pieces'] = product_df['pieces'].str.extract(r'(\\d+)(p|Reb)')[0]\n\n#weight\nproduct_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})\n\nproduct_df = pd.merge(product_df, prices_df, how='left')\n\ndel w","metadata":{"execution":{"iopub.status.busy":"2023-05-30T05:03:34.950186Z","iopub.execute_input":"2023-05-30T05:03:34.950598Z","iopub.status.idle":"2023-05-30T05:03:40.989129Z","shell.execute_reply.started":"2023-05-30T05:03:34.950560Z","shell.execute_reply":"2023-05-30T05:03:40.987860Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Imputing Missing data","metadata":{}},{"cell_type":"code","source":"#pieces\nproduct_df['pieces'] = product_df['pieces'].fillna(1)\n\n#use linear regression get weight&price\nfrom 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\n","metadata":{"execution":{"iopub.status.busy":"2023-05-30T05:04:21.725448Z","iopub.execute_input":"2023-05-30T05:04:21.725856Z","iopub.status.idle":"2023-05-30T05:04:21.758418Z","shell.execute_reply.started":"2023-05-30T05:04:21.725819Z","shell.execute_reply":"2023-05-30T05:04:21.756959Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Transforming Data Type","metadata":{}},{"cell_type":"code","source":"# product_df['Producto_ID'] = product_df['Producto_ID'].astype('int')\nproduct_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')\n\ntown_state_df['State'] = town_state_df['State'].astype('category')\ntrain_df['Canal_ID'] = train_df['Canal_ID'].astype('category')\ntest_df['Canal_ID'] = test_df['Canal_ID'].astype('category')","metadata":{"execution":{"iopub.status.busy":"2023-04-26T08:50:21.714899Z","iopub.execute_input":"2023-04-26T08:50:21.715282Z","iopub.status.idle":"2023-04-26T08:50:22.643163Z","shell.execute_reply.started":"2023-04-26T08:50:21.715249Z","shell.execute_reply":"2023-04-26T08:50:22.642079Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Drop Features","metadata":{}},{"cell_type":"code","source":"train_df.drop(['Venta_uni_hoy', 'Venta_hoy', 'Dev_uni_proxima', 'Dev_proxima'], axis=1, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2023-04-26T09:20:08.538655Z","iopub.status.idle":"2023-04-26T09:20:08.539231Z","shell.execute_reply.started":"2023-04-26T09:20:08.538914Z","shell.execute_reply":"2023-04-26T09:20:08.538940Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Merging Data","metadata":{}},{"cell_type":"code","source":"#merge town\ntrain_df = pd.merge(train_df, town_state_df[['Agencia_ID', 'Town_ID']], how='left')\n# test_df = pd.merge(test_df, town_state_df[['Agencia_ID', 'Town_ID']], how='left')\n\n#merge product\ntrain_df = pd.merge(\n    train_df,\n    product_df[[\n        'Producto_ID', 'popular_name', 'property','brand','Prom',\n        'pieces', 'weight', 'price'\n    ]], how='left')\n# test_df = pd.merge(\n#     test_df,\n#     product_df[[\n#         'Producto_ID', 'popular_name', 'property','brand','Prom',\n#         'pieces', 'weight', 'price'\n#     ]], how='left')\n\n#merge client\ntrain_df = pd.merge(train_df, client_df[['Cliente_ID','NombreCliente']],how='left')\n# test_df = pd.merge(test_df, client_df[['Cliente_ID','NombreCliente']], how='left')\n\n#get new variable weight/pieces\ntrain_df['wtpcs']=train_df['weight'].astype('float32')/train_df['pieces'].astype('float32')\n# test_df['wtpcs']=test_df['weight']/test_df['pieces']\ntrain_df.drop(['weight', 'pieces'], axis=1, inplace=True)\n# test_df.drop(['weight', 'pieces'], axis=1, inplace=True)\n\ndel product_df\ndel client_df\ndel town_state_df\ndel product_price_df","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df.head(20)","metadata":{"execution":{"iopub.status.busy":"2023-05-30T05:12:17.275035Z","iopub.execute_input":"2023-05-30T05:12:17.275431Z","iopub.status.idle":"2023-05-30T05:12:17.299451Z","shell.execute_reply.started":"2023-05-30T05:12:17.275397Z","shell.execute_reply":"2023-05-30T05:12:17.298033Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df['Semana'] = train_df['Semana'].astype('int8')\ntrain_df['Agencia_ID'] = train_df['Agencia_ID'].astype('int16')\ntrain_df['Ruta_SAK'] = train_df['Ruta_SAK'].astype('int16')\ntrain_df['Cliente_ID'] = train_df['Cliente_ID'].astype('int32')\ntrain_df['Producto_ID'] = train_df['Producto_ID'].astype('int32')\ntrain_df['Venta_uni_hoy'] = train_df['Venta_uni_hoy'].astype('int32')\ntrain_df['Venta_hoy'] = train_df['Venta_hoy'].astype('float32')\ntrain_df['Dev_uni_proxima'] = train_df['Dev_uni_proxima'].astype('int32')\ntrain_df['Dev_proxima'] = train_df['Dev_proxima'].astype('float32')\ntrain_df['Demanda_uni_equil'] = train_df['Demanda_uni_equil'].astype('int16')\n\ntrain_df['Town_ID'] = train_df['Town_ID'].astype('int16')\ntrain_df['Prom'] = train_df['Prom'].astype('int8')\ntrain_df['price'] = train_df['price'].astype('float16')\ntrain_df['wtpcs'] = train_df['wtpcs'].astype('float16')\n","metadata":{"execution":{"iopub.status.busy":"2023-05-30T05:18:12.506694Z","iopub.execute_input":"2023-05-30T05:18:12.507123Z","iopub.status.idle":"2023-05-30T05:18:14.179891Z","shell.execute_reply.started":"2023-05-30T05:18:12.507085Z","shell.execute_reply":"2023-05-30T05:18:14.178229Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df.to_csv('train_df2.0.csv')","metadata":{"execution":{"iopub.status.busy":"2023-05-30T05:18:36.389704Z","iopub.execute_input":"2023-05-30T05:18:36.390100Z","iopub.status.idle":"2023-05-30T05:25:26.107188Z","shell.execute_reply.started":"2023-05-30T05:18:36.390066Z","shell.execute_reply":"2023-05-30T05:25:26.105145Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df.drop('Semana', axis=1, inplace=True)\ntest_df.drop('Semana', axis=1, inplace=True)\n\nclient_med_s = train_df.groupby('Cliente_ID')['Demanda_uni_equil'].median().astype('int16')\nclient_med_s.name = 'client_med'\ntest_df = pd.merge(test_df, client_med_s.reset_index(), how='left')\ntrain_df = pd.merge(train_df, client_med_s.reset_index(), how='left')\ndel client_med_s\n\npopular_name_med_s = train_df.groupby('popular_name')['Demanda_uni_equil'].median().astype('int16')\npopular_name_med_s.name = 'popular_name_med'\ntest_df = pd.merge(test_df, popular_name_med_s.reset_index(), how='left')\ntrain_df = pd.merge(train_df, popular_name_med_s.reset_index(), how='left')\ndel popular_name_med_s\n\n\ntown_id_med_s = train_df.groupby('Town_ID')['Demanda_uni_equil'].median().astype('int16')\ntown_id_med_s.name = 'town_id_med'\ntest_df = pd.merge(test_df, town_id_med_s.reset_index(), how='left')\ntrain_df = pd.merge(train_df, town_id_med_s.reset_index(), how='left')\n\ndel town_id_med_s\n    \nruta_id_med_s = train_df.groupby('Ruta_SAK')['Demanda_uni_equil'].median().astype('int16')\nruta_id_med_s.name = 'ruta_id_med'\ntest_df = pd.merge(test_df, ruta_id_med_s.reset_index(), how='left')\ntrain_df = pd.merge(train_df, ruta_id_med_s.reset_index(), how='left')\ndel ruta_id_med_s\n\n\nbrand_med_s = train_df.groupby('brand')['Demanda_uni_equil'].median().astype('int16')\nbrand_med_s.name = 'brand_med'\ntest_df = pd.merge(test_df, brand_med_s.reset_index(), how='left')\ntrain_df = pd.merge(train_df, brand_med_s.reset_index(), how='left')\ndel brand_med_s\n\n\nproperty_med_s = train_df.groupby('property')['Demanda_uni_equil'].median().astype('int16')\nproperty_med_s.name = 'property_med'\ntest_df = pd.merge(test_df, property_med_s.reset_index(), how='left')\ntrain_df = pd.merge(train_df, property_med_s.reset_index(), how='left')\ndel property_med_s\n\nagencia_med_s = train_df.groupby('Agencia_ID')['Demanda_uni_equil'].median().astype('int32')\nagencia_med_s.name = 'agencia_med'\ntest_df = pd.merge(test_df, agencia_med_s.reset_index(), how='left')\ntrain_df = pd.merge(train_df, agencia_med_s.reset_index(), how='left')\ndel agencia_med_s\n\ncliente_type_med_s=train_df.groupby('NombreCliente')['Demanda_uni_equil'].median().astype('int32')\ncliente_type_med_s.name = 'cliente_type_med'\ntest_df = pd.merge(test_df, cliente_type_med_s.reset_index(), how='left')\ntrain_df = pd.merge(train_df, cliente_type_med_s.reset_index(), how='left')\ndel cliente_type_med_s","metadata":{"execution":{"iopub.status.busy":"2023-04-25T08:41:56.52649Z","iopub.execute_input":"2023-04-25T08:41:56.526793Z","iopub.status.idle":"2023-04-25T08:42:26.364808Z","shell.execute_reply.started":"2023-04-25T08:41:56.526765Z","shell.execute_reply":"2023-04-25T08:42:26.363726Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Imputing test missing data","metadata":{}},{"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['brand_med'] = test_df['brand_med'].fillna(test_df['brand_med'].mean())\ntest_df['ruta_id_med'] = test_df['ruta_id_med'].fillna(test_df['ruta_id_med'].mean())\ntest_df['property_med'] = test_df['property_med'].fillna(test_df['property_med'].mean())\ntest_df['agencia_med'] = test_df['agencia_med'].fillna(test_df['agencia_med'].mean())\ntest_df['cliente_type_med'] = test_df['cliente_type_med'].fillna(test_df['cliente_type_med'].mean())","metadata":{"execution":{"iopub.status.busy":"2023-04-25T08:42:26.366157Z","iopub.execute_input":"2023-04-25T08:42:26.367215Z","iopub.status.idle":"2023-04-25T08:42:26.471715Z","shell.execute_reply.started":"2023-04-25T08:42:26.367156Z","shell.execute_reply":"2023-04-25T08:42:26.470665Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# New Variables","metadata":{}},{"cell_type":"code","source":"# Top 10 client\ntops=[653378,653039,652850,1216931,653124,2502084,653037,1200400,5903732,19260]\ntrain_df['top10'] = 0\ntest_df['top10']=0\ntrain_df.loc[train_df['Cliente_ID'].isin(tops), 'top10'] = 1\ntest_df.loc[test_df['Cliente_ID'].isin(tops), 'top10'] = 1\n\n\n#sum of prior client's demand\nclientee= train_df.groupby(by=['Cliente_ID',\"Producto_ID\"]).sum()[['Demanda_uni_equil']]\ncl=clientee['Demanda_uni_equil']\ncl.name = 'sum of demand'\ntrain_df = pd.merge(train_df,cl.reset_index(), how='left')\ntest_df = pd.merge(test_df,cl.reset_index(), how='left')\ntest_df['sum of demand'] = test_df['sum of demand'].fillna(test_df['sum of demand'].mean())\ndel clientee\ndel cl\n\n#count of client by town\ntown_client= train_df.groupby(by=[\"Town_ID\"]).count()[['Cliente_ID']]\ntc=town_client['Cliente_ID']\ntc.name = 'town_client'\ntrain_df = pd.merge(train_df,tc.reset_index(), how='left')\ntest_df = pd.merge(test_df,tc.reset_index(), how='left')\ntest_df['town_client'] = test_df['town_client'].fillna(test_df['town_client'].mean())\ndel town_client\ndel tc","metadata":{"execution":{"iopub.status.busy":"2023-04-25T08:42:26.473433Z","iopub.execute_input":"2023-04-25T08:42:26.473824Z","iopub.status.idle":"2023-04-25T08:42:53.693436Z","shell.execute_reply.started":"2023-04-25T08:42:26.473786Z","shell.execute_reply":"2023-04-25T08:42:53.692374Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Dropping Features","metadata":{}},{"cell_type":"code","source":"train_df.drop(\n    ['Agencia_ID','Ruta_SAK', 'Cliente_ID', 'Producto_ID', 'Town_ID', 'popular_name', 'property','brand'],\n    axis=1, inplace=True)\ntest_df.drop(\n    ['Agencia_ID','Ruta_SAK', 'Cliente_ID', 'Producto_ID', 'Town_ID', 'popular_name', 'property','brand'],\n    axis=1, inplace=True)\n\n#transform data type\ntrain_df['top10'] = train_df['top10'].astype('int8')\ntrain_df['sum of demand'] = train_df['sum of demand'].astype('int32')\ntrain_df['town_client'] = train_df['town_client'].astype('int32')\ntrain_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')\ntrain_df['property_med'] = train_df['property_med'].astype('float32')\ntrain_df['agencia_med'] = train_df['agencia_med'].astype('float32')\ntrain_df['cliente_type_med'] = train_df['cliente_type_med'].astype('float32')\ntrain_df['wtpcs']=train_df['wtpcs'].astype('float16')\ntrain_df['NombreCliente'] = train_df['NombreCliente'].astype('category')\n\ntest_df['top10'] = test_df['top10'].astype('int8')\ntest_df['sum of demand'] = test_df['sum of demand'].astype('int32')\ntest_df['town_client'] = test_df['town_client'].astype('int32')\ntest_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')\ntest_df['property_med'] = test_df['property_med'].astype('float32')\ntest_df['agencia_med'] = test_df['agencia_med'].astype('float32')\ntest_df['cliente_type_med'] = test_df['cliente_type_med'].astype('float32')\ntest_df['wtpcs']=test_df['wtpcs'].astype('float16')\ntest_df['NombreCliente'] = test_df['NombreCliente'].astype('category')\n\n\n","metadata":{"execution":{"iopub.status.busy":"2023-04-25T08:42:53.694852Z","iopub.execute_input":"2023-04-25T08:42:53.695228Z","iopub.status.idle":"2023-04-25T08:42:54.983304Z","shell.execute_reply.started":"2023-04-25T08:42:53.695175Z","shell.execute_reply":"2023-04-25T08:42:54.982254Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Drawing matrix and importance","metadata":{}},{"cell_type":"code","source":"# '''correlation matrix'''\n# plt.subplots(figsize=(20, 16))\n# k = 20 #number of variables for heatmap\n# corrmat = sample_train_df.corr()\n# cols = corrmat.nlargest(k, 'log_target')['log_target'].index\n\n# cm = np.corrcoef(sample_train_df[cols].values.T)\n# hm = 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)\n# plt.show()","metadata":{"execution":{"iopub.status.busy":"2023-04-25T08:42:55.011769Z","iopub.execute_input":"2023-04-25T08:42:55.012436Z","iopub.status.idle":"2023-04-25T08:42:55.01764Z","shell.execute_reply.started":"2023-04-25T08:42:55.012395Z","shell.execute_reply":"2023-04-25T08:42:55.016409Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# import lightgbm as lgb\n# model_lgb = lgb.LGBMRegressor(num_leaves=1000,\n#                               max_depth=5,\n#                               learning_rate=0.1,\n#                               random_state=2)\n# model_lgb.fit(sample_train_df.drop(['Demanda_uni_equil', 'log_target'], axis=1), sample_train_df['log_target'])","metadata":{"execution":{"iopub.status.busy":"2023-04-25T08:42:55.019132Z","iopub.execute_input":"2023-04-25T08:42:55.02004Z","iopub.status.idle":"2023-04-25T08:42:55.028388Z","shell.execute_reply.started":"2023-04-25T08:42:55.020003Z","shell.execute_reply":"2023-04-25T08:42:55.027412Z"},"trusted":true},"execution_count":null,"outputs":[]},{"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)\n# df[df.importance > 10]","metadata":{"execution":{"iopub.status.busy":"2023-04-25T08:42:55.029833Z","iopub.execute_input":"2023-04-25T08:42:55.031041Z","iopub.status.idle":"2023-04-25T08:42:55.037947Z","shell.execute_reply.started":"2023-04-25T08:42:55.031001Z","shell.execute_reply":"2023-04-25T08:42:55.036268Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# corrmat[\"log_target\"].sort_values(ascending = False)","metadata":{"execution":{"iopub.status.busy":"2023-04-25T08:42:55.039682Z","iopub.execute_input":"2023-04-25T08:42:55.040238Z","iopub.status.idle":"2023-04-25T08:42:55.046105Z","shell.execute_reply.started":"2023-04-25T08:42:55.040183Z","shell.execute_reply":"2023-04-25T08:42:55.044883Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Defining Evaluation","metadata":{}},{"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\n\n'''KFold for cross validation'''\nkf = KFold(n_splits=5, shuffle=True, random_state=0)\n\n'''Define the validation function'''\ndef rmsle_cv(model, X, y, cv=kf):\n    rmsle=np.sqrt(-cross_val_score(model,X, y,scoring=\"neg_mean_squared_log_error\",cv=cv))\n    return(rmsle)\n\n'''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    print(\"Folds of rmsle:\")\n    print(cv)\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}\n\n'''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","metadata":{"execution":{"iopub.status.busy":"2023-04-25T08:42:55.047527Z","iopub.execute_input":"2023-04-25T08:42:55.04849Z","iopub.status.idle":"2023-04-25T08:42:55.060773Z","shell.execute_reply.started":"2023-04-25T08:42:55.04845Z","shell.execute_reply":"2023-04-25T08:42:55.060092Z"},"trusted":true},"execution_count":null,"outputs":[]},{"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'])","metadata":{"execution":{"iopub.status.busy":"2023-04-25T08:42:55.062011Z","iopub.execute_input":"2023-04-25T08:42:55.063012Z","iopub.status.idle":"2023-04-25T08:42:55.842247Z","shell.execute_reply.started":"2023-04-25T08:42:55.062969Z","shell.execute_reply":"2023-04-25T08:42:55.841213Z"},"trusted":true},"execution_count":null,"outputs":[]},{"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)\nprint(train_X)\nprint(test_X)\n'''Transform categorical features to dummy variables'''\ntrain_X = pd.get_dummies(train_X)\ntest_X = pd.get_dummies(test_X)","metadata":{"execution":{"iopub.status.busy":"2023-04-25T08:42:55.843674Z","iopub.execute_input":"2023-04-25T08:42:55.844716Z","iopub.status.idle":"2023-04-25T08:42:57.204559Z","shell.execute_reply.started":"2023-04-25T08:42:55.844665Z","shell.execute_reply":"2023-04-25T08:42:57.203501Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(train_X)\nprint(test_X)","metadata":{"execution":{"iopub.status.busy":"2023-04-25T08:42:57.207274Z","iopub.execute_input":"2023-04-25T08:42:57.207923Z","iopub.status.idle":"2023-04-25T08:42:57.229096Z","shell.execute_reply.started":"2023-04-25T08:42:57.207883Z","shell.execute_reply":"2023-04-25T08:42:57.227109Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sample_train_df['Demanda_uni_equil'] = np.expm1(sample_train_df['log_target']).astype('int32')","metadata":{"execution":{"iopub.status.busy":"2023-04-25T08:42:57.230951Z","iopub.execute_input":"2023-04-25T08:42:57.231375Z","iopub.status.idle":"2023-04-25T08:42:57.237752Z","shell.execute_reply.started":"2023-04-25T08:42:57.231336Z","shell.execute_reply":"2023-04-25T08:42:57.23659Z"},"trusted":true},"execution_count":null,"outputs":[]},{"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)","metadata":{"execution":{"iopub.status.busy":"2023-04-25T08:42:57.239435Z","iopub.execute_input":"2023-04-25T08:42:57.240112Z","iopub.status.idle":"2023-04-25T08:42:57.255635Z","shell.execute_reply.started":"2023-04-25T08:42:57.240072Z","shell.execute_reply":"2023-04-25T08:42:57.254349Z"},"trusted":true},"execution_count":null,"outputs":[]},{"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)","metadata":{"execution":{"iopub.status.busy":"2023-04-25T08:42:57.260441Z","iopub.execute_input":"2023-04-25T08:42:57.260727Z","iopub.status.idle":"2023-04-25T08:43:09.04014Z","shell.execute_reply.started":"2023-04-25T08:42:57.260699Z","shell.execute_reply":"2023-04-25T08:43:09.039255Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}