XMLDataScrape.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# E.coli Data XML Data Scraping Phase "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xml.etree.cElementTree as et\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import requests\n",
    "\n",
    "from statsmodels.tsa.seasonal import seasonal_decompose\n",
    "from statsmodels.tsa.arima_model import ARIMA\n",
    "from statsmodels.graphics import tsaplots\n",
    "\n",
    "from pandas.plotting import autocorrelation_plot\n",
    "from statsmodels.graphics.tsaplots import plot_acf, plot_pacf\n",
    "from sklearn.metrics import mean_squared_error\n",
    "\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "\n",
    "from IPython.core.debugger import set_trace"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Define \"get value of node\" function to handle errors where there is null data\n",
    "def getvalueofnode(node):\n",
    "    \"\"\" return node text or None \"\"\"\n",
    "    return node.text if node is not None else None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# retrieve the XML data from the URL\n",
    "beaches_history = requests.get('http://app.toronto.ca/tpha/ws/beaches/history.xml?v=1.0&from=2009-06-01&to=2018-09-15').text "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#parse the XML\n",
    "parsed_xml = et.fromstring(beaches_history)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Define dataframe columns and create dataframe to receive parsed data\n",
    "metacols = ['beachID','beachName','beachLat','beachLong']\n",
    "dfcols = ['beachID', 'sampleDate', 'publishDate', 'eColiCount','beachAdvisory']\n",
    "df_beaches_meta = pd.DataFrame(columns=metacols)\n",
    "df_beaches_history = pd.DataFrame(columns=dfcols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Iterate through all elements and return the beach metadata via attributes\n",
    "for node in parsed_xml.iter():\n",
    "    beachID = node.attrib.get('id')\n",
    "    beachName = node.attrib.get('name')\n",
    "    beachLat = node.attrib.get('lat')\n",
    "    beachLong = node.attrib.get('long')\n",
    "\n",
    "    #Only append the result to the dataframe for elements where beachID is not blank\n",
    "    if beachID is not None:\n",
    " \n",
    "        df_beaches_meta = df_beaches_meta.append(\n",
    "            pd.Series([beachID, beachName, beachLat, beachLong], index=metacols),\n",
    "            ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Iterate through all elements and return the data we want via attributes\n",
    "for node in parsed_xml.iter():\n",
    "    beachID = node.attrib.get('beachId')\n",
    "    sampleDate = node.find('sampleDate')\n",
    "    publishDate = node.find('publishDate')\n",
    "    eColiCount = node.find('eColiCount')\n",
    "    beachAdvisory = node.find('beachAdvisory')\n",
    "\n",
    "    #Only append the result to the dataframe for elements where beachID is not blank\n",
    "    if beachID is not None:\n",
    " \n",
    "        df_beaches_history = df_beaches_history.append(\n",
    "            pd.Series([beachID, getvalueofnode(sampleDate), getvalueofnode(publishDate),\n",
    "                        getvalueofnode(eColiCount),getvalueofnode(beachAdvisory)], index=dfcols),\n",
    "            ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Replace empty data with np.nan values\n",
    "df_beaches_history.replace(to_replace=[None], value=np.nan, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Merge historical data with current data\n",
    "beach_data_merged = results=df_beaches_history.merge(df_beaches_meta,on='beachID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Clean the beach data\n",
    "beach_data_clean=df_beaches_history.drop(['beachAdvisory','publishDate'],axis=1)\n",
    "beach_complete=beach_data_clean.pivot(index='sampleDate',columns='beachID',values='eColiCount')\n",
    "beach_complete.index = pd.to_datetime(beach_complete.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>beachID</th>\n",
       "      <th>1</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sampleDate</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2009-06-01</th>\n",
       "      <td>27</td>\n",
       "      <td>12</td>\n",
       "      <td>39</td>\n",
       "      <td>28</td>\n",
       "      <td>11</td>\n",
       "      <td>18</td>\n",
       "      <td>24</td>\n",
       "      <td>63</td>\n",
       "      <td>51</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-02</th>\n",
       "      <td>10</td>\n",
       "      <td>14</td>\n",
       "      <td>52</td>\n",
       "      <td>16</td>\n",
       "      <td>10</td>\n",
       "      <td>18</td>\n",
       "      <td>22</td>\n",
       "      <td>18</td>\n",
       "      <td>19</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-03</th>\n",
       "      <td>22</td>\n",
       "      <td>11</td>\n",
       "      <td>14</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>14</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-04</th>\n",
       "      <td>26</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-05</th>\n",
       "      <td>35</td>\n",
       "      <td>10</td>\n",
       "      <td>31</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "beachID      1  10  11   2   3   4   5   6   7   8   9\n",
       "sampleDate                                            \n",
       "2009-06-01  27  12  39  28  11  18  24  63  51  10  10\n",
       "2009-06-02  10  14  52  16  10  18  22  18  19  10  10\n",
       "2009-06-03  22  11  14  10  10  11  14  10  10  10  10\n",
       "2009-06-04  26  10  11  10  10  11  11  10  10  10  10\n",
       "2009-06-05  35  10  31  11  10  10  10  12  10  10  10"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "beach_complete.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>beachID</th>\n",
       "      <th>MCurtis-1</th>\n",
       "      <th>Bluffers-10</th>\n",
       "      <th>Rouge-11</th>\n",
       "      <th>Sunnyside-2</th>\n",
       "      <th>Hanlans-3</th>\n",
       "      <th>Gibraltar-4</th>\n",
       "      <th>Centre-5</th>\n",
       "      <th>Wards-6</th>\n",
       "      <th>Cherry-7</th>\n",
       "      <th>Woodbine-8</th>\n",
       "      <th>KewBalmy-9</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sampleDate</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2009-06-01</th>\n",
       "      <td>27</td>\n",
       "      <td>12</td>\n",
       "      <td>39</td>\n",
       "      <td>28</td>\n",
       "      <td>11</td>\n",
       "      <td>18</td>\n",
       "      <td>24</td>\n",
       "      <td>63</td>\n",
       "      <td>51</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-02</th>\n",
       "      <td>10</td>\n",
       "      <td>14</td>\n",
       "      <td>52</td>\n",
       "      <td>16</td>\n",
       "      <td>10</td>\n",
       "      <td>18</td>\n",
       "      <td>22</td>\n",
       "      <td>18</td>\n",
       "      <td>19</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-03</th>\n",
       "      <td>22</td>\n",
       "      <td>11</td>\n",
       "      <td>14</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>14</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-04</th>\n",
       "      <td>26</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-05</th>\n",
       "      <td>35</td>\n",
       "      <td>10</td>\n",
       "      <td>31</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "beachID    MCurtis-1 Bluffers-10 Rouge-11 Sunnyside-2 Hanlans-3 Gibraltar-4  \\\n",
       "sampleDate                                                                    \n",
       "2009-06-01        27          12       39          28        11          18   \n",
       "2009-06-02        10          14       52          16        10          18   \n",
       "2009-06-03        22          11       14          10        10          11   \n",
       "2009-06-04        26          10       11          10        10          11   \n",
       "2009-06-05        35          10       31          11        10          10   \n",
       "\n",
       "beachID    Centre-5 Wards-6 Cherry-7 Woodbine-8 KewBalmy-9  \n",
       "sampleDate                                                  \n",
       "2009-06-01       24      63       51         10         10  \n",
       "2009-06-02       22      18       19         10         10  \n",
       "2009-06-03       14      10       10         10         10  \n",
       "2009-06-04       11      10       10         10         10  \n",
       "2009-06-05       10      12       10         10         10  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Renaming the beach columns from their beachIDs to their names + beachIDs so that\n",
    "#the beach columns are more easily recognized.\n",
    "beach_complete = beach_complete.rename(columns={\"1\": \"MCurtis-1\", \"10\": \"Bluffers-10\", \"11\": \"Rouge-11\", \n",
    "                                       \"2\": \"Sunnyside-2\", \"3\": \"Hanlans-3\",\n",
    "                                       \"4\": \"Gibraltar-4\", \"5\": \"Centre-5\",\n",
    "                                       \"6\": \"Wards-6\", \"7\": \"Cherry-7\", \"8\": \"Woodbine-8\",\n",
    "                                       \"9\": \"KewBalmy-9\"})\n",
    "beach_complete.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "beach_complete.fillna(np.nan, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>beachID</th>\n",
       "      <th>MCurtis-1</th>\n",
       "      <th>Bluffers-10</th>\n",
       "      <th>Rouge-11</th>\n",
       "      <th>Sunnyside-2</th>\n",
       "      <th>Hanlans-3</th>\n",
       "      <th>Gibraltar-4</th>\n",
       "      <th>Centre-5</th>\n",
       "      <th>Wards-6</th>\n",
       "      <th>Cherry-7</th>\n",
       "      <th>Woodbine-8</th>\n",
       "      <th>KewBalmy-9</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sampleDate</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2009-06-01</th>\n",
       "      <td>27.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-02</th>\n",
       "      <td>10.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-03</th>\n",
       "      <td>22.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-04</th>\n",
       "      <td>26.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-05</th>\n",
       "      <td>35.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-06</th>\n",
       "      <td>133.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-07</th>\n",
       "      <td>604.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-08</th>\n",
       "      <td>405.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>99.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-09</th>\n",
       "      <td>NaN</td>\n",
       "      <td>37.0</td>\n",
       "      <td>368.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-10</th>\n",
       "      <td>170.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-11</th>\n",
       "      <td>62.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-12</th>\n",
       "      <td>26.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-13</th>\n",
       "      <td>114.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-14</th>\n",
       "      <td>219.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-15</th>\n",
       "      <td>199.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-16</th>\n",
       "      <td>47.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-17</th>\n",
       "      <td>62.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>138.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-18</th>\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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-19</th>\n",
       "      <td>343.0</td>\n",
       "      <td>315.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>108.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-20</th>\n",
       "      <td>758.0</td>\n",
       "      <td>425.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>206.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>93.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-21</th>\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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-22</th>\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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-23</th>\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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-24</th>\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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-25</th>\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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-26</th>\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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-27</th>\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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-28</th>\n",
       "      <td>NaN</td>\n",
       "      <td>28.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>351.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-29</th>\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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-30</th>\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",
       "      <td>NaN</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-05</th>\n",
       "      <td>11.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-06</th>\n",
       "      <td>12.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-07</th>\n",
       "      <td>115.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>343.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-08</th>\n",
       "      <td>895.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>319.0</td>\n",
       "      <td>722.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>189.0</td>\n",
       "      <td>116.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-09</th>\n",
       "      <td>440.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>659.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>247.0</td>\n",
       "      <td>475.0</td>\n",
       "      <td>234.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-10</th>\n",
       "      <td>385.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>343.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>213.0</td>\n",
       "      <td>103.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>84.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-11</th>\n",
       "      <td>313.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-12</th>\n",
       "      <td>40.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-13</th>\n",
       "      <td>28.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-14</th>\n",
       "      <td>57.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-15</th>\n",
       "      <td>34.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>53.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-16</th>\n",
       "      <td>17.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-17</th>\n",
       "      <td>100.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>128.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>127.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-18</th>\n",
       "      <td>752.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>651.0</td>\n",
       "      <td>436.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>420.0</td>\n",
       "      <td>684.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-19</th>\n",
       "      <td>165.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>346.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>116.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-20</th>\n",
       "      <td>55.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>107.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-21</th>\n",
       "      <td>145.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>161.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>52.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-22</th>\n",
       "      <td>546.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>474.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>177.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>97.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-23</th>\n",
       "      <td>328.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>285.0</td>\n",
       "      <td>219.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>131.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-24</th>\n",
       "      <td>72.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>288.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-25</th>\n",
       "      <td>NaN</td>\n",
       "      <td>12.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>123.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-26</th>\n",
       "      <td>NaN</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>169.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-27</th>\n",
       "      <td>1953.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>254.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-28</th>\n",
       "      <td>691.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>423.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-29</th>\n",
       "      <td>169.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>308.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>93.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-30</th>\n",
       "      <td>24.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>61.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-01</th>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>54.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-02</th>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-03</th>\n",
       "      <td>38.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>929 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "beachID     MCurtis-1  Bluffers-10  Rouge-11  Sunnyside-2  Hanlans-3  \\\n",
       "sampleDate                                                             \n",
       "2009-06-01       27.0         12.0      39.0         28.0       11.0   \n",
       "2009-06-02       10.0         14.0      52.0         16.0       10.0   \n",
       "2009-06-03       22.0         11.0      14.0         10.0       10.0   \n",
       "2009-06-04       26.0         10.0      11.0         10.0       10.0   \n",
       "2009-06-05       35.0         10.0      31.0         11.0       10.0   \n",
       "2009-06-06      133.0         13.0      29.0         18.0       12.0   \n",
       "2009-06-07      604.0         16.0      20.0         97.0       37.0   \n",
       "2009-06-08      405.0         17.0      99.0        142.0       31.0   \n",
       "2009-06-09        NaN         37.0     368.0         63.0       11.0   \n",
       "2009-06-10      170.0         25.0     155.0         26.0       11.0   \n",
       "2009-06-11       62.0         10.0      27.0         12.0       10.0   \n",
       "2009-06-12       26.0         14.0      61.0         14.0       10.0   \n",
       "2009-06-13      114.0         14.0      46.0         20.0       10.0   \n",
       "2009-06-14      219.0         10.0      11.0         19.0       11.0   \n",
       "2009-06-15      199.0         10.0      13.0         14.0       11.0   \n",
       "2009-06-16       47.0         10.0      28.0         16.0       10.0   \n",
       "2009-06-17       62.0         60.0     138.0         37.0       11.0   \n",
       "2009-06-18        NaN          NaN       NaN          NaN        NaN   \n",
       "2009-06-19      343.0        315.0      86.0         65.0       11.0   \n",
       "2009-06-20      758.0        425.0     101.0         63.0        NaN   \n",
       "2009-06-21        NaN          NaN       NaN          NaN        NaN   \n",
       "2009-06-22        NaN          NaN       NaN          NaN        NaN   \n",
       "2009-06-23        NaN          NaN       NaN          NaN        NaN   \n",
       "2009-06-24        NaN          NaN       NaN          NaN        NaN   \n",
       "2009-06-25        NaN          NaN       NaN          NaN        NaN   \n",
       "2009-06-26        NaN          NaN       NaN          NaN        NaN   \n",
       "2009-06-27        NaN          NaN       NaN          NaN        NaN   \n",
       "2009-06-28        NaN         28.0       NaN         34.0        NaN   \n",
       "2009-06-29        NaN          NaN       NaN          NaN        NaN   \n",
       "2009-06-30        NaN          NaN       NaN          NaN        NaN   \n",
       "...               ...          ...       ...          ...        ...   \n",
       "2018-08-05       11.0         10.0      10.0         60.0       36.0   \n",
       "2018-08-06       12.0         11.0      15.0        114.0       91.0   \n",
       "2018-08-07      115.0         14.0      87.0        343.0       57.0   \n",
       "2018-08-08      895.0         16.0     319.0        722.0       46.0   \n",
       "2018-08-09      440.0         24.0      83.0        659.0       64.0   \n",
       "2018-08-10      385.0         53.0      24.0        343.0       52.0   \n",
       "2018-08-11      313.0         29.0      19.0         80.0       20.0   \n",
       "2018-08-12       40.0         11.0      16.0         37.0       16.0   \n",
       "2018-08-13       28.0         10.0      45.0         60.0       16.0   \n",
       "2018-08-14       57.0         22.0      62.0         76.0       15.0   \n",
       "2018-08-15       34.0         27.0      35.0         87.0       13.0   \n",
       "2018-08-16       17.0         12.0      37.0         76.0       10.0   \n",
       "2018-08-17      100.0         14.0     140.0        128.0       10.0   \n",
       "2018-08-18      752.0         15.0     651.0        436.0        NaN   \n",
       "2018-08-19      165.0         13.0     109.0        346.0        NaN   \n",
       "2018-08-20       55.0         13.0      27.0        107.0       38.0   \n",
       "2018-08-21      145.0         23.0       NaN        161.0       30.0   \n",
       "2018-08-22      546.0         63.0       NaN        474.0       45.0   \n",
       "2018-08-23      328.0         54.0     170.0        285.0      219.0   \n",
       "2018-08-24       72.0         20.0      79.0         97.0      288.0   \n",
       "2018-08-25        NaN         12.0      61.0        123.0       36.0   \n",
       "2018-08-26        NaN         12.0       NaN        169.0       11.0   \n",
       "2018-08-27     1953.0         11.0       NaN        254.0       10.0   \n",
       "2018-08-28      691.0         10.0      46.0        423.0       10.0   \n",
       "2018-08-29      169.0         10.0      19.0        308.0       11.0   \n",
       "2018-08-30       24.0         11.0      15.0        158.0       11.0   \n",
       "2018-08-31       61.0         12.0      24.0         48.0       10.0   \n",
       "2018-09-01        NaN         14.0      49.0         23.0       10.0   \n",
       "2018-09-02        NaN         15.0      59.0         40.0       11.0   \n",
       "2018-09-03       38.0         13.0      42.0        132.0       12.0   \n",
       "\n",
       "beachID     Gibraltar-4  Centre-5  Wards-6  Cherry-7  Woodbine-8  KewBalmy-9  \n",
       "sampleDate                                                                    \n",
       "2009-06-01         18.0      24.0     63.0      51.0        10.0        10.0  \n",
       "2009-06-02         18.0      22.0     18.0      19.0        10.0        10.0  \n",
       "2009-06-03         11.0      14.0     10.0      10.0        10.0        10.0  \n",
       "2009-06-04         11.0      11.0     10.0      10.0        10.0        10.0  \n",
       "2009-06-05         10.0      10.0     12.0      10.0        10.0        10.0  \n",
       "2009-06-06         10.0      12.0     16.0      10.0        12.0        10.0  \n",
       "2009-06-07         28.0      41.0     47.0      10.0        12.0        11.0  \n",
       "2009-06-08         33.0      43.0     39.0      10.0        10.0        12.0  \n",
       "2009-06-09         20.0      16.0     11.0      12.0        18.0        34.0  \n",
       "2009-06-10         18.0      21.0     11.0      12.0        18.0        31.0  \n",
       "2009-06-11         11.0      17.0     11.0      10.0        10.0        10.0  \n",
       "2009-06-12         10.0      11.0     12.0      11.0        13.0        14.0  \n",
       "2009-06-13         10.0      12.0     15.0      14.0        13.0        14.0  \n",
       "2009-06-14         10.0      13.0     16.0      15.0        10.0        11.0  \n",
       "2009-06-15         10.0      13.0     11.0      13.0        10.0        11.0  \n",
       "2009-06-16         10.0      15.0     10.0      11.0        10.0        12.0  \n",
       "2009-06-17         10.0      14.0     10.0      11.0        50.0        81.0  \n",
       "2009-06-18          NaN       NaN      NaN       NaN         NaN         NaN  \n",
       "2009-06-19         10.0      13.0     33.0      46.0        88.0       108.0  \n",
       "2009-06-20          NaN       NaN      NaN     206.0        61.0        93.0  \n",
       "2009-06-21          NaN       NaN      NaN       NaN         NaN         NaN  \n",
       "2009-06-22          NaN       NaN      NaN       NaN         NaN         NaN  \n",
       "2009-06-23          NaN       NaN      NaN       NaN         NaN         NaN  \n",
       "2009-06-24          NaN       NaN      NaN       NaN         NaN         NaN  \n",
       "2009-06-25          NaN       NaN      NaN       NaN         NaN         NaN  \n",
       "2009-06-26          NaN       NaN      NaN       NaN         NaN         NaN  \n",
       "2009-06-27          NaN       NaN      NaN       NaN         NaN         NaN  \n",
       "2009-06-28          NaN       NaN      NaN     351.0        31.0        40.0  \n",
       "2009-06-29          NaN       NaN      NaN       NaN         NaN         NaN  \n",
       "2009-06-30          NaN       NaN      NaN       NaN         NaN         NaN  \n",
       "...                 ...       ...      ...       ...         ...         ...  \n",
       "2018-08-05         19.0      49.0     11.0      11.0        10.0        23.0  \n",
       "2018-08-06         20.0      51.0     10.0      13.0        11.0        41.0  \n",
       "2018-08-07         23.0      71.0     17.0      14.0        13.0        32.0  \n",
       "2018-08-08         26.0     189.0    116.0      60.0        11.0        18.0  \n",
       "2018-08-09         37.0     247.0    475.0     234.0        20.0        15.0  \n",
       "2018-08-10         58.0      86.0    213.0     103.0        23.0        84.0  \n",
       "2018-08-11         43.0      24.0     30.0      33.0        12.0        82.0  \n",
       "2018-08-12         18.0      21.0     10.0      27.0        20.0        21.0  \n",
       "2018-08-13         15.0      37.0     11.0      20.0        46.0        27.0  \n",
       "2018-08-14         14.0      40.0     11.0      14.0        51.0        41.0  \n",
       "2018-08-15         13.0      41.0     10.0      14.0        20.0        53.0  \n",
       "2018-08-16         12.0      28.0     11.0      11.0        13.0        29.0  \n",
       "2018-08-17         11.0      84.0     19.0      10.0        76.0       127.0  \n",
       "2018-08-18         20.0      67.0     48.0      13.0       420.0       684.0  \n",
       "2018-08-19         20.0      12.0     28.0      15.0       116.0       100.0  \n",
       "2018-08-20         16.0      13.0     12.0      15.0        36.0        22.0  \n",
       "2018-08-21         23.0      32.0     21.0      32.0        69.0        52.0  \n",
       "2018-08-22         25.0      32.0     20.0     177.0        83.0        97.0  \n",
       "2018-08-23         61.0      16.0     20.0     131.0        52.0        60.0  \n",
       "2018-08-24         54.0      20.0     35.0      23.0        51.0        34.0  \n",
       "2018-08-25         14.0      22.0     29.0      22.0        38.0        55.0  \n",
       "2018-08-26         13.0      19.0     21.0      33.0        19.0        55.0  \n",
       "2018-08-27         26.0      23.0     37.0      30.0        15.0        16.0  \n",
       "2018-08-28         30.0      36.0     60.0      49.0        41.0        39.0  \n",
       "2018-08-29         25.0      51.0     28.0      40.0       158.0        93.0  \n",
       "2018-08-30         19.0      44.0     16.0      16.0        51.0        28.0  \n",
       "2018-08-31         20.0       NaN     13.0      15.0        36.0        42.0  \n",
       "2018-09-01         22.0       NaN     10.0      12.0        59.0        54.0  \n",
       "2018-09-02         11.0      34.0     10.0      11.0        23.0        20.0  \n",
       "2018-09-03         10.0      57.0     10.0      12.0        19.0        17.0  \n",
       "\n",
       "[929 rows x 11 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "beach_complete.apply(pd.to_numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "beach_complete.to_csv('beach_complete.csv',sep=',',na_rep=np.nan)"
   ]
  }
 ],
 "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.5.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}