Module2_Local_Data_Engineering_Bike_Sharing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Architecting ML on AWS Notebooks\n",
    "## Module 2: Local Data Engineering on Bike Sharing Dataset\n",
    "\n",
    "`(Revision History:\n",
    "PA1, 2020-04-14, @akirmak: Initial version. Extended work of (ChandraLingam's AmazonSageMakerCourse in Udemy (https://github.com/ChandraLingam/AmazonSageMakerCourse and few other Open Source Notebooks published for Bike Rental Demand Competition) \n",
    "`\n",
    "\n",
    "## Overview \n",
    "This notebook explains how we can go about explore and prepare data for model building. The notebook is structured in the following way \n",
    "\n",
    " - About Dataset\n",
    " - Data Summary\n",
    " - Feature Engineering\n",
    " - Missing Value Analysis\n",
    " - Outlier Analysis\n",
    " - Correlation Analysis\n",
    " - Visualizing Distribution Of Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Use Case\n",
    "\n",
    "\n",
    "\n",
    "## DataSet\n",
    "### Overview\n",
    "Open an account & download the dataset from Kaggle: https://www.kaggle.com/c/bike-sharing-demand\n",
    "(In this lab, the dataset is already included in the repo)\n",
    "\n",
    "Quote Kaggle: \n",
    "\n",
    "\"Bike sharing systems are a means of renting bicycles where the process of obtaining membership, rental, and bike return is automated via a network of kiosk locations throughout a city. Using these systems, people are able rent a bike from a one location and return it to a different place on an as-needed basis. Currently, there are over 500 bike-sharing programs around the world.\n",
    "\n",
    "The data generated by these systems makes them attractive for researchers because the duration of travel, departure location, arrival location, and time elapsed is explicitly recorded. In this competition, participants are asked to combine historical usage patterns with weather data in order to forecast bike rental demand in the Capital Bikeshare program in Washington, D.C.\"\n",
    "\n",
    "### Data Fields\n",
    "\n",
    "Dependent Variables:\n",
    "* datetime - hourly date + timestamp  \n",
    "* season -  1 = spring, 2 = summer, 3 = fall, 4 = winter \n",
    "* holiday - whether the day is considered a holiday\n",
    "* workingday - whether the day is neither a weekend nor holiday\n",
    "* weather - \n",
    "    * 1: Clear, Few clouds, Partly cloudy, Partly cloudy\n",
    "    * 2: Mist + Cloudy, Mist + Broken clouds, Mist + Few clouds, Mist\n",
    "    * 3: Light Snow, Light Rain + Thunderstorm + Scattered clouds, Light Rain + Scattered clouds\n",
    "    * 4: Heavy Rain + Ice Pallets + Thunderstorm + Mist, Snow + Fog \n",
    "* temp - temperature in Celsius\n",
    "* atemp - \"feels like\" temperature in Celsius\n",
    "* humidity - relative humidity\n",
    "* windspeed - wind speed\n",
    "\n",
    "Independent Variables:\n",
    "* casual - number of non-registered user rentals initiated\n",
    "* registered - number of registered user rentals initiated\n",
    "* count - number of total rentals (Dependent Variable)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import sys\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "import pylab\n",
    "import calendar\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sn\n",
    "from scipy import stats\n",
    "#import missingno as msno\n",
    "from datetime import datetime\n",
    "import matplotlib.pyplot as plt\n",
    "import warnings\n",
    "pd.options.mode.chained_assignment = None\n",
    "warnings.filterwarnings(\"ignore\", category=DeprecationWarning)\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/bikesharing/train.csv', parse_dates=['datetime'])\n",
    "df_test = pd.read_csv('data/bikesharing/test.csv', parse_dates=['datetime'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_test.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We need to convert datetime to numeric for training.\n",
    "# Let's extract key features into separate numeric columns\n",
    "def hba_add_extended_date_features(df):\n",
    "    df['year'] = df['datetime'].dt.year\n",
    "    df['month'] = df['datetime'].dt.month\n",
    "    df['day'] = df['datetime'].dt.day\n",
    "    df['dayofweek'] = df['datetime'].dt.dayofweek\n",
    "    df['hour'] = df['datetime'].dt.hour"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hba_add_extended_date_features(df)\n",
    "hba_add_extended_date_features(df_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = ['count', 'season', 'holiday', 'workingday', 'weather', 'temp',\n",
    "       'atemp', 'humidity', 'windspeed', 'year', 'month', 'day', 'dayofweek','hour']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Correlation will indicate how strongly features are related to the output\n",
    "df.corr()['count']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_hour = df.groupby(['hour'])\n",
    "average_by_hour = group_hour['count'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "plt.plot(average_by_hour.index,average_by_hour)\n",
    "plt.xlabel('hour')\n",
    "plt.ylabel('Count')\n",
    "plt.xticks(np.arange(24))\n",
    "plt.grid(True)\n",
    "plt.title('Rental Count Average by hour')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_year_hour = df.groupby(['year','hour'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "average_year_hour = group_year_hour['count'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "for year in average_year_hour.index.levels[0]:\n",
    "    #print (year)\n",
    "    #print(average_year_month[year])\n",
    "    plt.plot(average_year_hour[year].index,average_year_hour[year],label=year)\n",
    "    \n",
    "plt.legend()    \n",
    "plt.xlabel('hour')\n",
    "plt.ylabel('Count')\n",
    "plt.xticks(np.arange(24))\n",
    "plt.grid(True)\n",
    "plt.title('Rental Count Average by Year,Hour')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_workingday_hour = df.groupby(['workingday','hour'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "average_workingday_hour = group_workingday_hour['count'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "for workingday in average_workingday_hour.index.levels[0]:\n",
    "    #print (year)\n",
    "    #print(average_year_month[year])\n",
    "    plt.plot(average_workingday_hour[workingday].index,average_workingday_hour[workingday],label=workingday)\n",
    "    \n",
    "plt.legend()    \n",
    "plt.xlabel('hour')\n",
    "plt.ylabel('Count')\n",
    "plt.xticks(np.arange(24))\n",
    "plt.grid(True)\n",
    "plt.title('Rental Count Average by Working Day,Hour')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save all data\n",
    "df.to_csv('hba_bike_all.csv',index=False,\n",
    "          columns=columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Training and Validation Set\n",
    "### Target Variable as first column followed by input features\n",
    "### Training, Validation files do not have a column header"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Training = 70% of the data\n",
    "# Validation = 30% of the data\n",
    "# Randomize the datset\n",
    "np.random.seed(5)\n",
    "l = list(df.index)\n",
    "np.random.shuffle(l)\n",
    "df = df.iloc[l]\n",
    "\n",
    "rows = df.shape[0]\n",
    "train = int(.7 * rows)\n",
    "test = int(.3 * rows)\n",
    "\n",
    "rows, train, test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write Training Set\n",
    "df[:train].to_csv('hba_bike_train.csv'\n",
    "                          ,index=False,header=False\n",
    "                          ,columns=columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write Validation Set\n",
    "df[train:].to_csv('hba_bike_validation.csv'\n",
    "                          ,index=False,header=False\n",
    "                          ,columns=columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Test Data has only input features\n",
    "df_test.to_csv('hba_bike_test.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns\n",
    "','.join(columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write Column List\n",
    "with open('hba_bike_train_column_list.txt','w') as f:\n",
    "    f.write(','.join(columns))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Feature Engineering (Part I) (Done on ALL of the training + validation data)\n",
    "In addition to existing independent variables, we will create new variables to improve the prediction power of model. Here, we generated new variables like hour, month, day and year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dailyData = df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dailyData.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#dailyData[\"date\"] = dailyData.datetime.apply(lambda x : x.split()[0])\n",
    "#dailyData[\"year\"] = dailyData.datetime.apply(lambda x : x.split()[0].split(\"-\")[0])\n",
    "#dailyData[\"month\"] = dailyData.datetime.apply(lambda x : x.split()[0].split(\"-\")[1])\n",
    "#dailyData[\"day\"] = dailyData.datetime.apply(lambda x : x.split()[0].split(\"-\")[2])\n",
    "#dailyData[\"hour\"] = dailyData.datetime.apply(lambda x : x.split()[1].split(\":\")[0])\n",
    "\n",
    "#dailyData[\"weekday_name\"] = dailyData.datetime.apply(lambda dateString : calendar.day_name[datetime.strptime(dateString,\"%Y-%m-%d\").weekday()])\n",
    "dailyData[\"season_name\"] = dailyData.season.map({1: \"Spring\", 2 : \"Summer\", 3 : \"Fall\", 4 :\"Winter\" })\n",
    "dailyData[\"weather_name\"] = dailyData.weather.map({1: \"Clear\",\\\n",
    "                                        2 : \"Cloudy\", \\\n",
    "                                        3 : \"Rainy\", \\\n",
    "                                        4 : \"Extreme\" })\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "dailyData.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dailyData.shape\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "dailyData.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Explore the Data\n",
    "Explore to get a fair understanding of the data set.\n",
    "\n",
    "### Outliers Analysis\n",
    "\n",
    "At first look, \"count\" variable contains lot of outlier data points which skews the distribution towards right (as there are more data points beyond Outer Quartile Limit).But in addition to that, following inferences can also been made from the simple boxplots given below.\n",
    "\n",
    " - Spring season has got relatively lower count.The dip in median value\n",
    "   in boxplot gives evidence for it.\n",
    " - The boxplot with \"Hour Of The Day\" is quiet interesting.The median value are relatively higher at 7AM - 8AM and 5PM - 6PM. It can be attributed to regular school and office users at that time. \n",
    " - Most of the outlier points are mainly contributed from \"Working Day\" than \"Non Working Day\". It is quiet visible from from figure 4."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "fig, axes = plt.subplots(nrows=3,ncols=2)\n",
    "fig.set_size_inches(20, 10)\n",
    "sn.boxplot(data=dailyData,y=\"count\",orient=\"v\",ax=axes[0][0])\n",
    "sn.boxplot(data=dailyData,y=\"count\",x=\"season_name\",orient=\"v\",ax=axes[0][1])\n",
    "sn.boxplot(data=dailyData,y=\"count\",x=\"hour\",orient=\"v\",ax=axes[1][0])\n",
    "sn.boxplot(data=dailyData,y=\"count\",x=\"workingday\",orient=\"v\",ax=axes[1][1])\n",
    "sn.boxplot(data=dailyData,y=\"count\",x=\"weather_name\",orient=\"v\",ax=axes[2][0])\n",
    "\n",
    "axes[0][0].set(ylabel='Count',title=\"Count\")\n",
    "axes[0][1].set(xlabel='Season', ylabel='Count',title=\"Count Across Season\")\n",
    "axes[1][0].set(xlabel='Hour Of The Day', ylabel='Count',title=\"Count Across Hour Of The Day\")\n",
    "axes[1][1].set(xlabel='Working Day', ylabel='Count',title=\"Count Across Working Day\")\n",
    "axes[2][0].set(xlabel='Weather', ylabel='Count',title=\"Count Across Weather\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import matplotlib.pyplot as plt\n",
    "dailyData.hist(bins=50, figsize=(20,15))\n",
    "plt.show()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Correlation Analysis\n",
    "\n",
    "One common to understand how a dependent variable is influenced by features (numerical) is to fibd a correlation matrix between them. Lets plot a correlation plot between \"count\" and [\"temp\",\"atemp\",\"humidity\",\"windspeed\"].\n",
    "\n",
    " - temp and humidity features has got positive and negative correlation\n",
    "   with count respectively.Although the correlation between them are not\n",
    "   very prominent still the count variable has got little dependency on\n",
    "   \"temp\" and \"humidity\".\n",
    " - windspeed is not gonna be really useful numerical feature and it is visible from it correlation value with \"count\"\n",
    " - \"atemp\" is variable is not taken into since \"atemp\" and \"temp\" has got strong correlation with each other. During model building any one of the variable has to be dropped since they will exhibit multicollinearity in the data.\n",
    " - \"Casual\" and \"Registered\" are also not taken into account since they are leakage variables in nature and need to dropped during model building.\n",
    "\n",
    "Regression plot in seaborn is one useful way to depict the relationship between two features. Here we consider \"count\" vs \"temp\", \"humidity\", \"windspeed\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "corrMatt = dailyData[[\"season\", \"holiday\", \"workingday\", \"temp\",\"atemp\",\"casual\",\"registered\",\"humidity\",\"windspeed\",\"count\"]].corr()\n",
    "mask = np.array(corrMatt)\n",
    "mask[np.tril_indices_from(mask)] = False\n",
    "fig,ax= plt.subplots()\n",
    "fig.set_size_inches(20,10)\n",
    "sn.heatmap(corrMatt, mask=mask,vmax=.8, square=True,annot=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "fig,(ax1,ax2,ax3) = plt.subplots(ncols=3)\n",
    "fig.set_size_inches(20, 5)\n",
    "sn.regplot(x=\"temp\", y=\"count\", data=dailyData,ax=ax1)\n",
    "sn.regplot(x=\"windspeed\", y=\"count\", data=dailyData,ax=ax2)\n",
    "sn.regplot(x=\"humidity\", y=\"count\", data=dailyData,ax=ax3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Hypothesis Testing (Trends based on Hour, Weekday,Usertype etc.)\n",
    "\n",
    " - It is quiet obvious that people tend to rent bike during summer\n",
    "   season since it is really conducive to ride bike at that\n",
    "   season.Therefore June, July and August has got relatively higher\n",
    "   demand for bicycle.\n",
    " - On weekdays more people tend to rent bicycle around 7AM-8AM and 5PM-6PM. As we mentioned earlier this can be attributed to regular school and office commuters.\n",
    " - Above pattern is not observed on \"Saturday\" and \"Sunday\".More people tend to rent bicycle between 10AM and 4PM.\n",
    " - The peak user count around 7AM-8AM and 5PM-6PM is purely contributed by registered user."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "fig,(ax1,ax2,ax3,ax4)= plt.subplots(nrows=4)\n",
    "fig.set_size_inches(20,20)\n",
    "sortOrder = [\"January\",\"February\",\"March\",\"April\",\"May\",\"June\",\"July\",\"August\",\"September\",\"October\",\"November\",\"December\"]\n",
    "#hueOrder = [\"Sunday\",\"Monday\",\"Tuesday\",\"Wednesday\",\"Thursday\",\"Friday\",\"Saturday\"]\n",
    "\n",
    "monthAggregated = pd.DataFrame(dailyData.groupby(\"month\")[\"count\"].mean()).reset_index()\n",
    "monthSorted = monthAggregated.sort_values(by=\"count\",ascending=False)\n",
    "#sn.barplot(data=monthSorted,x=\"month\",y=\"count\",ax=ax1,order=sortOrder)\n",
    "sn.barplot(data=monthSorted,x=\"month\",y=\"count\",ax=ax1)\n",
    "ax1.set(xlabel='Month', ylabel='Avearage Count',title=\"Average Count By Month\")\n",
    "\n",
    "hourAggregated = pd.DataFrame(dailyData.groupby([\"hour\",\"season_name\"],sort=True)[\"count\"].mean()).reset_index()\n",
    "sn.pointplot(x=hourAggregated[\"hour\"], y=hourAggregated[\"count\"],hue=hourAggregated[\"season_name\"], data=hourAggregated, join=True,ax=ax2)\n",
    "ax2.set(xlabel='Hour Of The Day', ylabel='Users Count',title=\"Average Users Count By Hour Of The Day Across Season\",label='big')\n",
    "\n",
    "hourAggregated = pd.DataFrame(dailyData.groupby([\"hour\",\"dayofweek\"],sort=True)[\"count\"].mean()).reset_index()\n",
    "#sn.pointplot(x=hourAggregated[\"hour\"], y=hourAggregated[\"count\"],hue=hourAggregated[\"day\"],hue_order=hueOrder, data=hourAggregated, join=True,ax=ax3)\n",
    "#sn.pointplot(x=hourAggregated[\"hour\"], y=hourAggregated[\"count\"],hue=hourAggregated[\"dayofweek\"],hue_order=hueOrder,data=hourAggregated, join=True,ax=ax3)\n",
    "sn.pointplot(x=hourAggregated[\"hour\"], y=hourAggregated[\"count\"],hue=hourAggregated[\"dayofweek\"],data=hourAggregated, join=True,ax=ax3)\n",
    "ax3.set(xlabel='Hour Of The Day', ylabel='Users Count',title=\"Average Users Count By Hour Of The Day Across Weekdays\",label='big')\n",
    "\n",
    "hourTransformed = pd.melt(dailyData[[\"hour\",\"casual\",\"registered\"]], id_vars=['hour'], value_vars=['casual', 'registered'])\n",
    "hourAggregated = pd.DataFrame(hourTransformed.groupby([\"hour\",\"variable\"],sort=True)[\"value\"].mean()).reset_index()\n",
    "sn.pointplot(x=hourAggregated[\"hour\"], y=hourAggregated[\"value\"],hue=hourAggregated[\"variable\"],hue_order=[\"casual\",\"registered\"], data=hourAggregated, join=True,ax=ax4)\n",
    "ax4.set(xlabel='Hour Of The Day', ylabel='Users Count',title=\"Average Users Count By Hour Of The Day Across User Type\",label='big')\n",
    "\n",
    "# count by years - fix this\n",
    "# yearTransformed = pd.melt(dailyData[[\"month\",\"year\"]], id_vars=['month'], value_vars=[\"2011\", \"2012\"])\n",
    "#yearAggregated = pd.DataFrame(yearTransformed.groupby([\"year\",\"variable\"],sort=True)[\"value\"].mean()).reset_index()\n",
    "#sn.pointplot(x=yearAggregated[\"month\"], y=yearAggregated[\"value\"],hue=yearAggregated[\"variable\"],hue_order=[\"2011\",\"2012\"], data=yearAggregated, join=True,ax=ax5)\n",
    "#ax4.set(xlabel='Hour Of The Day', ylabel='Users Count',title=\"Average Users Count By Year\",label='Year')\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Log Transformations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we know that dependent variables have natural outliers so we will predict log of dependent variables. Log Transformations. The log transformation can be used to make highly skewed distributions less skewed. This can be valuable both for making patterns in the data more interpretable and for helping to meet the assumptions of inferential statistics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hbay_trans = np.log1p(dailyData[\"casual\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "f, (ax0, ax1) = plt.subplots(1, 2)\n",
    "\n",
    "ax0.hist(dailyData[\"casual\"], bins=10)\n",
    "#ax0.set_xlim([0, 500])\n",
    "ax0.set_ylabel('Probability')\n",
    "ax0.set_xlabel('Target')\n",
    "ax0.set_title('Target distribution')\n",
    "\n",
    "ax1.hist(hbay_trans, bins=10)\n",
    "ax1.set_ylabel('Probability')\n",
    "ax1.set_xlabel('Target')\n",
    "ax1.set_title('Transformed target distribution')\n",
    "\n",
    "f.suptitle(\"Synthetic data\", y=0.035)\n",
    "f.tight_layout(rect=[0.05, 0.05, 0.95, 0.95])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Feature Engineering II\n",
    "In addition to existing independent variables, we will create new variables to improve the prediction power of model. \n",
    "Here we will create more variables, let’s look at the some of these:\n",
    "\n",
    "weekend, day type, bin based on temperature/hour/year. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# to be replaced by decision tree regressor partitioning\n",
    "#def hba_hour_bin(hr):\n",
    "#    if hr<8:\n",
    "#        return 1\n",
    "#    elif hr>=22:\n",
    "#        return 2\n",
    "#    elif hr>9 & hr<18:\n",
    "#        return 3\n",
    "#    elif hr==8:\n",
    "#        return 4\n",
    "#    elif hr==9:\n",
    "#        return 5\n",
    "#    elif hr==20 | hr==21:\n",
    "#        return 6\n",
    "#    elif hr==19 | hr==18:\n",
    "#        return 7\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Hour bins\n",
    "\n",
    "# dailyData[\"day_part\"].apply(hba_hour_bin(dailyData[\"hour\"]))\n",
    "\n",
    "#dailyData[\"day_part\"] = np.where(dailyData[\"hour\"] < 8,1,0)\n",
    "#dailyData[\"day_part\"] = np.where(dailyData[\"hour\"] >= 22,2,0)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Weekend: Created a separate variable for weekend (0/1)\n",
    "dailyData[\"weekend\"] = 0\n",
    "\n",
    "dailyData.loc[dailyData['dayofweek'] == 5  , 'weekend'] = 1 # saturday\n",
    "dailyData.loc[dailyData['dayofweek'] == 6, 'weekend'] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Year: Created a separate variable for every Q\n",
    "dailyData[\"quarter\"] = 0\n",
    "\n",
    "# dailyData.loc[dailyData['year'] == 2011  , 'year_part'] = 1\n",
    "dailyData.loc[dailyData['month'] == 1  , 'quarter'] = 1\n",
    "dailyData.loc[dailyData['month'] == 2  , 'quarter'] = 1\n",
    "dailyData.loc[dailyData['month'] == 3  , 'quarter'] = 1\n",
    "dailyData.loc[dailyData['month'] == 4  , 'quarter'] = 2\n",
    "dailyData.loc[dailyData['month'] == 5  , 'quarter'] = 2\n",
    "dailyData.loc[dailyData['month'] == 6  , 'quarter'] = 2\n",
    "dailyData.loc[dailyData['month'] == 7  , 'quarter'] = 3\n",
    "dailyData.loc[dailyData['month'] == 8  , 'quarter'] = 3\n",
    "dailyData.loc[dailyData['month'] == 9  , 'quarter'] = 3\n",
    "dailyData.loc[dailyData['month'] == 10  , 'quarter'] = 4\n",
    "dailyData.loc[dailyData['month'] == 11  , 'quarter'] = 4\n",
    "dailyData.loc[dailyData['month'] == 12  , 'quarter'] = 4\n",
    "\n",
    "#dailyData['quarter'] = np.where(dailyData['month']==1 , 2, 1)\n",
    "#dailyData.loc[(dailyData['year'] == 2011).bool() & (dailyData['year'] == 2011).bool() , 'year_part'] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dailyData.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Resampling (aggregations etc.) via changing index on a separate data frame (original intact)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ospd = dailyData\n",
    "ospd[\"idx_date\"] = dailyData[\"datetime\"]\n",
    "ospd = ospd.set_index('idx_date')\n",
    "ospd.index = pd.to_datetime(ospd.index)\n",
    "\n",
    "ospd.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "ospd.tail(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Display a random sampling of 5 rows\n",
    "ospd.sample(5, random_state=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "# Display figures inline in Jupyter notebook\n",
    "\n",
    "import seaborn as sns\n",
    "# Use seaborn style defaults and set the default figure size\n",
    "sns.set(rc={'figure.figsize':(11, 4)})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ospd['registered'].plot(linewidth=0.5);\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "cols_plot = ['casual', 'registered']\n",
    "axes = ospd[cols_plot].plot(marker='.', alpha=0.5, linestyle='None', figsize=(11, 9), subplots=True)\n",
    "for ax in axes:\n",
    "  ax.set_ylabel('Daily Totals')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ax = ospd.loc['2011-01':'2011-02', 'casual'].plot()\n",
    "ax.set_ylabel('Daily Consumption (Casual)');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ospd['casual+registered'] = ospd['casual'] + ospd['registered']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Resampling\n",
    "\n",
    "We can see that the weekly mean time series is smoother than the daily time series because higher frequency variability has been averaged out in the resampling."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Specify the data columns we want to include (i.e. exclude Year, Month, Weekday Name)\n",
    "ospd_data_columns = ['casual', 'registered', 'casual+registered']\n",
    "# Resample to weekly frequency, aggregating with mean\n",
    "opsd_weekly_mean = ospd[ospd_data_columns].resample('W').mean()\n",
    "opsd_weekly_mean.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compute the monthly sums, setting the value to NaN for any month which has\n",
    "# fewer than 28 days of data\n",
    "ospd_monthly = ospd[ospd_data_columns].resample('M').sum(min_count=28)\n",
    "ospd_monthly.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots()\n",
    "ax.plot(ospd_monthly['casual+registered'], color='black', label='Casual Consumption')\n",
    "ospd_monthly[['casual', 'registered']].plot.area(ax=ax, linewidth=0)\n",
    "# ax.xaxis.set_major_locator(mdates.MonthLocator())\n",
    "ax.legend()\n",
    "ax.set_ylabel('Monthly Total Consumption (Count)');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# DO NOT Drop resampled fields before training (since original training DF kept intact)\n",
    "#ospd.drop(\"day_part\", axis=1, inplace=True) \n",
    "#ospd.drop(\"casual+registered\", axis=1, inplace=True) \n",
    "\n",
    "ospd.sample(3, random_state=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#df_validation.head()\n",
    "dailyData.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Storing Useful Variables\n",
    "Before exiting this notebook, run the following cells to save some variables for later use.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%store df\n",
    "%store dailyData\n",
    "\n",
    "%store df_test\n",
    "%store columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Congratulations. \n",
    "TBD"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_python3",
   "language": "python",
   "name": "conda_python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}