00_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import plotly.graph_objs as go\n",
    "\n",
    "from dateutil import tz\n",
    "from tqdm import tqdm\n",
    "from glob import glob\n",
    "from collections import Counter\n",
    "from plotly.offline import iplot\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "\n",
    "\n",
    "InteractiveShell.ast_node_interactivity = 'all'\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['./data/bangkok_pm25.csv',\n",
       " './data/patumwan_weather.csv',\n",
       " './data/training.csv']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list_files = sorted(glob('./data/*.csv'))\n",
    "list_files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# http://air4thai.pcd.go.th/webV2/history/api/data.php?stationID=50t&param=PM25&type=hr&sdate=2016-10-01&edate=2019-01-17&stime=00&etime=24\n",
    "# https://api.wunderground.com/\n",
    "\n",
    "import requests\n",
    "from requests.adapters import HTTPAdapter\n",
    "from requests.packages.urllib3.util.retry import Retry\n",
    "import json\n",
    "import pandas as pd\n",
    "from datetime import timedelta, date\n",
    "from tqdm import tqdm\n",
    "\n",
    "start_date = date(2016, 3, 3)\n",
    "end_date = date(2019, 1, 28)\n",
    "\n",
    "\n",
    "def daterange(start_date, end_date):\n",
    "    for n in tqdm(range(int ((end_date - start_date).days))):\n",
    "        yield start_date + timedelta(n)\n",
    "\n",
    "write_header = 1\n",
    "write_header_file = 1 # change to 0 to continue without writing header to csv\n",
    "for single_date in daterange(start_date, end_date):\n",
    "    df_daily = pd.DataFrame()\n",
    "    ymd = single_date.strftime(\"%Y%m%d\")\n",
    "    url_test = \"https://api-ak.wunderground.com/api/606f3f6977348613/history_\"+ymd+\"null/units:metric/v:2.0/q/pws:IBANGKOK26.json\"\n",
    "    req = requests.get(url_test)\n",
    "    js = json.loads(req.text)\n",
    "    if js['history']['days'] == []:\n",
    "        print(\"empty data on\",ymd)\n",
    "        continue\n",
    "    for ob in js['history']['days'][0]['observations']:\n",
    "        ob['date'] = ob['date']['iso8601']\n",
    "        df_row = pd.DataFrame(ob, index=[0]) \n",
    "        df_row.index = df_row['date']\n",
    "        df_row = df_row.drop(columns=['date'])\n",
    "        if write_header == 1:\n",
    "            df_daily = df_row\n",
    "            write_header = 0\n",
    "        else:\n",
    "            df_daily = df_daily.append(df_row)\n",
    "\n",
    "    with open('patumwan_weather.csv','a+') as fd:\n",
    "        fd.write(df_daily.to_csv(header=write_header_file))\n",
    "        write_header_file = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert(x):\n",
    "    from_zone = tz.tzutc()\n",
    "    to_zone = tz.tzlocal()\n",
    "#     print(x)\n",
    "    return x.replace(tzinfo=from_zone).astimezone(to_zone)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>UTC Hour</th>\n",
       "      <th>PM2.5</th>\n",
       "      <th>PM10_mask</th>\n",
       "      <th>Retrospective</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>62.9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-03 08:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>62.9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-03 09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>55.5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-03 10:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>55.5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-03 11:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>47.9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-03 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>43.6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-03 13:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>28.6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-03 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>15</td>\n",
       "      <td>33.6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-03 15:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>34.8</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-03 16:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>17</td>\n",
       "      <td>31.3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-03 17:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>19</td>\n",
       "      <td>42.1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-03 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>38.6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-03 20:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>21</td>\n",
       "      <td>45.5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-03 21:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>22</td>\n",
       "      <td>49.9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-03 22:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>23</td>\n",
       "      <td>44.1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-03 23:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>42.1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-04 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>43.1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-04 01:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>44.8</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-04 02:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>37.5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-04 03:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>39.6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-04 04:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Year  Month  Day  UTC Hour  PM2.5  PM10_mask  Retrospective  \\\n",
       "0   2016      3    3         8   62.9          1              0   \n",
       "1   2016      3    3         9   62.9          1              0   \n",
       "2   2016      3    3        10   55.5          1              0   \n",
       "3   2016      3    3        11   55.5          1              0   \n",
       "4   2016      3    3        12   47.9          1              0   \n",
       "5   2016      3    3        13   43.6          1              0   \n",
       "6   2016      3    3        14   28.6          1              0   \n",
       "7   2016      3    3        15   33.6          1              0   \n",
       "8   2016      3    3        16   34.8          1              0   \n",
       "9   2016      3    3        17   31.3          1              0   \n",
       "10  2016      3    3        19   42.1          1              0   \n",
       "11  2016      3    3        20   38.6          1              0   \n",
       "12  2016      3    3        21   45.5          1              0   \n",
       "13  2016      3    3        22   49.9          1              0   \n",
       "14  2016      3    3        23   44.1          1              0   \n",
       "15  2016      3    4         0   42.1          1              0   \n",
       "16  2016      3    4         1   43.1          1              0   \n",
       "17  2016      3    4         2   44.8          1              0   \n",
       "18  2016      3    4         3   37.5          1              0   \n",
       "19  2016      3    4         4   39.6          1              0   \n",
       "\n",
       "                  date  \n",
       "0  2016-03-03 08:00:00  \n",
       "1  2016-03-03 09:00:00  \n",
       "2  2016-03-03 10:00:00  \n",
       "3  2016-03-03 11:00:00  \n",
       "4  2016-03-03 12:00:00  \n",
       "5  2016-03-03 13:00:00  \n",
       "6  2016-03-03 14:00:00  \n",
       "7  2016-03-03 15:00:00  \n",
       "8  2016-03-03 16:00:00  \n",
       "9  2016-03-03 17:00:00  \n",
       "10 2016-03-03 19:00:00  \n",
       "11 2016-03-03 20:00:00  \n",
       "12 2016-03-03 21:00:00  \n",
       "13 2016-03-03 22:00:00  \n",
       "14 2016-03-03 23:00:00  \n",
       "15 2016-03-04 00:00:00  \n",
       "16 2016-03-04 01:00:00  \n",
       "17 2016-03-04 02:00:00  \n",
       "18 2016-03-04 03:00:00  \n",
       "19 2016-03-04 04:00:00  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 23338 entries, 0 to 23337\n",
      "Data columns (total 3 columns):\n",
      "PM2.5          23338 non-null float64\n",
      "date           23338 non-null object\n",
      "day_of_week    23338 non-null object\n",
      "dtypes: float64(1), object(2)\n",
      "memory usage: 547.1+ KB\n"
     ]
    },
    {
     "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>PM2.5</th>\n",
       "      <th>date</th>\n",
       "      <th>day_of_week</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>62.9</td>\n",
       "      <td>2016-03-03 15</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>62.9</td>\n",
       "      <td>2016-03-03 16</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>55.5</td>\n",
       "      <td>2016-03-03 17</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>55.5</td>\n",
       "      <td>2016-03-03 18</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>47.9</td>\n",
       "      <td>2016-03-03 19</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>43.6</td>\n",
       "      <td>2016-03-03 20</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>28.6</td>\n",
       "      <td>2016-03-03 21</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>33.6</td>\n",
       "      <td>2016-03-03 22</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>34.8</td>\n",
       "      <td>2016-03-03 23</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>31.3</td>\n",
       "      <td>2016-03-04 00</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>42.1</td>\n",
       "      <td>2016-03-04 02</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>38.6</td>\n",
       "      <td>2016-03-04 03</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>45.5</td>\n",
       "      <td>2016-03-04 04</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>49.9</td>\n",
       "      <td>2016-03-04 05</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>44.1</td>\n",
       "      <td>2016-03-04 06</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>42.1</td>\n",
       "      <td>2016-03-04 07</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>43.1</td>\n",
       "      <td>2016-03-04 08</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>44.8</td>\n",
       "      <td>2016-03-04 09</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>37.5</td>\n",
       "      <td>2016-03-04 10</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>39.6</td>\n",
       "      <td>2016-03-04 11</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    PM2.5           date day_of_week\n",
       "0    62.9  2016-03-03 15    Thursday\n",
       "1    62.9  2016-03-03 16    Thursday\n",
       "2    55.5  2016-03-03 17    Thursday\n",
       "3    55.5  2016-03-03 18    Thursday\n",
       "4    47.9  2016-03-03 19    Thursday\n",
       "5    43.6  2016-03-03 20    Thursday\n",
       "6    28.6  2016-03-03 21    Thursday\n",
       "7    33.6  2016-03-03 22    Thursday\n",
       "8    34.8  2016-03-03 23    Thursday\n",
       "9    31.3  2016-03-04 00      Friday\n",
       "10   42.1  2016-03-04 02      Friday\n",
       "11   38.6  2016-03-04 03      Friday\n",
       "12   45.5  2016-03-04 04      Friday\n",
       "13   49.9  2016-03-04 05      Friday\n",
       "14   44.1  2016-03-04 06      Friday\n",
       "15   42.1  2016-03-04 07      Friday\n",
       "16   43.1  2016-03-04 08      Friday\n",
       "17   44.8  2016-03-04 09      Friday\n",
       "18   37.5  2016-03-04 10      Friday\n",
       "19   39.6  2016-03-04 11      Friday"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pm25_df = pd.read_csv(list_files[0])\n",
    "pm25_df['date'] = pm25_df['Year'].astype(str) + '-' + pm25_df['Month'].astype(str).str.zfill(2) + '-' + pm25_df['Day'].astype(str).str.zfill(2) + ' ' + pm25_df['UTC Hour'].astype(str).str.zfill(2)\n",
    "pm25_df['date'] = pd.to_datetime(pm25_df['date'], format='%Y-%m-%d %H')\n",
    "\n",
    "# convert time zone\n",
    "pm25_df.head(20)\n",
    "pm25_df['date'] = pm25_df['date'].apply(convert)\n",
    "pm25_df['Year'] = pm25_df['date'].dt.year\n",
    "pm25_df['Month'] = pm25_df['date'].dt.month\n",
    "pm25_df['Day'] = pm25_df['date'].dt.day\n",
    "pm25_df['UTC Hour'] = pm25_df['date'].dt.hour\n",
    "\n",
    "pm25_df['day_of_week'] = pm25_df['date'].dt.day_name()\n",
    "pm25_df['date'] = pm25_df['date'].dt.strftime('%Y-%m-%d %H')\n",
    "dateinfo = pm25_df[['date', 'Year', 'Month', 'Day', 'UTC Hour']]\n",
    "pm25_df = pm25_df.drop(['Year', 'Month', 'Day', 'UTC Hour', 'Retrospective', 'PM10_mask'], axis=1)\n",
    "\n",
    "pm25_df.info()\n",
    "pm25_df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 245497 entries, 0 to 245496\n",
      "Data columns (total 41 columns):\n",
      "date                  245497 non-null object\n",
      "temperature           245491 non-null float64\n",
      "dewpoint              245491 non-null float64\n",
      "humidity              245492 non-null float64\n",
      "wind_speed            245492 non-null float64\n",
      "wind_gust_speed       228129 non-null float64\n",
      "wind_dir_degrees      244164 non-null float64\n",
      "wind_dir              244273 non-null object\n",
      "pressure              245497 non-null float64\n",
      "windchill             0 non-null float64\n",
      "heatindex             207504 non-null float64\n",
      "precip                244511 non-null float64\n",
      "precip_rate           245497 non-null float64\n",
      "precip_1hr            245497 non-null float64\n",
      "precip_today          245210 non-null float64\n",
      "solarradiation        245491 non-null float64\n",
      "uv_index              245316 non-null float64\n",
      "temperature_indoor    9150 non-null float64\n",
      "humidity_indoor       9150 non-null float64\n",
      "software_type         245497 non-null object\n",
      "soil_temperature      0 non-null float64\n",
      "soil_moisture         0 non-null float64\n",
      "leaf_wetness          0 non-null float64\n",
      "aqpm10                0 non-null float64\n",
      "aqpm25                0 non-null float64\n",
      "aqozone               0 non-null float64\n",
      "aqno                  0 non-null float64\n",
      "aqno2t                0 non-null float64\n",
      "aqno2                 0 non-null float64\n",
      "aqno2y                0 non-null float64\n",
      "aqnox                 0 non-null float64\n",
      "aqnoy                 0 non-null float64\n",
      "aqno3                 0 non-null float64\n",
      "aqso4                 0 non-null float64\n",
      "aqso2                 0 non-null float64\n",
      "aqso2t                0 non-null float64\n",
      "aqco                  0 non-null float64\n",
      "aqcot                 0 non-null float64\n",
      "aqec                  0 non-null float64\n",
      "aqoc                  0 non-null float64\n",
      "aqbc                  0 non-null float64\n",
      "dtypes: float64(38), object(3)\n",
      "memory usage: 76.8+ MB\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>temperature</th>\n",
       "      <th>dewpoint</th>\n",
       "      <th>humidity</th>\n",
       "      <th>wind_speed</th>\n",
       "      <th>wind_gust_speed</th>\n",
       "      <th>wind_dir_degrees</th>\n",
       "      <th>wind_dir</th>\n",
       "      <th>pressure</th>\n",
       "      <th>windchill</th>\n",
       "      <th>...</th>\n",
       "      <th>aqnoy</th>\n",
       "      <th>aqno3</th>\n",
       "      <th>aqso4</th>\n",
       "      <th>aqso2</th>\n",
       "      <th>aqso2t</th>\n",
       "      <th>aqco</th>\n",
       "      <th>aqcot</th>\n",
       "      <th>aqec</th>\n",
       "      <th>aqoc</th>\n",
       "      <th>aqbc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-03-02 17</td>\n",
       "      <td>27.9</td>\n",
       "      <td>19.4</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>117.0</td>\n",
       "      <td>ESE</td>\n",
       "      <td>1011.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-03-02 17</td>\n",
       "      <td>27.9</td>\n",
       "      <td>20.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>117.0</td>\n",
       "      <td>ESE</td>\n",
       "      <td>1011.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-03-02 17</td>\n",
       "      <td>28.1</td>\n",
       "      <td>20.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>3.2</td>\n",
       "      <td>4.8</td>\n",
       "      <td>117.0</td>\n",
       "      <td>ESE</td>\n",
       "      <td>1011.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-03-02 17</td>\n",
       "      <td>28.1</td>\n",
       "      <td>20.6</td>\n",
       "      <td>63.0</td>\n",
       "      <td>1.6</td>\n",
       "      <td>6.4</td>\n",
       "      <td>117.0</td>\n",
       "      <td>ESE</td>\n",
       "      <td>1011.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-03-02 18</td>\n",
       "      <td>27.8</td>\n",
       "      <td>20.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>117.0</td>\n",
       "      <td>ESE</td>\n",
       "      <td>1010.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            date  temperature  dewpoint  humidity  wind_speed  \\\n",
       "0  2016-03-02 17         27.9      19.4      60.0         0.0   \n",
       "1  2016-03-02 17         27.9      20.0      62.0         0.0   \n",
       "2  2016-03-02 17         28.1      20.0      62.0         3.2   \n",
       "3  2016-03-02 17         28.1      20.6      63.0         1.6   \n",
       "4  2016-03-02 18         27.8      20.0      63.0         0.0   \n",
       "\n",
       "   wind_gust_speed  wind_dir_degrees wind_dir  pressure  windchill ...   \\\n",
       "0              0.0             117.0      ESE    1011.4        NaN ...    \n",
       "1              0.0             117.0      ESE    1011.4        NaN ...    \n",
       "2              4.8             117.0      ESE    1011.1        NaN ...    \n",
       "3              6.4             117.0      ESE    1011.1        NaN ...    \n",
       "4              0.0             117.0      ESE    1010.7        NaN ...    \n",
       "\n",
       "   aqnoy  aqno3  aqso4  aqso2  aqso2t  aqco  aqcot  aqec  aqoc aqbc  \n",
       "0    NaN    NaN    NaN    NaN     NaN   NaN    NaN   NaN   NaN  NaN  \n",
       "1    NaN    NaN    NaN    NaN     NaN   NaN    NaN   NaN   NaN  NaN  \n",
       "2    NaN    NaN    NaN    NaN     NaN   NaN    NaN   NaN   NaN  NaN  \n",
       "3    NaN    NaN    NaN    NaN     NaN   NaN    NaN   NaN   NaN  NaN  \n",
       "4    NaN    NaN    NaN    NaN     NaN   NaN    NaN   NaN   NaN  NaN  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather_df = pd.read_csv(list_files[1])\n",
    "weather_df['date'] = pd.to_datetime(weather_df['date'])\n",
    "weather_df['date'] = weather_df.date.dt.strftime('%Y-%m-%d %H')\n",
    "weather_df.info()\n",
    "weather_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.merge(pm25_df, weather_df, on='date')"
   ]
  },
  {
   "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>PM2.5</th>\n",
       "      <th>date</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>temperature</th>\n",
       "      <th>dewpoint</th>\n",
       "      <th>humidity</th>\n",
       "      <th>wind_speed</th>\n",
       "      <th>wind_gust_speed</th>\n",
       "      <th>wind_dir_degrees</th>\n",
       "      <th>wind_dir</th>\n",
       "      <th>...</th>\n",
       "      <th>aqnoy</th>\n",
       "      <th>aqno3</th>\n",
       "      <th>aqso4</th>\n",
       "      <th>aqso2</th>\n",
       "      <th>aqso2t</th>\n",
       "      <th>aqco</th>\n",
       "      <th>aqcot</th>\n",
       "      <th>aqec</th>\n",
       "      <th>aqoc</th>\n",
       "      <th>aqbc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>62.9</td>\n",
       "      <td>2016-03-03 15</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>28.6</td>\n",
       "      <td>21.7</td>\n",
       "      <td>66.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14.5</td>\n",
       "      <td>211.0</td>\n",
       "      <td>SSW</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>62.9</td>\n",
       "      <td>2016-03-03 15</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>28.3</td>\n",
       "      <td>22.2</td>\n",
       "      <td>69.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.4</td>\n",
       "      <td>100.0</td>\n",
       "      <td>East</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>62.9</td>\n",
       "      <td>2016-03-03 15</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>28.2</td>\n",
       "      <td>22.2</td>\n",
       "      <td>69.0</td>\n",
       "      <td>3.2</td>\n",
       "      <td>8.0</td>\n",
       "      <td>237.0</td>\n",
       "      <td>WSW</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>62.9</td>\n",
       "      <td>2016-03-03 16</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>28.1</td>\n",
       "      <td>22.2</td>\n",
       "      <td>70.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.6</td>\n",
       "      <td>333.0</td>\n",
       "      <td>NNW</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>62.9</td>\n",
       "      <td>2016-03-03 16</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>28.0</td>\n",
       "      <td>22.8</td>\n",
       "      <td>72.0</td>\n",
       "      <td>1.6</td>\n",
       "      <td>6.4</td>\n",
       "      <td>69.0</td>\n",
       "      <td>ENE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 43 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   PM2.5           date day_of_week  temperature  dewpoint  humidity  \\\n",
       "0   62.9  2016-03-03 15    Thursday         28.6      21.7      66.0   \n",
       "1   62.9  2016-03-03 15    Thursday         28.3      22.2      69.0   \n",
       "2   62.9  2016-03-03 15    Thursday         28.2      22.2      69.0   \n",
       "3   62.9  2016-03-03 16    Thursday         28.1      22.2      70.0   \n",
       "4   62.9  2016-03-03 16    Thursday         28.0      22.8      72.0   \n",
       "\n",
       "   wind_speed  wind_gust_speed  wind_dir_degrees wind_dir  ...   aqnoy  aqno3  \\\n",
       "0         0.0             14.5             211.0      SSW  ...     NaN    NaN   \n",
       "1         0.0              6.4             100.0     East  ...     NaN    NaN   \n",
       "2         3.2              8.0             237.0      WSW  ...     NaN    NaN   \n",
       "3         0.0              1.6             333.0      NNW  ...     NaN    NaN   \n",
       "4         1.6              6.4              69.0      ENE  ...     NaN    NaN   \n",
       "\n",
       "   aqso4  aqso2  aqso2t  aqco  aqcot  aqec  aqoc  aqbc  \n",
       "0    NaN    NaN     NaN   NaN    NaN   NaN   NaN   NaN  \n",
       "1    NaN    NaN     NaN   NaN    NaN   NaN   NaN   NaN  \n",
       "2    NaN    NaN     NaN   NaN    NaN   NaN   NaN   NaN  \n",
       "3    NaN    NaN     NaN   NaN    NaN   NaN   NaN   NaN  \n",
       "4    NaN    NaN     NaN   NaN    NaN   NaN   NaN   NaN  \n",
       "\n",
       "[5 rows x 43 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "227213"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = []\n",
    "for c, v in zip(df.columns, df.isna().sum() < 10):\n",
    "    if v == True:\n",
    "        col.append(c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[col]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop('software_type', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date|</th>\n",
       "      <th>PM2.5|mean</th>\n",
       "      <th>PM2.5|median</th>\n",
       "      <th>PM2.5|std</th>\n",
       "      <th>PM2.5|min</th>\n",
       "      <th>PM2.5|max</th>\n",
       "      <th>temperature|mean</th>\n",
       "      <th>temperature|median</th>\n",
       "      <th>temperature|std</th>\n",
       "      <th>temperature|min</th>\n",
       "      <th>...</th>\n",
       "      <th>precip_1hr|mean</th>\n",
       "      <th>precip_1hr|median</th>\n",
       "      <th>precip_1hr|std</th>\n",
       "      <th>precip_1hr|min</th>\n",
       "      <th>precip_1hr|max</th>\n",
       "      <th>solarradiation|mean</th>\n",
       "      <th>solarradiation|median</th>\n",
       "      <th>solarradiation|std</th>\n",
       "      <th>solarradiation|min</th>\n",
       "      <th>solarradiation|max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-03-03 15</td>\n",
       "      <td>62.9</td>\n",
       "      <td>62.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.9</td>\n",
       "      <td>62.9</td>\n",
       "      <td>28.366667</td>\n",
       "      <td>28.30</td>\n",
       "      <td>0.208167</td>\n",
       "      <td>28.2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-03-03 16</td>\n",
       "      <td>62.9</td>\n",
       "      <td>62.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.9</td>\n",
       "      <td>62.9</td>\n",
       "      <td>28.000000</td>\n",
       "      <td>28.00</td>\n",
       "      <td>0.081650</td>\n",
       "      <td>27.9</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-03-03 17</td>\n",
       "      <td>55.5</td>\n",
       "      <td>55.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>55.5</td>\n",
       "      <td>55.5</td>\n",
       "      <td>27.700000</td>\n",
       "      <td>27.70</td>\n",
       "      <td>0.081650</td>\n",
       "      <td>27.6</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-03-03 18</td>\n",
       "      <td>55.5</td>\n",
       "      <td>55.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>55.5</td>\n",
       "      <td>55.5</td>\n",
       "      <td>27.433333</td>\n",
       "      <td>27.40</td>\n",
       "      <td>0.057735</td>\n",
       "      <td>27.4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-03-03 19</td>\n",
       "      <td>47.9</td>\n",
       "      <td>47.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>47.9</td>\n",
       "      <td>47.9</td>\n",
       "      <td>27.250000</td>\n",
       "      <td>27.25</td>\n",
       "      <td>0.057735</td>\n",
       "      <td>27.2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 46 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           date|  PM2.5|mean  PM2.5|median  PM2.5|std  PM2.5|min  PM2.5|max  \\\n",
       "0  2016-03-03 15        62.9          62.9        0.0       62.9       62.9   \n",
       "1  2016-03-03 16        62.9          62.9        0.0       62.9       62.9   \n",
       "2  2016-03-03 17        55.5          55.5        0.0       55.5       55.5   \n",
       "3  2016-03-03 18        55.5          55.5        0.0       55.5       55.5   \n",
       "4  2016-03-03 19        47.9          47.9        0.0       47.9       47.9   \n",
       "\n",
       "   temperature|mean  temperature|median  temperature|std  temperature|min  \\\n",
       "0         28.366667               28.30         0.208167             28.2   \n",
       "1         28.000000               28.00         0.081650             27.9   \n",
       "2         27.700000               27.70         0.081650             27.6   \n",
       "3         27.433333               27.40         0.057735             27.4   \n",
       "4         27.250000               27.25         0.057735             27.2   \n",
       "\n",
       "          ...          precip_1hr|mean  precip_1hr|median  precip_1hr|std  \\\n",
       "0         ...                      0.0                0.0             0.0   \n",
       "1         ...                      0.0                0.0             0.0   \n",
       "2         ...                      0.0                0.0             0.0   \n",
       "3         ...                      0.0                0.0             0.0   \n",
       "4         ...                      0.0                0.0             0.0   \n",
       "\n",
       "   precip_1hr|min  precip_1hr|max  solarradiation|mean  solarradiation|median  \\\n",
       "0             0.0             0.0                  0.0                    0.0   \n",
       "1             0.0             0.0                  0.0                    0.0   \n",
       "2             0.0             0.0                  0.0                    0.0   \n",
       "3             0.0             0.0                  0.0                    0.0   \n",
       "4             0.0             0.0                  0.0                    0.0   \n",
       "\n",
       "   solarradiation|std  solarradiation|min  solarradiation|max  \n",
       "0                 0.0                 0.0                 0.0  \n",
       "1                 0.0                 0.0                 0.0  \n",
       "2                 0.0                 0.0                 0.0  \n",
       "3                 0.0                 0.0                 0.0  \n",
       "4                 0.0                 0.0                 0.0  \n",
       "\n",
       "[5 rows x 46 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tmp = df.groupby(['date']).agg(['mean', 'median', 'std', 'min', 'max']).reset_index()\n",
    "tmp.columns = tmp.columns.map('|'.join)\n",
    "tmp.head()\n",
    "# df = df.groupby(['date']).agg(['mean', 'median']).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp = tmp.rename(columns={\"date|\": 'date'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp['date'] = pd.to_datetime(tmp['date'], format='%Y-%m-%d %H')\n",
    "tmp['day_of_week'] = tmp['date'].dt.day_name()\n",
    "dateinfo['date'] = pd.to_datetime(dateinfo['date'], format='%Y-%m-%d %H')\n",
    "tmp = pd.merge(tmp, dateinfo, on='date')\n",
    "tmp = tmp.rename(columns={\"UTC Hour\": \"Hour\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp['day_of_year'] = tmp['date'].dt.dayofyear\n",
    "tmp['quarter'] = tmp['date'].dt.quarter\n",
    "tmp['week_of_year'] = tmp['date'].dt.weekofyear"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp.to_csv('./data/training.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>PM2.5|mean</th>\n",
       "      <th>PM2.5|median</th>\n",
       "      <th>PM2.5|std</th>\n",
       "      <th>PM2.5|min</th>\n",
       "      <th>PM2.5|max</th>\n",
       "      <th>temperature|mean</th>\n",
       "      <th>temperature|median</th>\n",
       "      <th>temperature|std</th>\n",
       "      <th>temperature|min</th>\n",
       "      <th>...</th>\n",
       "      <th>solarradiation|min</th>\n",
       "      <th>solarradiation|max</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>day_of_year</th>\n",
       "      <th>quarter</th>\n",
       "      <th>week_of_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-03-03 15:00:00</td>\n",
       "      <td>62.9</td>\n",
       "      <td>62.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.9</td>\n",
       "      <td>62.9</td>\n",
       "      <td>28.366667</td>\n",
       "      <td>28.30</td>\n",
       "      <td>0.208167</td>\n",
       "      <td>28.2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>15</td>\n",
       "      <td>63</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-03-03 16:00:00</td>\n",
       "      <td>62.9</td>\n",
       "      <td>62.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.9</td>\n",
       "      <td>62.9</td>\n",
       "      <td>28.000000</td>\n",
       "      <td>28.00</td>\n",
       "      <td>0.081650</td>\n",
       "      <td>27.9</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>63</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-03-03 17:00:00</td>\n",
       "      <td>55.5</td>\n",
       "      <td>55.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>55.5</td>\n",
       "      <td>55.5</td>\n",
       "      <td>27.700000</td>\n",
       "      <td>27.70</td>\n",
       "      <td>0.081650</td>\n",
       "      <td>27.6</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>17</td>\n",
       "      <td>63</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-03-03 18:00:00</td>\n",
       "      <td>55.5</td>\n",
       "      <td>55.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>55.5</td>\n",
       "      <td>55.5</td>\n",
       "      <td>27.433333</td>\n",
       "      <td>27.40</td>\n",
       "      <td>0.057735</td>\n",
       "      <td>27.4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>18</td>\n",
       "      <td>63</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-03-03 19:00:00</td>\n",
       "      <td>47.9</td>\n",
       "      <td>47.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>47.9</td>\n",
       "      <td>47.9</td>\n",
       "      <td>27.250000</td>\n",
       "      <td>27.25</td>\n",
       "      <td>0.057735</td>\n",
       "      <td>27.2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>19</td>\n",
       "      <td>63</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 54 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 date  PM2.5|mean  PM2.5|median  PM2.5|std  PM2.5|min  \\\n",
       "0 2016-03-03 15:00:00        62.9          62.9        0.0       62.9   \n",
       "1 2016-03-03 16:00:00        62.9          62.9        0.0       62.9   \n",
       "2 2016-03-03 17:00:00        55.5          55.5        0.0       55.5   \n",
       "3 2016-03-03 18:00:00        55.5          55.5        0.0       55.5   \n",
       "4 2016-03-03 19:00:00        47.9          47.9        0.0       47.9   \n",
       "\n",
       "   PM2.5|max  temperature|mean  temperature|median  temperature|std  \\\n",
       "0       62.9         28.366667               28.30         0.208167   \n",
       "1       62.9         28.000000               28.00         0.081650   \n",
       "2       55.5         27.700000               27.70         0.081650   \n",
       "3       55.5         27.433333               27.40         0.057735   \n",
       "4       47.9         27.250000               27.25         0.057735   \n",
       "\n",
       "   temperature|min      ...       solarradiation|min  solarradiation|max  \\\n",
       "0             28.2      ...                      0.0                 0.0   \n",
       "1             27.9      ...                      0.0                 0.0   \n",
       "2             27.6      ...                      0.0                 0.0   \n",
       "3             27.4      ...                      0.0                 0.0   \n",
       "4             27.2      ...                      0.0                 0.0   \n",
       "\n",
       "   day_of_week  Year  Month  Day  Hour  day_of_year  quarter  week_of_year  \n",
       "0     Thursday  2016      3    3    15           63        1             9  \n",
       "1     Thursday  2016      3    3    16           63        1             9  \n",
       "2     Thursday  2016      3    3    17           63        1             9  \n",
       "3     Thursday  2016      3    3    18           63        1             9  \n",
       "4     Thursday  2016      3    3    19           63        1             9  \n",
       "\n",
       "[5 rows x 54 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tmp.head()"
   ]
  },
  {
   "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}