{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"import os\nimport numpy as np\nimport pandas as pd\nfrom sklearn.preprocessing import LabelEncoder","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:12:32.758214Z","iopub.execute_input":"2023-05-13T03:12:32.759277Z","iopub.status.idle":"2023-05-13T03:12:34.293260Z","shell.execute_reply.started":"2023-05-13T03:12:32.759224Z","shell.execute_reply":"2023-05-13T03:12:34.291240Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def load_data(): \n    \"\"\" Loads and returns competition data, including modified products data.\n    \"\"\"\n    # Set work directory\n    #os.chdir('D:/OneDrive/Documents/Kaggle/Grupo Bimbo Inventory Demand/data')      \n    \n    # Load data efficiently to use less RAM\n    train_types = {\n                    'Semana': np.uint8, 'Agencia_ID': np.uint16, 'Canal_ID': np.uint8\n                    ,'Ruta_SAK': np.uint16, 'Cliente_ID': np.uint32, 'Producto_ID': np.uint16\n                    ,'Demanda_uni_equil': np.uint16, 'Venta_uni_hoy': np.uint16, 'Venta_hoy': 'float32'\n                    ,'Dev_uni_proxima': np.uint32,'Dev_proxima': 'float32'\n                    }   \n         \n    test_types =  {\n                    'Semana': np.uint8, 'Agencia_ID': np.uint16, 'Canal_ID': np.uint8\n                    ,'Ruta_SAK': np.uint16, 'Cliente_ID': np.uint32, 'Producto_ID': np.uint16\n                    }\n\n    \n    train = pd.read_csv(\"../input/grupo-bimbo-inventory-demand/train.csv.zip\", sep = \",\", usecols = train_types.keys(), dtype = train_types) \n    test = pd.read_csv(\"../input/grupo-bimbo-inventory-demand/test.csv.zip\", sep = \",\", usecols = test_types.keys(), dtype = test_types)\n\n        \n    # Return data\n    return train, test","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:12:34.296538Z","iopub.execute_input":"2023-05-13T03:12:34.297101Z","iopub.status.idle":"2023-05-13T03:12:34.311445Z","shell.execute_reply.started":"2023-05-13T03:12:34.297054Z","shell.execute_reply":"2023-05-13T03:12:34.309826Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train, test = load_data()","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:12:34.326003Z","iopub.execute_input":"2023-05-13T03:12:34.326510Z","iopub.status.idle":"2023-05-13T03:14:12.851719Z","shell.execute_reply.started":"2023-05-13T03:12:34.326466Z","shell.execute_reply":"2023-05-13T03:14:12.850271Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# price\n\nsale_price_sr = (train.Venta_hoy / train.Venta_uni_hoy)\nreturn_price_sr = (train.Dev_proxima / train.Dev_uni_proxima)\nproduct_price_df = pd.DataFrame({'Producto_ID': train.Producto_ID, 'sale_price': sale_price_sr, 'return_price': return_price_sr})\n\ndel sale_price_sr\ndel return_price_sr\n\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()","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:14:12.853283Z","iopub.execute_input":"2023-05-13T03:14:12.853750Z","iopub.status.idle":"2023-05-13T03:14:21.639043Z","shell.execute_reply.started":"2023-05-13T03:14:12.853706Z","shell.execute_reply":"2023-05-13T03:14:21.637816Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def add_time_series_features(train, test):\n    \"\"\" Takes train and test data as inputs. \n        Adds lagged adjusted demand variables to the data periods t-2, t-3, t-4, and t-5.\n        Returns modified data. \n    \"\"\"\n    # Add lagged time series variables to train/test\n    # Groups by (Cliente_ID, Producto_ID)\n    for i in range(1, 6):\n        # Add tminus to train\n        columns = ['Semana', 'Cliente_ID', 'Producto_ID', 'Demanda_uni_equil']       \n        train_tminus = train.loc[:, columns]\n        train_tminus = train_tminus.rename(columns = {'Demanda_uni_equil': 'Lag' + str(i)})\n        train_tminus['Semana'] = train_tminus['Semana'] + i\n        columns.remove('Demanda_uni_equil')\n        train_tminus = pd.DataFrame({'Lag' + str(i) : train_tminus.groupby(columns)['Lag' + str(i)].mean()}).reset_index()         \n        \n        train = train.merge(train_tminus, how = 'left', on = ['Semana', 'Cliente_ID', 'Producto_ID'])   \n        train['Lag' + str(i)] = train['Lag' + str(i)].astype(np.float16)\n                \n        # Add tminus for Semana == 10\n        columns = ['Semana', 'Cliente_ID', 'Producto_ID', 'Lag' + str(i)]\n        test = test.merge(train_tminus.loc[train_tminus['Semana'] == 10, columns], how = 'left', on = ['Semana', 'Cliente_ID', 'Producto_ID'])\n        test['Lag' + str(i)] = test['Lag' + str(i)].astype(np.float16)     \n    \n        # Add tminus for Semana == 11\n        if(i != 1):\n            test = test.merge(train_tminus.loc[train_tminus['Semana'] == 11, columns], how = 'left', on = ['Semana', 'Cliente_ID', 'Producto_ID']) \n            test = test.rename(columns = {'Lag' + str(i) + '_x': 'Lag' + str(i)})\n            test.loc[test['Semana'] == 11, 'Lag' + str(i)] = test.loc[test['Semana'] == 11, 'Lag' + str(i) + '_y']\n            test = test.drop('Lag' + str(i) + '_y', axis = 1)\n            test['Lag' + str(i)] = test['Lag' + str(i)].astype(np.float16)\n        \n        # Replace null values with zeros\n        train.loc[train['Lag' + str(i)].isnull(), 'Lag' + str(i)] = 0\n        test.loc[test['Lag' + str(i)].isnull(), 'Lag' + str(i)] = 0    \n            \n    return train, test  ","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:14:21.640722Z","iopub.execute_input":"2023-05-13T03:14:21.641095Z","iopub.status.idle":"2023-05-13T03:14:21.655888Z","shell.execute_reply.started":"2023-05-13T03:14:21.641064Z","shell.execute_reply":"2023-05-13T03:14:21.654951Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train, test = add_time_series_features(train, test)","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:14:21.657071Z","iopub.execute_input":"2023-05-13T03:14:21.657839Z","iopub.status.idle":"2023-05-13T03:28:51.712560Z","shell.execute_reply.started":"2023-05-13T03:14:21.657809Z","shell.execute_reply":"2023-05-13T03:28:51.710487Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train['tst'] = 0\ntest['tst'] = 1\n\ndata2 = pd.concat([train, test], axis=0, ignore_index=True)\nprint(data2)\n#del train\n#del test","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:28:51.714975Z","iopub.execute_input":"2023-05-13T03:28:51.715351Z","iopub.status.idle":"2023-05-13T03:28:53.635330Z","shell.execute_reply.started":"2023-05-13T03:28:51.715308Z","shell.execute_reply":"2023-05-13T03:28:53.633933Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df = data2[data2[\"tst\"] == 0]","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:28:53.641704Z","iopub.execute_input":"2023-05-13T03:28:53.642223Z","iopub.status.idle":"2023-05-13T03:29:06.998904Z","shell.execute_reply.started":"2023-05-13T03:28:53.642179Z","shell.execute_reply":"2023-05-13T03:29:06.997840Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_df = data2[data2[\"tst\"] == 1]\n'''\n# Imputing Test Missing Data\ntest_df['ruta_id_med'] = test_df['ruta_id_med'].fillna(test_df['ruta_id_med'].mean()).astype('int16')\ntest_df['client_med'] = test_df['client_med'].fillna(test_df['client_med'].mean()).astype('int16')\ntest_df['product_id_med'] = test_df['product_id_med'].fillna(test_df['product_id_med'].mean()).astype('int16')\n'''\ndel data2","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:07.000362Z","iopub.execute_input":"2023-05-13T03:29:07.000786Z","iopub.status.idle":"2023-05-13T03:29:07.827064Z","shell.execute_reply.started":"2023-05-13T03:29:07.000749Z","shell.execute_reply":"2023-05-13T03:29:07.825867Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# product \ndtype = {'Producto_ID': 'int32'}\nproduct_df = pd.read_csv(\"../input/grupo-bimbo-inventory-demand/producto_tabla.csv.zip\", 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]","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:07.828539Z","iopub.execute_input":"2023-05-13T03:29:07.829466Z","iopub.status.idle":"2023-05-13T03:29:07.920107Z","shell.execute_reply.started":"2023-05-13T03:29:07.829431Z","shell.execute_reply":"2023-05-13T03:29:07.919246Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# product需存在於 train 或 test\nproduct_df['in_train'] = 0\nproduct_df['in_test'] = 0\nproduct_df.loc[product_df['Producto_ID'].isin(train['Producto_ID'].unique()), 'in_test'] = 1\nproduct_df.loc[product_df['Producto_ID'].isin(test['Producto_ID'].unique()), 'in_train'] = 1\nproduct_df = product_df[(product_df['in_test'] == 1) | (product_df['in_train'] == 1)]\n\nproduct_df.head()","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:07.921192Z","iopub.execute_input":"2023-05-13T03:29:07.922112Z","iopub.status.idle":"2023-05-13T03:29:08.362869Z","shell.execute_reply.started":"2023-05-13T03:29:07.922079Z","shell.execute_reply":"2023-05-13T03:29:08.361754Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#手動補植\nproduct_df['pieces'] = product_df['pieces'].str.extract(r'(\\d+)(p|Reb)')[0]\nproduct_df['pieces'] = product_df['pieces'].fillna(1)","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.364300Z","iopub.execute_input":"2023-05-13T03:29:08.364646Z","iopub.status.idle":"2023-05-13T03:29:08.376706Z","shell.execute_reply.started":"2023-05-13T03:29:08.364618Z","shell.execute_reply":"2023-05-13T03:29:08.375475Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#手動補植\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})","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.378422Z","iopub.execute_input":"2023-05-13T03:29:08.378772Z","iopub.status.idle":"2023-05-13T03:29:08.400472Z","shell.execute_reply.started":"2023-05-13T03:29:08.378743Z","shell.execute_reply":"2023-05-13T03:29:08.399188Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"product_df.head()","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.402087Z","iopub.execute_input":"2023-05-13T03:29:08.402400Z","iopub.status.idle":"2023-05-13T03:29:08.426005Z","shell.execute_reply.started":"2023-05-13T03:29:08.402374Z","shell.execute_reply":"2023-05-13T03:29:08.424730Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"product_df[product_df['property'].isnull()]","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.427488Z","iopub.execute_input":"2023-05-13T03:29:08.427994Z","iopub.status.idle":"2023-05-13T03:29:08.451218Z","shell.execute_reply.started":"2023-05-13T03:29:08.427962Z","shell.execute_reply":"2023-05-13T03:29:08.450016Z"},"trusted":true},"execution_count":null,"outputs":[]},{"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'] = '6'\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","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.453316Z","iopub.execute_input":"2023-05-13T03:29:08.453776Z","iopub.status.idle":"2023-05-13T03:29:08.475064Z","shell.execute_reply.started":"2023-05-13T03:29:08.453743Z","shell.execute_reply":"2023-05-13T03:29:08.473421Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#print(product_df[(product_df['Producto_ID']==1289)])","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.477158Z","iopub.execute_input":"2023-05-13T03:29:08.477981Z","iopub.status.idle":"2023-05-13T03:29:08.482251Z","shell.execute_reply.started":"2023-05-13T03:29:08.477947Z","shell.execute_reply":"2023-05-13T03:29:08.480980Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"product_df[product_df['popular_name'].isnull()]","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.484069Z","iopub.execute_input":"2023-05-13T03:29:08.485208Z","iopub.status.idle":"2023-05-13T03:29:08.507227Z","shell.execute_reply.started":"2023-05-13T03:29:08.485175Z","shell.execute_reply":"2023-05-13T03:29:08.506294Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"product_df.loc[877, 'popular_name'] = 'Tortilla Hna Chihuahua'\nproduct_df.loc[877, 'unit'] = '535g'\nproduct_df.loc[877, 'pieces'] = '10'\n\nproduct_df.loc[1585, 'popular_name'] = 'Principe Cho Bco MG'\nproduct_df.loc[1585, 'unit'] = '110g'\nproduct_df.loc[1585, 'pieces'] = '10'\n\nproduct_df.loc[1748, 'popular_name'] = 'Combo Salma mas Levite'\nproduct_df.loc[1748, 'unit'] = '1360g'\nproduct_df.loc[1748, 'pieces'] = None","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.508785Z","iopub.execute_input":"2023-05-13T03:29:08.509735Z","iopub.status.idle":"2023-05-13T03:29:08.522346Z","shell.execute_reply.started":"2023-05-13T03:29:08.509694Z","shell.execute_reply":"2023-05-13T03:29:08.521152Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#product_df.head()","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.524025Z","iopub.execute_input":"2023-05-13T03:29:08.524574Z","iopub.status.idle":"2023-05-13T03:29:08.537477Z","shell.execute_reply.started":"2023-05-13T03:29:08.524546Z","shell.execute_reply":"2023-05-13T03:29:08.536427Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#合併product & price\nproduct_df = pd.merge(product_df, prices_df, how='left')\nproduct_df.head()","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.538693Z","iopub.execute_input":"2023-05-13T03:29:08.539401Z","iopub.status.idle":"2023-05-13T03:29:08.570610Z","shell.execute_reply.started":"2023-05-13T03:29:08.539369Z","shell.execute_reply":"2023-05-13T03:29:08.569460Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"product_df['pieces'] = product_df['pieces'].fillna(1)","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.572537Z","iopub.execute_input":"2023-05-13T03:29:08.572873Z","iopub.status.idle":"2023-05-13T03:29:08.580244Z","shell.execute_reply.started":"2023-05-13T03:29:08.572846Z","shell.execute_reply":"2023-05-13T03:29:08.578911Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# weight補值\nproduct_df[product_df['weight'].isnull()]['price'].max()","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.582315Z","iopub.execute_input":"2023-05-13T03:29:08.582761Z","iopub.status.idle":"2023-05-13T03:29:08.597102Z","shell.execute_reply.started":"2023-05-13T03:29:08.582722Z","shell.execute_reply":"2023-05-13T03:29:08.595667Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"product_df[product_df['price'].isnull()]['weight'].max()","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.599216Z","iopub.execute_input":"2023-05-13T03:29:08.599630Z","iopub.status.idle":"2023-05-13T03:29:08.611542Z","shell.execute_reply.started":"2023-05-13T03:29:08.599598Z","shell.execute_reply":"2023-05-13T03:29:08.610421Z"},"trusted":true},"execution_count":null,"outputs":[]},{"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","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.613716Z","iopub.execute_input":"2023-05-13T03:29:08.614240Z","iopub.status.idle":"2023-05-13T03:29:08.906169Z","shell.execute_reply.started":"2023-05-13T03:29:08.614198Z","shell.execute_reply":"2023-05-13T03:29:08.904974Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#product_df[product_df['weight'].isnull()]","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.912548Z","iopub.execute_input":"2023-05-13T03:29:08.912928Z","iopub.status.idle":"2023-05-13T03:29:08.918760Z","shell.execute_reply.started":"2023-05-13T03:29:08.912885Z","shell.execute_reply":"2023-05-13T03:29:08.917314Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"(product_df.drop(['unit'], axis=1).isnull().sum() == 0).all()","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.920432Z","iopub.execute_input":"2023-05-13T03:29:08.920851Z","iopub.status.idle":"2023-05-13T03:29:08.939887Z","shell.execute_reply.started":"2023-05-13T03:29:08.920820Z","shell.execute_reply":"2023-05-13T03:29:08.938542Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# cp value\ncp_df = product_df[['Producto_ID', 'weight', 'price']].copy()\ncp_df['cp_value'] = cp_df['weight'] / cp_df['price']\nproduct_df = pd.merge(product_df, cp_df[['Producto_ID', 'cp_value']], how='left')","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.941538Z","iopub.execute_input":"2023-05-13T03:29:08.941867Z","iopub.status.idle":"2023-05-13T03:29:08.959663Z","shell.execute_reply.started":"2023-05-13T03:29:08.941841Z","shell.execute_reply":"2023-05-13T03:29:08.958337Z"},"trusted":true},"execution_count":null,"outputs":[]},{"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')\nproduct_df['cp_value'] = product_df['cp_value'].astype('float32')","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.961482Z","iopub.execute_input":"2023-05-13T03:29:08.961811Z","iopub.status.idle":"2023-05-13T03:29:08.973646Z","shell.execute_reply.started":"2023-05-13T03:29:08.961785Z","shell.execute_reply":"2023-05-13T03:29:08.972333Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"product_df.drop(['in_train','in_test','weight','NombreProducto','popular_name','property','unit','pieces','price'], axis=1, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.975111Z","iopub.execute_input":"2023-05-13T03:29:08.975481Z","iopub.status.idle":"2023-05-13T03:29:08.989717Z","shell.execute_reply.started":"2023-05-13T03:29:08.975451Z","shell.execute_reply":"2023-05-13T03:29:08.988544Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"product_df.head()","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:08.991448Z","iopub.execute_input":"2023-05-13T03:29:08.992497Z","iopub.status.idle":"2023-05-13T03:29:09.008525Z","shell.execute_reply.started":"2023-05-13T03:29:08.992465Z","shell.execute_reply":"2023-05-13T03:29:09.007585Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(product_df[(product_df['Producto_ID']==1277)])","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:35:10.684296Z","iopub.execute_input":"2023-05-13T03:35:10.684819Z","iopub.status.idle":"2023-05-13T03:35:10.695762Z","shell.execute_reply.started":"2023-05-13T03:35:10.684780Z","shell.execute_reply":"2023-05-13T03:35:10.694017Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df = train_df.merge(product_df, how = 'left', on = ['Producto_ID'])\ntest_df = test_df.merge(product_df, how = 'left', on = ['Producto_ID'])","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:32:47.463700Z","iopub.execute_input":"2023-05-13T03:32:47.464207Z","iopub.status.idle":"2023-05-13T03:32:56.432756Z","shell.execute_reply.started":"2023-05-13T03:32:47.464173Z","shell.execute_reply":"2023-05-13T03:32:56.431308Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df.tail()","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:34:24.155257Z","iopub.execute_input":"2023-05-13T03:34:24.155651Z","iopub.status.idle":"2023-05-13T03:34:24.176641Z","shell.execute_reply.started":"2023-05-13T03:34:24.155623Z","shell.execute_reply":"2023-05-13T03:34:24.175273Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\ntrain_df.drop(['Agencia_ID','Canal_ID','Ruta_SAK','Venta_uni_hoy', 'Venta_hoy',\n            'Dev_uni_proxima', 'Dev_proxima','tst'], axis=1, inplace=True)\n\ntrain_df.head()","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:30.011541Z","iopub.execute_input":"2023-05-13T03:29:30.011941Z","iopub.status.idle":"2023-05-13T03:29:36.337784Z","shell.execute_reply.started":"2023-05-13T03:29:30.011888Z","shell.execute_reply":"2023-05-13T03:29:36.336579Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#test_df.drop(['Agencia_ID','Canal_ID','Ruta_SAK','tst'], axis=1, inplace=True)\ntest_df.head()","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:34:56.463887Z","iopub.execute_input":"2023-05-13T03:34:56.464994Z","iopub.status.idle":"2023-05-13T03:34:56.484115Z","shell.execute_reply.started":"2023-05-13T03:34:56.464953Z","shell.execute_reply":"2023-05-13T03:34:56.482786Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"'''\ndef encode_labels(train, test, threshold): \n    \"\"\" Converts categorical features to integers in train and test data.\n        Groups values in categorical features appearing less than threshold into a separate category.\n        Returns train and test data.\n    \"\"\"\n    \n    # Identify columns for label encoding\n    columns = list(train.columns[train.dtypes == 'object'])\n       \n    # Return data if no columns identified   \n    if(len(columns) == 0):\n        return train, test\n       \n    # Transform columns\n    for column in columns: \n        \n        # Filter data\n        classes = train[column].unique()\n        counts = train[column].value_counts()\n        counts_classes = counts.index[counts <= threshold]\n        \n        # Set classes under threshold to 'identific'\n        train.loc[train[column].isin(counts_classes), column] = 'identific'        \n        test.loc[test[column].isin(counts_classes), column] = 'identific'          \n        \n        # Classes in test not in train sent to 'identific'\n        test.loc[test[column].isin(classes) == False, column] = 'identific'\n        \n        # Perform label encoding\n        le = LabelEncoder()\n        le.fit(train[column])\n        train[column] = le.transform(train[column]).astype(np.uint32)\n        test[column] = le.transform(test[column]).astype(np.uint32)\n        \n    return train, test\n'''\n# train, test = encode_labels(train, test, threshold = 5)","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:36.525798Z","iopub.execute_input":"2023-05-13T03:29:36.526290Z","iopub.status.idle":"2023-05-13T03:29:36.537433Z","shell.execute_reply.started":"2023-05-13T03:29:36.526249Z","shell.execute_reply":"2023-05-13T03:29:36.536066Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\nprint (\"Shape Before (train): \" + str(train.shape))\ntrain_df = train_df[train_df['Semana'] > 5]  \ntrain_df = train_df.reset_index(drop=True)\nprint (\"Shape After (train): \" + str(train_df.shape))\n","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:29:36.539231Z","iopub.execute_input":"2023-05-13T03:29:36.539660Z","iopub.status.idle":"2023-05-13T03:29:41.537035Z","shell.execute_reply.started":"2023-05-13T03:29:36.539629Z","shell.execute_reply":"2023-05-13T03:29:41.535798Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df.head()","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:36:41.131076Z","iopub.execute_input":"2023-05-13T03:36:41.131520Z","iopub.status.idle":"2023-05-13T03:36:41.150739Z","shell.execute_reply.started":"2023-05-13T03:36:41.131488Z","shell.execute_reply":"2023-05-13T03:36:41.149564Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_df.head()","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:35:26.033563Z","iopub.execute_input":"2023-05-13T03:35:26.033964Z","iopub.status.idle":"2023-05-13T03:35:26.053370Z","shell.execute_reply.started":"2023-05-13T03:35:26.033936Z","shell.execute_reply":"2023-05-13T03:35:26.051920Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\ncolumns = [\n            'Semana', 'Cliente_ID', 'Producto_ID','Lag1', 'Lag2'\n            , 'Lag3', 'Lag4', 'Lag5','cp_value'\n          ]\n\ntrain_df = train_df.reindex(columns = columns)\n#columns.remove('Demanda_uni_equil')\ntest_df = test_df.reindex(columns = columns) \n","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:37:16.407511Z","iopub.execute_input":"2023-05-13T03:37:16.408042Z","iopub.status.idle":"2023-05-13T03:37:17.970257Z","shell.execute_reply.started":"2023-05-13T03:37:16.407998Z","shell.execute_reply":"2023-05-13T03:37:17.968946Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\ntrain.to_csv(\"train_mod_lag.csv\", index = False, header = True)\ntest.to_csv(\"test_mod_lag.csv\", index = False, header = True)\n","metadata":{"execution":{"iopub.status.busy":"2023-05-13T03:37:30.779393Z","iopub.execute_input":"2023-05-13T03:37:30.779925Z","iopub.status.idle":"2023-05-13T03:46:54.466228Z","shell.execute_reply.started":"2023-05-13T03:37:30.779874Z","shell.execute_reply":"2023-05-13T03:46:54.464967Z"},"trusted":true},"execution_count":null,"outputs":[]}]}