walmart sales forecasting.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "import matplotlib.pylab as plt\n",
    "import seaborn as sns\n",
    "#import pandas_profiling \n",
    "import seaborn as sns\n",
    "\n",
    "\n",
    "from datetime import datetime\n",
    "\n",
    "import statsmodels.api as sm\n",
    "from sklearn import metrics\n",
    "#from sklearn.cross_validation import train_test_split\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "from sklearn.tree import DecisionTreeRegressor\n",
    "from sklearn.ensemble import AdaBoostRegressor\n",
    "from sklearn.ensemble import GradientBoostingRegressor\n",
    "from sklearn.svm import SVC, LinearSVC\n",
    "from sklearn.metrics import mean_squared_error as mse\n",
    "from sklearn.metrics import mean_absolute_error, mean_squared_error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# data preprocessing\n",
    "\n",
    "train=pd.read_csv(r\"C:\\Users\\Manali\\Desktop\\datasets\\walmart-recruiting-store-sales-forecasting\\train.csv\")\n",
    "test=pd.read_csv(r\"C:\\Users\\Manali\\Desktop\\datasets\\walmart-recruiting-store-sales-forecasting\\test.csv\")\n",
    "feature=pd.read_csv(r\"C:\\Users\\Manali\\Desktop\\datasets\\walmart-recruiting-store-sales-forecasting\\features.csv\")\n",
    "stores=pd.read_csv(r\"C:\\Users\\Manali\\Desktop\\datasets\\walmart-recruiting-store-sales-forecasting\\stores.csv\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For Output_excel file...\n",
    "\n",
    "writer=pd.ExcelWriter('Walmart Store Sales Prediction output.xlsx', engine='xlsxwriter')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_bt = pd.merge(train,stores) \n",
    "train = pd.merge(train_bt,feature)\n",
    "#\n",
    "test_bt = pd.merge(test,stores)\n",
    "test= pd.merge(test_bt,feature)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Store</th>\n",
       "      <th>Dept</th>\n",
       "      <th>Date</th>\n",
       "      <th>Weekly_Sales</th>\n",
       "      <th>IsHoliday</th>\n",
       "      <th>Type</th>\n",
       "      <th>Size</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Fuel_Price</th>\n",
       "      <th>MarkDown1</th>\n",
       "      <th>MarkDown2</th>\n",
       "      <th>MarkDown3</th>\n",
       "      <th>MarkDown4</th>\n",
       "      <th>MarkDown5</th>\n",
       "      <th>CPI</th>\n",
       "      <th>Unemployment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2010-02-05</td>\n",
       "      <td>24924.50</td>\n",
       "      <td>False</td>\n",
       "      <td>A</td>\n",
       "      <td>151315</td>\n",
       "      <td>42.31</td>\n",
       "      <td>2.572</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>211.096358</td>\n",
       "      <td>8.106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2010-02-05</td>\n",
       "      <td>50605.27</td>\n",
       "      <td>False</td>\n",
       "      <td>A</td>\n",
       "      <td>151315</td>\n",
       "      <td>42.31</td>\n",
       "      <td>2.572</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>211.096358</td>\n",
       "      <td>8.106</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Store  Dept        Date  Weekly_Sales  IsHoliday Type    Size  Temperature  \\\n",
       "0      1     1  2010-02-05      24924.50      False    A  151315        42.31   \n",
       "1      1     2  2010-02-05      50605.27      False    A  151315        42.31   \n",
       "\n",
       "   Fuel_Price  MarkDown1  MarkDown2  MarkDown3  MarkDown4  MarkDown5  \\\n",
       "0       2.572        NaN        NaN        NaN        NaN        NaN   \n",
       "1       2.572        NaN        NaN        NaN        NaN        NaN   \n",
       "\n",
       "          CPI  Unemployment  \n",
       "0  211.096358         8.106  \n",
       "1  211.096358         8.106  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Store</th>\n",
       "      <th>Dept</th>\n",
       "      <th>Date</th>\n",
       "      <th>IsHoliday</th>\n",
       "      <th>Type</th>\n",
       "      <th>Size</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Fuel_Price</th>\n",
       "      <th>MarkDown1</th>\n",
       "      <th>MarkDown2</th>\n",
       "      <th>MarkDown3</th>\n",
       "      <th>MarkDown4</th>\n",
       "      <th>MarkDown5</th>\n",
       "      <th>CPI</th>\n",
       "      <th>Unemployment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2012-11-02</td>\n",
       "      <td>False</td>\n",
       "      <td>A</td>\n",
       "      <td>151315</td>\n",
       "      <td>55.32</td>\n",
       "      <td>3.386</td>\n",
       "      <td>6766.44</td>\n",
       "      <td>5147.7</td>\n",
       "      <td>50.82</td>\n",
       "      <td>3639.9</td>\n",
       "      <td>2737.42</td>\n",
       "      <td>223.462779</td>\n",
       "      <td>6.573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2012-11-02</td>\n",
       "      <td>False</td>\n",
       "      <td>A</td>\n",
       "      <td>151315</td>\n",
       "      <td>55.32</td>\n",
       "      <td>3.386</td>\n",
       "      <td>6766.44</td>\n",
       "      <td>5147.7</td>\n",
       "      <td>50.82</td>\n",
       "      <td>3639.9</td>\n",
       "      <td>2737.42</td>\n",
       "      <td>223.462779</td>\n",
       "      <td>6.573</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Store  Dept        Date  IsHoliday Type    Size  Temperature  Fuel_Price  \\\n",
       "0      1     1  2012-11-02      False    A  151315        55.32       3.386   \n",
       "1      1     2  2012-11-02      False    A  151315        55.32       3.386   \n",
       "\n",
       "   MarkDown1  MarkDown2  MarkDown3  MarkDown4  MarkDown5         CPI  \\\n",
       "0    6766.44     5147.7      50.82     3639.9    2737.42  223.462779   \n",
       "1    6766.44     5147.7      50.82     3639.9    2737.42  223.462779   \n",
       "\n",
       "   Unemployment  \n",
       "0         6.573  \n",
       "1         6.573  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 421570 entries, 0 to 421569\n",
      "Data columns (total 16 columns):\n",
      "Store           421570 non-null int64\n",
      "Dept            421570 non-null int64\n",
      "Date            421570 non-null object\n",
      "Weekly_Sales    421570 non-null float64\n",
      "IsHoliday       421570 non-null bool\n",
      "Type            421570 non-null object\n",
      "Size            421570 non-null int64\n",
      "Temperature     421570 non-null float64\n",
      "Fuel_Price      421570 non-null float64\n",
      "MarkDown1       150681 non-null float64\n",
      "MarkDown2       111248 non-null float64\n",
      "MarkDown3       137091 non-null float64\n",
      "MarkDown4       134967 non-null float64\n",
      "MarkDown5       151432 non-null float64\n",
      "CPI             421570 non-null float64\n",
      "Unemployment    421570 non-null float64\n",
      "dtypes: bool(1), float64(10), int64(3), object(2)\n",
      "memory usage: 51.9+ MB\n",
      "None\n",
      "*****************************************\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 115064 entries, 0 to 115063\n",
      "Data columns (total 15 columns):\n",
      "Store           115064 non-null int64\n",
      "Dept            115064 non-null int64\n",
      "Date            115064 non-null object\n",
      "IsHoliday       115064 non-null bool\n",
      "Type            115064 non-null object\n",
      "Size            115064 non-null int64\n",
      "Temperature     115064 non-null float64\n",
      "Fuel_Price      115064 non-null float64\n",
      "MarkDown1       114915 non-null float64\n",
      "MarkDown2       86437 non-null float64\n",
      "MarkDown3       105235 non-null float64\n",
      "MarkDown4       102176 non-null float64\n",
      "MarkDown5       115064 non-null float64\n",
      "CPI             76902 non-null float64\n",
      "Unemployment    76902 non-null float64\n",
      "dtypes: bool(1), float64(9), int64(3), object(2)\n",
      "memory usage: 13.3+ MB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "print (train.info())\n",
    "print (\"*****************************************\")\n",
    "print (test.info())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Store', 'Dept', 'Weekly_Sales', 'Size', 'Temperature', 'Fuel_Price', 'MarkDown1', 'MarkDown2', 'MarkDown3', 'MarkDown4', 'MarkDown5', 'CPI', 'Unemployment']\n",
      "['Date', 'Type']\n"
     ]
    }
   ],
   "source": [
    "# data description\n",
    "# 1. training data\n",
    "\n",
    "numeric_var_train=[key for key in dict(train.dtypes) if dict(train.dtypes)[key] in ['float64', 'int64', 'float32', 'int32']]\n",
    "cat_var_train=[key for key in dict(train.dtypes) if dict(train.dtypes)[key] in ['object']]\n",
    "# Train Numerical Data\n",
    "train_num=train[numeric_var_train]\n",
    "\n",
    "# Train Categorical Data\n",
    "train_cat=train[cat_var_train]\n",
    "print (numeric_var_train)\n",
    "print (cat_var_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>N</th>\n",
       "      <th>NMISS</th>\n",
       "      <th>SUM</th>\n",
       "      <th>MEAN</th>\n",
       "      <th>MEDIAN</th>\n",
       "      <th>STD</th>\n",
       "      <th>VAR</th>\n",
       "      <th>MIN</th>\n",
       "      <th>P1</th>\n",
       "      <th>P5</th>\n",
       "      <th>P10</th>\n",
       "      <th>P25</th>\n",
       "      <th>P50</th>\n",
       "      <th>P75</th>\n",
       "      <th>P90</th>\n",
       "      <th>P95</th>\n",
       "      <th>P99</th>\n",
       "      <th>MAX</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Store</th>\n",
       "      <td>421570.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.359084e+06</td>\n",
       "      <td>22.200546</td>\n",
       "      <td>22.00000</td>\n",
       "      <td>12.785297</td>\n",
       "      <td>1.634638e+02</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>22.00000</td>\n",
       "      <td>33.000000</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>43.000000</td>\n",
       "      <td>45.000000</td>\n",
       "      <td>45.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dept</th>\n",
       "      <td>421570.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.865882e+07</td>\n",
       "      <td>44.260317</td>\n",
       "      <td>37.00000</td>\n",
       "      <td>30.492054</td>\n",
       "      <td>9.297654e+02</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>18.000000</td>\n",
       "      <td>37.00000</td>\n",
       "      <td>74.000000</td>\n",
       "      <td>92.000000</td>\n",
       "      <td>95.000000</td>\n",
       "      <td>98.000000</td>\n",
       "      <td>99.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Weekly_Sales</th>\n",
       "      <td>421570.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.737219e+09</td>\n",
       "      <td>15981.258123</td>\n",
       "      <td>7612.03000</td>\n",
       "      <td>22711.183519</td>\n",
       "      <td>5.157979e+08</td>\n",
       "      <td>-4988.940</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>59.974500</td>\n",
       "      <td>291.097000</td>\n",
       "      <td>2079.650000</td>\n",
       "      <td>7612.03000</td>\n",
       "      <td>20205.852500</td>\n",
       "      <td>42845.673000</td>\n",
       "      <td>61201.951000</td>\n",
       "      <td>106479.586000</td>\n",
       "      <td>693099.360000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Size</th>\n",
       "      <td>421570.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.764039e+10</td>\n",
       "      <td>136727.915739</td>\n",
       "      <td>140167.00000</td>\n",
       "      <td>60980.583328</td>\n",
       "      <td>3.718632e+09</td>\n",
       "      <td>34875.000</td>\n",
       "      <td>34875.000000</td>\n",
       "      <td>39690.000000</td>\n",
       "      <td>39910.000000</td>\n",
       "      <td>93638.000000</td>\n",
       "      <td>140167.00000</td>\n",
       "      <td>202505.000000</td>\n",
       "      <td>204184.000000</td>\n",
       "      <td>206302.000000</td>\n",
       "      <td>219622.000000</td>\n",
       "      <td>219622.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Temperature</th>\n",
       "      <td>421570.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.533217e+07</td>\n",
       "      <td>60.090059</td>\n",
       "      <td>62.09000</td>\n",
       "      <td>18.447931</td>\n",
       "      <td>3.403262e+02</td>\n",
       "      <td>-2.060</td>\n",
       "      <td>18.300000</td>\n",
       "      <td>27.310000</td>\n",
       "      <td>33.980000</td>\n",
       "      <td>46.680000</td>\n",
       "      <td>62.09000</td>\n",
       "      <td>74.280000</td>\n",
       "      <td>83.580000</td>\n",
       "      <td>87.270000</td>\n",
       "      <td>92.810000</td>\n",
       "      <td>100.140000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fuel_Price</th>\n",
       "      <td>421570.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.416908e+06</td>\n",
       "      <td>3.361027</td>\n",
       "      <td>3.45200</td>\n",
       "      <td>0.458515</td>\n",
       "      <td>2.102356e-01</td>\n",
       "      <td>2.472</td>\n",
       "      <td>2.565000</td>\n",
       "      <td>2.653000</td>\n",
       "      <td>2.720000</td>\n",
       "      <td>2.933000</td>\n",
       "      <td>3.45200</td>\n",
       "      <td>3.738000</td>\n",
       "      <td>3.917000</td>\n",
       "      <td>4.029000</td>\n",
       "      <td>4.202000</td>\n",
       "      <td>4.468000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MarkDown1</th>\n",
       "      <td>150681.0</td>\n",
       "      <td>270889.0</td>\n",
       "      <td>1.091898e+09</td>\n",
       "      <td>7246.420196</td>\n",
       "      <td>5347.45000</td>\n",
       "      <td>8291.221345</td>\n",
       "      <td>6.874435e+07</td>\n",
       "      <td>0.270</td>\n",
       "      <td>17.760000</td>\n",
       "      <td>149.190000</td>\n",
       "      <td>375.200000</td>\n",
       "      <td>2240.270000</td>\n",
       "      <td>5347.45000</td>\n",
       "      <td>9210.900000</td>\n",
       "      <td>15282.470000</td>\n",
       "      <td>21801.350000</td>\n",
       "      <td>41524.030000</td>\n",
       "      <td>88646.760000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MarkDown2</th>\n",
       "      <td>111248.0</td>\n",
       "      <td>310322.0</td>\n",
       "      <td>3.709708e+08</td>\n",
       "      <td>3334.628621</td>\n",
       "      <td>192.00000</td>\n",
       "      <td>9475.357325</td>\n",
       "      <td>8.978240e+07</td>\n",
       "      <td>-265.760</td>\n",
       "      <td>-0.990000</td>\n",
       "      <td>1.950000</td>\n",
       "      <td>6.980000</td>\n",
       "      <td>41.600000</td>\n",
       "      <td>192.00000</td>\n",
       "      <td>1926.940000</td>\n",
       "      <td>8549.740000</td>\n",
       "      <td>16497.470000</td>\n",
       "      <td>50366.600000</td>\n",
       "      <td>104519.540000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MarkDown3</th>\n",
       "      <td>137091.0</td>\n",
       "      <td>284479.0</td>\n",
       "      <td>1.973317e+08</td>\n",
       "      <td>1439.421384</td>\n",
       "      <td>24.60000</td>\n",
       "      <td>9623.078290</td>\n",
       "      <td>9.260364e+07</td>\n",
       "      <td>-29.100</td>\n",
       "      <td>0.060000</td>\n",
       "      <td>0.650000</td>\n",
       "      <td>1.650000</td>\n",
       "      <td>5.080000</td>\n",
       "      <td>24.60000</td>\n",
       "      <td>103.990000</td>\n",
       "      <td>400.090000</td>\n",
       "      <td>1059.900000</td>\n",
       "      <td>63143.290000</td>\n",
       "      <td>141630.610000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MarkDown4</th>\n",
       "      <td>134967.0</td>\n",
       "      <td>286603.0</td>\n",
       "      <td>4.566161e+08</td>\n",
       "      <td>3383.168256</td>\n",
       "      <td>1481.31000</td>\n",
       "      <td>6292.384031</td>\n",
       "      <td>3.959410e+07</td>\n",
       "      <td>0.220</td>\n",
       "      <td>3.970000</td>\n",
       "      <td>28.760000</td>\n",
       "      <td>108.710000</td>\n",
       "      <td>504.220000</td>\n",
       "      <td>1481.31000</td>\n",
       "      <td>3595.040000</td>\n",
       "      <td>7871.420000</td>\n",
       "      <td>12645.960000</td>\n",
       "      <td>35785.260000</td>\n",
       "      <td>67474.850000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MarkDown5</th>\n",
       "      <td>151432.0</td>\n",
       "      <td>270138.0</td>\n",
       "      <td>7.009750e+08</td>\n",
       "      <td>4628.975079</td>\n",
       "      <td>3359.45000</td>\n",
       "      <td>5962.887455</td>\n",
       "      <td>3.555603e+07</td>\n",
       "      <td>135.160</td>\n",
       "      <td>351.970000</td>\n",
       "      <td>715.520000</td>\n",
       "      <td>1070.830000</td>\n",
       "      <td>1878.440000</td>\n",
       "      <td>3359.45000</td>\n",
       "      <td>5563.800000</td>\n",
       "      <td>8337.700000</td>\n",
       "      <td>11269.240000</td>\n",
       "      <td>27754.230000</td>\n",
       "      <td>108519.280000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CPI</th>\n",
       "      <td>421570.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.217360e+07</td>\n",
       "      <td>171.201947</td>\n",
       "      <td>182.31878</td>\n",
       "      <td>39.159276</td>\n",
       "      <td>1.533449e+03</td>\n",
       "      <td>126.064</td>\n",
       "      <td>126.111903</td>\n",
       "      <td>126.496258</td>\n",
       "      <td>128.823806</td>\n",
       "      <td>132.022667</td>\n",
       "      <td>182.31878</td>\n",
       "      <td>212.416993</td>\n",
       "      <td>219.444244</td>\n",
       "      <td>221.941558</td>\n",
       "      <td>225.473509</td>\n",
       "      <td>227.232807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Unemployment</th>\n",
       "      <td>421570.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.355819e+06</td>\n",
       "      <td>7.960289</td>\n",
       "      <td>7.86600</td>\n",
       "      <td>1.863296</td>\n",
       "      <td>3.471872e+00</td>\n",
       "      <td>3.879</td>\n",
       "      <td>4.156000</td>\n",
       "      <td>5.326000</td>\n",
       "      <td>5.965000</td>\n",
       "      <td>6.891000</td>\n",
       "      <td>7.86600</td>\n",
       "      <td>8.572000</td>\n",
       "      <td>9.816000</td>\n",
       "      <td>12.187000</td>\n",
       "      <td>14.180000</td>\n",
       "      <td>14.313000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     N     NMISS           SUM           MEAN        MEDIAN  \\\n",
       "Store         421570.0       0.0  9.359084e+06      22.200546      22.00000   \n",
       "Dept          421570.0       0.0  1.865882e+07      44.260317      37.00000   \n",
       "Weekly_Sales  421570.0       0.0  6.737219e+09   15981.258123    7612.03000   \n",
       "Size          421570.0       0.0  5.764039e+10  136727.915739  140167.00000   \n",
       "Temperature   421570.0       0.0  2.533217e+07      60.090059      62.09000   \n",
       "Fuel_Price    421570.0       0.0  1.416908e+06       3.361027       3.45200   \n",
       "MarkDown1     150681.0  270889.0  1.091898e+09    7246.420196    5347.45000   \n",
       "MarkDown2     111248.0  310322.0  3.709708e+08    3334.628621     192.00000   \n",
       "MarkDown3     137091.0  284479.0  1.973317e+08    1439.421384      24.60000   \n",
       "MarkDown4     134967.0  286603.0  4.566161e+08    3383.168256    1481.31000   \n",
       "MarkDown5     151432.0  270138.0  7.009750e+08    4628.975079    3359.45000   \n",
       "CPI           421570.0       0.0  7.217360e+07     171.201947     182.31878   \n",
       "Unemployment  421570.0       0.0  3.355819e+06       7.960289       7.86600   \n",
       "\n",
       "                       STD           VAR        MIN            P1  \\\n",
       "Store            12.785297  1.634638e+02      1.000      1.000000   \n",
       "Dept             30.492054  9.297654e+02      1.000      1.000000   \n",
       "Weekly_Sales  22711.183519  5.157979e+08  -4988.940      5.000000   \n",
       "Size          60980.583328  3.718632e+09  34875.000  34875.000000   \n",
       "Temperature      18.447931  3.403262e+02     -2.060     18.300000   \n",
       "Fuel_Price        0.458515  2.102356e-01      2.472      2.565000   \n",
       "MarkDown1      8291.221345  6.874435e+07      0.270     17.760000   \n",
       "MarkDown2      9475.357325  8.978240e+07   -265.760     -0.990000   \n",
       "MarkDown3      9623.078290  9.260364e+07    -29.100      0.060000   \n",
       "MarkDown4      6292.384031  3.959410e+07      0.220      3.970000   \n",
       "MarkDown5      5962.887455  3.555603e+07    135.160    351.970000   \n",
       "CPI              39.159276  1.533449e+03    126.064    126.111903   \n",
       "Unemployment      1.863296  3.471872e+00      3.879      4.156000   \n",
       "\n",
       "                        P5           P10           P25           P50  \\\n",
       "Store             3.000000      5.000000     11.000000      22.00000   \n",
       "Dept              4.000000      7.000000     18.000000      37.00000   \n",
       "Weekly_Sales     59.974500    291.097000   2079.650000    7612.03000   \n",
       "Size          39690.000000  39910.000000  93638.000000  140167.00000   \n",
       "Temperature      27.310000     33.980000     46.680000      62.09000   \n",
       "Fuel_Price        2.653000      2.720000      2.933000       3.45200   \n",
       "MarkDown1       149.190000    375.200000   2240.270000    5347.45000   \n",
       "MarkDown2         1.950000      6.980000     41.600000     192.00000   \n",
       "MarkDown3         0.650000      1.650000      5.080000      24.60000   \n",
       "MarkDown4        28.760000    108.710000    504.220000    1481.31000   \n",
       "MarkDown5       715.520000   1070.830000   1878.440000    3359.45000   \n",
       "CPI             126.496258    128.823806    132.022667     182.31878   \n",
       "Unemployment      5.326000      5.965000      6.891000       7.86600   \n",
       "\n",
       "                        P75            P90            P95            P99  \\\n",
       "Store             33.000000      40.000000      43.000000      45.000000   \n",
       "Dept              74.000000      92.000000      95.000000      98.000000   \n",
       "Weekly_Sales   20205.852500   42845.673000   61201.951000  106479.586000   \n",
       "Size          202505.000000  204184.000000  206302.000000  219622.000000   \n",
       "Temperature       74.280000      83.580000      87.270000      92.810000   \n",
       "Fuel_Price         3.738000       3.917000       4.029000       4.202000   \n",
       "MarkDown1       9210.900000   15282.470000   21801.350000   41524.030000   \n",
       "MarkDown2       1926.940000    8549.740000   16497.470000   50366.600000   \n",
       "MarkDown3        103.990000     400.090000    1059.900000   63143.290000   \n",
       "MarkDown4       3595.040000    7871.420000   12645.960000   35785.260000   \n",
       "MarkDown5       5563.800000    8337.700000   11269.240000   27754.230000   \n",
       "CPI              212.416993     219.444244     221.941558     225.473509   \n",
       "Unemployment       8.572000       9.816000      12.187000      14.180000   \n",
       "\n",
       "                        MAX  \n",
       "Store             45.000000  \n",
       "Dept              99.000000  \n",
       "Weekly_Sales  693099.360000  \n",
       "Size          219622.000000  \n",
       "Temperature      100.140000  \n",
       "Fuel_Price         4.468000  \n",
       "MarkDown1      88646.760000  \n",
       "MarkDown2     104519.540000  \n",
       "MarkDown3     141630.610000  \n",
       "MarkDown4      67474.850000  \n",
       "MarkDown5     108519.280000  \n",
       "CPI              227.232807  \n",
       "Unemployment      14.313000  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating Data audit Report\n",
    "# Use a general function that returns multiple values    \n",
    "def var_summary(x):\n",
    "    return pd.Series([x.count(), x.isnull().sum(), x.sum(), x.mean(), x.median(),  x.std(), x.var(), x.min(), x.dropna().quantile(0.01), x.dropna().quantile(0.05),x.dropna().quantile(0.10),x.dropna().quantile(0.25),x.dropna().quantile(0.50),x.dropna().quantile(0.75), x.dropna().quantile(0.90),x.dropna().quantile(0.95), x.dropna().quantile(0.99),x.max()], \n",
    "                  index=['N', 'NMISS', 'SUM', 'MEAN','MEDIAN', 'STD', 'VAR', 'MIN', 'P1' , 'P5' ,'P10' ,'P25' ,'P50' ,'P75' ,'P90' ,'P95' ,'P99' ,'MAX'])\n",
    " \n",
    "num_summary=train_num.apply(lambda x: var_summary(x)).T\n",
    "num_summary.to_excel(writer,'Numeric_variable Summary',index=True)\n",
    "num_summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>N</th>\n",
       "      <td>421570</td>\n",
       "      <td>421570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NMISS</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ColumnsNames</th>\n",
       "      <td>2011-12-23    3027\n",
       "2011-11-25    3021\n",
       "2011-12-...</td>\n",
       "      <td>A    215478\n",
       "B    163495\n",
       "C     42597\n",
       "Name: Type...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                           Date  \\\n",
       "N                                                        421570   \n",
       "NMISS                                                         0   \n",
       "ColumnsNames  2011-12-23    3027\n",
       "2011-11-25    3021\n",
       "2011-12-...   \n",
       "\n",
       "                                                           Type  \n",
       "N                                                        421570  \n",
       "NMISS                                                         0  \n",
       "ColumnsNames  A    215478\n",
       "B    163495\n",
       "C     42597\n",
       "Name: Type...  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def cat_summary(x):\n",
    "    return pd.Series([x.count(), x.isnull().sum(), x.value_counts()], \n",
    "                  index=['N', 'NMISS', 'ColumnsNames'])\n",
    "\n",
    "cat_summary=train_cat.apply(lambda x: cat_summary(x))\n",
    "cat_summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Store', 'Dept', 'Size', 'Temperature', 'Fuel_Price', 'MarkDown1', 'MarkDown2', 'MarkDown3', 'MarkDown4', 'MarkDown5', 'CPI', 'Unemployment']\n",
      "['Date', 'Type']\n"
     ]
    }
   ],
   "source": [
    "# testing data\n",
    "\n",
    "numeric_var_test=[key for key in dict(test.dtypes) if dict(test.dtypes)[key] in ['float64', 'int64', 'float32', 'int32']]\n",
    "cat_var_test=[key for key in dict(test.dtypes) if dict(test.dtypes)[key] in ['object']]\n",
    "# Train Numerical Data\n",
    "test_num=test[numeric_var_test]\n",
    "\n",
    "# Train Categorical Data\n",
    "test_cat=test[cat_var_test]\n",
    "print(numeric_var_test)\n",
    "print(cat_var_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>N</th>\n",
       "      <th>NMISS</th>\n",
       "      <th>SUM</th>\n",
       "      <th>MEAN</th>\n",
       "      <th>MEDIAN</th>\n",
       "      <th>STD</th>\n",
       "      <th>VAR</th>\n",
       "      <th>MIN</th>\n",
       "      <th>P1</th>\n",
       "      <th>P5</th>\n",
       "      <th>P10</th>\n",
       "      <th>P25</th>\n",
       "      <th>P50</th>\n",
       "      <th>P75</th>\n",
       "      <th>P90</th>\n",
       "      <th>P95</th>\n",
       "      <th>P99</th>\n",
       "      <th>MAX</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Store</th>\n",
       "      <td>115064.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.558817e+06</td>\n",
       "      <td>22.238207</td>\n",
       "      <td>22.000</td>\n",
       "      <td>12.809930</td>\n",
       "      <td>1.640943e+02</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>5.000</td>\n",
       "      <td>11.000</td>\n",
       "      <td>22.000</td>\n",
       "      <td>33.000</td>\n",
       "      <td>40.000</td>\n",
       "      <td>43.000</td>\n",
       "      <td>45.000</td>\n",
       "      <td>45.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dept</th>\n",
       "      <td>115064.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.101883e+06</td>\n",
       "      <td>44.339524</td>\n",
       "      <td>37.000</td>\n",
       "      <td>30.656410</td>\n",
       "      <td>9.398155e+02</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>4.000</td>\n",
       "      <td>7.000</td>\n",
       "      <td>18.000</td>\n",
       "      <td>37.000</td>\n",
       "      <td>74.000</td>\n",
       "      <td>92.000</td>\n",
       "      <td>95.000</td>\n",
       "      <td>98.000</td>\n",
       "      <td>99.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Size</th>\n",
       "      <td>115064.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.570597e+10</td>\n",
       "      <td>136497.688921</td>\n",
       "      <td>140167.000</td>\n",
       "      <td>61106.926438</td>\n",
       "      <td>3.734056e+09</td>\n",
       "      <td>34875.000</td>\n",
       "      <td>34875.000</td>\n",
       "      <td>39690.000</td>\n",
       "      <td>39910.000</td>\n",
       "      <td>93638.000</td>\n",
       "      <td>140167.000</td>\n",
       "      <td>202505.000</td>\n",
       "      <td>204184.000</td>\n",
       "      <td>206302.000</td>\n",
       "      <td>219622.000</td>\n",
       "      <td>219622.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Temperature</th>\n",
       "      <td>115064.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.206760e+06</td>\n",
       "      <td>53.941804</td>\n",
       "      <td>54.470</td>\n",
       "      <td>18.724153</td>\n",
       "      <td>3.505939e+02</td>\n",
       "      <td>-7.290</td>\n",
       "      <td>11.440</td>\n",
       "      <td>23.980</td>\n",
       "      <td>29.970</td>\n",
       "      <td>39.820</td>\n",
       "      <td>54.470</td>\n",
       "      <td>67.350</td>\n",
       "      <td>79.480</td>\n",
       "      <td>83.820</td>\n",
       "      <td>92.140</td>\n",
       "      <td>101.950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fuel_Price</th>\n",
       "      <td>115064.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.121070e+05</td>\n",
       "      <td>3.581546</td>\n",
       "      <td>3.606</td>\n",
       "      <td>0.239442</td>\n",
       "      <td>5.733244e-02</td>\n",
       "      <td>2.872</td>\n",
       "      <td>2.957</td>\n",
       "      <td>3.161</td>\n",
       "      <td>3.227</td>\n",
       "      <td>3.431</td>\n",
       "      <td>3.606</td>\n",
       "      <td>3.766</td>\n",
       "      <td>3.866</td>\n",
       "      <td>3.951</td>\n",
       "      <td>4.079</td>\n",
       "      <td>4.125</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    N  NMISS           SUM           MEAN      MEDIAN  \\\n",
       "Store        115064.0    0.0  2.558817e+06      22.238207      22.000   \n",
       "Dept         115064.0    0.0  5.101883e+06      44.339524      37.000   \n",
       "Size         115064.0    0.0  1.570597e+10  136497.688921  140167.000   \n",
       "Temperature  115064.0    0.0  6.206760e+06      53.941804      54.470   \n",
       "Fuel_Price   115064.0    0.0  4.121070e+05       3.581546       3.606   \n",
       "\n",
       "                      STD           VAR        MIN         P1         P5  \\\n",
       "Store           12.809930  1.640943e+02      1.000      1.000      3.000   \n",
       "Dept            30.656410  9.398155e+02      1.000      1.000      4.000   \n",
       "Size         61106.926438  3.734056e+09  34875.000  34875.000  39690.000   \n",
       "Temperature     18.724153  3.505939e+02     -7.290     11.440     23.980   \n",
       "Fuel_Price       0.239442  5.733244e-02      2.872      2.957      3.161   \n",
       "\n",
       "                   P10        P25         P50         P75         P90  \\\n",
       "Store            5.000     11.000      22.000      33.000      40.000   \n",
       "Dept             7.000     18.000      37.000      74.000      92.000   \n",
       "Size         39910.000  93638.000  140167.000  202505.000  204184.000   \n",
       "Temperature     29.970     39.820      54.470      67.350      79.480   \n",
       "Fuel_Price       3.227      3.431       3.606       3.766       3.866   \n",
       "\n",
       "                    P95         P99         MAX  \n",
       "Store            43.000      45.000      45.000  \n",
       "Dept             95.000      98.000      99.000  \n",
       "Size         206302.000  219622.000  219622.000  \n",
       "Temperature      83.820      92.140     101.950  \n",
       "Fuel_Price        3.951       4.079       4.125  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "num_summary=test_num.apply(lambda x: var_summary(x)).T\n",
    "#num_summary.to_excel(writer,'Numeric_variable Summary',index=True)\n",
    "num_summary.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def cat_summary(x):\n",
    "    return pd.Series([x.count(), x.isnull().sum(), x.value_counts()], \n",
    "                  index=['N', 'NMISS', 'ColumnsNames'])\n",
    "\n",
    "cat_summary=test_cat.apply(lambda x: cat_summary(x))\n",
    "cat_summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas_profiling\n",
    "\n",
    "pandas_profiling.ProfileReport(train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}