{"cells":[{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"26fbf301-a175-4ecb-99f2-8c2091e5e2d6"},"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":"641107e4-d63d-4543-909b-cac5da6ca34d"},"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":"86463adf-788e-48d8-8651-31cd0d173a1f"},"outputs":[],"source":"agg1 = get_product_agg(['Demanda_uni_equil','Dev_uni_proxima'])"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"e80e86b9-1d55-4265-ae04-3e8837bd8d0e"},"outputs":[],"source":"agg1.shape"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"b33615f0-0416-46e9-a5c2-ab866e9e2c1a"},"outputs":[],"source":"agg1.head()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"c7194fae-f925-4873-93bd-8214579abef0"},"outputs":[],"source":"agg2 = get_product_agg(['Venta_uni_hoy'])\nagg = agg1.join(agg2)"},{"cell_type":"markdown","metadata":{"_cell_guid":"fd420247-1c12-42b5-82a9-2723a950bf22"},"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":"e2987b19-9609-43bc-9981-4384fed5b7e4"},"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":"d5c4772e-8e21-4228-9b6a-b81db1259ca4"},"outputs":[],"source":"products.tail()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"2a2fcc4f-cbb8-4829-b3f4-38a01b2e2771"},"outputs":[],"source":"products.short_name.value_counts(dropna=False)"},{"cell_type":"markdown","metadata":{"_cell_guid":"f6af89a4-149a-49a8-b27a-c130156178a1"},"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":"08fd7e46-7bc3-4a8d-b64a-7dc7e6fb6ce8"},"outputs":[],"source":"sns.distplot(products.weight.dropna())"},{"cell_type":"markdown","metadata":{"_cell_guid":"6d1e8ef1-9e13-4ef6-b952-5148f59bddca"},"source":"distribution of pieces"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"964361f4-4d84-4612-b5da-b9a6255c6b5d"},"outputs":[],"source":"sns.distplot(products.pieces.dropna())"},{"cell_type":"markdown","metadata":{"_cell_guid":"5f454d89-0a05-4f2e-816e-3b712ec58a91"},"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":"5a5388f7-2f89-4485-970d-7f761a6b798a"},"outputs":[],"source":"products.short_name.nunique()"},{"cell_type":"markdown","metadata":{"_cell_guid":"baa4910b-0add-48fa-83be-cb7e72c5033d"},"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":"b49b0bb6-3612-4d59-acde-c9ec431eea72"},"outputs":[],"source":"from nltk.corpus import stopwords\nprint(stopwords.words(\"spanish\"))"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"3c54e76b-7e5a-4ba4-8f09-8259336a20f3"},"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":"e6bba678-5451-4ddd-a04c-743e03c78d89"},"outputs":[],"source":"products['short_name_processed'].nunique()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"c7b3d8ba-2b19-49cb-90a7-55b6bd555ac0"},"outputs":[],"source":"from nltk.stem.snowball import SnowballStemmer\nstemmer = SnowballStemmer(\"spanish\")"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"22cc9705-b516-4c9b-a02b-99fcb09b0019"},"outputs":[],"source":"print(stemmer.stem(\"Tortillas\"))"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"08d36f2b-18f6-463b-b9b7-1d7fd2f08f3f"},"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":"cc5574ec-7be4-435a-a851-7292f59f6c20"},"outputs":[],"source":"products.short_name_processed.nunique()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"e6e2b6bb-6038-4591-a37c-4545ace4e633"},"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":"0484c947-6560-4db2-a0eb-7d51df91dee3"},"outputs":[],"source":"vectorizer.get_feature_names()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"e990784f-0671-4f81-8431-79f863e46f88"},"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":"c18d406d-a8d1-4c1e-bd51-0bbcf034ea49"},"outputs":[],"source":"product_bag_words.drop('Producto_ID', axis=1).sum().sort_values(ascending=False).head(100)"},{"cell_type":"markdown","metadata":{"_cell_guid":"cd2e0ace-8565-43ad-aa83-6d41c6f84619"},"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":"8930f1f4-ff56-40d7-8591-d217e895d6e3"},"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":"3e699c2c-fd8c-4c89-bd83-d0e52d875371"},"outputs":[],"source":"plt.figure(figsize = (12,15))\ndf.head(50).sort_values().plot(kind='barh')"},{"cell_type":"markdown","metadata":{"_cell_guid":"0b310c3a-8fe4-4173-b1de-648b343db194"},"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":"9abe1583-25a4-46df-9347-07b7c1f21bbe"},"source":"#### a quick look at the distributions"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"44212c10-b2d2-4971-8ab3-8f2720913ff7"},"outputs":[],"source":"sns.distplot(df)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"53703dfe-3926-461e-9929-baef87282081"},"outputs":[],"source":"sns.distplot(np.log1p(df))"},{"cell_type":"markdown","metadata":{"_cell_guid":"440af534-dce6-469a-9de8-9153736a37ca"},"source":"#### expanding the aggregate"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"ce4b3af3-4422-421c-b9e3-bc8ba9def22e"},"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":"713fe470-7f71-4e25-b2cb-3e3635634da8"},"outputs":[],"source":"df.describe().T"},{"cell_type":"markdown","metadata":{"_cell_guid":"f45bb1ce-443a-4c7f-a023-01de1174106f"},"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":"55636f1f-374d-4c74-af31-a89c73237d98"},"outputs":[],"source":"df[df.Demanda_uni_equil_sum == 0].count()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"1e604e21-b42c-41d2-a857-5a2bfda5d6e7"},"outputs":[],"source":"df[df.Demanda_uni_equil_sum == 0]"},{"cell_type":"markdown","metadata":{"_cell_guid":"93874da8-10b9-4720-9836-81b20016560a"},"source":"Similarly there are products with 0 sales and only returns"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"24bfeb81-8168-4b84-a3d3-19bf69da76ef"},"outputs":[],"source":"df[df.Venta_uni_hoy_sum == 0]"},{"cell_type":"markdown","metadata":{"_cell_guid":"23afbace-c62c-424e-9aad-e53b4ebc5de6"},"source":"there are products that were never returned"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"0e7bd431-66fb-4c0e-ae5f-e847f9aa6792"},"outputs":[],"source":"df[df.Dev_uni_proxima_sum == 0].count()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"5d0417ed-9bfb-4b1f-93f0-54bdf26f70e0"},"outputs":[],"source":"df[df.Dev_uni_proxima_sum == 0].head(20)"},{"cell_type":"markdown","metadata":{"_cell_guid":"e505257b-16b8-4b74-bf67-453f9b3eb9b7"},"source":"Let's cut products into 10 quantiles by summary adjusted demand."},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"8531c5ef-bbc4-4b63-a071-d95cc880b6c1"},"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":"7c0ecd90-134f-484b-955d-3495fa74b349"},"outputs":[],"source":"df[df.Q == '[0, 49]'].index.values"},{"cell_type":"markdown","metadata":{"_cell_guid":"f727ddf7-f644-432b-871a-91d06b68fe96"},"source":"distribution of returns by product"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"aeee1980-4bb5-4e22-834c-f790b502f12c"},"outputs":[],"source":"sns.distplot(df.Dev_uni_proxima_sum)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"7b69cf65-ac78-424e-af07-d8becebc402d"},"outputs":[],"source":"sns.distplot(np.log1p(df.Dev_uni_proxima_sum))"},{"cell_type":"markdown","metadata":{"_cell_guid":"6c8066e3-cce1-4d82-9988-550427b2e597"},"source":"Distribution of counts by product"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"5c66ccd8-ade3-457d-82e8-36a96c30315c"},"outputs":[],"source":"sns.distplot(df.Dev_uni_proxima_count)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"2e0249cc-ea33-4c71-bc2d-236cf7cd496a"},"outputs":[],"source":"sns.distplot(np.log1p(df.Dev_uni_proxima_count))"},{"cell_type":"markdown","metadata":{"_cell_guid":"d4248504-d234-4501-8641-fd0849cd2fdb"},"source":"#### lets aggregate by week and short_name now"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"71601f9c-0d80-401a-8fec-1aecb265739e"},"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":"a4c1a6f0-1012-4329-9328-495ab444abca"},"outputs":[],"source":"df_hmp.head()"},{"cell_type":"markdown","metadata":{"_cell_guid":"d0136c61-5955-4bf2-a33a-b0de04071e3a"},"source":"#### a quick check if demand distribution changes week to week"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"8589d6cc-562f-4b5b-9d76-50a900fab9dd"},"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":"a8043ddc-2134-4a7d-a659-1970ddb1704a"},"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":"359040ff-1d43-4fc3-9d79-d0d73630263b"},"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":"2cd1cce7-b1d6-48b1-99a1-071c68d59e33"},"source":"#### Same series of charts but for returns"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"3da5a90f-35d4-4880-b0e1-8c4a14fafc34"},"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}