{"metadata": {"kernelspec": {"name": "python3", "display_name": "Python 3", "language": "python"}, "language_info": {"name": "python", "codemirror_mode": {"name": "ipython", "version": 3}, "pygments_lexer": "ipython3", "nbconvert_exporter": "python", "version": "3.6.3", "mimetype": "text/x-python", "file_extension": ".py"}}, "nbformat": 4, "cells": [{"metadata": {"_cell_guid": "4fd51975-815b-43c3-982f-0bcfecacd540", "_uuid": "66a0ff948578bc28a98ad41fb8316bc51aa7c46d"}, "cell_type": "markdown", "source": ["**Introduction**\n", "As part of this challenge we are trying to predict sales of various items sold by Favorita retailer. Following are the datasets provided to us:\n", "*  train.csv\n", "* stores.csv\n", "* transactions.csv\n", "* items.csv\n", "* holidays_events.csv\n", "* oils.csv\n", "\n", "**Description of Dataset**\n", "Please find link to dataset explaination here[https://www.kaggle.com/c/favorita-grocery-sales-forecasting/data](http://www.kaggle.com/c/favorita-grocery-sales-forecasting/data)\n"]}, {"metadata": {"_cell_guid": "0b9f9b53-8f1d-4b6c-aa17-40e6ee35a2c9", "_uuid": "15731920b8a79d10bbc7a823694508f6a567e891"}, "cell_type": "markdown", "source": ["* The first step is to read the data from various datasets and carry out some basic analysis. We use pandas read_csv to read the data set.\n", "* The second step is to analyse the dataset by means of various graphs and try to understand /co-relate with different datasets we are given. \n", "* The third step is to carry out feature engineering whereby we identify some of the key features from the dataset.\n", "* The fourth and final step is to train the model and test it. May be we could do K Means??"]}, {"metadata": {"_cell_guid": "aa7925f1-9a3e-43b9-a833-976bfd08e73c", "_uuid": "2fc1508d27b64ac3090a089f60b6f273f5a6bf66"}, "cell_type": "markdown", "source": ["**I. Reading various data from input**"]}, {"metadata": {"_cell_guid": "b93dfbdf-47e3-4f3a-a53a-f53ea4df3fc2", "_uuid": "e572722b2230283893a3a2684ea1783bff1a5cbe"}, "execution_count": null, "source": ["# This Python 3 environment comes with many helpful analytics libraries installed\n", "# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n", "# For example, here's several helpful packages to load in \n", "\n", "import numpy as np # linear algebra\n", "import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n", "import matplotlib.pyplot as plt\n", "import xgboost as xgb\n", "\n", "# Input data files are available in the \"../input/\" directory.\n", "# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory\n", "\n", "from subprocess import check_output\n", "print(check_output([\"ls\", \"../input\"]).decode(\"utf8\"))\n", "\n", "# Any results you write to the current directory are saved as output."], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "a63fcd2c-8ce6-4fa8-9533-e1bcfadb9281", "_uuid": "adc98ac74e04744b9f43f0762c0b03dd8f3be4ce"}, "execution_count": null, "source": ["#Since training data is huge, so I am planning to read few millions of rows from the csv file.\n", "train = pd.read_csv('../input/train.csv', usecols=[0, 1, 2, 3, 4,5], parse_dates=[\"date\"],skiprows=range(1, 89940990))\n", "\n", "#print the last 10 rows of the data, this will help us to think what we can dow with the data.\n", "train.tail(5)"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "69c0dedd-aac3-4225-89ef-bd9def6b6600", "_uuid": "b1f5a3e1bb5ea7b8edcb12cbb96a385101c71405"}, "cell_type": "markdown", "source": ["Training data and items csv are some way or other way related to each other. So I think lets read the items csv and try to merge with training data which will help us in getting more insight from the data."]}, {"metadata": {"_cell_guid": "f0283c69-c506-424d-912e-ca50d89d80e9", "collapsed": true, "_uuid": "5a11bf486ac988f92f3f6672e40adf9f6e587c5f"}, "execution_count": null, "source": ["items = pd.read_csv(\"../input/items.csv\")"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "e6436fb5-8813-4328-98af-90be11aad07b", "_uuid": "131078c577c89abcbf16b1f7fd281e56c560c680"}, "execution_count": null, "source": ["train_items = pd.merge(train, items, how='inner')\n", "train_items.tail(5)"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "3a1f521b-a81e-45fd-8d2c-6d12b0f60f22", "_uuid": "e64e9bf61e8782ac5acdb4aaa60ba183606a3816"}, "cell_type": "markdown", "source": ["After merging two sets of data (training and items) we can now carry out analysis of items sold."]}, {"metadata": {"_cell_guid": "7815df92-3029-4dbb-ab05-806873762f53", "_uuid": "ec8186f86fff91914ff2510a499351e3029b902a"}, "execution_count": null, "source": ["#Lets find out most popular item ordered by people across the 6 millions rows we have read.\n", "#We will group by item_nbr and add the unit sales.\n", "df = train_items['unit_sales'].groupby(train_items['item_nbr']).sum()\n", "#In order to find top 10 popular items we will sort the numpy array and pick the top 10 from\n", "#the list.\n", "df = df.sort_values()\n", "df_highest = df.nlargest(n=10)\n", "\n", "#Plot the highest list of items.\n", "df_highest.plot(kind='bar',figsize = (10,10),  title = \"Top 10 items sold across all stores\")\n", "plt.show()"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "30dcd027-c350-4118-808d-8f8406464520", "_uuid": "23718879358bb37b03af79551458e471db014398"}, "execution_count": null, "source": ["#Next we find lowest/less demand product. We use nsmallest to find the bottom 10 items,\n", "#probably it doesn;t matter even if we stock them.\n", "df_lowest = df.nsmallest(n=10)\n", "df_lowest.plot(kind='bar',figsize = (10,10),  title = \"Bottom 10 items sold\")\n", "plt.show()"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "e4404dd6-4fa8-4fa0-a128-9abf40acc007", "collapsed": true, "_uuid": "64d2eec2607e5fd51b3f67f3b46108ab353956d6"}, "execution_count": null, "source": ["#Next we could find out popular items in a given year. This will be useful to find out \n", "#if there were any new items introduced in the recent times.\n", "#In order to do that we need to covert the date field into python date format and then\n", "# extract various fields from it.\n", "\n", "train_items['date'] = pd.to_datetime(train_items['date'], format='%Y-%m-%d')\n", "train_items['day_item_purchased'] = train_items['date'].dt.day\n", "train_items['month_item_purchased'] =train_items['date'].dt.month\n", "train_items['quarter_item_purchased'] = train_items['date'].dt.quarter\n", "train_items['year_item_purchased'] = train_items['date'].dt.year"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "ff082173-3718-4d1d-9f75-1c4d31439f36", "collapsed": true, "_uuid": "d71cec243dc24849a9b7c875ba82c8a227d85258"}, "execution_count": null, "source": ["train_items.drop('date', axis=1, inplace=True)"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "ff70e183-49e0-4287-9faa-dc31686263ea", "scrolled": true, "_uuid": "2abee79c4e4aa287b3e4f47f1af717a65be47034"}, "execution_count": null, "source": ["#Lets print out new training dataset\n", "print (train_items.tail(2))"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "2795b121-41c8-47fe-8181-a0948aeabec7", "_uuid": "913e4ed9934c099106b4ef77b10bcb1bfe2eb9c6"}, "execution_count": null, "source": ["df_year = train_items.groupby(['quarter_item_purchased', 'item_nbr'])['unit_sales'].sum()\n", "df_year = df_year.sort_values()\n", "df_year_highest = df_year.nlargest(n=10)\n", "#Plot the highest list of items.\n", "df_year_highest.plot(kind='bar',figsize = (10,10),  title = \"Top items sold Quarterly\")\n", "plt.show()"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "8fa0be6b-60a0-4bbf-b20e-bbe925f01cf2", "_uuid": "740afb47640bc944c5f0986f8785748c9301db9c"}, "execution_count": null, "source": ["plt.figure(figsize=(9,10))\n", "df_items = train_items.groupby(['family'])['unit_sales'].sum()\n", "df_items = df_items.sort_values()\n", "df_items_highest = df_items.nlargest(n=10)\n", "plt.pie(df_items_highest, labels=df_items_highest.index,shadow=False,autopct='%1.1f%%')\n", "plt.tight_layout()\n", "plt.show()\n"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "f557c239-c61d-410d-bbee-20a136f45151", "collapsed": true, "_uuid": "0eb6fe05f8d16870009a245e2e3a37a420628f0e"}, "execution_count": null, "source": ["grocery_info = train_items.loc[train_items['family'] == 'GROCERY I']"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "26162c2f-3f61-4a3e-9ea9-7f61da389bce", "_uuid": "2adb837eb952a0ad58694281666c5295e4ebc724"}, "execution_count": null, "source": ["plt.figure(figsize=(12,12))\n", "#print (grocery_info.tail(2))\n", "plt.plot(grocery_info['day_item_purchased'],grocery_info['unit_sales'])\n", "plt.show()"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "49c2ad10-5350-43e0-b4f2-56cc846bfe61", "_uuid": "2972e02459c1da3376ef203ca7eba2fa8f420076"}, "execution_count": null, "source": ["plt.figure(figsize=(9,10))\n", "df_items = train_items.groupby(['family','perishable'])['unit_sales'].sum()\n", "df_items = df_items.sort_values()\n", "df_items_perish_highest = df_items.nlargest(n=10)\n", "plt.pie(df_items_perish_highest, labels=df_items_perish_highest.index,shadow=False,autopct='%1.1f%%')\n", "plt.tight_layout()\n", "plt.show()"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "6424c842-613d-4377-a7b9-bfaecfcb4ff9", "collapsed": true, "_uuid": "79566255ce8a884da047b03e2b167f4726dc9d05"}, "cell_type": "markdown", "source": ["Lets read the transactions data and carry out analysis."]}, {"metadata": {"_cell_guid": "38b62890-4f15-471e-92b8-3abe3f32fdd9", "scrolled": true, "collapsed": true, "_uuid": "cc2249cc298cd2537acad24ff771f0ff9dec013e"}, "execution_count": null, "source": ["transaction = pd.read_csv(\"../input/transactions.csv\")"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "5cc9baf1-3904-4cf8-bdff-30971325624c", "_uuid": "768786877039b318ce25206618c1417882ad817c"}, "cell_type": "markdown", "source": ["Convert date to pandas data time format, so that  we can group items for a given time frame (monthly, yearly, quarterly)"]}, {"metadata": {"_cell_guid": "9e528fec-723b-4180-a90d-eb9d7221a278", "_uuid": "51bd0a395b6ebbf861980dd32abec4e06059b320"}, "execution_count": null, "source": ["transaction['date'] = pd.to_datetime(transaction['date'], format='%Y-%m-%d')\n", "transaction['day_item_purchased'] = transaction['date'].dt.day\n", "transaction['month_item_purchased'] =transaction['date'].dt.month\n", "transaction['quarter_item_purchased'] = transaction['date'].dt.quarter\n", "transaction['year_item_purchased'] = transaction['date'].dt.year\n", "print (transaction.tail(2))"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "c1755234-9b24-4b9b-b29c-18d477fc99cc", "_uuid": "9880257a31e3dfb0f58de21d9a9eed0b7f3819d1"}, "execution_count": null, "source": ["plt.figure(figsize=(25,25))\n", "plt.plot(transaction['date'],transaction['transactions'])\n", "plt.show()\n"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "48e5b899-b915-43cc-86a2-63a824acdec0", "_uuid": "7c148f43dbd760c3c78091edd15c7807f079c498"}, "execution_count": null, "source": ["plt.figure(figsize=(8,12))\n", "trans_day = transaction['transactions'].groupby(transaction['year_item_purchased']).sum()\n", "trans_day.plot(kind='bar')\n", "plt.show()"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "3d187ac3-6032-4f67-8ca5-176339f69cb8", "_uuid": "e6d65f3b5515b21330d30e84fdb2f4d704d40951"}, "execution_count": null, "source": ["stores = pd.read_csv(\"../input/stores.csv\")\n", "print (stores.head())"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "0bc71778-e8df-49a0-8492-0f7ff3533423", "_uuid": "4d8c6f579f86136c7381fb0669792840473aa9ad"}, "execution_count": null, "source": ["#Lets find out number of cities in each state, which in nothing but finding out number of stores in each\n", "#in each state.\n", "df = stores['city'].groupby(stores['state']).count()\n", "df.plot(kind='bar', figsize = (12,8), yticks=np.arange(min(df), max(df)+1, 1.0), title = \"Number of cities in each state\")\n", "plt.show()"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "c3bf1e4d-dcd2-4c51-9ab1-bd5b148f818d", "_uuid": "b93d34ad491bc322f8e8ba31da6ab75cf0ceb85e"}, "execution_count": null, "source": ["#Looks like onpromotion field is always NaN, if so we will get rid of that columns \n", "#from the training data\n", "print(train['onpromotion'].notnull().any())\n", "train_new=train.drop('onpromotion',axis=1)\n", "print(train_new.tail(5))"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "34dd322c-83a0-46c0-9ea1-103fa859d9e9", "collapsed": true, "_uuid": "7b47f186b73e83bbd2eac0645788d36b4ebce89a"}, "execution_count": null, "source": ["oils = pd.read_csv(\"../input/oil.csv\")\n", "oils['date'] = pd.to_datetime(oils['date'], format='%Y-%m-%d')\n", "oils['day_item_purchased'] = oils['date'].dt.day\n", "oils['month_item_purchased'] =oils['date'].dt.month\n", "oils['quarter_item_purchased'] = oils['date'].dt.quarter\n", "oils['year_item_purchased'] = oils['date'].dt.year"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "6ee70c4f-2001-4bad-935a-3681797fa2d7", "_uuid": "4a2eadfc0d711a618ceb8a49e88589817a1ae892"}, "execution_count": null, "source": ["plt.figure(figsize=(25,25))\n", "#trans_day = transaction['transactions'].groupby(transaction['year_item_purchased']).sum()\n", "plt.plot(oils['date'],oils['dcoilwtico'])\n", "#trans_day.plot(kind='bar')\n", "plt.show()"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "c1be1de8-4717-47b6-81e9-ea64177e243f", "_uuid": "70e87dc5959476728ac66855c8ab9bd6a9b43862"}, "execution_count": null, "source": ["print(train_items.tail(2))\n", "#train_items.fillna(1,inplace=True)\n", "train_items.loc[(train_items.unit_sales<0),'unit_sales'] = 0 "], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "25158408-7ce0-4cf1-a3d7-a46d8425b898", "_uuid": "524896ed2dec4f015e62cfadc9e8011cb72686d9"}, "execution_count": null, "source": ["train_items['family'] = train_items['family'].astype('category')\n", "train_items['onpromotion'] = train_items['onpromotion'].astype('category')\n", "train_items['perishable'] = train_items['perishable'].astype('category')\n", "cat_columns = train_items.select_dtypes(['category']).columns\n", "train_items[cat_columns] = train_items[cat_columns].apply(lambda x: x.cat.codes)\n", "print (train_items.tail(2))"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "cb45e9f9-8ebe-4bc4-b3b3-eb9ea975b43c", "_uuid": "b2ca5d6e61c3ff884cc685ed3cafc396234d91f2"}, "execution_count": null, "source": ["from sklearn.cross_validation import train_test_split\n", "import xgboost as xgb\n", "X_train, X_valid = train_test_split(train_items, test_size=0.20, random_state=20)\n", "y_train = np.log1p(X_train.unit_sales)\n", "y_valid = np.log1p(X_valid.unit_sales)\n", "features = list(train_items.columns.values)\n", "dtrain = xgb.DMatrix(X_train[features], y_train)\n", "dvalid = xgb.DMatrix(X_valid[features], y_valid)"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "1f6e5ebf-b9b6-4f23-afdc-6f989e64f78d", "collapsed": true, "_uuid": "e217f73093dd46462d618a79888b1f88c30ff91c"}, "execution_count": null, "source": ["def rmspe(y, yhat):\n", "    return np.sqrt(np.mean((yhat/y-1) ** 2))"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "c8f2ad58-102d-419f-83a2-b9c053bc608f", "collapsed": true, "_uuid": "2f72f61e1ef9283e5a1c98d063c69153e09d456d"}, "execution_count": null, "source": ["def rmspe_xg(yhat, y):\n", "    y = np.expm1(y.get_label())\n", "    yhat = np.expm1(yhat)\n", "    return \"rmspe\", rmspe(y,yhat)"], "cell_type": "code", "outputs": []}, {"metadata": {"_cell_guid": "b8c78b32-4fa9-4f74-8776-f14a8da75858", "collapsed": true, "_uuid": "480f042ee191312c7c7c8831499a9cd88c16edf0"}, "execution_count": null, "source": ["params = {\"objective\": \"reg:linear\",\n", "          \"booster\" : \"gbtree\",\n", "          \"eta\": 0.3,\n", "          \"max_depth\": 10,\n", "          \"subsample\": 0.9,\n", "          \"colsample_bytree\": 0.7,\n", "          \"silent\": 1,\n", "          \"seed\": 1\n", "          }\n", "num_boost_round=100\n", "\n", "watchlist = [(dtrain, 'train'), (dvalid, 'eval')]\n", "gbm = xgb.train(params, dtrain, num_boost_round=num_boost_round, evals=watchlist, \\\n", "  early_stopping_rounds=300, verbose_eval=True)\n", "\n", "print(\"Validating\")\n", "yhat = gbm.predict(xgb.DMatrix(X_valid[features]))\n", "error = rmspe(X_valid.unit_sales.values, np.expm1(yhat))\n", "print (error)\n", "print('RMSPE: {:.6f}'.format(error))"], "cell_type": "code", "outputs": []}], "nbformat_minor": 1}