{"nbformat_minor": 1, "metadata": {"kernelspec": {"language": "python", "display_name": "Python 3", "name": "python3"}, "language_info": {"nbconvert_exporter": "python", "codemirror_mode": {"version": 3, "name": "ipython"}, "version": "3.6.4", "pygments_lexer": "ipython3", "file_extension": ".py", "mimetype": "text/x-python", "name": "python"}}, "cells": [{"metadata": {"_cell_guid": "37f34e7f-c446-4f0d-a555-ed840d2da2b6", "_uuid": "d2d63809e071f9cf6c13fede6baf9539646ed049"}, "cell_type": "markdown", "source": ["***2017-12-31***\n", "\n", "[Click here for a short video introduction of this kernel](https://youtu.be/12Dqik58SEo)\n", "\n", "* [Introduction](#introduction)\n", "* [Data](#data)\n", "* [Preparations](#preparation)\n", "* [Data exploration](#exploration)\n", "    * [Stores Data](#store)\n", "    * [Transaction Data](#transaction)\n", "    * [Oil Data](#oil)\n", "    * [Items Data](#item)\n", "    * [Holiday Data](#holiday)\n", "    "]}, {"metadata": {"_cell_guid": "e75d8c42-94f0-4145-95d3-47132caf2a38", "_uuid": "e6124386fd1d7f6850e1b8a2e66d459f7fc0e393"}, "cell_type": "markdown", "source": ["# Introduction<a id='introduction'></a>\n"]}, {"metadata": {"_cell_guid": "b70bcfc3-61ba-40a8-880e-83e468395449", "_uuid": "e7e44658b61f006cd07d8aada038a749f6f83931"}, "cell_type": "markdown", "source": ["This notebook is created for [ Corporaci\u00f3n Favorita Grocery Sales Forecasting](https://www.kaggle.com/c/favorita-grocery-sales-forecasting) at Kaggle. The aim of this notebook is to get an ***intuitive and interactive analysis*** on supplementary data. \n", "\n", "We created this notebook with python ([pandas](http://pandas.pydata.org/)) and visualization toolboxs ([plotly](https://plot.ly/python/), [squarify](https://github.com/laserson/squarify)). And I obtained the geo information of Ecuador from [VictorGrobberio](https://www.kaggle.com/victorgrobberio/datasets), and it enables us to put sales data onto the map of Ecuador, which makes analysis much more intuitive. \n", "\n", "**Here is my [LinkedIn](https://www.linkedin.com/in/botao-deng-9a632a104/) profile, and if you wanted a discussion with regard to the content of this notebook, feel free to connect. BTW, I'm open to data-related position for now, so if you think I can contribute to your company after reading this notebook, please let me know. :)**"]}, {"metadata": {"_cell_guid": "9ac23f95-f4da-4579-96f3-a9498bd13e67", "_uuid": "4c9d83683801b2143d7b2b01f5e69d12bef29d51"}, "cell_type": "markdown", "source": ["# Data <a id='data)'></a>"]}, {"metadata": {"_cell_guid": "71e90b35-a4c2-41f2-8c7f-c80b2571dc35", "_uuid": "3f1a5b5db13f281519c6b14cbebc5a9addd877ec"}, "cell_type": "markdown", "source": ["There are five supplementary files in this challenge, and each of them provided information below([source](https://www.kaggle.com/c/favorita-grocery-sales-forecasting/data)):\n", "\n", " `stores.csv:` Metadata of a store, including the `city` , `state`, `type` and `cluster` it belongs to.\n", " \n", " `transactions.csv:` The count of sales transactions for each date and each store.\n", " \n", " `oil.csv:` Daily oil price. Includes values during both the train and test data timeframe. (Ecuador is an oil-dependent country and it's economical health is highly vulnerable to shocks in oil prices.)\n", " \n", " `items.csv:` Metadata of an item. including which `family` and `class` it belongs to and if it's `perishable` or not.\n", " \n", " `holidays_events.csv:` Holidays in Ecuador. Some holidays can be transferred to another day (possibly from weekend to weekday)"]}, {"metadata": {"_cell_guid": "626786d9-e0e0-4094-ba61-994171337155", "_uuid": "d005dd0bd32c6ff80cea0280ee2ac233ff059fe5"}, "cell_type": "markdown", "source": ["# Preparations <a id='preparation'></a>"]}, {"execution_count": null, "outputs": [], "source": ["import pandas as pd\n", "import numpy as np\n", "import plotly.graph_objs as go\n", "import plotly.tools as tls\n", "import plotly.plotly as py\n", "import plotly.offline as py\n", "import squarify\n", "import seaborn as sns\n", "import geojson\n", "import IPython.display\n", "\n", "py.init_notebook_mode(connected=True)"], "cell_type": "code", "metadata": {"_cell_guid": "918e27bb-0915-49f1-9308-2f06ca9af2da", "_uuid": "a8102b54dff5932c4131f818d923077d0acd37d5", "collapsed": true, "_kg_hide-output": false, "_kg_hide-input": false}}, {"execution_count": null, "outputs": [], "source": ["items = pd.read_csv('../input/favorita-grocery-sales-forecasting/items.csv')\n", "holiday = pd.read_csv(\"../input/favorita-grocery-sales-forecasting/holidays_events.csv\")\n", "stores = pd.read_csv(\"../input/favorita-grocery-sales-forecasting/stores.csv\")\n", "oil = pd.read_csv(\"../input/favorita-grocery-sales-forecasting/oil.csv\")\n", "transaction = pd.read_csv(\"../input/favorita-grocery-sales-forecasting/transactions.csv\", parse_dates=['date'])\n", "train = pd.read_csv(\"../input/favorita-grocery-sales-forecasting/train.csv\", nrows=6000000, parse_dates=['date'])"], "cell_type": "code", "metadata": {"_cell_guid": "61d3f117-912a-493b-aa73-1338e195deec", "_uuid": "d335a302e875e52dbb43eb538fc599fa4b158513", "collapsed": true, "_kg_hide-input": false}}, {"metadata": {"_cell_guid": "4a2059d9-5e28-474a-b5e2-52389a9c9e27", "_uuid": "028c1b0bd893f235df73547110934a296cf41684"}, "cell_type": "markdown", "source": ["# Data Exploration<a id='exploration'></a>"]}, {"metadata": {"_cell_guid": "d82cf28a-eeac-4c0a-8d1b-18b96ef0b550", "_uuid": "f55cc54fafe10df85b749b341215b492186ff3d9"}, "cell_type": "markdown", "source": ["### Stores data<a id='store'></a>:"]}, {"execution_count": null, "outputs": [], "source": ["# Take a look at the data. \n", "# 22 cities, 16 states\n", "stores.head(5)"], "cell_type": "code", "metadata": {"scrolled": true, "_cell_guid": "b7f7cf13-c845-4aa2-876c-4a67d92a30ef", "_uuid": "3dc7b0a522e1c536fc08455f20b1bc83a9da10ad", "collapsed": true, "_kg_hide-input": false}}, {"execution_count": null, "outputs": [], "source": ["stores['city'] = list(map(str.upper, stores['city']))\n", "stores['state'] = list(map(str.upper, stores['state']))"], "cell_type": "code", "metadata": {"_cell_guid": "a2f1c8d7-1587-4506-9a07-25a92a42d0e3", "_uuid": "c125b6d1b37fc69c5c02e21f1179bcfd40e72b32", "collapsed": true, "_kg_hide-output": false, "_kg_hide-input": false}}, {"execution_count": null, "outputs": [], "source": ["number_of_stores_per_city = {}\n", "store_per_type = stores.groupby(['city','type']).store_nbr.size().unstack().fillna(0)\n", "store_per_cluster = stores.groupby(['city','cluster']).store_nbr.size().unstack().fillna(0)\n", "\n", "for i, c in enumerate(stores.city.value_counts().index):\n", "    number_of_stores_per_city[c] = [stores.city.value_counts().values[i]]\n", "    number_of_stores_per_city[c].extend(store_per_type.loc[c].values)\n", "    number_of_stores_per_city[c].extend(store_per_cluster.loc[c].values)\n", "\n", "cities = [s.upper() for s in stores.city.unique()]\n", "states = [s.upper() for s in stores.state.unique()]"], "cell_type": "code", "metadata": {"_cell_guid": "9c1fb140-05a1-4b4a-98f3-e3698b945712", "_uuid": "6cd2a3bed8bf5671c53cae1ec4bf63fbc148ce95", "collapsed": true, "_kg_hide-output": false, "_kg_hide-input": false}}, {"execution_count": null, "outputs": [], "source": ["\n", "with open(\"../input/ecuador-geo-info/ecuador.geojson\") as json_file:\n", "    json_data = geojson.load(json_file)"], "cell_type": "code", "metadata": {"_cell_guid": "daf3e746-627b-4414-af24-7310b52cf888", "_uuid": "378ff89f714484c4f071c9ab1e9c3cc6cf7e710d", "collapsed": true, "_kg_hide-output": false, "_kg_hide-input": false}}, {"execution_count": null, "outputs": [], "source": ["patches, lons, lats, text = [], [], [], []\n", "for k, feature in enumerate(json_data['features']):\n", "    state = feature['properties']['DPA_DESPRO']\n", "    city = feature['properties']['DPA_DESCAN']\n", "    \n", "    if(city not in cities):\n", "        continue\n", "    \n", "    m, M = np.array(feature[\"geometry\"][\"coordinates\"][0][0])[:,0].max(), np.array(feature[\"geometry\"][\"coordinates\"][0][0])[:,0].min()\n", "    lons.append(0.5*(m+M))\n", "    \n", "    m, M = np.array(feature[\"geometry\"][\"coordinates\"][0][0])[:,1].max(),np.array(feature[\"geometry\"][\"coordinates\"][0][0])[:,1].min()\n", "    lats.append(0.5*(m+M)) \n", "    \n", "    num_of_stores = number_of_stores_per_city[city]\n", "    t = \"State: \" + state + '<br>' + \"City: \" + city + '<br> ' + \\\n", "        \"Number of stores: \" + str(num_of_stores[0])\n", "    \n", "    text.append(t)\n", "    \n", "    sub = {'type':\"FeatureCollection\"}\n", "    sub['features'] = [json_data['features'][k]]\n", "    sub['number'] = num_of_stores\n", "    patches.append(sub) # patches is a list of dictionary. \n"], "cell_type": "code", "metadata": {"_cell_guid": "1282d256-9541-4d8f-b63c-69dd341c00cf", "_uuid": "9ad71ff445e1c6685446b9a76df8c61d533830bd", "collapsed": true, "_kg_hide-output": false, "_kg_hide-input": false}}, {"execution_count": null, "outputs": [], "source": ["# A list of layers, each layer cooresponds to a city. \n", "# Representing how many stores opened in each city. \n", "stores_per_city_layers = []\n", "mapbox_access_token = \"pk.eyJ1IjoiYmF0byIsImEiOiJjamJwZzRvaGE2MTljMzJtcjhzaDJvaXFxIn0.TkTLg13Af-ERPjOWzB-BFQ\"\n", "\n", "for i in range(len(patches)):\n", "    num_stores_each_city = dict(\n", "        sourcetype = \"geojson\",\n", "        source = patches[i],\n", "        type = \"fill\",\n", "        opacity= (1-0.3)/(18-1) * (patches[i]['number'][0]-18)+1,\n", "        color = 'rgb(163,22,190)',\n", "    )\n", "    stores_per_city_layers.append(num_stores_each_city)\n", "\n", "# per_city_layout: the layout for the button representing how \n", "# many stores in each city. \n", "per_city_layout = dict(\n", "        layers=stores_per_city_layers,\n", "        accesstoken=mapbox_access_token,\n", "        bearing=0,\n", "        center=dict(\n", "            lat=-2,\n", "            lon=-78\n", "        ),\n", "        pitch=0,\n", "        zoom=5.2,\n", "        style='light'\n", "    )"], "cell_type": "code", "metadata": {"_cell_guid": "9f9ef00d-28f7-4160-b047-4054b433bf27", "_uuid": "4fc020c667572213b15a43763ecc0ffac612e6b8", "collapsed": true, "_kg_hide-output": false, "_kg_hide-input": false}}, {"execution_count": null, "outputs": [], "source": ["# Create a list of dict, each dict cooresponds to a relayout button for a type\n", "store_type_buttons= []\n", "types_of_stores = ['A','B','C','D','E']\n", "\n", "\n", "for i in range(1, len(types_of_stores)+1):\n", "    type_per_city_layers = []\n", "    for j in range(len(patches)):\n", "        type_i_for_city_j_layer=dict(\n", "            sourcetype = \"geojson\",\n", "            source = patches[j],\n", "            type = \"fill\",\n", "            opacity= patches[j]['number'][i]/6 * 3,\n", "            color = 'rgb(163,22,190)'\n", "        )\n", "        \n", "        type_per_city_layers.append(type_i_for_city_j_layer)\n", "    \n", "    per_type_layout = dict(\n", "        layers=type_per_city_layers,\n", "        accesstoken=mapbox_access_token,\n", "        bearing=0,\n", "        center=dict(\n", "            lat=-2,\n", "            lon=-78\n", "        ),\n", "        pitch=0,\n", "        zoom=5.2,\n", "        style='light'\n", "    )\n", "    \n", "    store_type_buttons.append(\n", "        dict(\n", "            label=\"Type \"+types_of_stores[i-1],\n", "            method=\"relayout\",\n", "            args = ['mapbox', per_type_layout],\n", "        )\n", "        \n", "        \n", "    )"], "cell_type": "code", "metadata": {"_cell_guid": "52ef7c65-f857-493a-bb4b-ab18a3b6c01e", "_uuid": "fc12da4838c876cadec48c762bb40282793e177d", "collapsed": true, "_kg_hide-output": false, "_kg_hide-input": false}}, {"execution_count": null, "outputs": [], "source": ["stores_cluster_buttons = []\n", "cluster_of_stores = range(6,17+6)\n", "\n", "for i in cluster_of_stores:\n", "    cluster_per_city_layers=[]\n", "    for j in range(len(patches)):\n", "        cluster_i_for_city_j_layer=dict(\n", "            sourcetype = \"geojson\",\n", "            source = patches[j],\n", "            type = \"fill\",\n", "            opacity= patches[j]['number'][i]/4 * 3,\n", "            color = 'rgb(163,22,190)'\n", "        )\n", "        \n", "        cluster_per_city_layers.append(cluster_i_for_city_j_layer)\n", "        \n", "    per_cluster_layout = dict(\n", "        layers=cluster_per_city_layers,\n", "        accesstoken=mapbox_access_token,\n", "        bearing=0,\n", "        center=dict(\n", "            lat=-2,\n", "            lon=-78\n", "        ),\n", "        pitch=0,\n", "        zoom=5.2,\n", "        style='light'\n", "    )\n", "    \n", "    stores_cluster_buttons.append(\n", "        dict(\n", "            label=\"Cluster \"+str(i-6),\n", "            method=\"relayout\",\n", "            args = ['mapbox', per_cluster_layout],\n", "        )\n", "        \n", "        \n", "    )"], "cell_type": "code", "metadata": {"_cell_guid": "89242b94-841e-4a8e-97dc-cb954b8c18b7", "_uuid": "528a559d3321e33af77da72600a74edec96856ee", "collapsed": true, "_kg_hide-output": false, "_kg_hide-input": false}}, {"execution_count": null, "outputs": [], "source": ["stores_trace = go.Data([\n", "    go.Scattermapbox(\n", "        visible=True,\n", "        lat=lats,\n", "        lon=lons,\n", "        text = text,\n", "        mode='markers',\n", "        name= \"Number of stores per city\",\n", "        hoverinfo='text',\n", "        showlegend=False,\n", "        marker=dict(size=5, opacity=0)\n", "    )\n", "])\n", "\n", "updatemenu = list([\n", "    \n", "    dict(type=\"buttons\",\n", "         active=0,\n", "        buttons=list([\n", "            dict(label=\"Number of stores\",\n", "                method='relayout',\n", "                args= ['mapbox', per_city_layout],\n", "                ),\n", "            \n", "        ]),\n", "        x = -0.01, y=0.9\n", "    ),\n", "    \n", "    dict(type=\"dropdown\",\n", "        buttons= store_type_buttons,\n", "        x = -0.01, y=0.8\n", "    ),\n", "    \n", "    dict(type=\"dropdown\",\n", "        buttons=stores_cluster_buttons,\n", "        x=-0.01, y=0.7)\n", "    \n", "])\n", "\n", "stores_layout = go.Layout(\n", "    title=\"Number of Stores per city\",\n", "    height=600,\n", "    autosize=True,\n", "    mapbox=dict(\n", "        #layers=stores_per_city,\n", "        accesstoken=mapbox_access_token,\n", "        bearing=0,\n", "        center=dict(\n", "            lat=-2,\n", "            lon=-78\n", "        ),\n", "        pitch=0,\n", "        zoom=5.2,\n", "        style='light'\n", "    ),\n", "    updatemenus=updatemenu\n", ")\n", "\n", "\n", "stores_fig = dict(data=stores_trace, layout=stores_layout)\n", "py.iplot(stores_fig)"], "cell_type": "code", "metadata": {"_cell_guid": "aaf293f6-911f-44e9-9987-21bbf8280e25", "_uuid": "76de611ad58b42e2f2eccaf476cdc37b753fc801", "collapsed": true, "_kg_hide-output": false, "_kg_hide-input": false}}, {"metadata": {"_cell_guid": "32b38e4b-e633-490c-a589-a618b8653347", "_uuid": "d6b9ff7683b4de519e2fd63b0faccd44e5c934fe"}, "cell_type": "markdown", "source": ["\n", "Take away message from the plot above\n", "\n", "1. Favorita stores have not spread over the entire country yet, about half of its store located at the captical, Quito, and Guayaquil, one of the most popular city in Ecuador, serving as the a gateway to Pacific beaches. \n", "\n", "2. The majority of stores located at mid-western Ecuador. \n", "\n", "3. Type A,B,C,D is spreading all over the country. While type E Favorita located just around the Pacific beaches and Esmeraldas, another costal city. So perhaps type E store sells more seaproducts than other types. \n", "\n", "4. There is no clear pattern between geological location of store and store cluster. So the clusters are grouped together based on other factors.  "]}, {"metadata": {"_cell_guid": "50c78ea2-3152-48de-936a-6781f9e79dc2", "_uuid": "eb5b4e57879c01020fbea42c2ed6ad5116dae9e4"}, "cell_type": "markdown", "source": ["### Transaction Data<a id='transaction'></a>"]}, {"execution_count": null, "outputs": [], "source": ["transaction.head(5)"], "cell_type": "code", "metadata": {"_cell_guid": "e3642fd6-4cbe-4fda-8dab-41509a8fb5d0", "_uuid": "92aa5fb468ab1e93c400542052e0bda35bde9380", "collapsed": true, "_kg_hide-input": false}}, {"execution_count": null, "outputs": [], "source": ["transaction['year'] = pd.DatetimeIndex(transaction['date']).year.astype(np.uint16)\n", "transaction['month'] = pd.DatetimeIndex(transaction['date']).month.astype(np.uint8)\n", "transaction['day'] = pd.DatetimeIndex(transaction['date']).day.astype(np.uint8)"], "cell_type": "code", "metadata": {"_cell_guid": "763d6d2e-b4c7-4244-b8ce-2f2603da8fb9", "_uuid": "4629a8e75f19a86b0dc5bfc58e76851ce9877cae", "collapsed": true, "_kg_hide-output": false, "_kg_hide-input": false}}, {"execution_count": null, "outputs": [], "source": ["which_store_trace = []\n", "number_of_stores = 54\n", "transaction_per_month_trace=[]\n", "per_month_transaction = transaction.groupby(['store_nbr', 'year', 'month']).mean()['transactions']\n", "\n", "buttons = [\n", "    \n", "    dict(\n", "        label = \"None\",\n", "        method=\"update\",\n", "        args=[{'visible':[False]*number_of_stores*2}]\n", "    ),\n", "    dict(\n", "        label = \"all stores Daily\",\n", "        method='update',\n", "        args=[{'visible':[True]*number_of_stores+[False]*number_of_stores},\n", "             {\"title\": \"All stores daily transaction from 2013 to 2017\"}]\n", "    ),\n", "]\n", "\n", "monthly_buttons = [\n", "    dict(\n", "        label = \"all stores Monthly\",\n", "        method='update',\n", "        args=[{'visible':[False]*number_of_stores+[True]*number_of_stores},\n", "             {\"title\": \"All stores monthly transaction from 2013 to 2017\"}]\n", "    ),\n", "    dict(\n", "        label = \"None\",\n", "        method=\"update\",\n", "        args=[{'visible':[False]*number_of_stores*2}]\n", "    )\n", "    \n", "]\n", "for i in range(1, number_of_stores+1):\n", "    visible = [False]*number_of_stores*2\n", "    \n", "    which_store_trace.append(go.Scatter(\n", "        x = transaction.loc[lambda df:df.store_nbr==i].date.dt.strftime(\"%Y-%m-%d\"),\n", "        y = transaction.loc[lambda df:df.store_nbr==i].transactions.values,\n", "        name = \"store \"+str(i)+\" daily transactions from 2013 to 2017\",\n", "        visible=False,\n", "    ))\n", "    \n", "    transaction_per_month_trace.append(go.Scatter(\n", "        x = transaction.loc[lambda df:(df.store_nbr==i)].loc[lambda df:(df.day==24)].date.dt.strftime(\"%Y-%m-%d\"),\n", "        y = per_month_transaction.iloc[per_month_transaction.index.get_level_values('store_nbr')==i].values,\n", "        name = \"store \"+str(i)+\" monthly transactions from 2013 to 2017\"\n", "    ))\n", "    \n", "    visible[i-1]=True\n", "    buttons.append(dict(\n", "        label = \"store \" + str(i) + \" Daily\",\n", "        method=\"update\",\n", "        args=[{'visible':visible},\n", "             {'title': \"Store \"+str(i)+\" transactions from 2013 to 2017\"}]\n", "    ))\n", "    visible = [False]*number_of_stores*2\n", "    \n", "    visible[number_of_stores+i-1]=True\n", "    monthly_buttons.append(dict(\n", "        label = \"store \" + str(i) + \" Monthly\",\n", "        method = 'update',\n", "        args = [{'visible':visible},\n", "               {'title':\"Store \"+str(i)+\" monthly transaction from 2013 to 2017\"}]\n", "    ))\n", "\n", "updatemenu = list([\n", "    dict(\n", "        type=\"dropdown\",\n", "        buttons = buttons,\n", "        x = 1.2, y = 0.7\n", "    ),\n", "    \n", "    dict(\n", "        type=\"dropdown\",\n", "        buttons = monthly_buttons,\n", "        x = 1.2,\n", "        y = 0.9,\n", "    )\n", "                  \n", "                  \n", "])\n", "\n", "\n", "transaction_per_store_layout = dict(title=\"Transaction per store\", \n", "                                   showlegend=False, \n", "                                   updatemenus=updatemenu,\n", "                                   yaxis=dict(title=\"Number of transaction\"),\n", "                                    xaxis=dict(rangeslider=dict(), type='date')\n", "                                   )\n", "\n", "transaction_per_store_fig = dict(data=which_store_trace+transaction_per_month_trace, layout=transaction_per_store_layout)\n", "py.iplot(transaction_per_store_fig)"], "cell_type": "code", "metadata": {"_cell_guid": "f6bab1fc-ca5e-40a2-bfa2-b038acf26ed9", "_uuid": "3c0cf1dcb875e0622ac522826195887fb3438149", "collapsed": true, "_kg_hide-input": false}}, {"metadata": {"_cell_guid": "0fd28c0f-3f44-4430-a232-dd8211b3d18a", "_uuid": "da51b003264d6dc5a00eb524c73ba1b54cd213de"}, "cell_type": "markdown", "source": ["1. There are several stores that consistently have higher number of transaction over years. They are store 44, 47, 45, 46, 3. Among them, 44, 45, 46, 47 are stores that located at Quito, and all of them belongs to type A. And I think it's safe to say that type A store probably sells grocery product. Store 3 located at Guayaquil. Therefore, I think large amount of profits comes from the Capital and Guayaquial. \n", "\n", "2. Christmas has transaction spikes every year. \n", "\n", "3. Let's take a closer look at stores that are not located at Quito and Guayaquil. \n", "\t- Store 5, located at Santo Domingo, has fewer and fewer transaction over the years. Especially at 2016 and 2017. \n", "\t- Store 12, located at Latacunga, have the fewest monthly average transactions over the years except mid 2015. Might due to there was some important festival at 2015 Latacunga?\n", "\t- Store 14, located at Ibarra, has a drastically decreasing transaction over the years. And it should be taken care of. \n", "\t- Store 23, located at Ambato, also has a decreasing transaction over the years. \n", "\t- Store 27, located at Daule, has a steady increasing transaction. \n"]}, {"metadata": {"_cell_guid": "cd9dfd38-a93b-411b-95a8-a441c7cde115", "_uuid": "2fc15524f5df82a9b7a693b188e9f813d7b563b7"}, "cell_type": "markdown", "source": ["## Oil data<a id='oil'></a>"]}, {"metadata": {"_cell_guid": "a406c60d-c0bb-4983-8267-c6c6479982ac", "_uuid": "b245537f66514b1b6fe65ceb5d4a58bdbf1268e1"}, "cell_type": "markdown", "source": ["The feature \"dcoilwtico\" stands for \"Crude Oil Prices: West Texas Intermediate (WTI) - Cushing, Oklahoma\". We can see that the last few days in this dataframe is 2017-8-31. And that means it covers the oil price changes for both training and testing set."]}, {"execution_count": null, "outputs": [], "source": ["oil.head(5)"], "cell_type": "code", "metadata": {"_cell_guid": "d3292ebe-7b9b-40c9-9035-6a532d65120e", "_uuid": "bb1e9746bfdc3edbbf02157a776ec43ebf0f12c6", "collapsed": true}}, {"execution_count": null, "outputs": [], "source": ["trace = go.Scatter(\n", "    name= \"Oil prices\",\n", "    x=oil['date'], \n", "    y=oil['dcoilwtico'], \n", "    mode='lines+markers', \n", "    marker=dict(size=2, color = 'rgba(0, 152, 0, .8)'),\n", "    fill='tonexty')\n", "data = [trace]\n", "layout = dict(title=\"Crude Oil prices from 2013.1 to 2017.8 at Oklahoma\", \n", "             yaxis=dict(title=\"Daily Oil price\"))\n", "fig = go.Figure(data = data, layout = layout)"], "cell_type": "code", "metadata": {"_cell_guid": "13fa3a8a-b0f3-4b60-a689-435e3d542f86", "_uuid": "a3059b5b8e90d799fd1f958ea16651a4558650bb", "collapsed": true, "_kg_hide-input": false}}, {"execution_count": null, "outputs": [], "source": ["py.iplot(fig)"], "cell_type": "code", "metadata": {"scrolled": false, "_cell_guid": "e7675251-6cb6-4193-8dd4-1072727b082b", "_uuid": "19d3858db520cc1c4ec6a1218a65ca50d058893d", "_kg_hide-output": false, "collapsed": true, "_kg_hide-input": false}}, {"metadata": {"_cell_guid": "cf07ec6c-de66-4a09-bafc-cbfc7498049d", "_uuid": "7da9afb5c01f14facb894e06387bc8fd358e1d15"}, "cell_type": "markdown", "source": ["Message from the plot: \n", "\n", "Generally the daily oil price goes downward from 2013 to 2017. It reaches its peak around Sep., 2013 with \\$110 mark. But started from July, 2014, there is a drastic drop of oild price from \\$100 to \\$45 until Mar.2015. There are some ups and downs after that. But after Jul.2016, the price is fairly stable around \\$50. "]}, {"metadata": {"_cell_guid": "ac883dd1-1b82-4cd0-9b37-86f1cf187c8f", "_uuid": "0ccb5d578faef843cafe61e4c76112a16202424e"}, "cell_type": "markdown", "source": ["### Items data<a id='item'></a>"]}, {"execution_count": null, "outputs": [], "source": ["items.head(10)"], "cell_type": "code", "metadata": {"scrolled": true, "_cell_guid": "67842bdf-21e8-4062-acf2-faf4bfa6c33b", "_uuid": "2d5265a1e800b56ce709a666ee039802b5bd5a22", "collapsed": true}}, {"metadata": {"_cell_guid": "341f9a7c-dcb2-4c43-bb69-fbaac17a7763", "_uuid": "aedd06251e67802cb6d8e57e9af33aa9dc24b6c3"}, "cell_type": "markdown", "source": ["There are 4 columns of the items data. Each item has a unique mapping from the 'item_nbr', there are 4100 items in this dataset, and there are 4100 unique 'item_nbr'. The 'family' column is pretty self-explantory, it relates to the parent catogory that the item belongs to. Items that are marked as perishable has a score weight of 1.25, while not perishable items' weight is just 1. "]}, {"execution_count": null, "outputs": [], "source": ["trace_item = go.Bar(\n", "    y = items.family.value_counts(ascending=True).index,\n", "    x = items.family.value_counts(ascending=True).values,\n", "    marker=dict(\n", "        color=items.family.value_counts(ascending=True).values,\n", "        colorscale=\"Rainbow\"\n", "    ),\n", "    orientation='h'\n", ")\n", "\n", "layout_item = dict(\n", "    title=\"Counts of items per family\",\n", "    width=800, height=800,\n", "    margin=dict(l=140)\n", ")\n", "\n", "fig_item = go.Figure(data=[trace_item], layout=layout_item)\n", "\n", "py.iplot(fig_item)"], "cell_type": "code", "metadata": {"scrolled": false, "_cell_guid": "7fe52937-8620-475e-956e-b74182ca8b52", "_uuid": "cc965ad3c41fd9a5137a4a605567aa7003c70c53", "collapsed": true, "_kg_hide-input": false}}, {"execution_count": null, "outputs": [], "source": ["trace_item_perishable = go.Bar(\n", "    y = items.loc[lambda df:df.perishable==1].family.value_counts(ascending=True).index,\n", "    x = items.loc[lambda df:df.perishable==1].family.value_counts(ascending=True).values,\n", "    orientation='h',\n", "    name = 'perishable',\n", "    marker=dict(color=\"#F39C12\")\n", ")\n", "\n", "\n", "trace_item_unperishable=go.Bar(\n", "    y = items.loc[lambda df:df.perishable==0].family.value_counts(ascending=True).index,\n", "    x = items.loc[lambda df:df.perishable==0].family.value_counts(ascending=True).values,\n", "    orientation='h',\n", "    name = \"non-perishable\",\n", "    marker=dict(color=\"#3498DB\")\n", ")\n", "\n", "layout_perish = dict(\n", "    width=800, height=800,\n", "    title = \"Perishable item per family vs non-Perish item per family\",\n", "    margin=dict(l=140)\n", ")\n", "\n", "fig = go.Figure(data=[trace_item_perishable, trace_item_unperishable], layout=layout_perish)\n", "\n", "py.iplot(fig)"], "cell_type": "code", "metadata": {"scrolled": false, "_cell_guid": "b328bbb8-37f5-4461-99c4-a8234571e137", "_uuid": "40b6f47419e49488197d266009382abbd05be8fc", "collapsed": true, "_kg_hide-input": false}}, {"metadata": {"_cell_guid": "c8a49fe0-a811-4ac9-9f19-61d8e9138806", "_uuid": "72c14fb876b9b274b665982d828299a6b003695d"}, "cell_type": "markdown", "source": ["Message from the plots above:\n", "\n", "1. We can see that there are more non-perishable item exists in the dataset. \n", "2. No family contains both perishable and non-perishable items, which means we can directly map an item's family to its perishability. \"\n", "3. The top 3 most common items family are \"GROCERY\", \"BEVERAGE\" and \"CLEANING\". "]}, {"execution_count": null, "outputs": [], "source": ["pie_perish_trace = go.Pie(\n", "    labels = ['non-perishable', 'perishable'], \n", "    values = items.perishable.value_counts().values,\n", "    marker=dict(colors=[\"#3498DB\", \"#F39C12\"]),\n", "    text=['non-perishable','perishable'],\n", "    hoverinfo=\"label+value\",\n", "    domain = dict(x=[0, 0.48], y=[0.5, 1]),\n", "    showlegend = False\n", ")\n", "\n", "pie_class_trace = go.Pie(\n", "    labels = items['class'].value_counts().index[0:5],\n", "    values = items['class'].value_counts().values[0:5],\n", "    domain = dict(x=[0.52, 1], y=[0.5,1]),\n", "    showlegend = False,\n", "    text = [\"class \"+str(x) for x in items['class'].value_counts().index[0:5]],\n", "    hoverinfo=\"text+percent\",\n", "    textinfo=\"value+text\"\n", ")\n", "\n", "item_pie_layout = dict(\n", "    width=800, height=700,\n", "    title=\"Percentage of perishable items          Most frequent classes of the items\"\n", ")\n", "\n", "pie_perish_fig = go.Figure(data=[pie_perish_trace, pie_class_trace], layout=item_pie_layout)\n", "py.iplot(pie_perish_fig)\n"], "cell_type": "code", "metadata": {"scrolled": false, "_cell_guid": "dcb25d3b-6b44-4c11-ad71-c27d4f11bdab", "_uuid": "05a98e051b9ed9e28994e84d423f2a6f6dbd5444", "collapsed": true, "_kg_hide-input": false}}, {"execution_count": null, "outputs": [], "source": ["item_counts_per_family = items['family'].value_counts().values\n", "item_index_per_family = items['family'].value_counts().index\n", "\n", "x = 0\n", "y = 0\n", "width =250\n", "height=250\n", "\n", "normed_family = squarify.normalize_sizes(item_counts_per_family, width, height)\n", "rects_family = squarify.squarify(normed_family, x, y, width, height)"], "cell_type": "code", "metadata": {"_cell_guid": "fcd9a64e-12a9-4b68-b506-340b51be2840", "_uuid": "1d7ba7c4a0280cf1c98706084e4a1834e2f93520", "collapsed": true, "_kg_hide-input": false}}, {"execution_count": null, "outputs": [], "source": ["shape_family = []\n", "annotation_family = []\n", "counter = 0\n", "color_brewer = sns.diverging_palette(220, 20, n=33)\n", "color_template = [\"rgba\" + str(tuple(x)) for x in color_brewer]\n", "\n", "for r in rects_family:\n", "    shape_family.append(\n", "        dict(\n", "            type='rect',\n", "            x0 = r['x'],\n", "            y0 = r['y'],\n", "            x1 = r['x']+r['dx'],\n", "            y1 = r['y']+r['dy'],\n", "            line = dict(width=2),\n", "            fillcolor = color_template[counter]\n", "        )\n", "    )\n", "    \n", "    annotation_family.append(\n", "        dict(\n", "            x = r['x']+(r['dx']/2),\n", "            y = r['y']+(r['dy']/1.5),\n", "            text = item_index_per_family[counter],\n", "            showarrow=False,\n", "            align=\"center\",\n", "            font=dict(\n", "                color=\"blue\",\n", "                size=max(1, r['dx']/4)\n", "            ),\n", "        )\n", "    )\n", "    \n", "    counter+=1"], "cell_type": "code", "metadata": {"_cell_guid": "d222eec9-c5c8-43eb-a431-a238b628d368", "_uuid": "87d65515fc1f66868c885f5450378a3cbe330f59", "collapsed": true, "_kg_hide-input": false}}, {"execution_count": null, "outputs": [], "source": ["treemap_family_item_info = go.Scatter(\n", "    x = [r['x']+(r['dx']/2) for r in rects_family],\n", "    y = [r['y']+(r['dy']/2) for r in rects_family],\n", "    text = [str(v) for v in item_counts_per_family],\n", "    mode = 'text',\n", ")\n", "\n", "treemap_all_item_layout = dict(\n", "    height=900, width=900,\n", "    shapes = shape_family,\n", "    hovermode='closest',\n", "    annotations = annotation_family,\n", "    xaxis=dict(showgrid=False),\n", "    yaxis=dict(showgrid=False),\n", ")\n", "\n", "\n", "treemap_item_fig = dict(data=[treemap_family_item_info],\n", "                       layout=treemap_all_item_layout)\n", "\n", "py.iplot(treemap_item_fig)"], "cell_type": "code", "metadata": {"_cell_guid": "a95d87b5-beb9-448c-b186-919432f636b4", "_uuid": "0c64e111e7d520c3c296209176cb6210912b944b", "collapsed": true, "_kg_hide-input": false}}, {"metadata": {"_cell_guid": "6ccd0d6f-10c5-4021-b42c-56af2e8969d6", "_uuid": "359b350da76cbaf0d8db789657bdf1d254d4a684", "collapsed": true}, "cell_type": "markdown", "source": ["### Holiday Data<a id='holiday'></a>"]}, {"execution_count": null, "outputs": [], "source": ["print(holiday.shape)\n", "holiday.iloc[0:30]"], "cell_type": "code", "metadata": {"scrolled": false, "_cell_guid": "2a468294-81e0-48fe-b7e9-7db9113fd759", "_uuid": "55f5337b23b053f4ccad309c78c6cb4d7a84d5f4", "collapsed": true}}, {"execution_count": null, "outputs": [], "source": ["holiday_type_trace = go.Pie(\n", "    labels = holiday.type.value_counts().index,\n", "    values = holiday.type.value_counts().values,\n", "    domain = dict(x=[0,0.49], y=[0, 0.49]),\n", "    showlegend = False,\n", "    text = holiday.type.value_counts().index,\n", "    hoverinfo = \"text+percent\",\n", "    textinfo = \"value+text\",\n", "    hole = 0.3\n", ")\n", "\n", "holiday_locale_trace=go.Pie(\n", "    labels = holiday.locale.value_counts().index,\n", "    values = holiday.locale.value_counts().values,\n", "    domain = dict(x=[0.52,1], y=[0, 0.49]),\n", "    showlegend = False,\n", "    text = holiday.locale.value_counts().index,\n", "    hoverinfo=\"text+percent\",\n", "    textinfo=\"value+text\",\n", "    hole = 0.3\n", ")\n", "\n", "holiday_locale_name_trace=go.Pie(\n", "    labels = holiday.locale_name.value_counts().index,\n", "    values = holiday.locale_name.value_counts().values,\n", "    domain = dict(x=[0,0.48], y=[0.51, 1]),\n", "    showlegend = False,\n", "    text = holiday.locale_name.value_counts().index,\n", "    hoverinfo=\"text+percent\",\n", "    textinfo=\"value\",\n", "    hole = 0.3\n", ")\n", "\n", "holiday_transferred_trace=go.Pie(\n", "    labels = holiday.transferred.value_counts().index,\n", "    values = holiday.transferred.value_counts().values,\n", "    domain = dict(x=[0.52, 1], y=[0.51, 1]),\n", "    showlegend = False,\n", "    text = holiday.transferred.value_counts().index,\n", "    hoverinfo=\"text+percent\",\n", "    textinfo=\"text+value\",\n", "    hole = 0.3\n", ")\n", "\n", "holiday_layout = dict(\n", "    width=800, height=800,\n", "    annotations= [\n", "        dict(\n", "            font=dict(\n", "                size=15,\n", "                family=\"Droid Sans\"\n", "            ),\n", "            text = \"locale name\",\n", "            x = 0.18, y=0.78,\n", "            showarrow=False\n", "        ),\n", "        \n", "        dict(\n", "            font=dict(\n", "                size=15,\n", "                family=\"Droid Sans\"\n", "            ),\n", "            text = \"Transferred\",\n", "            x = 0.82, y=0.78,\n", "            showarrow=False\n", "        ),\n", "        \n", "        dict(\n", "            font=dict(\n", "                size=15,\n", "                family=\"Droid Sans\"\n", "            ),\n", "            text = \"Type\",\n", "            x = 0.215, y=0.23,\n", "            showarrow=False\n", "        ),\n", "        \n", "        dict(\n", "            font=dict(\n", "                size=15,\n", "                family=\"Droid Sans\"\n", "            ),\n", "            text = \"Locale\",\n", "            x = 0.80, y=0.23,\n", "            showarrow=False\n", "        ),\n", "    ]\n", ")\n", "\n", "\n", "holiday_type_fig = go.Figure(data=[holiday_type_trace, holiday_locale_trace,\n", "                                  holiday_locale_name_trace, holiday_transferred_trace], \n", "                            layout = holiday_layout)\n", "\n", "py.iplot(holiday_type_fig)"], "cell_type": "code", "metadata": {"_cell_guid": "e8f7b8ee-fff7-4947-8167-bb29daa3c39e", "_uuid": "516bf1dd6148b877840214b2d13c4aeaab3e8fe4", "collapsed": true, "_kg_hide-input": false}}, {"metadata": {"_cell_guid": "24f59ef1-44d4-4c11-b099-2ff11eb537bc", "_uuid": "8cc812cac876ec36bb634482d5ba8acf9fb3e09d"}, "cell_type": "markdown", "source": ["Take awae message:\n", "\n", "1. Only 3.43% of holiday are transferred holiday. \n", "2. 'Bridge' type and 'Work Day' type has equal number of days. And that's because 'bridage' is extra holiday that are added to get a long break weekend. And those holiday needed to be pay back by 'Work Day'. \n", "3. The 'transfer' type means officially that day is not a holiday, but some holiday is transferred to that day. (Those whose 'transferred' is true. ) Therefore, 12 holiday have 'transferred' as true, and 12 holiday has 'type' as 'transfer'. \n"]}, {"execution_count": null, "outputs": [], "source": [], "cell_type": "code", "metadata": {"_cell_guid": "2970d2b6-6c7a-4d3a-a8d3-1e1ff08339ff", "_uuid": "1dfb939d376033ea069e74b6b3c8b4b2f2415021", "collapsed": true}}], "nbformat": 4}