{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "ea36d8c8-357b-562e-14fe-d46e204ed0fa"
      },
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "import matplotlib.pyplot as plt\n",
        "import seaborn as sns\n",
        "import numpy as np\n",
        "sns.set_style('whitegrid')\n",
        "%matplotlib inline"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "be1a6721-95c7-8195-fef6-2811b316e18d"
      },
      "source": [
        "Based on an excellent Notebook https://www.kaggle.com/omarelgabry/rossmann-store-sales/a-journey-through-rossmann-stores"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "2ae9dbd8-af99-8e32-29ad-4e777c40e36c"
      },
      "outputs": [],
      "source": [
        "df_train = pd.read_csv(\"../input/train.csv\")\n",
        "df_store = pd.read_csv(\"../input/store.csv\")\n",
        "df_test = pd.read_csv(\"../input/test.csv\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "c103c034-9acd-3917-a8ff-92c8c96346cc"
      },
      "source": [
        "List important attributes of the training dataset"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "5b4348ff-11a4-f2d2-fcd9-75aba9d8cbb1"
      },
      "outputs": [],
      "source": [
        "df_train.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "cb2d5756-6953-2bad-cdcf-8da460b6256d"
      },
      "source": [
        "List important attributes of the stores"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "27c7192e-8ec5-8761-e563-bb4561fde61d"
      },
      "outputs": [],
      "source": [
        "df_store.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "819609a4-a38f-7695-6bf0-0d9a4a4dd2a6"
      },
      "source": [
        "When are the stores open and closed?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "844237ad-4129-3363-b491-ea3487192d45"
      },
      "outputs": [],
      "source": [
        "fig, (axis1) = plt.subplots(1,1,figsize=(15,4))\n",
        "sns.countplot(x = 'Open', hue = 'DayOfWeek', data = df_train,)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "644c65a7-eb9d-9b69-9a45-09bf27f8d671"
      },
      "source": [
        "Split date into year and month"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "c450b254-426f-cbb4-187d-91a572fa6246"
      },
      "outputs": [],
      "source": [
        "df_train['Year'] = df_train['Date'].apply(lambda x: int(x[:4]))\n",
        "df_train.Year.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "799c2170-51df-b457-eb94-34f3169113a5"
      },
      "outputs": [],
      "source": [
        "df_train['Month'] = df_train['Date'].apply(lambda x: int(x[5:7]))\n",
        "df_train.Month.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "fecd489b-458a-c4fd-d8fa-1cc68ec6011c"
      },
      "source": [
        "How are the sales distribuetd over the months?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "5f899c7d-a85c-aa7e-5e13-2fb90c7787ce"
      },
      "outputs": [],
      "source": [
        "average_monthly_sales = df_train.groupby('Month')[\"Sales\"].mean()\n",
        "fig = plt.subplots(1,1,sharex=True,figsize=(10,5))\n",
        "average_monthly_sales.plot(legend=True,marker='o',title=\"Average Sales\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "e15775cd-d44a-1fb2-5d9b-8fb1694914fd"
      },
      "source": [
        "What is the pattern of daily sales? Any periodic peaks, seasonality?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "51459d63-ddc5-f5f2-433c-f3c3e9b78627"
      },
      "outputs": [],
      "source": [
        "average_daily_sales = df_train.groupby('Date')[\"Sales\"].mean()\n",
        "fig = plt.subplots(1,1,sharex=True,figsize=(25,8))\n",
        "average_daily_sales.plot(title=\"Average Daily Sales\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "27b5c2a5-cecf-d335-e8ae-2ab551ceab18"
      },
      "source": [
        "Are the sales correlated with customer visits?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "69f1b84e-d384-4728-314c-d8254871a3f9"
      },
      "outputs": [],
      "source": [
        "average_daily_visits = df_train.groupby('Date')[\"Customers\"].mean()\n",
        "fig = plt.subplots(1,1,sharex=True,figsize=(25,8))\n",
        "average_daily_visits.plot(title=\"Average Daily Visits\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "e0feaeae-63e3-2c4e-d0d8-334efbf33e0a"
      },
      "source": [
        "How to average monthly sales evolve? Per centage change from Month-to-Month"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "6c5a8752-703d-b073-dc7b-e951811c4c5b"
      },
      "outputs": [],
      "source": [
        "fig, (axis1,axis2) = plt.subplots(2,1,sharex=True,figsize=(15,8))\n",
        "\n",
        "average_monthly_sales = df_train.groupby('Month')[\"Sales\"].mean()\n",
        "\n",
        "# plot average sales over time (year-month)\n",
        "ax1 = average_monthly_sales.plot(legend = False, ax = axis1, marker = 'o', \n",
        "                                title = \"Avg. Monthly Sales\")\n",
        "\n",
        "ax1.set_xticks(range(len(average_monthly_sales)))\n",
        "ax1.set_xticklabels(average_monthly_sales.index.tolist(), rotation=90)\n",
        "\n",
        "average_monthly_sales_change = df_train.groupby('Month')[\"Sales\"].sum().pct_change()\n",
        "# plot precent change for sales over time(year-month)\n",
        "ax2 = average_monthly_sales_change.plot(legend = False, ax = axis2, marker = 'o', \n",
        "                                        colormap = \"summer\", title = \"% Change Monthly Sales\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "39dff227-850f-c258-e498-d59d92e1c77e"
      },
      "source": [
        "Side by side view of monthly average monthly sales and customer visits (single most crucial factors)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "ffb8be36-e6d2-5926-9826-d3b84b6b5770"
      },
      "outputs": [],
      "source": [
        "fig, (axis1,axis2) = plt.subplots(1,2,figsize=(15,4))\n",
        "\n",
        "sns.barplot(x ='Month', y ='Sales', data = df_train, ax=axis1)\n",
        "sns.barplot(x ='Month', y ='Customers', data = df_train, ax=axis2)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "dd440345-80c3-6a97-396b-7440339499eb"
      },
      "source": [
        "How do weekly sales and customer visit look? Mondays and Fridays are important!"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "574324ec-3651-8efe-eb09-ad15054f8da2"
      },
      "outputs": [],
      "source": [
        "fig, (axis1,axis2) = plt.subplots(1,2,figsize=(15,4))\n",
        "\n",
        "sns.barplot(x='DayOfWeek', y='Sales', data = df_train, order = [1,2,3,4,5,6,7], ax = axis1)\n",
        "sns.barplot(x='DayOfWeek', y='Customers', data = df_train, order = [1,2,3,4,5,6,7], ax = axis2)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "94262300-6358-e10b-096d-0e7694683a1b"
      },
      "source": [
        "A year-wise sales profile weighted against promotions! Promotions clearly lift the sales."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "5de6d9a3-5b10-0220-ef7d-244cb5089281"
      },
      "outputs": [],
      "source": [
        "sns.factorplot(x =\"Year\", y =\"Sales\", hue =\"Promo\", data = df_train,\n",
        "                   size = 6, kind =\"box\", palette =\"muted\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "0615a7b8-4b12-4a0a-f113-0ba43110c3a0"
      },
      "outputs": [],
      "source": [
        "df_train.StateHoliday.unique()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "8f4975f4-f1ab-c2c4-89e2-ac7201696541"
      },
      "outputs": [],
      "source": [
        "df_train['StateHoliday'] = df_train['StateHoliday'].replace(0, '0')\n",
        "df_train.StateHoliday.unique()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "cd4bd0ed-03f1-468b-925b-5dd48b46868c"
      },
      "source": [
        "State holidays means no sales. Store = Closed?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "faf074bb-45d0-3aac-a077-da4e83634bb9"
      },
      "outputs": [],
      "source": [
        "sns.factorplot(x =\"Year\", y =\"Sales\", hue =\"StateHoliday\", data = df_train, \n",
        "               size = 6, kind =\"bar\", palette =\"muted\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "33c98b3b-f1a5-112b-ad88-8664ae79cf6c"
      },
      "outputs": [],
      "source": [
        "df_train[\"HolidayBin\"] = df_train['StateHoliday'].map({\"0\": 0, \"a\": 1, \"b\": 1, \"c\": 1})\n",
        "df_train.HolidayBin.unique()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "f5a54b42-3d58-8900-70af-3331fda9f39d"
      },
      "source": [
        "Sales disappear on holidays! Stores = Closed"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "b3ba4361-4ea9-81ab-7914-342f55eaa901"
      },
      "outputs": [],
      "source": [
        "sns.factorplot(x =\"Month\", y =\"Sales\", hue =\"HolidayBin\", data = df_train, \n",
        "               size = 6, kind =\"bar\", palette =\"muted\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "3097629f-b9ee-6382-ed3c-2b1f617014dc"
      },
      "source": [
        "Weekly profile during the holidays. Promotions lift sales/customer visits. But nothing more than that!"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "7400247d-54c8-3091-3d5c-6db70496cad1"
      },
      "outputs": [],
      "source": [
        "sns.factorplot(x=\"DayOfWeek\", y=\"Customers\", hue=\"HolidayBin\", col=\"Promo\", data=df_train,\n",
        "                   capsize=.2, palette=\"YlGnBu_d\", size=6, aspect=.75)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "f4b3c78c-b3c1-f1a4-4119-f7c12581deac"
      },
      "outputs": [],
      "source": [
        "df_train.SchoolHoliday.unique()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "d2303953-d591-13f6-3431-a6f876ff3819"
      },
      "source": [
        "School holidays are not distinctive. Promotions lift the sales/customer visits"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "07735130-2095-a324-f117-076c184488fe"
      },
      "outputs": [],
      "source": [
        "sns.factorplot(x=\"DayOfWeek\", y=\"Customers\", hue=\"SchoolHoliday\", col=\"Promo\", data=df_train,\n",
        "                   capsize=.2, palette=\"YlGnBu_d\", size=6, aspect=.75)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "62fa193b-0088-6814-b013-aeb2b83274e4"
      },
      "outputs": [],
      "source": [
        "average_customers = df_train.groupby('Month')[\"Customers\"].mean()\n",
        "average_sales = df_train.groupby('Month')['Sales'].mean()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "ee1ba5a7-3b04-51c1-ceed-e59dafdd689e"
      },
      "source": [
        "Average monthly sales profiles. December is important, then July."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "54545bf9-a9b8-dc66-22fd-19e245c5f71c"
      },
      "outputs": [],
      "source": [
        "fig, (axis1,axis2) = plt.subplots(1,2,figsize=(15,4))\n",
        "sns.barplot(average_sales.index, average_sales.values,ax=axis1)\n",
        "sns.barplot(average_customers.index, average_customers.values,ax=axis2)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "b186c1f4-312c-4ffc-49b9-dd8ca5136e91"
      },
      "source": [
        "Histogram of sales and customer visits"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "b0457aa7-5bc7-568f-be8b-2af7c53e711b"
      },
      "outputs": [],
      "source": [
        "fig, (axis1,axis2) = plt.subplots(1,2,figsize=(15,4))\n",
        "sns.distplot(df_train.Sales, color=\"m\",ax = axis1)\n",
        "sns.distplot(df_train.Customers, color=\"r\",ax = axis2)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "5bdd2cfc-03e8-6db2-8940-e2c932237d15"
      },
      "source": [
        "Store attributes such as assortment, competition"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "e1d4dc2a-9333-d34d-57f0-2b375aed4d3c"
      },
      "outputs": [],
      "source": [
        "df_store.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "f0b234ca-3179-197a-f493-411fc2d1da79"
      },
      "outputs": [],
      "source": [
        "df_store.Store.unique()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "0810f0ec-6b7f-781d-09e7-d5252ad79f25"
      },
      "outputs": [],
      "source": [
        "df_train.Store.unique()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "ad71b7a4-52e4-cb21-14a3-87f8fd1638f2"
      },
      "source": [
        "Sum sales and customer visits for each stores. Alternative is to use mean (used later)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "514d7961-9ed9-ce30-443a-0eb4a7367228"
      },
      "outputs": [],
      "source": [
        "total_sales_customers =  df_train.groupby('Store')['Sales', 'Customers'].sum()\n",
        "total_sales_customers.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "95c020a4-ff90-0978-1bf2-f509409c8013"
      },
      "outputs": [],
      "source": [
        "df_total_sales_customers = pd.DataFrame({'Sales':  total_sales_customers['Sales'],\n",
        "                                         'Customers': total_sales_customers['Customers']}, \n",
        "                                         index = total_sales_customers.index)\n",
        "\n",
        "df_total_sales_customers = df_total_sales_customers.reset_index()\n",
        "df_total_sales_customers.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "9b64ce24-926c-ec90-86fb-9e25455cb842"
      },
      "outputs": [],
      "source": [
        "avg_sales_customers =  df_train.groupby('Store')['Sales', 'Customers'].mean()\n",
        "avg_sales_customers.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "8b319e5a-df1a-3c52-aeb8-6796d3079a1e"
      },
      "source": [
        "Combine store data with average store sales and customer visits"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "b7cc1b5f-638e-618a-86e7-e26c269d1ea3"
      },
      "outputs": [],
      "source": [
        "df_avg_sales_customers = pd.DataFrame({'Sales':  avg_sales_customers['Sales'],\n",
        "                                         'Customers': avg_sales_customers['Customers']}, \n",
        "                                         index = avg_sales_customers.index)\n",
        "\n",
        "df_avg_sales_customers = df_avg_sales_customers.reset_index()\n",
        "\n",
        "df_stores_avg = df_avg_sales_customers.join(df_store.set_index('Store'), on='Store')\n",
        "df_stores_avg.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "73d7896b-d1c5-ac32-83e2-e6c9ac111a7c"
      },
      "outputs": [],
      "source": [
        "df_stores_new = df_total_sales_customers.join(df_store.set_index('Store'), on='Store')\n",
        "df_stores_new.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "5dbf863e-9092-699b-5ab5-6471af11cc71"
      },
      "source": [
        "Store sales and customer visits across store types"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "358491a8-9cdb-7e74-67eb-5c0e4ac7b01b"
      },
      "outputs": [],
      "source": [
        "average_storetype = df_stores_new.groupby('StoreType')['Sales', 'Customers'].mean()\n",
        "\n",
        "fig, (axis1,axis2) = plt.subplots(1,2,figsize=(15,4))\n",
        "sns.barplot(average_storetype.index, average_storetype['Sales'], ax=axis1)\n",
        "sns.barplot(average_storetype.index, average_storetype['Customers'], ax=axis2)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "6e88d4ae-a1c0-7a1f-79ee-01a5e53f0cd5"
      },
      "source": [
        "Store sales and customer visits across assortment type"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "b55c200c-184d-ce2d-a055-84a7be917d4c"
      },
      "outputs": [],
      "source": [
        "average_assortment = df_stores_new.groupby('Assortment')['Sales', 'Customers'].mean()\n",
        "\n",
        "fig, (axis1,axis2) = plt.subplots(1,2,figsize=(15,4))\n",
        "sns.barplot(average_assortment.index, average_assortment['Sales'], ax=axis1)\n",
        "sns.barplot(average_assortment.index, average_assortment['Customers'], ax=axis2)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "fec3114a-3661-6712-3d1a-480fc953fe79"
      },
      "source": [
        "Correlation with store attribtues. Customer visits and sales are naturally correlated"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "32fffc06-16c1-7b30-3456-44a6be50791f"
      },
      "outputs": [],
      "source": [
        "stores_sales_corr = df_stores_new[['Customers', 'Sales', 'CompetitionDistance', 'Promo2']]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "380383e4-1aef-684e-b5ae-24e71fb96c09"
      },
      "outputs": [],
      "source": [
        "stores_sales_corr.corr()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "3d032c3a-49f4-fbcc-62e9-d7d19d8b80d4"
      },
      "outputs": [],
      "source": [
        "sns.jointplot(df_stores_new.Sales, df_stores_new.CompetitionDistance, kind = 'scatter', size = 8)\n",
        "#sns.jointplot(df_stores_new.Customers, df_stores_new.CompetitionDistance, kind = 'scatter', size = 10)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "5496a962-d024-0c13-f319-122f9e859da8"
      },
      "source": [
        "Using MonthYear to sum up sales and customer is nice way to go deep but not too deep"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "4ea00dea-232a-2d05-fc9b-6adadab708bf"
      },
      "outputs": [],
      "source": [
        "store_ids = [169]\n",
        "df_select_stores = df_train[df_train.Store.isin(store_ids)]\n",
        "df_select_stores['MonthYear'] = df_select_stores.Date.apply(lambda x: str(x)[:7])\n",
        "df_select_stores.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "d192f375-e9f6-a227-e131-3078466c3467"
      },
      "outputs": [],
      "source": [
        "average_store_sales = df_select_stores.groupby(['MonthYear'])['Sales', 'Customers'].mean()\n",
        "average_store_sales.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "db6c75e4-a2eb-3b27-238f-39576d96b7dd"
      },
      "outputs": [],
      "source": [
        "average_store_sales = average_store_sales.reset_index()\n",
        "average_store_sales.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "8a9d83b4-4b17-d493-103c-be5120862eed"
      },
      "source": [
        "How competition affects to lower store sales"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "82e2463a-5a0a-b8e4-d816-685ef1ce52b1"
      },
      "outputs": [],
      "source": [
        "ax = average_store_sales['Sales'].plot(legend=True, marker='o', figsize=(15,4))\n",
        "\n",
        "start, end = ax.get_xlim()\n",
        "labels = list(np.arange(start, end, 1))\n",
        "\n",
        "ax.set_xticks(labels)\n",
        "ax.set_xticklabels(average_store_sales.iloc[labels]['MonthYear'], rotation = 90)\n",
        "\n",
        "# competitor begins\n",
        "y = df_store[\"CompetitionOpenSinceYear\"].loc[df_store[\"Store\"]  == store_ids[0]].values[0]\n",
        "m = df_store[\"CompetitionOpenSinceMonth\"].loc[df_store[\"Store\"] == store_ids[0]].values[0]\n",
        "\n",
        "#\n",
        "ax.axvline(x = ((y - 2013) * 12) + (m - 1), linewidth = 3, color = 'grey')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "f1bb4ff8-105e-0432-6791-ea1cd22f2edc",
        "collapsed": true
      },
      "outputs": [],
      "source": [
        "from scipy import stats"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "3c57cab5-89f2-9654-702b-3e407f49fcac"
      },
      "outputs": [],
      "source": [
        "sns.jointplot(x=\"Sales\", y=\"Customers\", data=df_stores_avg, kind=\"hex\",\n",
        "              color='k',\n",
        "              ratio=3);"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "aa8d7575-99fe-c1ea-faab-e7aa732e18b9"
      },
      "outputs": [],
      "source": [
        "sns.distplot(df_stores_avg.Sales, kde=False, fit=stats.norm);"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "e9af9771-ef3c-e060-5204-1311f06222ca"
      },
      "source": [
        "Process test dataset for predictions"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "24f3a780-60d3-5ca3-d639-e252ad80b098"
      },
      "outputs": [],
      "source": [
        "df_test.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "aa61942a-d555-0a22-af6e-12e79d635086"
      },
      "outputs": [],
      "source": [
        "df_test.info()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "d2a4a96d-06e2-ae75-c138-2e4c3d6de510"
      },
      "outputs": [],
      "source": [
        "#\n",
        "df_test['Year'] = df_test['Date'].apply(lambda x: int(x[:4]))\n",
        "df_test.Year.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "1d47bff8-3fd5-e157-e25f-343e948f063d"
      },
      "outputs": [],
      "source": [
        "df_test['Month'] = df_test['Date'].apply(lambda x: int(x[5:7]))\n",
        "df_test.Month.unique()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "1cd21c52-082a-99c2-c227-91ec7e0fa21b"
      },
      "outputs": [],
      "source": [
        "df_test['MonthYear'] = df_test['Date'].apply(lambda x: str(x)[:7])\n",
        "df_test.MonthYear.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "76320f80-786c-136c-32de-1f4befbd3ab0"
      },
      "outputs": [],
      "source": [
        "df_test[\"HolidayBin\"] = df_test.StateHoliday.map({\"0\": 0, \"a\": 1, \"b\": 1, \"c\": 1})\n",
        "df_test.HolidayBin.unique()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "dd3574a3-49ca-cdf1-c710-3c2d1815ee57"
      },
      "outputs": [],
      "source": [
        "df_train.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "df15a054-f299-c3a2-9f39-c8df3d51189f"
      },
      "outputs": [],
      "source": [
        "df_train.columns"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "066c6fef-2e24-0e0b-faf5-0c9617039860",
        "collapsed": true
      },
      "outputs": [],
      "source": [
        "from sklearn.linear_model import LinearRegression"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "00930c22-61f7-e986-a33a-32c4c3fcda4f"
      },
      "outputs": [],
      "source": [
        "df_test = df_test.fillna(df_test.mean())\n",
        "df_test.isnull().any()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "e78b1bbb-dafa-a4a4-5d36-6c33e8af771e",
        "collapsed": true
      },
      "outputs": [],
      "source": [
        "closed_store_ids = df_test[\"Id\"][df_test[\"Open\"] == 0].values\n",
        "\n",
        "# remove all rows(store,date) that were closed\n",
        "df_test = df_test[df_test[\"Open\"] != 0]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "76b0f437-2fe0-ade4-351a-06f82fdd3340"
      },
      "outputs": [],
      "source": [
        "df_test = df_test.drop(['Date', 'MonthYear', 'StateHoliday'], axis=1)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "de0479e4-d42f-0bda-5793-6d4056d1777b"
      },
      "outputs": [],
      "source": [
        "train_stores = dict(list(df_train.groupby('Store')))\n",
        "test_stores = dict(list(df_test.groupby('Store')))\n",
        "submission = pd.Series()\n",
        "scores = []\n",
        "\n",
        "for i in test_stores:\n",
        "    \n",
        "    # current store\n",
        "    store = train_stores[i]\n",
        "    \n",
        "    # define training and testing sets\n",
        "    X_train = store.drop([\"Date\", \"Sales\", \"Customers\", \"Store\", \"StateHoliday\"],axis=1)\n",
        "    Y_train = store[\"Sales\"]\n",
        "    \n",
        "    X_test  = test_stores[i].copy()\n",
        "\n",
        "    \n",
        "    store_ids = X_test[\"Id\"]\n",
        "    X_test.drop([\"Id\",\"Store\"], axis=1,inplace=True)\n",
        "    \n",
        "    # Linear Regression\n",
        "    lreg = LinearRegression()\n",
        "    lreg.fit(X_train, Y_train)\n",
        "    \n",
        "    Y_pred = lreg.predict(X_test)\n",
        "    \n",
        "    scores.append(lreg.score(X_train, Y_train))\n",
        "\n",
        "    # Xgboost\n",
        "    # params = {\"objective\": \"reg:linear\",  \"max_depth\": 10}\n",
        "    # T_train_xgb = xgb.DMatrix(X_train, Y_train)\n",
        "    # X_test_xgb  = xgb.DMatrix(X_test)\n",
        "    # gbm = xgb.train(params, T_train_xgb, 100)\n",
        "    # Y_pred = gbm.predict(X_test_xgb)\n",
        "    \n",
        "    # append predicted values of current store to submission\n",
        "    submission = submission.append(pd.Series(Y_pred, index=store_ids))\n",
        "\n",
        "# append rows(store,date) that were closed, and assign their sales value to 0\n",
        "submission = submission.append(pd.Series(0, index=closed_store_ids))\n",
        "\n",
        "# save to csv file\n",
        "submission = pd.DataFrame({ \"Id\": submission.index, \"Sales\": submission.values})\n",
        "submission.to_csv('rossmann_submission.csv', index=False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "8c1f87d1-fe95-1309-549f-fc9254e31d3c"
      },
      "outputs": [],
      "source": [
        "submission.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "eb3644f0-5ac9-bd08-93cb-eacfd0f0e83e"
      },
      "outputs": [],
      "source": [
        "submission[submission['Id'] == 544]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "0bf1f6ae-cb1d-2ef8-f506-8d8b3316241a"
      },
      "outputs": [],
      "source": [
        ""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "498bffa3-9c2b-e0b6-da71-095b45f7f795",
        "collapsed": true
      },
      "outputs": [],
      "source": [
        ""
      ]
    }
  ],
  "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.6.0"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}