{"cells":[{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"3c818ab7-6d21-4f8d-a250-3eed105125a1"},"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":"424617e2-8b1a-4a27-bf0e-45b3c83bf2bf"},"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":"cb882f87-c576-441e-a7c9-a56a6b312750"},"outputs":[],"source":"agg1 = get_product_agg(['Demanda_uni_equil','Dev_uni_proxima'])"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"da878234-ce85-46f2-8c1e-9952f26ee1ef"},"outputs":[],"source":"agg1.shape"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"55da4db5-6f9d-4cee-b3a3-52403725d56c"},"outputs":[],"source":"agg1.head()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"ac29a088-b37c-4539-b6f9-d0d3d57ba6c7"},"outputs":[],"source":"agg2 = get_product_agg(['Venta_uni_hoy'])\nagg = agg1.join(agg2)"},{"cell_type":"markdown","metadata":{"_cell_guid":"5bdb5ed4-51a2-4a69-bc81-f78fc9a29c5e"},"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":"b87f3c7c-7990-48cc-ad17-1426f01a3d1c"},"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":"22ef3c49-ebd6-4d27-b9b9-e9d8c8ee4766"},"outputs":[],"source":"products.tail()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"9a56078e-f664-44e4-a20a-d4e34012d146"},"outputs":[],"source":"products.short_name.value_counts(dropna=False)"},{"cell_type":"markdown","metadata":{"_cell_guid":"4d52f5bf-29a5-45e7-b1d5-a39845d50a6f"},"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":"bc8cb169-670b-4bfc-a27e-2e729d57d711"},"outputs":[],"source":"sns.distplot(products.weight.dropna())"},{"cell_type":"markdown","metadata":{"_cell_guid":"b5ac3c3a-2970-4a13-a054-141f93743594"},"source":"distribution of pieces"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"7fa1b044-dfad-499d-8820-929b5e1b59a4"},"outputs":[],"source":"sns.distplot(products.pieces.dropna())"},{"cell_type":"markdown","metadata":{"_cell_guid":"330431b9-c0d7-407e-93bb-9106571f968f"},"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":"51df93f7-f4df-456e-b1eb-2ea8eaff4539"},"outputs":[],"source":"products.short_name.nunique()"},{"cell_type":"markdown","metadata":{"_cell_guid":"bb641efe-4941-44a2-8476-c35e494c6288"},"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":"b415a95c-3599-4ffa-b58c-e0ea4c556d2a"},"outputs":[],"source":"from nltk.corpus import stopwords\nprint(stopwords.words(\"spanish\"))"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"a6be17fa-27ca-4803-82ea-d4f5e8bdbd55"},"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":"c72c6e22-219a-42b4-8301-0465207cec20"},"outputs":[],"source":"products['short_name_processed'].nunique()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"0d85761d-c780-4879-9c55-0e0359e6f1e4"},"outputs":[],"source":"from nltk.stem.snowball import SnowballStemmer\nstemmer = SnowballStemmer(\"spanish\")"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"25168d08-ea55-440f-a979-53ac5ad14b2e"},"outputs":[],"source":"print(stemmer.stem(\"Tortillas\"))"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"02d165bc-1f2b-4b1d-af04-13601a8059a7"},"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":"8f067e50-092a-473d-9fa8-248f95286b63"},"outputs":[],"source":"products.short_name_processed.nunique()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"815927b9-18bc-4a98-bb28-1640dba3a6ff"},"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":"4a6c36fd-a52e-4d35-895d-28d6d3eb9d3b"},"outputs":[],"source":"vectorizer.get_feature_names()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"7042ea47-1d47-404c-852c-14521c37b765"},"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":"6cd1537a-bbb5-47bd-bd35-275d00a366fa"},"outputs":[],"source":"product_bag_words.drop('Producto_ID', axis=1).sum().sort_values(ascending=False).head(100)"},{"cell_type":"markdown","metadata":{"_cell_guid":"2337a102-f82e-4a20-a86c-c4c94c896c3f"},"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":"da256061-819e-4640-b2c1-2bbe57b22f0e"},"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":"554d9846-d86d-45ce-9db7-88b060cbc57c"},"outputs":[],"source":"plt.figure(figsize = (12,15))\ndf.head(50).sort_values().plot(kind='barh')"},{"cell_type":"markdown","metadata":{"_cell_guid":"d9b73e8c-4462-4283-9ef0-8e5316bc6510"},"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":"e27c15ae-a26c-4e5f-92ec-b1834aa00298"},"source":"#### a quick look at the distributions"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"c90a277c-4010-4326-a93b-404ff50207d8"},"outputs":[],"source":"sns.distplot(df)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"e1e5a9f8-966c-4e5a-babe-c8485c142d6a"},"outputs":[],"source":"sns.distplot(np.log1p(df))"},{"cell_type":"markdown","metadata":{"_cell_guid":"d4ad02f8-b98f-4480-a6d2-aaaa7670dc2d"},"source":"#### expanding the aggregate"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"c5f4909f-2583-40a2-bd4b-5cf16c618d43"},"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":"89a75fec-971c-46a6-a4cb-5ca3d9345dcb"},"outputs":[],"source":"df.describe().T"},{"cell_type":"markdown","metadata":{"_cell_guid":"f0832278-4daa-4321-b3f3-4ddd19f7fe6f"},"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":"6d783790-a23a-431e-acbe-e0e1594406a7"},"outputs":[],"source":"df[df.Demanda_uni_equil_sum == 0].count()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"333a7153-16fd-4067-b972-218c8087f755"},"outputs":[],"source":"df[df.Demanda_uni_equil_sum == 0]"},{"cell_type":"markdown","metadata":{"_cell_guid":"1667c91a-89e1-4d85-90b4-f83c567162d6"},"source":"Similarly there are products with 0 sales and only returns"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"05bfc0b7-296d-41a1-85f9-eb4108d8eabd"},"outputs":[],"source":"df[df.Venta_uni_hoy_sum == 0]"},{"cell_type":"markdown","metadata":{"_cell_guid":"62bbff91-6a41-48b1-8f0e-7cd902a2fef5"},"source":"there are products that were never returned"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"cfaddcdf-c60c-49b4-8bb0-4851aaa7d821"},"outputs":[],"source":"df[df.Dev_uni_proxima_sum == 0].count()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"031bd318-06b5-4ab3-895e-a2d9df52097d"},"outputs":[],"source":"df[df.Dev_uni_proxima_sum == 0].head(20)"},{"cell_type":"markdown","metadata":{"_cell_guid":"7d03510d-3e99-4269-bce7-7194cf81b0cc"},"source":"Let's cut products into 10 quantiles by summary adjusted demand."},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"ff1cc009-9591-4135-9d2e-749757699deb"},"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":"ffd77f35-7f0a-4c63-b4f9-0bb9142e0895"},"outputs":[],"source":"df[df.Q == '[0, 49]'].index.values"},{"cell_type":"markdown","metadata":{"_cell_guid":"a05411d8-6c61-4f66-882d-927779c132db"},"source":"distribution of returns by product"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"2d212034-891c-4509-a5e4-3aa4b2c6290e"},"outputs":[],"source":"sns.distplot(df.Dev_uni_proxima_sum)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"95231824-be2d-4b6f-bc56-a58b026d803d"},"outputs":[],"source":"sns.distplot(np.log1p(df.Dev_uni_proxima_sum))"},{"cell_type":"markdown","metadata":{"_cell_guid":"0f363bda-ed14-44dd-be0a-2b492ba5e78a"},"source":"Distribution of counts by product"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"38fbdb3e-e919-4af7-a246-0f3761b6123e"},"outputs":[],"source":"sns.distplot(df.Dev_uni_proxima_count)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"3d0aa13d-ed3f-4740-ab02-170e272ade63"},"outputs":[],"source":"sns.distplot(np.log1p(df.Dev_uni_proxima_count))"},{"cell_type":"markdown","metadata":{"_cell_guid":"9db5b99b-931a-491a-9193-c90db4774724"},"source":"#### lets aggregate by week and short_name now"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"001d4cdc-2a16-4f60-b01a-22ca6c23fea8"},"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":"1c4dfb96-bc56-4468-866b-80b99c74ddaf"},"outputs":[],"source":"df_hmp.head()"},{"cell_type":"markdown","metadata":{"_cell_guid":"8af9f757-71cc-47f9-b267-6f28803bcba5"},"source":"#### a quick check if demand distribution changes week to week"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"2ff6474b-4478-4d1c-b841-6a06dce24b70"},"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":"0f1c66b8-7668-4621-8944-d13c89de84fe"},"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":"042c8f54-9fda-4b80-bd83-f2cb0ef496b2"},"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":"c130b7ab-8bdb-4042-bb5c-bf90e2d35143"},"source":"#### Same series of charts but for returns"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"50933f25-3e29-4eae-b8ba-4d94a5225b01"},"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}