{"cells":[{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"eda927d8-3861-4fe1-a185-13a09066b504"},"outputs":[],"source":"from bokeh.plotting import figure, show, output_notebook\nfrom bokeh.models import HoverTool, ColumnDataSource\nimport numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport seaborn as sns\noutput_notebook()\n\ndef get_product_agg(cols):\n    df_train = pd.read_csv('../input/train.csv', usecols = ['Semana', 'Producto_ID'] + cols,\n                           dtype  = {'Semana': 'int32',\n                                     'Producto_ID':'int32',\n                                     'Venta_hoy':'float32',\n                                     'Venta_uni_hoy': 'int32',\n                                     'Dev_uni_proxima':'int32',\n                                     'Dev_proxima':'float32',\n                                     'Demanda_uni_equil':'int32'})\n    agg  = df_train.groupby(['Semana', 'Producto_ID'], as_index=False).agg(['count','sum', 'min', 'max','median','mean'])\n    agg.columns  =  ['_'.join(col).strip() for col in agg.columns.values]\n    del(df_train)\n    return agg"},{"cell_type":"markdown","metadata":{"_cell_guid":"f43481d4-e81f-4e93-a750-94a421b2ad41"},"source":"### a simple product aggregate. Kaggle computing power allows us to only calculate 1-2 fields at a time."},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"785c6502-f612-4c8e-8d16-2b47ce0d085d"},"outputs":[],"source":"agg1 = get_product_agg(['Demanda_uni_equil','Dev_uni_proxima'])"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"21268065-610c-4cdf-8e8f-564d0ed7c8a2"},"outputs":[],"source":"agg1.shape"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"6db8e9ee-d723-4192-9262-1de827f3629b"},"outputs":[],"source":"agg1.head()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"7b77bf22-b49d-4fe0-aa87-19eeb1e5cae2"},"outputs":[],"source":"agg2 = get_product_agg(['Venta_uni_hoy'])\nagg = agg1.join(agg2)"},{"cell_type":"markdown","metadata":{"_cell_guid":"35a7ccc9-49cb-4836-9a2c-61d0775f39d7"},"source":"Let's preprocess products a little bit. I borrowed some of the preprocessing from [here](https://www.kaggle.com/lyytinen/grupo-bimbo-inventory-demand/basic-preprocessing-for-products) "},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"02e1a074-0133-4442-a0c3-eaf1380dc205"},"outputs":[],"source":"products  =  pd.read_csv(\"../input/producto_tabla.csv\")\nproducts  =  pd.read_csv(\"../input/producto_tabla.csv\")\nproducts['short_name'] = products.NombreProducto.str.extract('^(\\D*)', expand=False)\nproducts['brand'] = products.NombreProducto.str.extract('^.+\\s(\\D+) \\d+$', expand=False)\nw = products.NombreProducto.str.extract('(\\d+)(Kg|g)', expand=True)\nproducts['weight'] = w[0].astype('float')*w[1].map({'Kg':1000, 'g':1})\nproducts['pieces'] =  products.NombreProducto.str.extract('(\\d+)p ', expand=False).astype('float')\nproducts.head()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"ab71f799-72ce-4c89-bb63-45ebf2bd7d37"},"outputs":[],"source":"products.tail()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"b40240c3-8bb3-4dbf-8c0c-b41cd6f347f0"},"outputs":[],"source":"products.short_name.value_counts(dropna=False)"},{"cell_type":"markdown","metadata":{"_cell_guid":"5d41b45e-f2a2-4e9b-ac14-14196eb61503"},"source":"There are some weird products that weight 42 Kilos. Check out this Exhibidor :\n\n![Exhibidor bimbo](https://mir-s3-cdn-cf.behance.net/project_modules/disp/55c94f24003843.5632c737c062c.jpeg)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"9b03bc3e-57b3-457b-9a24-8acc6f550939"},"outputs":[],"source":"sns.distplot(products.weight.dropna())"},{"cell_type":"markdown","metadata":{"_cell_guid":"7e56f163-96f2-4bfb-83b9-afd8bd5a4347"},"source":"distribution of pieces"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"de47569e-0797-493c-8bde-649007d69693"},"outputs":[],"source":"sns.distplot(products.pieces.dropna())"},{"cell_type":"markdown","metadata":{"_cell_guid":"44883b53-8b00-48c5-87cd-8294cfd020f4"},"source":"#### Lets clean up product names a bit, we have ~1000 unique names once we cleaned the weights, but there is much more work to be done\n#### Products have some abbreviation leftovers that I did not clean, products have similar names but different word forms, etc."},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"b7c15522-c1f5-4739-8385-ddd118b85f71"},"outputs":[],"source":"products.short_name.nunique()"},{"cell_type":"markdown","metadata":{"_cell_guid":"22f2fdb8-38e2-4c0e-af2d-8b6114cb79a7"},"source":"Let's clean stop words and leave only the word stems (I did not clean abbreviations, sorry)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"068e810b-bed1-488d-9312-8ccdd56a0b16"},"outputs":[],"source":"from nltk.corpus import stopwords\nprint(stopwords.words(\"spanish\"))"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"24093514-7557-4a2b-bc86-f0a244a4b051"},"outputs":[],"source":"products['short_name_processed'] = (products['short_name']\n                                        .map(lambda x: \" \".join([i for i in x.lower()\n                                                                 .split() if i not in stopwords.words(\"spanish\")])))"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"d75f0bee-a41e-460a-af67-4211674e2343"},"outputs":[],"source":"products['short_name_processed'].nunique()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"e3f5c366-9524-4ae5-8a1a-3c6085f03e81"},"outputs":[],"source":"from nltk.stem.snowball import SnowballStemmer\nstemmer = SnowballStemmer(\"spanish\")"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"74a234cf-46e3-44ba-9ef7-ea9e0782c4f7"},"outputs":[],"source":"print(stemmer.stem(\"Tortillas\"))"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"8074e49f-e120-4c2d-9867-9cfaa8856d6c"},"outputs":[],"source":"products['short_name_processed'] = (products['short_name_processed']\n                                        .map(lambda x: \" \".join([stemmer.stem(i) for i in x.lower().split()])))"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"37530b7e-0aca-470f-8fc1-9590b79e61ea"},"outputs":[],"source":"products.short_name_processed.nunique()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"74d43d18-9dc5-4582-a6c8-8c7712afff3b"},"outputs":[],"source":"from sklearn.feature_extraction.text import CountVectorizer\nvectorizer = CountVectorizer(analyzer = \"word\",   \\\n                             tokenizer = None,    \\\n                             preprocessor = None, \\\n                             stop_words = None,   \\\n                             max_features = 1000) \n\nproduct_bag_words = vectorizer.fit_transform(products.short_name_processed).toarray()\nproduct_bag_words.shape"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"02b3ed6e-ebe6-4878-8081-aee61b1e5dc6"},"outputs":[],"source":"vectorizer.get_feature_names()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"d96ac5a4-2e22-461e-89ed-d41a1caca815"},"outputs":[],"source":"product_bag_words = pd.concat([products.Producto_ID, \n                               pd.DataFrame(product_bag_words, \n                                            columns= vectorizer.get_feature_names(), index = products.index)], axis=1)\nproduct_bag_words.head()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"302def98-7034-4140-b1be-587de783c544"},"outputs":[],"source":"product_bag_words.drop('Producto_ID', axis=1).sum().sort_values(ascending=False).head(100)"},{"cell_type":"markdown","metadata":{"_cell_guid":"49efeb24-9188-4f42-ad61-dde610b1c262"},"source":"####  let's have a look, what is the product with the biggest demand of all times"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"b727d245-4a0e-451b-be34-7374df813a9e"},"outputs":[],"source":"df = (pd.merge(agg1.reset_index(), products, on='Producto_ID', how='left').\n      groupby('short_name')['Demanda_uni_equil_sum'].sum().sort_values(ascending=False))"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"a4abc2cc-8571-422f-8ab9-3358224614f6"},"outputs":[],"source":"plt.figure(figsize = (12,15))\ndf.head(50).sort_values().plot(kind='barh')"},{"cell_type":"markdown","metadata":{"_cell_guid":"f1552be4-29fa-41e7-a504-eab11152282b"},"source":"The best seller is by far Nito. Check this out, looks yummy : \n\n![](http://static.manufactura.mx/media/2013/11/20/negrito.jpg)"},{"cell_type":"markdown","metadata":{"_cell_guid":"06baedb9-03fe-4545-be22-55cf7858d683"},"source":"#### a quick look at the distributions"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"b2974258-a6a1-461a-99c1-c13eb7f8bf3f"},"outputs":[],"source":"sns.distplot(df)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"83704237-a0d8-4e29-b163-e59c23376c74"},"outputs":[],"source":"sns.distplot(np.log1p(df))"},{"cell_type":"markdown","metadata":{"_cell_guid":"2b936d70-eb48-4a92-bf62-3076f4ca26cd"},"source":"#### expanding the aggregate"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"5de3fe37-f8fa-433f-b5ae-fcd1e1d619b0"},"outputs":[],"source":"df = (pd.merge(agg.reset_index(), products, on='Producto_ID', how='left').\n      groupby('short_name')['Demanda_uni_equil_sum', 'Venta_uni_hoy_sum', 'Dev_uni_proxima_sum', 'Dev_uni_proxima_count']\n      .sum().sort_values(by = 'Demanda_uni_equil_sum', ascending=False))"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"68e6c869-9b28-4718-b49a-6023a136bce5"},"outputs":[],"source":"df.describe().T"},{"cell_type":"markdown","metadata":{"_cell_guid":"e294b352-d7b4-4a53-bb62-47b9ed8f8dbb"},"source":"There are interesting things. There are products for which Demanda_uni_equil_sum = 0 and other fields are not equal to 0"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"3670cc94-c408-4bcb-8078-d5c18f56b46a"},"outputs":[],"source":"df[df.Demanda_uni_equil_sum == 0].count()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"c7c284a4-1b10-4ff7-8f6f-eea0c09c7f50"},"outputs":[],"source":"df[df.Demanda_uni_equil_sum == 0]"},{"cell_type":"markdown","metadata":{"_cell_guid":"537f9461-8908-491e-851e-484ad527e103"},"source":"Similarly there are products with 0 sales and only returns"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"daad0c51-5f78-4c6f-ad47-6b70b46541fb"},"outputs":[],"source":"df[df.Venta_uni_hoy_sum == 0]"},{"cell_type":"markdown","metadata":{"_cell_guid":"7ab119d3-45e9-4205-b301-9be7c1d5b387"},"source":"there are products that were never returned"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"a6f386c4-f545-4037-a5c5-dd975174d614"},"outputs":[],"source":"df[df.Dev_uni_proxima_sum == 0].count()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"267db19f-7102-488c-93cc-1a033224559f"},"outputs":[],"source":"df[df.Dev_uni_proxima_sum == 0].head(20)"},{"cell_type":"markdown","metadata":{"_cell_guid":"6bee6114-5b2f-4d95-8821-5f29399960d7"},"source":"Let's cut products into 10 quantiles by summary adjusted demand."},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"27080b22-9f72-426f-9139-32029950d689"},"outputs":[],"source":"df['Q'] = pd.qcut(df.Demanda_uni_equil_sum, 10)\ndf.Q.value_counts()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"838b5fdf-30f3-481d-9c62-1eacf695cd6f"},"outputs":[],"source":"df[df.Q == '[0, 49]'].index.values"},{"cell_type":"markdown","metadata":{"_cell_guid":"99ff412a-dfbc-4bc1-aad3-9a2003f343a4"},"source":"distribution of returns by product"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"04996b83-0616-49c5-9a9a-59cab6963e64"},"outputs":[],"source":"sns.distplot(df.Dev_uni_proxima_sum)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"5d7b8c29-05ff-4939-b3cf-7b87b55440ed"},"outputs":[],"source":"sns.distplot(np.log1p(df.Dev_uni_proxima_sum))"},{"cell_type":"markdown","metadata":{"_cell_guid":"fa25804e-270a-466c-9fc0-6dc68f78e9e0"},"source":"Distribution of counts by product"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"7009d43d-6dc1-4a06-a6f2-2184f190e27b"},"outputs":[],"source":"sns.distplot(df.Dev_uni_proxima_count)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"7011953c-151f-4cff-993e-3e4c063a1d5b"},"outputs":[],"source":"sns.distplot(np.log1p(df.Dev_uni_proxima_count))"},{"cell_type":"markdown","metadata":{"_cell_guid":"eb3ad3f6-6f09-4e58-a830-e3be1b2b93fe"},"source":"#### lets aggregate by week and short_name now"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"3eac3d19-d175-4b09-88ab-da970335122f"},"outputs":[],"source":"df_hmp = (pd.merge(agg.reset_index(), products, on='Producto_ID', how='left').\n      groupby(['Semana','short_name'])['Demanda_uni_equil_sum', 'Venta_uni_hoy_sum', 'Dev_uni_proxima_sum', 'Dev_uni_proxima_count'].sum().reset_index())"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"cb7201dc-da44-4f0f-a519-40ac112fdca5"},"outputs":[],"source":"df_hmp.head()"},{"cell_type":"markdown","metadata":{"_cell_guid":"5a9ede78-a74a-4a3b-a7fb-1479f82b30c8"},"source":"#### a quick check if demand distribution changes week to week"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"69125eae-01e3-450c-97dc-db6e46fee702"},"outputs":[],"source":"df_hmp['log1p_Demanda_uni_equil_sum'] = np.log1p(df_hmp.Demanda_uni_equil_sum)\ng = sns.FacetGrid(df_hmp, row = 'Semana')\ng = g.map(sns.distplot, 'log1p_Demanda_uni_equil_sum')"},{"cell_type":"markdown","metadata":{"_cell_guid":"67d10f15-71a0-4602-881f-eb04ad237dde"},"source":"#### Now let's look at which proucts sell by week with interactive heatmaps. Let's use our quantiles here."},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"ffb95696-6c35-4619-9816-f82002a96f99"},"outputs":[],"source":"from bokeh.charts import HeatMap\nfrom bokeh.plotting import vplot\n\nheatmaps = []\nfor i in df.Q.cat.categories.values:\n    hm = HeatMap(df_hmp[df_hmp.short_name.isin(df[df.Q == i].index.values)],\n                        x='short_name', y = 'Semana', values = 'Demanda_uni_equil_sum',\n                 hover_tool = True, title = 'Products with summary demand '+ str(i), xgrid = False,\n                 stat = 'sum',plot_width=950, plot_height=400, tools='hover, box_zoom, resize, save, wheel_zoom, reset',\n                 )\n    heatmaps.append(hm)\nshow(vplot(*heatmaps))"},{"cell_type":"markdown","metadata":{"_cell_guid":"8caf98b9-4577-4825-8b01-b5f123aafb9f"},"source":"#### Same series of charts but for returns"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"87774359-0251-4d1b-a08a-9d72f572d781"},"outputs":[],"source":"from bokeh.charts import HeatMap\nfrom bokeh.plotting import vplot\ndf['Q_ret'] = pd.qcut(df.Dev_uni_proxima_sum, 5)\nheatmaps = []\nfor i in df.Q_ret.cat.categories.values:\n    hm = HeatMap(df_hmp[df_hmp.short_name.isin(df[df.Q_ret == i].index.values)],\n                        x='short_name', y = 'Semana', values = 'Demanda_uni_equil_sum',\n                 hover_tool = True, title = 'Products with summary returns '+ str(i), xgrid = False,\n                 stat = 'sum',plot_width=800, plot_height=400, tools='hover, box_zoom, resize, save, wheel_zoom, reset',\n                 )\n    heatmaps.append(hm)\nshow(vplot(*heatmaps))"}],"metadata":{"_change_revision":0,"_is_fork":false,"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.5.2"}},"nbformat":4,"nbformat_minor":0}