{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "ddad0983-4002-4542-9991-514494c77e6f"
      },
      "outputs": [],
      "source": [
        "from bokeh.plotting import figure, show, output_notebook\n",
        "from bokeh.models import HoverTool, ColumnDataSource\n",
        "import numpy as np\n",
        "import pandas as pd\n",
        "import matplotlib.pyplot as plt\n",
        "import seaborn as sns\n",
        "output_notebook()\n",
        "\n",
        "def 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": "1cf5b8c0-1667-488f-8159-013d9363200b"
      },
      "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": "7305647c-39a7-43be-b4cc-775e17efbd64"
      },
      "outputs": [],
      "source": [
        "agg1 = get_product_agg(['Demanda_uni_equil','Dev_uni_proxima'])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "43e53676-8606-4c79-94c6-e88766387cdd"
      },
      "outputs": [],
      "source": [
        "agg1.shape"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "04cdf8f8-6aef-447b-8fce-1152f457a51b"
      },
      "outputs": [],
      "source": [
        "agg1.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "3b1412dd-e289-4caf-9beb-1bd67f5aab11"
      },
      "outputs": [],
      "source": [
        "agg2 = get_product_agg(['Venta_uni_hoy'])\n",
        "agg = agg1.join(agg2)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "8a3183be-fbfb-464d-b2d4-77944f4df749"
      },
      "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": "7999956a-6d6e-4839-9d2d-4a346183e2c7"
      },
      "outputs": [],
      "source": [
        "products  =  pd.read_csv(\"../input/producto_tabla.csv\")\n",
        "products  =  pd.read_csv(\"../input/producto_tabla.csv\")\n",
        "products['short_name'] = products.NombreProducto.str.extract('^(\\D*)', expand=False)\n",
        "products['brand'] = products.NombreProducto.str.extract('^.+\\s(\\D+) \\d+$', expand=False)\n",
        "w = products.NombreProducto.str.extract('(\\d+)(Kg|g)', expand=True)\n",
        "products['weight'] = w[0].astype('float')*w[1].map({'Kg':1000, 'g':1})\n",
        "products['pieces'] =  products.NombreProducto.str.extract('(\\d+)p ', expand=False).astype('float')\n",
        "products.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "e7abf44c-3300-45ff-a002-99f2185df04a"
      },
      "outputs": [],
      "source": [
        "products.tail()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "0426e890-2597-43c0-afcf-7795ac2e67f7"
      },
      "outputs": [],
      "source": [
        "products.short_name.value_counts(dropna=False)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "ef261815-762d-491f-b8f2-7ef4c380547b"
      },
      "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": "0b0e788c-937b-497c-bc0d-3fb7d13042e3"
      },
      "outputs": [],
      "source": [
        "sns.distplot(products.weight.dropna())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "c1a9d032-f18b-4b41-b058-ec46982ce1b8"
      },
      "source": [
        "distribution of pieces"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "c0326e6d-9765-4803-ae93-cab45f2ef29d"
      },
      "outputs": [],
      "source": [
        "sns.distplot(products.pieces.dropna())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "956f30ce-6613-472f-9d4a-c1699b70b65c"
      },
      "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": "59d15c4b-0c3f-4968-a083-cf37fb060fd7"
      },
      "outputs": [],
      "source": [
        "products.short_name.nunique()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "9d09ee1e-db22-4ea6-88ef-9110deb29718"
      },
      "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": "d63d965f-14e3-406d-ab4d-3f898fb6d7ed"
      },
      "outputs": [],
      "source": [
        "from nltk.corpus import stopwords\n",
        "print(stopwords.words(\"spanish\"))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "14cbe227-44f3-465d-b4f2-2a17a3eac5a7"
      },
      "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": "d2b45389-d261-440b-9039-752130b8cefe"
      },
      "outputs": [],
      "source": [
        "products['short_name_processed'].nunique()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "3d34e32c-095b-4c14-8b22-61706d7bc1c5"
      },
      "outputs": [],
      "source": [
        "from nltk.stem.snowball import SnowballStemmer\n",
        "stemmer = SnowballStemmer(\"spanish\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "718e6816-9ff6-42f9-a329-6ee05d66af51"
      },
      "outputs": [],
      "source": [
        "print(stemmer.stem(\"Tortillas\"))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "eed06f23-4d94-46df-91e2-79148bc06a78"
      },
      "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": "a0f2c834-ccdb-4fb3-ad38-b4f2da86532f"
      },
      "outputs": [],
      "source": [
        "products.short_name_processed.nunique()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "77c8577c-c7db-4b59-b3bd-29fd25e6f91a"
      },
      "outputs": [],
      "source": [
        "from sklearn.feature_extraction.text import CountVectorizer\n",
        "vectorizer = CountVectorizer(analyzer = \"word\",   \\\n",
        "                             tokenizer = None,    \\\n",
        "                             preprocessor = None, \\\n",
        "                             stop_words = None,   \\\n",
        "                             max_features = 1000) \n",
        "\n",
        "product_bag_words = vectorizer.fit_transform(products.short_name_processed).toarray()\n",
        "product_bag_words.shape"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "fa4a3657-ef07-4164-8028-a05967ec8fd6"
      },
      "outputs": [],
      "source": [
        "vectorizer.get_feature_names()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "1fc6b1b0-8749-40b4-86bb-5460863b7c6d"
      },
      "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)\n",
        "product_bag_words.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "93a850d2-f6e0-4149-bfda-27c1101f2e40"
      },
      "outputs": [],
      "source": [
        "product_bag_words.drop('Producto_ID', axis=1).sum().sort_values(ascending=False).head(100)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "e314b894-60a5-4c80-beed-72988341e49c"
      },
      "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": "56960a1e-1a30-4d54-88e4-6d3c07730a1c"
      },
      "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": "de32e148-f7d8-4428-804d-6ce3987ad7f9"
      },
      "outputs": [],
      "source": [
        "plt.figure(figsize = (12,15))\n",
        "df.head(50).sort_values().plot(kind='barh')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "c859f304-b52e-4303-b085-b7d324f796ae"
      },
      "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": "ba1fb7df-56d7-4047-8581-c80da57d13ec"
      },
      "source": [
        "#### a quick look at the distributions"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "e1b13042-5a14-4804-90a2-9c395b559601"
      },
      "outputs": [],
      "source": [
        "sns.distplot(df)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "9e598710-6236-4c40-bcec-b85eb077bace"
      },
      "outputs": [],
      "source": [
        "sns.distplot(np.log1p(df))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "8895990c-f7ab-4c96-b185-ebeab3207483"
      },
      "source": [
        "#### expanding the aggregate"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "ad4cc961-531a-4603-bbdd-3b7474e5ba6e"
      },
      "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": "79fc2a62-dcd8-47c0-868e-25734a688566"
      },
      "outputs": [],
      "source": [
        "df.describe().T"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "3debf7c1-68df-4072-a755-1fed675aec0b"
      },
      "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": "eb337faf-830c-4b51-8253-986cdf14e5a0"
      },
      "outputs": [],
      "source": [
        "df[df.Demanda_uni_equil_sum == 0].count()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "465d5ffc-3709-492e-9d65-35bcdf6d45f5"
      },
      "outputs": [],
      "source": [
        "df[df.Demanda_uni_equil_sum == 0]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "09a37574-0313-4cc3-bb6c-8d1d46d50fbd"
      },
      "source": [
        "Similarly there are products with 0 sales and only returns"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "9abfd838-6c10-44c8-9a9c-111a81246a93"
      },
      "outputs": [],
      "source": [
        "df[df.Venta_uni_hoy_sum == 0]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "4825d198-90c9-4670-a8e2-158fd483938f"
      },
      "source": [
        "there are products that were never returned"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "068a2ba9-109c-4597-9acd-2d5420b25e78"
      },
      "outputs": [],
      "source": [
        "df[df.Dev_uni_proxima_sum == 0].count()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "f49352d3-bdb9-4c81-90d8-0305186dd374"
      },
      "outputs": [],
      "source": [
        "df[df.Dev_uni_proxima_sum == 0].head(20)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "015d180d-7f83-4be0-aaaa-a3192537f541"
      },
      "source": [
        "Let's cut products into 10 quantiles by summary adjusted demand."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "25955c55-666d-4390-8c62-7987af92739a"
      },
      "outputs": [],
      "source": [
        "df['Q'] = pd.qcut(df.Demanda_uni_equil_sum, 10)\n",
        "df.Q.value_counts()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "d3416938-ff46-421e-bfd2-6db3905a9f66"
      },
      "outputs": [],
      "source": [
        "df[df.Q == '[0, 49]'].index.values"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "28dd97e8-ca6c-4abc-b80c-4e6733be678d"
      },
      "source": [
        "distribution of returns by product"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "daec238a-ebda-4675-a4ec-64e6833f0976"
      },
      "outputs": [],
      "source": [
        "sns.distplot(df.Dev_uni_proxima_sum)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "2aa1cd8e-2766-4c7c-8c24-9e18a923d694"
      },
      "outputs": [],
      "source": [
        "sns.distplot(np.log1p(df.Dev_uni_proxima_sum))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "72e1411c-c950-47c1-919b-cba7eb24ddd4"
      },
      "source": [
        "Distribution of counts by product"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "4a97e3a7-374d-4f05-9276-2ac384fbb31f"
      },
      "outputs": [],
      "source": [
        "sns.distplot(df.Dev_uni_proxima_count)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "cbb385f5-6f76-41c2-b546-98e4a60dd2b4"
      },
      "outputs": [],
      "source": [
        "sns.distplot(np.log1p(df.Dev_uni_proxima_count))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "0f47bdaa-bac2-48fc-a8b6-04a76bb91aa9"
      },
      "source": [
        "#### lets aggregate by week and short_name now"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "7924a7aa-835a-4ddb-8e5d-92e4748a0a8f"
      },
      "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": "264ed5c5-3968-4dbf-bb95-e344633df4da"
      },
      "outputs": [],
      "source": [
        "df_hmp.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "5b92a9c3-6a7d-4bff-88fd-ca4434062698"
      },
      "source": [
        "#### a quick check if demand distribution changes week to week"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "6f0564a3-0512-4ea9-98ad-4621b1f3fed3"
      },
      "outputs": [],
      "source": [
        "df_hmp['log1p_Demanda_uni_equil_sum'] = np.log1p(df_hmp.Demanda_uni_equil_sum)\n",
        "g = sns.FacetGrid(df_hmp, row = 'Semana')\n",
        "g = g.map(sns.distplot, 'log1p_Demanda_uni_equil_sum')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "b4b48320-8c42-4bce-8723-7b30efa2b192"
      },
      "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": "e8a8e88e-b628-412e-8f9a-ebf90505188e"
      },
      "outputs": [],
      "source": [
        "from bokeh.charts import HeatMap\n",
        "from bokeh.plotting import vplot\n",
        "\n",
        "heatmaps = []\n",
        "for 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)\n",
        "show(vplot(*heatmaps))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "6d7327f7-01b9-44b9-8c03-db0431b71185"
      },
      "source": [
        "#### Same series of charts but for returns"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "cf5aff92-cec3-4fc3-b6e3-c1e0b984090e"
      },
      "outputs": [],
      "source": [
        "from bokeh.charts import HeatMap\n",
        "from bokeh.plotting import vplot\n",
        "df['Q_ret'] = pd.qcut(df.Dev_uni_proxima_sum, 5)\n",
        "heatmaps = []\n",
        "for 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)\n",
        "show(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
}