Data_Wrangling_240302.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Preparation, Wrangling, and Analysis "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- You can use other/ different tools to prep and clean you data before pushing it to your model e.g. Excel, SAS, SSIS, Tableau Prep, Database Engine (SQL Server)\n",
    "- However, it's always recommended to use one tool that can do all. i.e. **Python**\n",
    "- Python has become the goto tool for DA, DS, DE, and ML projects since it can comprehensivly do the following:\n",
    "    - Wide range of Data Analysis Libraries\n",
    "    - Data connectivity and handling \n",
    "    - Statistical and interactive visualization\n",
    "    - Data integration\n",
    "    - Large and active support community\n",
    "\n",
    "Tip: For data scientists pulling data from a database, it's always recommended to finish any aggregation and integration in SQL before passing to Python.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- **Non-tabular**: raw text, unorganized information, images, unlabeled claims (healthcare, customer service), unorganized survey, images, audio, etc...\n",
    "- **Tabular Data:** Tables with columns and consistent formatting"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What is Data Wrangling? and Why Is It Important?\n",
    "- Data Wrangling is the process of converting and formatting data from its raw form to a usable format to help improve the data science pipeline and output.\n",
    "- Without feeding proper data into a model, it will cause bad performance and low accuracy.\n",
    "- It deals with:\n",
    "    - data quality\n",
    "    - level of aggregation\n",
    "    - data integration\n",
    "    - data efficiency\n",
    "    - data understanding\n",
    "    - better visualizations\n",
    "    - support feature engineering\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <b> Problem Statement </b>\n",
    " \n",
    "You are a junior data scientist and you are assigned a new task to perform data wrangling on a set of datasets. The datasets have many ambiguities. You have to identify those and apply different data wrangling techniques to get a dataset for further usage."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**NOTE**: When you obtain a dataset to work on, it is important to ask for a **Data Dictionary**\n",
    "- it's a document that lists your variables, what they are, and where they come from\n",
    "- most of the time they give you the definition of the column data \n",
    "- some also include additional info, such as data types, data source, how it was aggregated, etc..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Dictionary\n",
    "**Description of Attributes:**\n",
    "\n",
    "* **instant** - event or instant id\n",
    "* **date** - date of the ride \n",
    "* **season** -  1 = spring, 2 = summer, 3 = fall, 4 = winter \n",
    "* **holiday** - whether the day is considered a holiday\n",
    "* **weekday** - number of the day int he week\n",
    "* **weathersit** \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",
    "* **hum** - relative humidity\n",
    "* **windspeed** - wind speed\n",
    "* **casual** - number of non-registered user rentals initiated\n",
    "* **registered** - number of registered user rentals initiated\n",
    "* **cnt** - number of total rentals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>instant</th>\n",
       "      <th>dteday</th>\n",
       "      <th>season</th>\n",
       "      <th>yr</th>\n",
       "      <th>mnth</th>\n",
       "      <th>hr</th>\n",
       "      <th>holiday</th>\n",
       "      <th>weekday</th>\n",
       "      <th>weathersit</th>\n",
       "      <th>temp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   instant      dteday  season  yr  mnth  hr  holiday  weekday  weathersit  \\\n",
       "0        1  01-01-2011       1   0     1   0    False        6           1   \n",
       "1        2  01-01-2011       1   0     1   1    False        6           1   \n",
       "2        3  01-01-2011       1   0     1   2    False        6           1   \n",
       "3        4  01-01-2011       1   0     1   3    False        6           1   \n",
       "4        5  01-01-2011       1   0     1   4    False        6           1   \n",
       "\n",
       "   temp  \n",
       "0  0.24  \n",
       "1  0.22  \n",
       "2  0.22  \n",
       "3  0.24  \n",
       "4  0.24  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load the first dataset\n",
    "df_1 = pd.read_csv('DataWranglingDataset/rental_bike_descr.csv')\n",
    "df_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(610, 10)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "the data dictionary above gives us a clue that our first data set is missing columns and eventually we need to bring in additional columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note** if you get an error from `read_excel`, run `pip install openpyxl`"
   ]
  },
  {
   "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>Unnamed: 0</th>\n",
       "      <th>instant</th>\n",
       "      <th>atemp</th>\n",
       "      <th>hum</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.2879</td>\n",
       "      <td>0.81</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.2727</td>\n",
       "      <td>0.80</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0.2727</td>\n",
       "      <td>0.80</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>0.2879</td>\n",
       "      <td>0.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>0.2879</td>\n",
       "      <td>0.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  instant   atemp   hum  windspeed  casual  registered  cnt\n",
       "0           0        1  0.2879  0.81        0.0       3          13   16\n",
       "1           1        2  0.2727  0.80        0.0       8          32   40\n",
       "2           2        3  0.2727  0.80        0.0       5          27   32\n",
       "3           3        4  0.2879  0.75        0.0       3          10   13\n",
       "4           4        5  0.2879  0.75        0.0       0           1    1"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load the second dataset\n",
    "df_2 = pd.read_excel('DataWranglingDataset/rental_bike_season.xlsx')\n",
    "df_2.head()"
   ]
  },
  {
   "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>instant</th>\n",
       "      <th>atemp</th>\n",
       "      <th>hum</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.2879</td>\n",
       "      <td>0.81</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>0.2727</td>\n",
       "      <td>0.80</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>0.2727</td>\n",
       "      <td>0.80</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>0.2879</td>\n",
       "      <td>0.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>0.2879</td>\n",
       "      <td>0.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   instant   atemp   hum  windspeed  casual  registered  cnt\n",
       "0        1  0.2879  0.81        0.0       3          13   16\n",
       "1        2  0.2727  0.80        0.0       8          32   40\n",
       "2        3  0.2727  0.80        0.0       5          27   32\n",
       "3        4  0.2879  0.75        0.0       3          10   13\n",
       "4        5  0.2879  0.75        0.0       0           1    1"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# unnamed col seems to be unnecessary (it resembles the index). Therefor, we can drop it.\n",
    "\n",
    "df_2 = df_2.drop(columns='Unnamed: 0')\n",
    "df_2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filtering the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>instant</th>\n",
       "      <th>atemp</th>\n",
       "      <th>hum</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.2879</td>\n",
       "      <td>0.81</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>0.2879</td>\n",
       "      <td>0.75</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>22</td>\n",
       "      <td>0.4091</td>\n",
       "      <td>0.87</td>\n",
       "      <td>0.1940</td>\n",
       "      <td>3</td>\n",
       "      <td>31</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>66</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.47</td>\n",
       "      <td>0.1045</td>\n",
       "      <td>3</td>\n",
       "      <td>49</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>67</td>\n",
       "      <td>0.1970</td>\n",
       "      <td>0.64</td>\n",
       "      <td>0.1343</td>\n",
       "      <td>3</td>\n",
       "      <td>49</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    instant   atemp   hum  windspeed  casual  registered  cnt\n",
       "0         1  0.2879  0.81     0.0000       3          13   16\n",
       "3         4  0.2879  0.75     0.0000       3          10   13\n",
       "21       22  0.4091  0.87     0.1940       3          31   34\n",
       "65       66     NaN  0.47     0.1045       3          49   52\n",
       "66       67  0.1970  0.64     0.1343       3          49   52"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the data where have 3 casual users\n",
    "mask = df_2['casual'] == 3\n",
    "\n",
    "df_2[mask].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Types of Logical Operators:**</br>\n",
    "![logOp](https://miro.medium.com/v2/resize:fit:640/1*H9m-yjLwZ5-M16qld4fvOA.png)\n",
    " \n",
    "**Combining Multiple Conditions**</br>\n",
    "![MulOp](https://miro.medium.com/v2/resize:fit:720/1*nd09QyjA8OvZbSYK6znykQ.png)"
   ]
  },
  {
   "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>instant</th>\n",
       "      <th>atemp</th>\n",
       "      <th>hum</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>0.3485</td>\n",
       "      <td>0.76</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>23</td>\n",
       "      <td>0.4091</td>\n",
       "      <td>0.94</td>\n",
       "      <td>0.2239</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>25</td>\n",
       "      <td>0.4545</td>\n",
       "      <td>0.88</td>\n",
       "      <td>0.2985</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.39</td>\n",
       "      <td>0.3582</td>\n",
       "      <td>5</td>\n",
       "      <td>17</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>45</td>\n",
       "      <td>0.2273</td>\n",
       "      <td>0.44</td>\n",
       "      <td>0.3284</td>\n",
       "      <td>11</td>\n",
       "      <td>20</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>138</td>\n",
       "      <td>0.1970</td>\n",
       "      <td>0.59</td>\n",
       "      <td>0.1940</td>\n",
       "      <td>4</td>\n",
       "      <td>19</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>139</td>\n",
       "      <td>0.1970</td>\n",
       "      <td>0.64</td>\n",
       "      <td>0.1940</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td>208</td>\n",
       "      <td>0.1212</td>\n",
       "      <td>0.46</td>\n",
       "      <td>0.2985</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>349</th>\n",
       "      <td>350</td>\n",
       "      <td>0.2727</td>\n",
       "      <td>0.56</td>\n",
       "      <td>0.1343</td>\n",
       "      <td>4</td>\n",
       "      <td>19</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>351</th>\n",
       "      <td>352</td>\n",
       "      <td>0.2727</td>\n",
       "      <td>0.69</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     instant   atemp   hum  windspeed  casual  registered  cnt\n",
       "9         10  0.3485  0.76     0.0000       8           6   14\n",
       "22        23  0.4091  0.94     0.2239      11          17   28\n",
       "24        25  0.4545  0.88     0.2985       4          13   17\n",
       "43        44     NaN  0.39     0.3582       5          17   22\n",
       "44        45  0.2273  0.44     0.3284      11          20   31\n",
       "137      138  0.1970  0.59     0.1940       4          19   23\n",
       "138      139  0.1970  0.64     0.1940       4          13   17\n",
       "207      208  0.1212  0.46     0.2985       5          10   15\n",
       "349      350  0.2727  0.56     0.1343       4          19   23\n",
       "351      352  0.2727  0.69     0.0000       9           6   15"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# for example: all rentals with casual > 3 and registered less or equal to 20\n",
    "\n",
    "mask2 = (df_2['casual'] > 3 ) & (df_2['registered'] <= 20)\n",
    "\n",
    "df_2[mask2].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Alternative method: `isin()` and `query()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>University</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mark</td>\n",
       "      <td>23</td>\n",
       "      <td>UTC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>John</td>\n",
       "      <td>21</td>\n",
       "      <td>CalTech</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Amy</td>\n",
       "      <td>22</td>\n",
       "      <td>MSU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Steve</td>\n",
       "      <td>21</td>\n",
       "      <td>MIT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ally</td>\n",
       "      <td>19</td>\n",
       "      <td>CalTech</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Name  Age University\n",
       "0   Mark   23        UTC\n",
       "1   John   21    CalTech\n",
       "2    Amy   22        MSU\n",
       "3  Steve   21        MIT\n",
       "4   Ally   19    CalTech"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "details = {\n",
    "    'Name' : ['Mark', 'John', 'Amy', 'Steve', 'Ally'],\n",
    "    'Age' : [23, 21, 22, 21, 19],\n",
    "    'University' : ['UTC', 'CalTech', 'MSU', 'MIT', 'CalTech'],\n",
    "}\n",
    "  \n",
    "# creating a Dataframe object \n",
    "df = pd.DataFrame(details)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>University</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mark</td>\n",
       "      <td>23</td>\n",
       "      <td>UTC</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Name  Age University\n",
       "0  Mark   23        UTC"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('Age >21 and Name == \"Mark\"') # similar to where clause in SQL Query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>University</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>John</td>\n",
       "      <td>21</td>\n",
       "      <td>CalTech</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Steve</td>\n",
       "      <td>21</td>\n",
       "      <td>MIT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ally</td>\n",
       "      <td>19</td>\n",
       "      <td>CalTech</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Name  Age University\n",
       "1   John   21    CalTech\n",
       "3  Steve   21        MIT\n",
       "4   Ally   19    CalTech"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the students in CalTech and MIT\n",
    "mask = df['University'].isin(['CalTech', 'MIT'])\n",
    "\n",
    "df[mask]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>University</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mark</td>\n",
       "      <td>23</td>\n",
       "      <td>UTC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Amy</td>\n",
       "      <td>22</td>\n",
       "      <td>MSU</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Name  Age University\n",
       "0  Mark   23        UTC\n",
       "2   Amy   22        MSU"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# not exclude the values, use ~\n",
    "df[~mask]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging and Concatenating Datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Types of Merging Data\n",
    "- Inner Join: Common data from both tables\n",
    "- Outer Join: Common and uncommon data from both tables\n",
    "- Left Join: Everything from left table, plus common data from right table\n",
    "- Right Join: Everything from right table, plus common data from left table\n",
    "\n",
    " \n",
    "Note: when a value does not exist in the joined table, we get a null, NA, NaN\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![joins](https://statisticsglobe.com/wp-content/uploads/2021/12/join-types-python-merge-programming.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![sql2](https://miro.medium.com/v2/resize:fit:1200/1*9eH1_7VbTZPZd9jBiGIyNA.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- we want to join df_1 and df_2 using 'instant' as a common factor\n",
    "- before we deploy the join, it's highly recommended that we evaluate the common factor (instant)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check1 - row count\n",
    "df_1.shape[0]  == df_2.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# using sum() check if both columns have the same scale\n",
    "df_1['instant'].sum() == df_2['instant'].sum() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "610"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# row by row equality scan\n",
    "(df_1['instant'] == df_2['instant']).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- all of the checks above gives us more confidence that 'instant' is an ideal common factor for joining"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>instant</th>\n",
       "      <th>dteday</th>\n",
       "      <th>season</th>\n",
       "      <th>yr</th>\n",
       "      <th>mnth</th>\n",
       "      <th>hr</th>\n",
       "      <th>holiday</th>\n",
       "      <th>weekday</th>\n",
       "      <th>weathersit</th>\n",
       "      <th>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>hum</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.2879</td>\n",
       "      <td>0.81</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.2727</td>\n",
       "      <td>0.80</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.2727</td>\n",
       "      <td>0.80</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.2879</td>\n",
       "      <td>0.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.2879</td>\n",
       "      <td>0.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   instant      dteday  season  yr  mnth  hr  holiday  weekday  weathersit  \\\n",
       "0        1  01-01-2011       1   0     1   0    False        6           1   \n",
       "1        2  01-01-2011       1   0     1   1    False        6           1   \n",
       "2        3  01-01-2011       1   0     1   2    False        6           1   \n",
       "3        4  01-01-2011       1   0     1   3    False        6           1   \n",
       "4        5  01-01-2011       1   0     1   4    False        6           1   \n",
       "\n",
       "   temp   atemp   hum  windspeed  casual  registered  cnt  \n",
       "0  0.24  0.2879  0.81        0.0       3          13   16  \n",
       "1  0.22  0.2727  0.80        0.0       8          32   40  \n",
       "2  0.22  0.2727  0.80        0.0       5          27   32  \n",
       "3  0.24  0.2879  0.75        0.0       3          10   13  \n",
       "4  0.24  0.2879  0.75        0.0       0           1    1  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_data = pd.merge(df_1 #dataset on the left\n",
    "                         , df_2 #dataset on the right\n",
    "                         , how='inner' # join type (here it doesn't matter since both datasets have the same ID (common factor) size)\n",
    "                         , on='instant') # specify the common factor (ID)\n",
    "\n",
    "combined_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Results above agree with the complete structure from the data dictionary. WE have all the columns we need for our analysis after the join."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Concatenation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In concatenation, make sure you have the same columns from both tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>instant</th>\n",
       "      <th>dteday</th>\n",
       "      <th>season</th>\n",
       "      <th>yr</th>\n",
       "      <th>mnth</th>\n",
       "      <th>hr</th>\n",
       "      <th>holiday</th>\n",
       "      <th>weekday</th>\n",
       "      <th>weathersit</th>\n",
       "      <th>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>hum</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>620</td>\n",
       "      <td>29-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.2273</td>\n",
       "      <td>0.64</td>\n",
       "      <td>0.1940</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>621</td>\n",
       "      <td>29-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.2273</td>\n",
       "      <td>0.64</td>\n",
       "      <td>0.1642</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>622</td>\n",
       "      <td>29-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.2121</td>\n",
       "      <td>0.64</td>\n",
       "      <td>0.1343</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>623</td>\n",
       "      <td>29-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.16</td>\n",
       "      <td>0.1818</td>\n",
       "      <td>0.69</td>\n",
       "      <td>0.1045</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>624</td>\n",
       "      <td>29-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.16</td>\n",
       "      <td>0.1818</td>\n",
       "      <td>0.64</td>\n",
       "      <td>0.1343</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   instant      dteday  season  yr  mnth  hr  holiday  weekday  weathersit  \\\n",
       "0      620  29-01-2011       1   0     1   1    False        6           1   \n",
       "1      621  29-01-2011       1   0     1   2    False        6           1   \n",
       "2      622  29-01-2011       1   0     1   3    False        6           1   \n",
       "3      623  29-01-2011       1   0     1   4    False        6           1   \n",
       "4      624  29-01-2011       1   0     1   6    False        6           1   \n",
       "\n",
       "   temp   atemp   hum  windspeed  casual  registered  cnt  \n",
       "0  0.22  0.2273  0.64     0.1940       0          20   20  \n",
       "1  0.22  0.2273  0.64     0.1642       0          15   15  \n",
       "2  0.20  0.2121  0.64     0.1343       3           5    8  \n",
       "3  0.16  0.1818  0.69     0.1045       1           2    3  \n",
       "4  0.16  0.1818  0.64     0.1343       0           2    2  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load 3rd dataset\n",
    "df_3 = pd.read_csv('DataWranglingDataset/final_rental_bike_dataset.csv')\n",
    "df_3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>instant</th>\n",
       "      <th>dteday</th>\n",
       "      <th>season</th>\n",
       "      <th>yr</th>\n",
       "      <th>mnth</th>\n",
       "      <th>hr</th>\n",
       "      <th>holiday</th>\n",
       "      <th>weekday</th>\n",
       "      <th>weathersit</th>\n",
       "      <th>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>hum</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>605</th>\n",
       "      <td>606</td>\n",
       "      <td>28-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>0.18</td>\n",
       "      <td>0.2121</td>\n",
       "      <td>0.93</td>\n",
       "      <td>0.1045</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>606</th>\n",
       "      <td>607</td>\n",
       "      <td>28-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>0.18</td>\n",
       "      <td>0.2121</td>\n",
       "      <td>0.93</td>\n",
       "      <td>0.1045</td>\n",
       "      <td>1</td>\n",
       "      <td>28</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>607</th>\n",
       "      <td>608</td>\n",
       "      <td>28-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>0.18</td>\n",
       "      <td>0.2121</td>\n",
       "      <td>0.93</td>\n",
       "      <td>0.1045</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>608</th>\n",
       "      <td>609</td>\n",
       "      <td>28-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.2727</td>\n",
       "      <td>0.80</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>2</td>\n",
       "      <td>36</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>609</th>\n",
       "      <td>610</td>\n",
       "      <td>28-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.2576</td>\n",
       "      <td>0.86</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     instant      dteday  season  yr  mnth  hr  holiday  weekday  weathersit  \\\n",
       "605      606  28-01-2011       1   0     1  11    False        5           3   \n",
       "606      607  28-01-2011       1   0     1  12    False        5           3   \n",
       "607      608  28-01-2011       1   0     1  13    False        5           3   \n",
       "608      609  28-01-2011       1   0     1  14    False        5           3   \n",
       "609      610  28-01-2011       1   0     1  15    False        5           2   \n",
       "\n",
       "     temp   atemp   hum  windspeed  casual  registered  cnt  \n",
       "605  0.18  0.2121  0.93     0.1045       0          30   30  \n",
       "606  0.18  0.2121  0.93     0.1045       1          28   29  \n",
       "607  0.18  0.2121  0.93     0.1045       0          31   31  \n",
       "608  0.22  0.2727  0.80     0.0000       2          36   38  \n",
       "609  0.20  0.2576  0.86     0.0000       1          40   41  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_data.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "610"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_data['instant'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>instant</th>\n",
       "      <th>dteday</th>\n",
       "      <th>season</th>\n",
       "      <th>yr</th>\n",
       "      <th>mnth</th>\n",
       "      <th>hr</th>\n",
       "      <th>holiday</th>\n",
       "      <th>weekday</th>\n",
       "      <th>weathersit</th>\n",
       "      <th>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>hum</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>611</td>\n",
       "      <td>28-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.2727</td>\n",
       "      <td>0.80</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>10</td>\n",
       "      <td>70</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>612</td>\n",
       "      <td>28-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.2424</td>\n",
       "      <td>0.75</td>\n",
       "      <td>0.1343</td>\n",
       "      <td>2</td>\n",
       "      <td>147</td>\n",
       "      <td>149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>613</td>\n",
       "      <td>28-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.2273</td>\n",
       "      <td>0.75</td>\n",
       "      <td>0.1940</td>\n",
       "      <td>2</td>\n",
       "      <td>107</td>\n",
       "      <td>109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>614</td>\n",
       "      <td>28-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.2424</td>\n",
       "      <td>0.75</td>\n",
       "      <td>0.1343</td>\n",
       "      <td>5</td>\n",
       "      <td>84</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>615</td>\n",
       "      <td>28-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.2273</td>\n",
       "      <td>0.70</td>\n",
       "      <td>0.1940</td>\n",
       "      <td>1</td>\n",
       "      <td>61</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   instant      dteday  season  yr  mnth  hr  holiday  weekday  weathersit  \\\n",
       "0      611  28-01-2011       1   0     1  16    False        5           1   \n",
       "1      612  28-01-2011       1   0     1  17    False        5           1   \n",
       "2      613  28-01-2011       1   0     1  18    False        5           1   \n",
       "3      614  28-01-2011       1   0     1  19    False        5           2   \n",
       "4      615  28-01-2011       1   0     1  20    False        5           2   \n",
       "\n",
       "   temp   atemp   hum  windspeed  casual  registered  cnt  \n",
       "0  0.22  0.2727  0.80     0.0000      10          70   80  \n",
       "1  0.24  0.2424  0.75     0.1343       2         147  149  \n",
       "2  0.24  0.2273  0.75     0.1940       2         107  109  \n",
       "3  0.24  0.2424  0.75     0.1343       5          84   89  \n",
       "4  0.24  0.2273  0.70     0.1940       1          61   62  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's  sort our data to make sure the missing 10 are available \n",
    "df_3 = df_3.sort_values(by=['instant']).reset_index(drop=True)\n",
    "df_3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>instant</th>\n",
       "      <th>dteday</th>\n",
       "      <th>season</th>\n",
       "      <th>yr</th>\n",
       "      <th>mnth</th>\n",
       "      <th>hr</th>\n",
       "      <th>holiday</th>\n",
       "      <th>weekday</th>\n",
       "      <th>weathersit</th>\n",
       "      <th>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>hum</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.2879</td>\n",
       "      <td>0.81</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.2727</td>\n",
       "      <td>0.80</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.2727</td>\n",
       "      <td>0.80</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.2879</td>\n",
       "      <td>0.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.2879</td>\n",
       "      <td>0.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   instant      dteday  season  yr  mnth  hr  holiday  weekday  weathersit  \\\n",
       "0        1  01-01-2011       1   0     1   0    False        6           1   \n",
       "1        2  01-01-2011       1   0     1   1    False        6           1   \n",
       "2        3  01-01-2011       1   0     1   2    False        6           1   \n",
       "3        4  01-01-2011       1   0     1   3    False        6           1   \n",
       "4        5  01-01-2011       1   0     1   4    False        6           1   \n",
       "\n",
       "   temp   atemp   hum  windspeed  casual  registered  cnt  \n",
       "0  0.24  0.2879  0.81        0.0       3          13   16  \n",
       "1  0.22  0.2727  0.80        0.0       8          32   40  \n",
       "2  0.22  0.2727  0.80        0.0       5          27   32  \n",
       "3  0.24  0.2879  0.75        0.0       3          10   13  \n",
       "4  0.24  0.2879  0.75        0.0       0           1    1  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_data = pd.concat([combined_data, df_3])\n",
    "\n",
    "final_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Quality Assurance (DQA)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(610, 16)\n",
      "(390, 16)\n",
      "(1000, 16)\n"
     ]
    }
   ],
   "source": [
    "print(combined_data.shape)\n",
    "print(df_3.shape)\n",
    "print(final_data.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A good final check: 610 + 390 rows = 1000 rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "instant         int64\n",
       "dteday         object\n",
       "season          int64\n",
       "yr              int64\n",
       "mnth            int64\n",
       "hr              int64\n",
       "holiday          bool\n",
       "weekday         int64\n",
       "weathersit      int64\n",
       "temp          float64\n",
       "atemp         float64\n",
       "hum           float64\n",
       "windspeed     float64\n",
       "casual          int64\n",
       "registered      int64\n",
       "cnt             int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_data.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "instant        0\n",
       "dteday         0\n",
       "season         0\n",
       "yr             0\n",
       "mnth           0\n",
       "hr             0\n",
       "holiday        0\n",
       "weekday        0\n",
       "weathersit     0\n",
       "temp           0\n",
       "atemp         11\n",
       "hum            0\n",
       "windspeed      0\n",
       "casual         0\n",
       "registered     0\n",
       "cnt            0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check for nulls\n",
    "final_data.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- We have 11 nulls that need to be addressed. Most ML models cannot work with nulls\n",
    "- Here we have multiple options to address the nulls (Imputation)\n",
    "    - we can fill the nulls with a value (will be addressed in detail in the next session)\n",
    "    - we can drop the row s that have nulls\n",
    "- We can consider dropping rows with nulls for now. but we have to make sure the following is true:\n",
    "    - dropping nulls is not going to cause information loss\n",
    "    - not going to impact the distribution of other columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.1 %\n"
     ]
    }
   ],
   "source": [
    "# to check for information loss, we can evaluate what percentage of nulls we have\n",
    "# perc missing values = count of nulls divided by the count of all values x 100\n",
    "\n",
    "percent_missing_val = (final_data.isna().sum().sum()/ len(final_data))*100\n",
    "print(round(percent_missing_val,2),'%')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- The missing values represent 1.1% of the overall data. \n",
    "- As long as they're below 4% (my recommendation) it is safe to drop the missing value rows without having a big impact"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0,  1,  2,  3,  4,  5,  6,  7,  8,  9, 10, 11, 12, 13, 14, 15, 16,\n",
       "       17, 18, 19, 20, 21, 22, 23])"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# if it's a 24hour time format, check that the numer values do not exceed 23\n",
    "final_data['hr'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1000"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# validation 5 - the cnt col is the total customers (casual + registered)\n",
    "(final_data['cnt'] == final_data['registered'] + final_data['casual']).sum()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_data['casual'].sum() + final_data['registered'].sum() == final_data['cnt'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>instant</th>\n",
       "      <th>dteday</th>\n",
       "      <th>season</th>\n",
       "      <th>yr</th>\n",
       "      <th>mnth</th>\n",
       "      <th>hr</th>\n",
       "      <th>holiday</th>\n",
       "      <th>weekday</th>\n",
       "      <th>weathersit</th>\n",
       "      <th>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>hum</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.2879</td>\n",
       "      <td>0.81</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.2727</td>\n",
       "      <td>0.80</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.2727</td>\n",
       "      <td>0.80</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.2879</td>\n",
       "      <td>0.75</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>01-01-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.2879</td>\n",
       "      <td>0.75</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>385</th>\n",
       "      <td>996</td>\n",
       "      <td>14-02-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.34</td>\n",
       "      <td>0.3182</td>\n",
       "      <td>0.46</td>\n",
       "      <td>0.2239</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>386</th>\n",
       "      <td>997</td>\n",
       "      <td>14-02-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.32</td>\n",
       "      <td>0.3030</td>\n",
       "      <td>0.53</td>\n",
       "      <td>0.2836</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>387</th>\n",
       "      <td>998</td>\n",
       "      <td>14-02-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.32</td>\n",
       "      <td>0.3030</td>\n",
       "      <td>0.53</td>\n",
       "      <td>0.2836</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388</th>\n",
       "      <td>999</td>\n",
       "      <td>14-02-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.34</td>\n",
       "      <td>0.3030</td>\n",
       "      <td>0.46</td>\n",
       "      <td>0.2985</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>389</th>\n",
       "      <td>1000</td>\n",
       "      <td>14-02-2011</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.34</td>\n",
       "      <td>0.3030</td>\n",
       "      <td>0.46</td>\n",
       "      <td>0.2985</td>\n",
       "      <td>2</td>\n",
       "      <td>96</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     instant      dteday  season  yr  mnth  hr  holiday  weekday  weathersit  \\\n",
       "0          1  01-01-2011       1   0     1   0    False        6           1   \n",
       "1          2  01-01-2011       1   0     1   1    False        6           1   \n",
       "2          3  01-01-2011       1   0     1   2    False        6           1   \n",
       "3          4  01-01-2011       1   0     1   3    False        6           1   \n",
       "4          5  01-01-2011       1   0     1   4    False        6           1   \n",
       "..       ...         ...     ...  ..   ...  ..      ...      ...         ...   \n",
       "385      996  14-02-2011       1   0     2   3    False        1           1   \n",
       "386      997  14-02-2011       1   0     2   4    False        1           1   \n",
       "387      998  14-02-2011       1   0     2   5    False        1           1   \n",
       "388      999  14-02-2011       1   0     2   6    False        1           1   \n",
       "389     1000  14-02-2011       1   0     2   7    False        1           1   \n",
       "\n",
       "     temp   atemp   hum  windspeed  casual  registered  cnt  \n",
       "0    0.24  0.2879  0.81     0.0000       3          13   16  \n",
       "1    0.22  0.2727  0.80     0.0000       8          32   40  \n",
       "2    0.22  0.2727  0.80     0.0000       5          27   32  \n",
       "3    0.24  0.2879  0.75     0.0000       3          10   13  \n",
       "4    0.24  0.2879  0.75     0.0000       0           1    1  \n",
       "..    ...     ...   ...        ...     ...         ...  ...  \n",
       "385  0.34  0.3182  0.46     0.2239       1           1    2  \n",
       "386  0.32  0.3030  0.53     0.2836       0           2    2  \n",
       "387  0.32  0.3030  0.53     0.2836       0           3    3  \n",
       "388  0.34  0.3030  0.46     0.2985       1          25   26  \n",
       "389  0.34  0.3030  0.46     0.2985       2          96   98  \n",
       "\n",
       "[1000 rows x 16 columns]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check for duplicate\n",
    "final_data.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['01-01-2011', '02-01-2011', '03-01-2011', '04-01-2011',\n",
       "       '05-01-2011', '06-01-2011', '07-01-2011', '08-01-2011',\n",
       "       '09-01-2011', '10-01-2011', '11-01-2011', '12-01-2011',\n",
       "       '13-01-2011', '14-01-2011', '15-01-2011', '16-01-2011',\n",
       "       '17-01-2011', '18-01-2011', '19-01-2011', '20-01-2011',\n",
       "       '21-01-2011', '22-01-2011', '23-01-2011', '24-01-2011',\n",
       "       '25-01-2011', '26-01-2011', '27-01-2011', '28-01-2011',\n",
       "       '29-01-2011', '30-01-2011', '31-01-2011', '01-02-2011',\n",
       "       '02-02-2011', '03-02-2011', '04-02-2011', '05-02-2011',\n",
       "       '06-02-2011', '07-02-2011', '08-02-2011', '09-02-2011',\n",
       "       '10-02-2011', '11-02-2011', '12-02-2011', '13-02-2011',\n",
       "       '14-02-2011'], dtype=object)"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_data['dteday'].unique()"
   ]
  },
  {
   "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.11.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}