{"cells":[{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"f49d95f1-84e3-4c24-a317-1e067865f12d"},"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":"cf7c750a-93ae-482d-9bf6-f71179a89643"},"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":"1e1c47bb-0cd9-4201-8762-f3681d5ba522"},"outputs":[],"source":"agg1 = get_product_agg(['Demanda_uni_equil','Dev_uni_proxima'])"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"92dc4b9e-bb32-417e-b97a-82b9c3d6ede8"},"outputs":[],"source":"agg1.shape"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"9f5d0f61-bf8c-4836-ac79-6334ccd18340"},"outputs":[],"source":"agg1.head()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"6e40c197-f74c-4d77-8d53-42af1cf2313a"},"outputs":[],"source":"agg2 = get_product_agg(['Venta_uni_hoy'])\nagg = agg1.join(agg2)"},{"cell_type":"markdown","metadata":{"_cell_guid":"5ff36c0b-3351-4762-aaa2-4cba53ae9c74"},"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":"924daad9-b2ff-4c3b-be61-ec8fdd1c347d"},"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":"a96b6876-d490-4026-b871-f36ff5327329"},"outputs":[],"source":"products.tail()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"e2662306-cbac-4b6d-95f6-9e76563437d9"},"outputs":[],"source":"products.short_name.value_counts(dropna=False)"},{"cell_type":"markdown","metadata":{"_cell_guid":"3b17ca74-0600-4ce1-a04e-278479eae493"},"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":"de74ea5f-618b-47a4-871e-a3b5b9df78ac"},"outputs":[],"source":"sns.distplot(products.weight.dropna())"},{"cell_type":"markdown","metadata":{"_cell_guid":"aa888f28-5d21-4bf1-be27-f71dd36ca9a5"},"source":"distribution of pieces"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"3441de4a-9c97-47e7-8667-2a725d661f9a"},"outputs":[],"source":"sns.distplot(products.pieces.dropna())"},{"cell_type":"markdown","metadata":{"_cell_guid":"6bc02188-3d91-471d-9d62-d5c173fe931c"},"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":"bc0e0b65-2fef-4c4f-aa7d-b38ba5ce37b4"},"outputs":[],"source":"products.short_name.nunique()"},{"cell_type":"markdown","metadata":{"_cell_guid":"ee553dbe-ce52-4ff4-92f5-3b0883a96cf0"},"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":"27fa17b4-4162-47ee-874b-1758655ce6a6"},"outputs":[],"source":"from nltk.corpus import stopwords\nprint(stopwords.words(\"spanish\"))"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"716cdfd1-f530-4a61-9b4e-0966d887a5cf"},"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":"c00e028e-d556-48e9-897e-8423314a5134"},"outputs":[],"source":"products['short_name_processed'].nunique()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"8542898b-c761-4e24-aec7-c7f37a0ca68e"},"outputs":[],"source":"from nltk.stem.snowball import SnowballStemmer\nstemmer = SnowballStemmer(\"spanish\")"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"6757b93e-6091-44db-a50d-bf23f50e94d9"},"outputs":[],"source":"print(stemmer.stem(\"Tortillas\"))"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"e2787097-cecc-4e01-932b-011715749715"},"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":"40dc2862-c3a2-4e39-981e-16e4ba409ed0"},"outputs":[],"source":"products.short_name_processed.nunique()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"357d15f5-ca01-44fc-a99c-143c3f89ad14"},"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":"84381ac7-4e82-405f-9585-05ad13fdd210"},"outputs":[],"source":"vectorizer.get_feature_names()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"05dca0e4-5a7e-4cdc-ac20-7ea329d58af3"},"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":"f6ba6d4f-0488-4aff-a891-706b4996081c"},"outputs":[],"source":"product_bag_words.drop('Producto_ID', axis=1).sum().sort_values(ascending=False).head(100)"},{"cell_type":"markdown","metadata":{"_cell_guid":"8d2aa889-10b0-4434-8789-b9a36f8b2e96"},"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":"6896c8bc-9322-4d2f-b92e-83cc92c6bad7"},"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":"8a92b453-192d-45f5-baea-911ef4c3ce25"},"outputs":[],"source":"plt.figure(figsize = (12,15))\ndf.head(50).sort_values().plot(kind='barh')"},{"cell_type":"markdown","metadata":{"_cell_guid":"cd44199b-5338-47b9-94a3-731c3184edd9"},"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":"14ed77d5-f8f1-4e17-b9dd-7f7a7a10fe56"},"source":"#### a quick look at the distributions"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"6a1b1fe8-27c1-4697-a4e3-0ae8c9bacbfc"},"outputs":[],"source":"sns.distplot(df)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"db5a5399-444e-4ca5-85eb-1ba1cec3c466"},"outputs":[],"source":"sns.distplot(np.log1p(df))"},{"cell_type":"markdown","metadata":{"_cell_guid":"7362c256-a122-4890-9c7d-492d97f074dc"},"source":"#### expanding the aggregate"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"1a7da127-47cd-408d-aabc-00ea9322ff17"},"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":"2c68f7b0-ed74-4811-96d8-1a377453bd83"},"outputs":[],"source":"df.describe().T"},{"cell_type":"markdown","metadata":{"_cell_guid":"a6c995c4-b24b-40b6-8be5-e75775fb4776"},"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":"be259baf-a40f-49d1-915c-a4517d4fa162"},"outputs":[],"source":"df[df.Demanda_uni_equil_sum == 0].count()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"b9001d6b-e2bb-4e6c-b97e-2c8259192dc6"},"outputs":[],"source":"df[df.Demanda_uni_equil_sum == 0]"},{"cell_type":"markdown","metadata":{"_cell_guid":"8622bf9b-224b-412a-b29f-0b6e87653518"},"source":"Similarly there are products with 0 sales and only returns"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"ca95c6b3-90ab-47c6-a756-1df01e9ed4d9"},"outputs":[],"source":"df[df.Venta_uni_hoy_sum == 0]"},{"cell_type":"markdown","metadata":{"_cell_guid":"a8b3331e-45f5-45cf-8705-48ed67fb8287"},"source":"there are products that were never returned"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"7d58f850-18cf-425b-82bb-8395fa7f9750"},"outputs":[],"source":"df[df.Dev_uni_proxima_sum == 0].count()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"c8cdf2ea-2fc3-486c-9fd9-23b4ac3a88cb"},"outputs":[],"source":"df[df.Dev_uni_proxima_sum == 0].head(20)"},{"cell_type":"markdown","metadata":{"_cell_guid":"aec0a8da-1ae2-48b1-b4ef-6cfe4220ddce"},"source":"Let's cut products into 10 quantiles by summary adjusted demand."},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"b66af45f-ad8b-4739-858b-43b39a2ae3a8"},"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":"3222d2d8-43aa-4ce6-a2cf-9661905015c3"},"outputs":[],"source":"df[df.Q == '[0, 49]'].index.values"},{"cell_type":"markdown","metadata":{"_cell_guid":"83bb128b-f2c5-48e6-9eba-679eb857ff3e"},"source":"distribution of returns by product"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"dbe0352e-a185-45d8-9f70-5ee17074474d"},"outputs":[],"source":"sns.distplot(df.Dev_uni_proxima_sum)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"fdb48114-a08d-4e67-8991-1d25532371d0"},"outputs":[],"source":"sns.distplot(np.log1p(df.Dev_uni_proxima_sum))"},{"cell_type":"markdown","metadata":{"_cell_guid":"7fdfcaa4-edd5-4e32-9cce-0971d2827e4d"},"source":"Distribution of counts by product"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"b4090ee8-2abc-4305-989b-6a1629071736"},"outputs":[],"source":"sns.distplot(df.Dev_uni_proxima_count)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"b4deab03-07c6-4248-a779-481f48f0cda5"},"outputs":[],"source":"sns.distplot(np.log1p(df.Dev_uni_proxima_count))"},{"cell_type":"markdown","metadata":{"_cell_guid":"07736114-2707-4afd-ab69-c90042891116"},"source":"#### lets aggregate by week and short_name now"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"492809a5-2918-4702-b206-f218c0b9f86a"},"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":"4097fd38-8ae4-420d-812a-6b08bcc76b6c"},"outputs":[],"source":"df_hmp.head()"},{"cell_type":"markdown","metadata":{"_cell_guid":"662b4667-8db9-4038-8fe0-5379e460e244"},"source":"#### a quick check if demand distribution changes week to week"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"2c2abfa4-c8ac-4840-b967-df44cbc64ac8"},"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":"797e2aa4-afed-4bc3-af71-abbe06427e14"},"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":"ea1319ff-04cb-445c-be4f-8fbc45435d80"},"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":"d4936b15-d255-481b-9411-2e2f433899f7"},"source":"#### Same series of charts but for returns"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"ac8662eb-743c-43ea-85ce-31ea341f29cf"},"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}