{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "4a1ace5d-9f0c-4fde-a5b8-340146bf4a0e"
      },
      "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": "8e8aa8dc-b4be-4b03-9c49-79d5fb057f5d"
      },
      "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": "fd635b1c-edd3-4d17-86d2-47517ad4e5b5"
      },
      "outputs": [],
      "source": [
        "agg1 = get_product_agg(['Demanda_uni_equil','Dev_uni_proxima'])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "22b3701e-801f-4d91-a491-ca399eedcc1b"
      },
      "outputs": [],
      "source": [
        "agg1.shape"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "7698dddb-1786-474c-961f-11680f171c9e"
      },
      "outputs": [],
      "source": [
        "agg1.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "06f71c3b-a5c9-4b53-a454-c6fd93a58fe8"
      },
      "outputs": [],
      "source": [
        "agg2 = get_product_agg(['Venta_uni_hoy'])\n",
        "agg = agg1.join(agg2)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "ae7e041d-e3d6-465c-8da6-58dae400f26f"
      },
      "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": "0e270693-4bcb-499f-b32c-e2fa44e18393"
      },
      "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": "4e29b985-edde-40b3-81cc-20ee87395a20"
      },
      "outputs": [],
      "source": [
        "products.tail()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "9b03a555-5885-4af1-b844-0772747c12cb"
      },
      "outputs": [],
      "source": [
        "products.short_name.value_counts(dropna=False)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "3262d0dd-5e64-424d-97d3-bf49a4ce7da1"
      },
      "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": "b7a344de-60bb-40ea-bc6d-1f3cbf290426"
      },
      "outputs": [],
      "source": [
        "sns.distplot(products.weight.dropna())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "8dce467b-dbc5-48b2-baab-9dbc52a2fd44"
      },
      "source": [
        "distribution of pieces"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "c802223b-859e-467d-92f0-f036a547e568"
      },
      "outputs": [],
      "source": [
        "sns.distplot(products.pieces.dropna())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "c331acda-31cc-4585-8054-2fd83f54b67b"
      },
      "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": "3741d7ca-2b0c-4e2d-b1bf-f614ad4b16b4"
      },
      "outputs": [],
      "source": [
        "products.short_name.nunique()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "f87b2250-ef9c-48a2-a7a0-3b3f63303669"
      },
      "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": "bf209ca0-9746-4bef-ab74-dbfabce5dea7"
      },
      "outputs": [],
      "source": [
        "from nltk.corpus import stopwords\n",
        "print(stopwords.words(\"spanish\"))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "3f96cebb-8e03-4494-acdb-b1e47b9c7128"
      },
      "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": "5ade4e76-d42e-4e50-b9c2-c6b73ba32577"
      },
      "outputs": [],
      "source": [
        "products['short_name_processed'].nunique()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "db581e6e-1240-4eed-b071-78f153080835"
      },
      "outputs": [],
      "source": [
        "from nltk.stem.snowball import SnowballStemmer\n",
        "stemmer = SnowballStemmer(\"spanish\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "5382d36f-12cd-47ed-963a-4138482fe31e"
      },
      "outputs": [],
      "source": [
        "print(stemmer.stem(\"Tortillas\"))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "28dea2bd-d216-43e6-b50f-eb3af87cde7f"
      },
      "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": "21ebd960-287f-4bbd-9955-849eb9a3cdc0"
      },
      "outputs": [],
      "source": [
        "products.short_name_processed.nunique()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "27605ecc-dae5-410d-9c53-968bc39b841b"
      },
      "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": "f5d0dfa5-7385-4f64-b5cb-6dae840ab0ed"
      },
      "outputs": [],
      "source": [
        "vectorizer.get_feature_names()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "6d5b492b-ee3f-4ad8-b80e-cdb45e823e72"
      },
      "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": "65ca4038-dfce-40c1-8867-291adba74a2b"
      },
      "outputs": [],
      "source": [
        "product_bag_words.drop('Producto_ID', axis=1).sum().sort_values(ascending=False).head(100)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "3f5349c9-e12c-4220-a51f-f1e1de61b5be"
      },
      "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": "3a83564e-7be7-453f-8368-4eafbc2bc0f4"
      },
      "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": "94b4a101-25b3-436e-98af-5eaaf058324f"
      },
      "outputs": [],
      "source": [
        "plt.figure(figsize = (12,15))\n",
        "df.head(50).sort_values().plot(kind='barh')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "7678651a-d6c9-4637-9f0b-1440751572c8"
      },
      "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": "5322c45d-59ea-4b5b-aedf-ae1d5f8be045"
      },
      "source": [
        "#### a quick look at the distributions"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "b6ac7d6c-029d-46e5-8f0a-8a0c6163eaa6"
      },
      "outputs": [],
      "source": [
        "sns.distplot(df)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "d79cf34e-ef5b-436e-8ee2-1c6c40d1e8f0"
      },
      "outputs": [],
      "source": [
        "sns.distplot(np.log1p(df))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "5edcc88e-7ec7-4408-b301-9eb5976fb373"
      },
      "source": [
        "#### expanding the aggregate"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "1281dc6d-c1da-46a3-9463-1e0b05713079"
      },
      "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": "1cd4d928-6619-4b48-ad6d-1949abc090f7"
      },
      "outputs": [],
      "source": [
        "df.describe().T"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "b9f3132d-69a2-4db2-bd76-cd435b7d0e8a"
      },
      "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": "627abe7e-69e2-40be-9854-e880511e8078"
      },
      "outputs": [],
      "source": [
        "df[df.Demanda_uni_equil_sum == 0].count()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "ca39cd2d-6f35-40b7-866a-ddf7c223a498"
      },
      "outputs": [],
      "source": [
        "df[df.Demanda_uni_equil_sum == 0]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "371da03d-b6e6-4056-a62d-500504509203"
      },
      "source": [
        "Similarly there are products with 0 sales and only returns"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "56c00bba-171c-4728-9532-eaa8507ed667"
      },
      "outputs": [],
      "source": [
        "df[df.Venta_uni_hoy_sum == 0]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "a5e9bb86-71c3-41ff-9ea1-11bbf2309bae"
      },
      "source": [
        "there are products that were never returned"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "679edeae-f565-4c71-82a4-17b213651790"
      },
      "outputs": [],
      "source": [
        "df[df.Dev_uni_proxima_sum == 0].count()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "9b4dbd2d-76d4-497b-b754-37f5a760a29d"
      },
      "outputs": [],
      "source": [
        "df[df.Dev_uni_proxima_sum == 0].head(20)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "3f822e89-4957-4b58-96e5-9942244f272d"
      },
      "source": [
        "Let's cut products into 10 quantiles by summary adjusted demand."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "f3f582c6-e3c8-41a2-9d7c-8b80fc641d15"
      },
      "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": "bc8db22c-c088-4c4c-a140-05ea3f9943ec"
      },
      "outputs": [],
      "source": [
        "df[df.Q == '[0, 49]'].index.values"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "59d5643d-fd0a-4733-a3a9-eed8dccc9355"
      },
      "source": [
        "distribution of returns by product"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "62c58651-3a17-4035-a8cb-1f198fc945e2"
      },
      "outputs": [],
      "source": [
        "sns.distplot(df.Dev_uni_proxima_sum)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "e8899c6b-f826-4f8f-935b-3b0c58c19378"
      },
      "outputs": [],
      "source": [
        "sns.distplot(np.log1p(df.Dev_uni_proxima_sum))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "eb0e527b-cf1b-4243-ba7f-54ba601bd61b"
      },
      "source": [
        "Distribution of counts by product"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "2a7c7b40-e70f-4f5a-beee-e32056d19dfd"
      },
      "outputs": [],
      "source": [
        "sns.distplot(df.Dev_uni_proxima_count)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "c831b85a-dd85-4bde-ac52-4475bf42f676"
      },
      "outputs": [],
      "source": [
        "sns.distplot(np.log1p(df.Dev_uni_proxima_count))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "aca27acd-0baa-4c66-8b4a-ca5d4229cdaa"
      },
      "source": [
        "#### lets aggregate by week and short_name now"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "e74c86bc-1fe9-4f8a-8653-241762d3f585"
      },
      "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": "e2045dd2-fd61-4b2c-9d00-83cd9ac5ff78"
      },
      "outputs": [],
      "source": [
        "df_hmp.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "5fa93acf-4c86-45bf-8e3b-9ca9a6e2f307"
      },
      "source": [
        "#### a quick check if demand distribution changes week to week"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "a209ef00-1245-43d6-964b-7d97c7ccc0ea"
      },
      "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": "ec201ab5-565b-43b1-ab95-e35ea3a31d96"
      },
      "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": "65ed4a6d-df85-4d0e-808a-981dc639abe1"
      },
      "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": "e16fde15-82cc-494f-8582-b8d2a2ad7548"
      },
      "source": [
        "#### Same series of charts but for returns"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "e02f39ca-6af9-44f9-8e19-8198b4236e02"
      },
      "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
}