{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load in \n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"import dask.dataframe as dd\nimport pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\n%matplotlib inline\nimport seaborn as sns","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"","_uuid":"","trusted":true},"cell_type":"code","source":"'''\nimport pandas as pd\ncliente_tabla = pd.read_csv(\"../input/grupo-bimbo-inventory-demand/cliente_tabla.csv\")\nproducto_tabla = pd.read_csv(\"../input/grupo-bimbo-inventory-demand/producto_tabla.csv\")\nsample_submission = pd.read_csv(\"../input/grupo-bimbo-inventory-demand/sample_submission.csv\")\ntest = pd.read_csv(\"../input/grupo-bimbo-inventory-demand/test.csv\")\ntown_state = pd.read_csv(\"../input/grupo-bimbo-inventory-demand/town_state.csv\")\ntrain = pd.read_csv(\"../input/grupo-bimbo-inventory-demand/train.csv\")\n'''","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"FULL_train = dd.read_csv('../input/grupo-bimbo-inventory-demand/train.csv')\nFULL_test = dd.read_csv('../input/grupo-bimbo-inventory-demand/test.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#+assumption: same cliend can order same product on from the same agency on the same week by different routes = > we assume it's the same amount regardless of route. \n#so we drop duplicates irregardless of wether they are meaningful or erroneous","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import dask\ndask.config.set(scheduler='threads')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"TRAIN_Semana_3 = FULL_train.loc[FULL_train['Semana'] == 3, ['Agencia_ID', 'Producto_ID', 'Cliente_ID', 'Demanda_uni_equil']].drop_duplicates(subset=['Agencia_ID', 'Producto_ID', 'Cliente_ID'])\nTRAIN_Semana_4 = FULL_train.loc[FULL_train['Semana'] == 4, ['Agencia_ID', 'Producto_ID', 'Cliente_ID', 'Demanda_uni_equil']].drop_duplicates(subset=['Agencia_ID', 'Producto_ID', 'Cliente_ID'])\nTRAIN_Semana_5 = FULL_train.loc[FULL_train['Semana'] == 5, ['Agencia_ID', 'Producto_ID', 'Cliente_ID', 'Demanda_uni_equil']].drop_duplicates(subset=['Agencia_ID', 'Producto_ID', 'Cliente_ID'])\nTRAIN_Semana_6 = FULL_train.loc[FULL_train['Semana'] == 6, ['Agencia_ID', 'Producto_ID', 'Cliente_ID', 'Demanda_uni_equil']].drop_duplicates(subset=['Agencia_ID', 'Producto_ID', 'Cliente_ID'])\nTRAIN_Semana_7 = FULL_train.loc[FULL_train['Semana'] == 7, ['Agencia_ID', 'Producto_ID', 'Cliente_ID', 'Demanda_uni_equil']].drop_duplicates(subset=['Agencia_ID', 'Producto_ID', 'Cliente_ID'])\nTRAIN_Semana_8 = FULL_train.loc[FULL_train['Semana'] == 8, ['Agencia_ID', 'Producto_ID', 'Cliente_ID', 'Demanda_uni_equil']].drop_duplicates(subset=['Agencia_ID', 'Producto_ID', 'Cliente_ID'])\nTRAIN_Semana_9 = FULL_train.loc[FULL_train['Semana'] == 9, ['Agencia_ID', 'Producto_ID', 'Cliente_ID', 'Demanda_uni_equil']].drop_duplicates(subset=['Agencia_ID', 'Producto_ID', 'Cliente_ID'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"TEST_Semana_10 = FULL_test.loc[FULL_test['Semana'] == 10, ['Agencia_ID', 'Producto_ID', 'Cliente_ID', 'Semana']].drop_duplicates(subset=['Agencia_ID', 'Producto_ID', 'Cliente_ID'])\nTEST_Semana_11 = FULL_test.loc[FULL_test['Semana'] == 11, ['Agencia_ID', 'Producto_ID', 'Cliente_ID', 'Semana']].drop_duplicates(subset=['Agencia_ID', 'Producto_ID', 'Cliente_ID'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"PRED_Semana_10 = TEST_Semana_10.merge(TRAIN_Semana_3, how='left', on=['Agencia_ID', 'Producto_ID', 'Cliente_ID'], suffixes=('_1', '+1') )\nPRED_Semana_10 = PRED_Semana_10.rename(columns={'Demanda_uni_equil': 'D_3'})\nPRED_Semana_10 = PRED_Semana_10.persist()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(len(TEST_Semana_10))\nprint(len(PRED_Semana_10)) #==len(tester) or len(TEST_Semana_10) True!","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"PRED_Semana_10 = PRED_Semana_10.merge(TRAIN_Semana_4, how='left', on=['Agencia_ID', 'Producto_ID', 'Cliente_ID'], suffixes=('_1', '+1') )\nPRED_Semana_10 = PRED_Semana_10.rename(columns={'Demanda_uni_equil': 'D_4'})\nPRED_Semana_10 = PRED_Semana_10.persist()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"PRED_Semana_10 = PRED_Semana_10.merge(TRAIN_Semana_5, how='left', on=['Agencia_ID', 'Producto_ID', 'Cliente_ID'], suffixes=('_1', '+1') )\nPRED_Semana_10 = PRED_Semana_10.rename(columns={'Demanda_uni_equil': 'D_5'})\nPRED_Semana_10 = PRED_Semana_10.persist()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"PRED_Semana_10 = PRED_Semana_10.merge(TRAIN_Semana_6, how='left', on=['Agencia_ID', 'Producto_ID', 'Cliente_ID'], suffixes=('_1', '+1') )\nPRED_Semana_10 = PRED_Semana_10.rename(columns={'Demanda_uni_equil': 'D_6'})\nPRED_Semana_10 = PRED_Semana_10.persist()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"PRED_Semana_10 = PRED_Semana_10.merge(TRAIN_Semana_7, how='left', on=['Agencia_ID', 'Producto_ID', 'Cliente_ID'], suffixes=('_1', '+1') )\nPRED_Semana_10 = PRED_Semana_10.rename(columns={'Demanda_uni_equil': 'D_7'})\nPRED_Semana_10 = PRED_Semana_10.persist()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"PRED_Semana_10 = PRED_Semana_10.merge(TRAIN_Semana_8, how='left', on=['Agencia_ID', 'Producto_ID', 'Cliente_ID'], suffixes=('_1', '+1') )\nPRED_Semana_10 = PRED_Semana_10.rename(columns={'Demanda_uni_equil': 'D_8'})\nPRED_Semana_10 = PRED_Semana_10.persist()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"PRED_Semana_10 = PRED_Semana_10.merge(TRAIN_Semana_9, how='left', on=['Agencia_ID', 'Producto_ID', 'Cliente_ID'], suffixes=('_1', '+1') )\nPRED_Semana_10 = PRED_Semana_10.rename(columns={'Demanda_uni_equil': 'D_9'})\nPRED_Semana_10 = PRED_Semana_10.persist()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(len(TEST_Semana_10))\nprint(len(PRED_Semana_10)) ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def predict(df, cols, result):\n    \n    #put all the shit into return statement otheriwise it\n    #doesn't work properly(recursively does each statement several\n    #times before proceeding to the ext or i dunno some shit)\n    return df.assign(result=df[cols].mean(axis=1).fillna(7).astype(int)) #we use round(0) vs .astype(int) coz we wanna preserve NaN information << revisited, non-relevant now\n\n\ncols = ['D_3', 'D_4', 'D_5', 'D_6', 'D_7', 'D_8', 'D_9']\n#cols = ['D_3', 'D_4']\nresult = 'D_10'\n#result = 'M_34'\nPRED_Semana_10_DONE = PRED_Semana_10.map_partitions(predict, cols, result)\nPRED_Semana_10_DONE = PRED_Semana_10_DONE.rename(columns={'result': result})\nPRED_Semana_10_DONE","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(len(TEST_Semana_10))\nprint(len(PRED_Semana_10)) \nprint(len(PRED_Semana_10_DONE)) ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"PRED_Semana_11 = TEST_Semana_11.merge(PRED_Semana_10_DONE.drop(['Semana'], axis=1), how='left', on=['Agencia_ID', 'Producto_ID', 'Cliente_ID'], suffixes=('_1', '+1') )\nPRED_Semana_11 = PRED_Semana_11.persist()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(len(TEST_Semana_11))\nprint(len(PRED_Semana_11)) ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cols = ['D_3', 'D_4', 'D_5', 'D_6', 'D_7', 'D_8', 'D_9', 'D_10']\nresult = 'D_11'\nPRED_Semana_11_DONE = PRED_Semana_11.map_partitions(predict, cols, result)\nPRED_Semana_11_DONE = PRED_Semana_11_DONE.rename(columns={'result': result})\nPRED_Semana_11_DONE","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(len(TEST_Semana_11))\nprint(len(PRED_Semana_11)) \nprint(len(PRED_Semana_11_DONE)) ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"CUT_PRED_Semana_10_DONE = PRED_Semana_10_DONE[['Agencia_ID', 'Producto_ID', 'Cliente_ID', 'Semana', 'D_10']]\nCUT_PRED_Semana_10_DONE = CUT_PRED_Semana_10_DONE.rename(columns={'D_10': 'Demanda_uni_equil'})\n\nCUT_PRED_Semana_11_DONE = PRED_Semana_11_DONE[['Agencia_ID', 'Producto_ID', 'Cliente_ID', 'Semana', 'D_11']]\nCUT_PRED_Semana_11_DONE = CUT_PRED_Semana_11_DONE.rename(columns={'D_11': 'Demanda_uni_equil'})\n\nFINAL_CUT_PRED = CUT_PRED_Semana_10_DONE.append(CUT_PRED_Semana_11_DONE)\n                                              \n#FULL_pred = FULL_test.merge(FINAL_CUT_PRED, how='left', on=['Agencia_ID', 'Producto_ID', 'Cliente_ID', 'Semana'], suffixes=('_1', '+1') )\n\n#FULL_pred","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"Base = FULL_test\nBase.tail()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"FULL_pred = Base.merge(FINAL_CUT_PRED, how='left', on=['Agencia_ID', 'Producto_ID', 'Cliente_ID', 'Semana'], suffixes=('_1', '+1') )\n#FULL_pred = Base.join(FINAL_CUT_PRED, how='left', on=['Agencia_ID', 'Producto_ID', 'Cliente_ID', 'Semana'])\nFULL_pred","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#FULL_pred.set_index('index')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#len(FULL_pred.dropna()) #.isnull().plot()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(len(FULL_test))\nprint(len(FULL_pred))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"FINAL_pred = FULL_pred[['id', 'Demanda_uni_equil']] #.persist() pls don't\n#FINAL_pred = FULL_pred[['Demanda_uni_equil']] #.persist() pls don't\nFINAL_pred","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(len(FULL_test))\nprint(len(FULL_pred))\nprint(len(FINAL_pred))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"FULL_test.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"panda = FINAL_pred.compute()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"panda","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"panda = panda.reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"panda","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"panda = panda.sort_values(by='id')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"panda","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"panda.to_csv('submission_integral_indexed.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"raw","source":"'''\n1) remove duplicates, coz if you leave only ---, there gonna be duplicates and the size of the table merged with all the weeks will explode (this adds +assumption)\n2) make sure the submission indexing column('id') remains intact for the merge base up untill the submission dump, where it will be sorted to for allignment with the test input structure - so don't make it an index, because merging causes repartitioning, which produces independent new indices for each partition\n3) do sanity checks for table lengths here and there\n4) use 'persist()' to fix data in a RAM to facilitate operations (here intermediate persistance between merges is probably not necessary)\n5) for predictions here the map_partition() w/ callback on the whole partition is used, but in general case probably still use apply(), maybe even inside map_partition() callback which operates on pd.DataFrame-s, because some code inside map_partition() callbacks becomes unstable unless written inside return statement; \nfor more sophisticated prediction technics (Exponential Smoothing, smart fillna() alternatives) - definately use callbacks inside pd.DataFrame.apply() inside dd.DataFrame.map_partitions() instead of pure callbacks inside pure map_partitions()\n6) for testing can just use 1 partition of some dd.DataFrame\n7) sorting inside dd.DataFrame is not available, that's why we condense all the data into test set shape and convert it into a pd.DataFrame where sorting is available\n8) metrics on Kaggle barely have any practical significance, since simply imputing the most common number (e.g. 7) everywhere gives a huge score, but is meaningless since about a half of the train data has target larger than 7, all the way up to 2000-3000, and accounts for roughly the same fraction of revenue, so ignoring such data is unacceptable and there should be predictions for typicaly large target values (and I addressed this problem in this solution to an extent)\n9) alternative yet flexible solutions include: \n-SQL + analytic functions (Google BigQuery integration w/ Kaggle)\n-Vowpal Wabbit\n'''"},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"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":4,"nbformat_minor":1}