{"metadata": {"kernelspec": {"display_name": "Python 3", "name": "python3", "language": "python"}, "language_info": {"file_extension": ".py", "name": "python", "codemirror_mode": {"version": 3, "name": "ipython"}, "version": "3.6.3", "mimetype": "text/x-python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3"}}, "nbformat": 4, "nbformat_minor": 1, "cells": [{"metadata": {"_uuid": "4fe03289d1aca82a88c1ff30cb0a6c909794ea1e", "_cell_guid": "0c45b2da-045f-4389-9c81-d225e2bea7b3", "collapsed": true}, "source": ["As part of this challenge we are going to analyze various tables and their connection. Following datasets are provided :\n", "train.csv\n", "stores.csv\n", "transactions.csv\n", "items.csv\n", "holidays_events.csv\n", "oils.csv\n", "Steps:\n", "1-->Analyze each table\n", "2-->Draw correlation between tables\n", "3-->Key performance indicators or feature engineering to figure out important features.\n", "4-->Train the model and test it.\n", "\n", "\n"], "cell_type": "markdown"}, {"execution_count": null, "metadata": {"_uuid": "bdd5ad2e6c4896d51b7088d72807f4d82b8499ee", "_cell_guid": "3f2be576-ab6e-4ccd-b97f-c994ffadcf5d", "collapsed": true}, "source": ["import pandas as pd\n", "import numpy as np\n", "import seaborn as sns\n", "from matplotlib import pyplot as plt\n", "import plotly.graph_objs as go\n", "\n", "import plotly.tools as tls\n", "import plotly.offline as py\n", "import plotly.graph_objs as go\n", "import missingno as msno"], "outputs": [], "cell_type": "code"}, {"metadata": {"_uuid": "aeced499883ee018271dc6fe499d0dad4ba5f4f0", "_cell_guid": "d003d199-a00d-4a9e-96ef-a296b7cc40a2"}, "source": [], "cell_type": "markdown"}, {"metadata": {"_uuid": "f14fafd570969cf7db3b7a55015f5086072e5432", "_cell_guid": "2d483fe8-d2ed-45de-95cf-c33721d57b36"}, "source": ["Reading files from csv"], "cell_type": "markdown"}, {"execution_count": null, "metadata": {"_uuid": "f5d49593a5fa95726894b1f29dce29610845747e", "_cell_guid": "c499f5d1-f5ed-45c1-846d-b578636ce772", "collapsed": true}, "source": ["holidays_events_df =pd.read_csv('../input/holidays_events.csv')\n", "items_df =pd.read_csv('../input/items.csv')\n", "oil_df =pd.read_csv('../input/oil.csv')\n", "stores_df =pd.read_csv('../input/stores.csv')\n", "transactions_df =pd.read_csv('../input/transactions.csv')\n", "oil_df =pd.read_csv(\"../input/oil.csv\",parse_dates=['date'],dtype={'dcoilwtico':np.float16})"], "outputs": [], "cell_type": "code"}, {"metadata": {"_uuid": "c5d329968cd4923cc3adcfd4c9816624e3e1e589", "_cell_guid": "2804fa5a-944e-4f67-bd4e-b687def1b696"}, "source": ["OIL"], "cell_type": "markdown"}, {"execution_count": null, "metadata": {"_uuid": "9471f0e23cee9d74d36b3b7968bca204a16a3c51", "_cell_guid": "3ccbefd3-da78-4864-85a2-014c045a29d9", "collapsed": true}, "source": ["oil_df['date'] = pd.to_datetime(oil_df['date'], format='%y-%m-%d')\n", "oil_df['day_item_purchased'] = oil_df['date'].dt.day\n", "oil_df['month_item_purchased'] =oil_df['date'].dt.month\n", "oil_df['quarter_item_purchased'] = oil_df['date'].dt.quarter\n", "oil_df['year_item_purchased'] = oil_df['date'].dt.year"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "471c1a6e760eeeee36a291eb59a2fd955b4bca79", "_cell_guid": "75f7e2a9-6489-4cc0-af02-8bb7f764cb8c"}, "source": ["plt.figure(figsize=(25,25))\n", "plt.plot(oil_df['date'],oil_df['dcoilwtico'])\n", "plt.show()"], "outputs": [], "cell_type": "code"}, {"metadata": {"_uuid": "793a7a8cf2f3c256f73c78caf83366c8e07990e6", "_cell_guid": "1d191cbb-8717-40cb-aedc-c6be68508f60"}, "source": ["\n", " Add month and year and day of the week. It can help us to figure out the favourite days for shopping."], "cell_type": "markdown"}, {"execution_count": null, "metadata": {"_uuid": "6787d93a453f40514239255f4974f2509247b993", "_cell_guid": "e8e12fb0-08bf-44f5-ac16-5689a2f5641a"}, "source": ["import calendar\n", "\n", "transactions_df[\"year\"] = transactions_df[\"date\"].astype(str).str[:4].astype(np.int64)\n", "transactions_df[\"month\"] = transactions_df[\"date\"].astype(str).str[5:7].astype(np.int64)\n", "transactions_df['date'] = pd.to_datetime(transactions_df['date'], errors ='coerce')\n", "transactions_df['day_of_week'] = transactions_df['date'].dt.weekday_name\n", "\n", "\n", "transactions_df[\"year\"] = transactions_df[\"year\"].astype(str)\n", "transactions_df.head()"], "outputs": [], "cell_type": "code"}, {"metadata": {"_uuid": "cf1020b60d5059e09aeaa595028199ee6b17bc87", "_cell_guid": "b219e7ea-2105-426e-855c-fc1d45f29012"}, "source": ["Favorita Transactions:\n", "Lets draw a heat map to visualize the transcations happening each month and year.since there is no data beyond 2017, it appears blank.Squares are getting darker as we pass through the years\n"], "cell_type": "markdown"}, {"execution_count": null, "metadata": {"_uuid": "2675ab47b7013d1eca897d01b11a992edd8a422e", "_cell_guid": "9cd11881-04ec-4e7b-a255-e7da709c7af7"}, "source": ["transactions1 = transactions_df.groupby('date')['transactions'].sum()\n", "py.iplot([go.Scatter(\n", "    x=transactions1.index,\n", "    y=transactions1\n", ")])"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "c9ae3214ae441852149e02ebfb86348599cb1e91", "_cell_guid": "cc5b884f-234b-4d79-9e96-e947cf4344bc"}, "source": ["#month and year\n", "\n", "%matplotlib inline\n", "import matplotlib.pyplot as plt\n", "import seaborn as sns\n", "\n", "x= transactions_df.groupby(['month','year'],as_index=False).agg({'transactions':'sum'})\n", "y=x.pivot(\"month\",\"year\",\"transactions\")\n", "fig, ax = plt.subplots(figsize=(10,7))\n", "sns.heatmap(y);"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "d6eaf396683385bb9ec9e8772853180cfa4977b6", "_cell_guid": "58b86bc3-d258-4999-ac0c-f78ea2cef33a"}, "source": ["# analysis of what happening in day\n", "x=transactions_df.groupby(['day_of_week', 'year'], as_index=False).agg({'transactions':'sum'})\n", "y= x.pivot(\"day_of_week\",\"year\",\"transactions\")\n", "fig, ax = plt.subplots(figsize=(10,7))\n", "sns.heatmap(y);\n", "#people are buying more on saturday and sunday"], "outputs": [], "cell_type": "code"}, {"metadata": {"_uuid": "36b98f79b38fea7a7fa740ced7949021209bbe08", "_cell_guid": "2d8d253c-30fa-4732-a59b-e5c8fe5e95f0"}, "source": ["\n"], "cell_type": "markdown"}, {"metadata": {"_uuid": "a0e45d0c358b3563c6c901ff44ab7a2bc5fe7c30", "_cell_guid": "6c39657f-dc01-4843-b78f-b8a66839efea"}, "source": ["Stores"], "cell_type": "markdown"}, {"execution_count": null, "metadata": {"_uuid": "d64c1f37968c1470e9ed586a14ae5ed78480e5a0", "_cell_guid": "941b46b3-0657-496f-8475-0b48bed5c875"}, "source": ["set(stores_df.state)"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "f948b0b5418993fb7a297673e2cad646595f4f98", "_cell_guid": "b9324b04-2b35-46b2-b92a-a34677424333"}, "source": ["regions_data = {    \n", "             \n", "    \n", "        'state': ['Azuay',\n", " 'Bolivar',\n", " 'Chimborazo',\n", " 'Cotopaxi',\n", " 'El Oro',\n", " 'Esmeraldas',\n", " 'Guayas',\n", " 'Imbabura',\n", " 'Loja',\n", " 'Los Rios',\n", " 'Manabi',\n", " 'Pastaza',\n", " 'Pichincha',\n", " 'Santa Elena',\n", " 'Santo Domingo de los Tsachilas',\n", " 'Tungurahua']}\n", "\n", "df_regions = pd.DataFrame(regions_data, columns = ['state'])\n", "df_regions_cities = pd.merge(df_regions, stores_df, on='state')\n", "\n", "transactions_regions = pd.merge(transactions_df, df_regions_cities, on='store_nbr')\n", "transactions_regions.head()"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "beb27d8d9b564e881c457d2826d4bfe5880fc1ee", "_cell_guid": "50ee7402-e704-4afa-b125-431f6b8ef549"}, "source": ["x= transactions_regions.groupby(['state','year'], as_index=False). agg({'transactions':'sum'})\n", "y=x.pivot(\"state\",\"year\",\"transactions\")\n", "fig, ax = plt.subplots(figsize=(12,9))\n", "sns.heatmap(y);\n", "# Guayas and Pichincha, have more transactions in comparison to other states."], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "51c36c0ad74ba113631edc35812a5891b4c50126", "_cell_guid": "a89d977f-9c1e-4b5e-b141-9d63780f2027"}, "source": ["x= transactions_regions.groupby(['store_nbr','year'], as_index=False). agg({'transactions':'sum'})\n", "y=x.pivot(\"store_nbr\",\"year\",\"transactions\")\n", "fig, ax = plt.subplots(figsize=(12,9))\n", "sns.heatmap(y);\n", "#store number 3,8,9,11,44,45,46,47,48,50 have more transactions."], "outputs": [], "cell_type": "code"}, {"metadata": {"_uuid": "c197a751b7433434ec222f9016ac8b2540e32817", "_cell_guid": "de90e56a-155f-4639-8b7c-f57bdd5cc36f"}, "source": ["Items"], "cell_type": "markdown"}, {"execution_count": null, "metadata": {"_uuid": "d902d10d9d4fad185549278f3bda2be4715fad1c", "_cell_guid": "5945dd65-7919-48ca-83ee-f5a7af89f1ee"}, "source": ["items_df.head()"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "badf00ecd28f9cf950ccfa92efe914d9658c6243", "_cell_guid": "d282d88d-013e-489b-bfc1-e1f09995fdcc"}, "source": ["items_df.family.unique()"], "outputs": [], "cell_type": "code"}, {"metadata": {"_uuid": "2be6e76e17c9827b5a5792063db0259f609dd49d", "_cell_guid": "0a78df9d-1ec0-46d3-b257-ab76edfd4512"}, "source": ["Percentage of each one in terms of transactions"], "cell_type": "markdown"}, {"execution_count": null, "metadata": {"_uuid": "38036f98a2ab62be1c1a8d3c8c64c191d4261ec4", "_cell_guid": "9e7b9c22-f0ec-4f7c-9029-54fad5cab564"}, "source": ["items_df_family =items_df.groupby(['family']).size().to_frame(name ='counts').reset_index()\n", "items_df_family['percentage'] =items_df_family['counts']/items_df_family['counts'].sum() *100\n", "items_df_family.head()"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "d46a3dbaa8880d179f39f369f3d76c9f76748e77", "_cell_guid": "5879ce5b-f431-44ad-81dd-585431db4e4d"}, "source": ["sns.set_style(\"white\")\n", "fig, ax =plt.subplots(figsize=(14,10))\n", "ax = sns.barplot(x=\"percentage\",y=\"family\", data =items_df_family)\n", "#Grocery has the maximum transactions."], "outputs": [], "cell_type": "code"}, {"metadata": {"_uuid": "5fb61dc55d04884753070d7ebc11b61fe6ffacba", "_cell_guid": "8d081551-8bed-4c02-a5aa-73fa776caa85"}, "source": ["\n"], "cell_type": "markdown"}, {"execution_count": null, "metadata": {"_uuid": "7fbf1d6ce1079e2614b3b26a8111e7bf9977448f", "_cell_guid": "82faf545-c4ca-4b70-8d9a-58f460e12114", "collapsed": true}, "source": ["types_dict = {'id':'int32',\n", "             'item_nbr': 'int32',\n", "             'store_nbr':'int8',\n", "             'unit_sales':'float32',\n", "             'onpromotion':'str',\n", "             }"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "7e6f9c151dae3efba92450dd3d601b2a0a71b2bf", "_cell_guid": "051ff206-bffd-4716-a4c3-3ec1f70065d5", "collapsed": true}, "source": [], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "77c47ca789eea12372d4930242b2305dbeca0c5f", "_cell_guid": "9bcc426c-7d02-4890-8e3a-8b694dfc762c"}, "source": ["grocery_train =pd.read_csv('../input/train.csv', low_memory=True,dtype =types_dict,\n", "                           converters={'unit_sales': lambda x:float(x) if float(x) > 0 else 0})\n", "# log transform\n", "#grocery_train[\"unit_sales\"] = grocery_train[\"unit_sales\"].apply(np.log1p)"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "cdf49e614e8578dae75961c45badb1f0c4bce1b0", "_cell_guid": "7e03bf5a-d3be-406c-93eb-35168b09cbea", "collapsed": true}, "source": [], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "bd6d035ebf3b8ca4ee3267e27c4b04e36e642c65", "_cell_guid": "e69d7b6d-b595-4ea7-808c-32abc2e50b38", "_kg_hide-output": true, "collapsed": true}, "source": ["# Calculate means\n", "grocery_train = grocery_train.groupby(\n", "    ['item_nbr', 'store_nbr', 'onpromotion']\n", ")['unit_sales'].mean().to_frame('unit_sales')\n", "# Inverse transform\n", "#grocery_train[\"unit_sales\"] = grocery_train[\"unit_sales\"].apply(np.expm1)\n", "#grocery_train"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "ce744e848742ba95642fb5fdfbde983d5b649518", "_cell_guid": "75c4341f-8cb1-4bd3-98d3-555bb877e60a", "collapsed": true}, "source": ["# Create submission\n", "pd.read_csv(\n", "    \"../input/test.csv\", usecols=[0, 2, 3, 4], dtype={'onpromotion': str}\n", ").set_index(\n", "    ['item_nbr', 'store_nbr', 'onpromotion']\n", ").join(\n", "    grocery_train, how='left'\n", ").fillna(0).to_csv(\n", "    'mean2.csv.gz', float_format='%.2f', index=None, compression=\"gzip\"\n", ")"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "f7c7d16db839e6047bf532eb72af6ada3072a6df", "_cell_guid": "ed1a8ccd-a197-4715-8e5f-847f658f9732", "collapsed": true}, "source": [], "outputs": [], "cell_type": "code"}, {"metadata": {"_uuid": "56ef1ffc0be0a874e827e8cb6ccca590067b9325", "_cell_guid": "d59b0861-87ed-48ff-aae4-7685baa51fca"}, "source": [], "cell_type": "markdown"}, {"metadata": {"_uuid": "caf81e87c3a40cfda2860b796dff81a6db3ed1da", "_cell_guid": "db4f6003-a72d-4666-b818-0428ff51b225"}, "source": [], "cell_type": "markdown"}, {"metadata": {"_uuid": "cd53be6360dd1290df01624931db1c4d690cf7e2", "_cell_guid": "066c0565-a998-4e12-9341-ee22f22b49d1"}, "source": [], "cell_type": "markdown"}]}