{"cells":[{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"b83e1789-0624-478b-8b15-f0019d45d151"},"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":"97731c5b-f1ed-4c57-a4d1-a73606861962"},"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":"86a095e5-5568-406a-9992-435c50cda4ae"},"outputs":[],"source":"agg1 = get_product_agg(['Demanda_uni_equil','Dev_uni_proxima'])"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"c14476e1-baf6-4442-9e9b-5eb64560b493"},"outputs":[],"source":"agg1.shape"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"aa14b864-f49e-47f7-b5cb-7ca6ab340f7e"},"outputs":[],"source":"agg1.head()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"018453eb-4b2a-4a73-94e2-9232e45fdb11"},"outputs":[],"source":"agg2 = get_product_agg(['Venta_uni_hoy'])\nagg = agg1.join(agg2)"},{"cell_type":"markdown","metadata":{"_cell_guid":"3723a50c-bf1a-4d0d-bd79-3eddf24a213b"},"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":"90de2aa7-8431-4b34-a388-42723773752b"},"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":"820913db-69c5-4375-9d8d-46cfee5e8bc2"},"outputs":[],"source":"products.tail()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"c49fde63-9f84-4cbf-a1d5-af80e22876bb"},"outputs":[],"source":"products.short_name.value_counts(dropna=False)"},{"cell_type":"markdown","metadata":{"_cell_guid":"1b2c0e40-ab9d-40c7-bff2-a92441b33815"},"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":"f4ff455c-9ee7-434d-b0f3-d623c6a3fdd0"},"outputs":[],"source":"sns.distplot(products.weight.dropna())"},{"cell_type":"markdown","metadata":{"_cell_guid":"89c13054-598e-4fdb-a24e-2417d03de6b2"},"source":"distribution of pieces"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"e016a4f4-7cdb-4060-b470-96203396cc9c"},"outputs":[],"source":"sns.distplot(products.pieces.dropna())"},{"cell_type":"markdown","metadata":{"_cell_guid":"deec067c-4264-4c88-b18d-bd3bb072b019"},"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":"186de44e-e94b-4608-9b50-e26c139b3cc8"},"outputs":[],"source":"products.short_name.nunique()"},{"cell_type":"markdown","metadata":{"_cell_guid":"12484c86-2e07-4699-80f8-3ec1153fcb0e"},"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":"cff5d8b2-75d6-447b-a618-9874808b72e9"},"outputs":[],"source":"from nltk.corpus import stopwords\nprint(stopwords.words(\"spanish\"))"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"8307a64f-d1e9-49a4-9fb1-67c66080b8ac"},"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":"11cef04d-be48-4cf4-b7fc-f558ff58a02c"},"outputs":[],"source":"products['short_name_processed'].nunique()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"5dec6b69-a558-4d29-a253-5b026c86a9d1"},"outputs":[],"source":"from nltk.stem.snowball import SnowballStemmer\nstemmer = SnowballStemmer(\"spanish\")"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"acf92aaf-b8ac-415d-8e67-9d56c7d34f62"},"outputs":[],"source":"print(stemmer.stem(\"Tortillas\"))"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"9be2a575-67d7-47f2-8de5-d56779b1d6cf"},"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":"71f297e2-4362-43c4-8f53-9ad6263a19b9"},"outputs":[],"source":"products.short_name_processed.nunique()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"b631ce7a-89ad-4922-beda-a71b6e7a93e0"},"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":"a1e21c05-7ba1-4893-a6df-c37af53ac5a7"},"outputs":[],"source":"vectorizer.get_feature_names()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"236db1c6-e55f-44a1-8746-e5140a9db0a5"},"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":"b6ce30ce-bfc8-46ca-a438-4951765918d4"},"outputs":[],"source":"product_bag_words.drop('Producto_ID', axis=1).sum().sort_values(ascending=False).head(100)"},{"cell_type":"markdown","metadata":{"_cell_guid":"9f1365b3-6a9d-4e04-9e67-724380d53625"},"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":"ac5008f8-4d6e-4573-82f4-fd0942b0c672"},"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":"8cf4d481-8534-4d09-a951-8dabf3feb265"},"outputs":[],"source":"plt.figure(figsize = (12,15))\ndf.head(50).sort_values().plot(kind='barh')"},{"cell_type":"markdown","metadata":{"_cell_guid":"73e11600-2f4a-420d-ac72-c9ab428b8c0a"},"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":"ef956a93-d9fb-4810-b318-7e61b91cf3b1"},"source":"#### a quick look at the distributions"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"9b902b22-cd2a-4820-a2db-19e9d110e83b"},"outputs":[],"source":"sns.distplot(df)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"888d7d0b-d902-4225-a7f9-fd0187c23db2"},"outputs":[],"source":"sns.distplot(np.log1p(df))"},{"cell_type":"markdown","metadata":{"_cell_guid":"13347f9a-6260-4537-a5b5-565c3b2f3c32"},"source":"#### expanding the aggregate"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"25fe3e59-de4b-4ec3-832b-8e6d3961b78c"},"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":"7b50d582-9563-4dda-bfd3-60eb8e6e3387"},"outputs":[],"source":"df.describe().T"},{"cell_type":"markdown","metadata":{"_cell_guid":"db63eb16-e137-4931-9ffb-f5d786a53ee6"},"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":"4649d324-ab9b-4aa6-8347-cfa94660c975"},"outputs":[],"source":"df[df.Demanda_uni_equil_sum == 0].count()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"2f6c4105-7053-40d9-b02f-922c8d690a2f"},"outputs":[],"source":"df[df.Demanda_uni_equil_sum == 0]"},{"cell_type":"markdown","metadata":{"_cell_guid":"0aba3456-a12a-47db-940a-5da5af9068f7"},"source":"Similarly there are products with 0 sales and only returns"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"08f89eab-9723-4140-909b-8b50e907f48e"},"outputs":[],"source":"df[df.Venta_uni_hoy_sum == 0]"},{"cell_type":"markdown","metadata":{"_cell_guid":"4ede8bb1-45d9-4964-87f2-0f0143cbf69b"},"source":"there are products that were never returned"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"44bbb1ba-3388-472e-8510-0b0a5037202a"},"outputs":[],"source":"df[df.Dev_uni_proxima_sum == 0].count()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"87dc737c-bb43-4640-b3d1-da074567f258"},"outputs":[],"source":"df[df.Dev_uni_proxima_sum == 0].head(20)"},{"cell_type":"markdown","metadata":{"_cell_guid":"6114be36-65d7-413a-8ba7-a55183736bed"},"source":"Let's cut products into 10 quantiles by summary adjusted demand."},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"d071e8cd-67f6-4c7f-8094-6fc3516dc861"},"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":"f82d7c32-9cde-4083-b199-bd1cee46be87"},"outputs":[],"source":"df[df.Q == '[0, 49]'].index.values"},{"cell_type":"markdown","metadata":{"_cell_guid":"08beafed-367b-4427-86bd-22bf7b797cd3"},"source":"distribution of returns by product"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"160f47c7-076b-417e-ae53-7edc1e470901"},"outputs":[],"source":"sns.distplot(df.Dev_uni_proxima_sum)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"fd2208ac-5adf-44ce-b53c-26f8d00b92e5"},"outputs":[],"source":"sns.distplot(np.log1p(df.Dev_uni_proxima_sum))"},{"cell_type":"markdown","metadata":{"_cell_guid":"055102b9-e7b0-412f-9702-3c2560376838"},"source":"Distribution of counts by product"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"efcbfafb-62dc-4758-a807-2d50bdf3a42a"},"outputs":[],"source":"sns.distplot(df.Dev_uni_proxima_count)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"134cf7a0-616a-4193-b759-55fac51131b5"},"outputs":[],"source":"sns.distplot(np.log1p(df.Dev_uni_proxima_count))"},{"cell_type":"markdown","metadata":{"_cell_guid":"31650208-5461-42df-a036-b6d751452478"},"source":"#### lets aggregate by week and short_name now"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"03af75e8-9730-4a0d-8bdc-2bf7830b26fd"},"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":"5cb607f6-f2b7-40a4-9474-d4af422d4f83"},"outputs":[],"source":"df_hmp.head()"},{"cell_type":"markdown","metadata":{"_cell_guid":"bfdedf14-02e5-401a-b83e-9cc31ad3f848"},"source":"#### a quick check if demand distribution changes week to week"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"9966b3db-bca7-4168-aa1b-e36c195245d3"},"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":"8a261630-578c-488b-92e5-8d5f5dd0f762"},"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":"f3ad8731-1bde-45c5-90a3-da66d0aaaa67"},"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":"940e8dfc-4bce-45a2-aded-13010d62b616"},"source":"#### Same series of charts but for returns"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"f068c0ee-718f-401c-8971-5f20ba6eac71"},"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}