make_dly_obs_table_standalone.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'matplotlib'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-61-abc021becafd>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mnumpy\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mnp\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0msqlite3\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 7\u001b[0;31m \u001b[0;32mimport\u001b[0m \u001b[0mmatplotlib\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      8\u001b[0m \u001b[0mdb_filepath\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m\"hampt_rd_data.sqlite\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mModuleNotFoundError\u001b[0m: No module named 'matplotlib'"
     ]
    }
   ],
   "source": [
    "#%matplotlib inline\n",
    "import os\n",
    "import sys\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import sqlite3\n",
    "db_filepath = \"hampt_rd_data.sqlite\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_db_table_as_df(name, sql=\"\"\"SELECT * FROM {};\"\"\", date_col=None, dbfilename=db_filepath):\n",
    "    con = sqlite3.connect(dbfilename)\n",
    "    sql = sql.format(name)\n",
    "    if name == 'datavalues':\n",
    "        date_col = 'Datetime'\n",
    "    df = pd.read_sql(sql, con, parse_dates=date_col)\n",
    "    if name == 'datavalues':\n",
    "        df = make_date_index(df, 'Datetime')\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_id(typ, data):\n",
    "    \"\"\"\n",
    "    gets either the siteid or variableid from the db\n",
    "    :param typ: String. Either \"Site\" or \"Variable\"\n",
    "    :param data: Dict. the site or variable data\n",
    "    :return: int. id of site or variable\n",
    "    \"\"\"\n",
    "    data_df = pd.DataFrame(data, index=[0])\n",
    "    code_name = '{}Code'.format(typ)\n",
    "    table_name = '{}s'.format(typ.lower())\n",
    "    id_name = '{}ID'.format(typ)\n",
    "    code = data[code_name]\n",
    "    check_by = [code_name]\n",
    "    table = get_db_table_as_df(table_name)\n",
    "    id_row = table[table[code_name] == code]\n",
    "    id_num = id_row[id_name].values[0]\n",
    "    return id_num"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_date_index(df, field, fmt=None):\n",
    "    df.loc[:, field] = pd.to_datetime(df.loc[:, field], format=fmt)\n",
    "    df.set_index(field, drop=True, inplace=True)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_table_for_variable_code(variable_code, site_id=None, start_date=None, end_date=None):\n",
    "    var_id = get_id('Variable', {'VariableCode': variable_code})\n",
    "    table_name = 'datavalues'\n",
    "    sql = \"\"\"SELECT * FROM {} WHERE VariableID={};\"\"\".format(table_name, var_id)\n",
    "    if start_date or end_date:\n",
    "        if not start_date:\n",
    "            start_date = '1900-01-01'\n",
    "        elif not end_date:\n",
    "            end_date = '2100-01-01'\n",
    "        sql = \"\"\"SELECT * FROM {} WHERE VariableID={} AND Datetime BETWEEN '{}' AND '{}';\"\"\".format(\n",
    "            table_name,\n",
    "            var_id,\n",
    "            start_date,\n",
    "            end_date\n",
    "        )\n",
    "\n",
    "    df = get_db_table_as_df(table_name, sql=sql)\n",
    "    df = df.sort_index()\n",
    "    if site_id:\n",
    "        df = df[df['SiteID'] == site_id]\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "def round_down_near_24(datetimes): # round down the times near midnight so the tide levels stay on the correct day\n",
    "    close_time_idx = datetimes.indexer_between_time('23:29', '23:59')\n",
    "    adjusted_times = datetimes[close_time_idx] - pd.Timedelta(minutes=15)\n",
    "    dt = pd.Series(datetimes)\n",
    "    dt[close_time_idx] = adjusted_times\n",
    "    dt = pd.DatetimeIndex(dt)\n",
    "    return dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "def cln_n_rnd_times(df):\n",
    "    for i in range(df.shape[1]):\n",
    "        datetimes = df.iloc[:, i]\n",
    "        times = pd.DatetimeIndex(datetimes)\n",
    "        rnd_dn = round_down_near_24(times)\n",
    "        df.iloc[:, i] = rnd_dn\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "def pivot_dv_df(df):\n",
    "    return df.pivot(columns='SiteID', values='Value')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "def rename_cols(df, var_abbrev):\n",
    "    if var_abbrev != \"\":\n",
    "        new_df = df.copy()\n",
    "        cols = df.columns.tolist()\n",
    "        new_cols = ['{}-{}'.format(var_abbrev, c) for c in cols]\n",
    "        new_df.columns = new_cols\n",
    "        return new_df\n",
    "    else:\n",
    "        return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "def filter_max_rain_time_dfs(rain_daily_df, time_df):\n",
    "    timemx_filt = pd.DataFrame(np.where(rain_daily_df>0, time_df, np.datetime64('NaT')))\n",
    "    timemx_filt.columns = time_df.columns\n",
    "    timemx_filt.index = time_df.index\n",
    "    return timemx_filt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "def tide_when_rain_max(rn_mx_time_df):\n",
    "    td_df = get_table_for_variable_code('six_min_tide')\n",
    "    try:\n",
    "        td_df = pivot_dv_df(td_df)\n",
    "    except:\n",
    "        td_df = remove_duplicates(td_df)\n",
    "        td_df = pivot_dv_df(td_df)\n",
    "    td_df = td_df.resample('15T').mean()\n",
    "    rn_mx_time_rnd = cln_n_rnd_times(rn_mx_time_df)\n",
    "    l = []\n",
    "    for c in rn_mx_time_rnd.columns:\n",
    "        times = rn_mx_time_rnd.loc[:, c]\n",
    "        tides = td_df.loc[times].resample('D').max()\n",
    "        rain_var = c.split('_')[0]\n",
    "        rain_site = c.split('-')[-1]\n",
    "        new_cols = ['{}-{}_td-{}'.format(rain_var, rain_site, col) for col in tides.columns]\n",
    "        tides.columns = new_cols\n",
    "        l.append(tides)\n",
    "    new_df = pd.concat(l, axis=1)\n",
    "    new_df.sort_index(inplace=True)\n",
    "    return new_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "def remove_duplicates(df):\n",
    "    siteids = df['SiteID'].unique()\n",
    "    df.reset_index(inplace=True)\n",
    "    #print df.shape\n",
    "    non_duplicated = list()\n",
    "    for site in siteids:\n",
    "        df_site = df[df['SiteID'] == site]\n",
    "        df_site_vals = df_site['Datetime']\n",
    "        df_no_dups = ~df_site_vals.duplicated()\n",
    "        df_no_dups_idx = df_site[df_no_dups].index\n",
    "        non_duplicated.extend(df_no_dups_idx.tolist())\n",
    "    df = df.loc[non_duplicated]\n",
    "    df.set_index('Datetime', drop=True, inplace=True)\n",
    "    #print df.shape \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "def daily_pivot_table(var_code, agg_function, abbreviation):    \n",
    "    df = get_table_for_variable_code(var_code)\n",
    "    try:\n",
    "        dfp = pivot_dv_df(df)\n",
    "    except ValueError:\n",
    "        df = remove_duplicates(df)\n",
    "        dfp = pivot_dv_df(df)\n",
    "    dfd = dfp.resample('D')\n",
    "    aggrd = dfd.agg(agg_function)\n",
    "    rnmed = rename_cols(aggrd, abbreviation)\n",
    "    return rnmed"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  Rainfall"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get rainfall data at 15 min interval\n",
    "rain_df = get_table_for_variable_code('rainfall')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Daily Rainfall"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>rd-19</th>\n",
       "      <th>rd-20</th>\n",
       "      <th>rd-1</th>\n",
       "      <th>rd-2</th>\n",
       "      <th>rd-7</th>\n",
       "      <th>rd-11</th>\n",
       "      <th>rd-12</th>\n",
       "      <th>rd-13</th>\n",
       "      <th>rd-14</th>\n",
       "      <th>rd-15</th>\n",
       "      <th>rd-16</th>\n",
       "      <th>rd-21</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Datetime</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",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2010-01-01</th>\n",
       "      <td>0.01</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0.06</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-02</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-03</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-04</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.11</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-05</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            rd-19  rd-20  rd-1  rd-2  rd-7  rd-11  rd-12  rd-13  rd-14  rd-15  \\\n",
       "Datetime                                                                        \n",
       "2010-01-01   0.01   0.01   0.0   0.0  0.05   0.03   0.06   0.02   0.01   0.01   \n",
       "2010-01-02   0.00   0.00   0.0   0.0  0.00   0.01   0.00   0.00   0.00   0.00   \n",
       "2010-01-03   0.00   0.00   0.0   0.0  0.00   0.00   0.00   0.02   0.00   0.00   \n",
       "2010-01-04   0.00   0.00   0.0   0.0  0.10   0.10   0.00   0.11   0.00   0.00   \n",
       "2010-01-05   0.00   0.00   0.0   0.0  0.00   0.00   0.00   0.00   0.12   0.00   \n",
       "\n",
       "            rd-16  rd-21  \n",
       "Datetime                  \n",
       "2010-01-01   0.02   0.03  \n",
       "2010-01-02   0.00   0.00  \n",
       "2010-01-03   0.00   0.00  \n",
       "2010-01-04   0.00   0.00  \n",
       "2010-01-05   0.00   0.11  "
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rain_daily15 = daily_pivot_table('rainfall', np.sum, '')\n",
    "rain_daily = daily_pivot_table('daily_rainfall', np.sum, '')\n",
    "rain_daily_comb_no_name = pd.concat([rain_daily, rain_daily15], axis=1)\n",
    "rain_daily_comb_named = rename_cols(rain_daily_comb_no_name, 'rd')\n",
    "rain_daily_comb_named.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>rd-19</th>\n",
       "      <th>rd-20</th>\n",
       "      <th>rd-1</th>\n",
       "      <th>rd-2</th>\n",
       "      <th>rd-7</th>\n",
       "      <th>rd-11</th>\n",
       "      <th>rd-12</th>\n",
       "      <th>rd-13</th>\n",
       "      <th>rd-14</th>\n",
       "      <th>rd-15</th>\n",
       "      <th>rd-16</th>\n",
       "      <th>rd-21</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Datetime</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",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2016-12-28</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-29</th>\n",
       "      <td>0.19</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.18</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.19</td>\n",
       "      <td>0.16</td>\n",
       "      <td>0.26</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-30</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-31</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-01</th>\n",
       "      <td>0.02</td>\n",
       "      <td>0.01</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",
       "</div>"
      ],
      "text/plain": [
       "            rd-19  rd-20  rd-1  rd-2  rd-7  rd-11  rd-12  rd-13  rd-14  rd-15  \\\n",
       "Datetime                                                                        \n",
       "2016-12-28   0.00   0.00  0.00   0.0   0.0    0.0    0.0   0.00   0.00   0.00   \n",
       "2016-12-29   0.19   0.20  0.18   0.0   0.0    0.2    0.0   0.19   0.16   0.26   \n",
       "2016-12-30   0.00   0.00  0.00   0.0   0.0    0.0    0.0   0.00   0.00   0.00   \n",
       "2016-12-31   0.00   0.00  0.00   0.0   0.0    0.0    0.0   0.00   0.00   0.00   \n",
       "2017-01-01   0.02   0.01   NaN   NaN   NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "\n",
       "            rd-16  rd-21  \n",
       "Datetime                  \n",
       "2016-12-28    0.0   0.00  \n",
       "2016-12-29    0.3   0.24  \n",
       "2016-12-30    0.0   0.00  \n",
       "2016-12-31    0.0   0.00  \n",
       "2017-01-01    NaN    NaN  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rain_daily_comb_named.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Hourly Rainfall"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>rhrmx-1</th>\n",
       "      <th>rhrmx-2</th>\n",
       "      <th>rhrmx-7</th>\n",
       "      <th>rhrmx-11</th>\n",
       "      <th>rhrmx-12</th>\n",
       "      <th>rhrmx-13</th>\n",
       "      <th>rhrmx-14</th>\n",
       "      <th>rhrmx-15</th>\n",
       "      <th>rhrmx-16</th>\n",
       "      <th>rhrmx-21</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Datetime</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>2010-01-01</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-02</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-03</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-04</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.11</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-05</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            rhrmx-1  rhrmx-2  rhrmx-7  rhrmx-11  rhrmx-12  rhrmx-13  rhrmx-14  \\\n",
       "Datetime                                                                        \n",
       "2010-01-01      NaN      NaN     0.04      0.01      0.04      0.01      0.01   \n",
       "2010-01-02      NaN      NaN     0.00      0.01      0.00      0.00      0.00   \n",
       "2010-01-03      NaN      NaN     0.00      0.00      0.00      0.02      0.00   \n",
       "2010-01-04      NaN      NaN     0.10      0.10      0.00      0.11      0.00   \n",
       "2010-01-05      NaN      NaN     0.00      0.00      0.00      0.00      0.12   \n",
       "\n",
       "            rhrmx-15  rhrmx-16  rhrmx-21  \n",
       "Datetime                                  \n",
       "2010-01-01      0.01      0.01      0.02  \n",
       "2010-01-02      0.00      0.00      0.00  \n",
       "2010-01-03      0.00      0.00      0.00  \n",
       "2010-01-04      0.00      0.00      0.00  \n",
       "2010-01-05      0.00      0.00      0.11  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rain15 = pivot_dv_df(rain_df)\n",
    "rain_hourly_totals = rain15.rolling(window='H').sum()\n",
    "rhr_mx = rain_hourly_totals.resample('D').max()\n",
    "rhr_mx = rename_cols(rhr_mx, 'rhrmx')\n",
    "rhr_mx.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/erica/miniconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: FutureWarning: pd.TimeGrouper is deprecated and will be removed; Please use pd.Grouper(freq=...)\n",
      "  \"\"\"Entry point for launching an IPython kernel.\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>rhr_mxtime-1</th>\n",
       "      <th>rhr_mxtime-2</th>\n",
       "      <th>rhr_mxtime-7</th>\n",
       "      <th>rhr_mxtime-11</th>\n",
       "      <th>rhr_mxtime-12</th>\n",
       "      <th>rhr_mxtime-13</th>\n",
       "      <th>rhr_mxtime-14</th>\n",
       "      <th>rhr_mxtime-15</th>\n",
       "      <th>rhr_mxtime-16</th>\n",
       "      <th>rhr_mxtime-21</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Datetime</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>2010-01-01</th>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2010-01-01 22:45:00</td>\n",
       "      <td>2010-01-01 00:15:00</td>\n",
       "      <td>2010-01-01 23:00:00</td>\n",
       "      <td>2010-01-01 00:45:00</td>\n",
       "      <td>2010-01-01 00:15:00</td>\n",
       "      <td>2010-01-01 05:00:00</td>\n",
       "      <td>2010-01-01 00:45:00</td>\n",
       "      <td>2010-01-01 23:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-02</th>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2010-01-02 13:15:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-03</th>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2010-01-03 07:00:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-04</th>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2010-01-04 13:30:00</td>\n",
       "      <td>2010-01-04 13:00:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2010-01-04 12:45:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-05</th>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2010-01-05 10:15:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2010-01-05 10:30:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           rhr_mxtime-1 rhr_mxtime-2        rhr_mxtime-7       rhr_mxtime-11  \\\n",
       "Datetime                                                                       \n",
       "2010-01-01          NaT          NaT 2010-01-01 22:45:00 2010-01-01 00:15:00   \n",
       "2010-01-02          NaT          NaT                 NaT 2010-01-02 13:15:00   \n",
       "2010-01-03          NaT          NaT                 NaT                 NaT   \n",
       "2010-01-04          NaT          NaT 2010-01-04 13:30:00 2010-01-04 13:00:00   \n",
       "2010-01-05          NaT          NaT                 NaT                 NaT   \n",
       "\n",
       "                 rhr_mxtime-12       rhr_mxtime-13       rhr_mxtime-14  \\\n",
       "Datetime                                                                 \n",
       "2010-01-01 2010-01-01 23:00:00 2010-01-01 00:45:00 2010-01-01 00:15:00   \n",
       "2010-01-02                 NaT                 NaT                 NaT   \n",
       "2010-01-03                 NaT 2010-01-03 07:00:00                 NaT   \n",
       "2010-01-04                 NaT 2010-01-04 12:45:00                 NaT   \n",
       "2010-01-05                 NaT                 NaT 2010-01-05 10:15:00   \n",
       "\n",
       "                 rhr_mxtime-15       rhr_mxtime-16       rhr_mxtime-21  \n",
       "Datetime                                                                \n",
       "2010-01-01 2010-01-01 05:00:00 2010-01-01 00:45:00 2010-01-01 23:00:00  \n",
       "2010-01-02                 NaT                 NaT                 NaT  \n",
       "2010-01-03                 NaT                 NaT                 NaT  \n",
       "2010-01-04                 NaT                 NaT                 NaT  \n",
       "2010-01-05                 NaT                 NaT 2010-01-05 10:30:00  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rhr_timemx = rain_hourly_totals.groupby(pd.TimeGrouper('D')).idxmax()\n",
    "rhr_timemx = rename_cols(rhr_timemx, 'rhr_mxtime')\n",
    "rhr_timemx = filter_max_rain_time_dfs(rain_daily15, rhr_timemx)\n",
    "rhr_timemx.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15-min max rainfall"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>r15mx-1</th>\n",
       "      <th>r15mx-2</th>\n",
       "      <th>r15mx-7</th>\n",
       "      <th>r15mx-11</th>\n",
       "      <th>r15mx-12</th>\n",
       "      <th>r15mx-13</th>\n",
       "      <th>r15mx-14</th>\n",
       "      <th>r15mx-15</th>\n",
       "      <th>r15mx-16</th>\n",
       "      <th>r15mx-21</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Datetime</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>2010-01-01</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-02</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-03</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-04</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.11</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-05</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            r15mx-1  r15mx-2  r15mx-7  r15mx-11  r15mx-12  r15mx-13  r15mx-14  \\\n",
       "Datetime                                                                        \n",
       "2010-01-01      NaN      NaN     0.02      0.01      0.02      0.01      0.01   \n",
       "2010-01-02      NaN      NaN     0.00      0.01      0.00      0.00      0.00   \n",
       "2010-01-03      NaN      NaN     0.00      0.00      0.00      0.02      0.00   \n",
       "2010-01-04      NaN      NaN     0.10      0.10      0.00      0.11      0.00   \n",
       "2010-01-05      NaN      NaN     0.00      0.00      0.00      0.00      0.12   \n",
       "\n",
       "            r15mx-15  r15mx-16  r15mx-21  \n",
       "Datetime                                  \n",
       "2010-01-01      0.01      0.01      0.02  \n",
       "2010-01-02      0.00      0.00      0.00  \n",
       "2010-01-03      0.00      0.00      0.00  \n",
       "2010-01-04      0.00      0.00      0.00  \n",
       "2010-01-05      0.00      0.00      0.11  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r15_mx = rain15.resample('D').max()\n",
    "r15_mx = rename_cols(r15_mx, 'r15mx')\n",
    "r15_mx.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/erica/miniconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: FutureWarning: pd.TimeGrouper is deprecated and will be removed; Please use pd.Grouper(freq=...)\n",
      "  \"\"\"Entry point for launching an IPython kernel.\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>r15_mxtime-1</th>\n",
       "      <th>r15_mxtime-2</th>\n",
       "      <th>r15_mxtime-7</th>\n",
       "      <th>r15_mxtime-11</th>\n",
       "      <th>r15_mxtime-12</th>\n",
       "      <th>r15_mxtime-13</th>\n",
       "      <th>r15_mxtime-14</th>\n",
       "      <th>r15_mxtime-15</th>\n",
       "      <th>r15_mxtime-16</th>\n",
       "      <th>r15_mxtime-21</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Datetime</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>2010-01-01</th>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2010-01-01 22:30:00</td>\n",
       "      <td>2010-01-01 00:15:00</td>\n",
       "      <td>2010-01-01 22:45:00</td>\n",
       "      <td>2010-01-01 00:45:00</td>\n",
       "      <td>2010-01-01 00:15:00</td>\n",
       "      <td>2010-01-01 05:00:00</td>\n",
       "      <td>2010-01-01 00:45:00</td>\n",
       "      <td>2010-01-01 23:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-02</th>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2010-01-02 13:15:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-03</th>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2010-01-03 07:00:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-04</th>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2010-01-04 13:30:00</td>\n",
       "      <td>2010-01-04 13:00:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2010-01-04 12:45:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-05</th>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2010-01-05 10:15:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2010-01-05 10:30:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           r15_mxtime-1 r15_mxtime-2        r15_mxtime-7       r15_mxtime-11  \\\n",
       "Datetime                                                                       \n",
       "2010-01-01          NaT          NaT 2010-01-01 22:30:00 2010-01-01 00:15:00   \n",
       "2010-01-02          NaT          NaT                 NaT 2010-01-02 13:15:00   \n",
       "2010-01-03          NaT          NaT                 NaT                 NaT   \n",
       "2010-01-04          NaT          NaT 2010-01-04 13:30:00 2010-01-04 13:00:00   \n",
       "2010-01-05          NaT          NaT                 NaT                 NaT   \n",
       "\n",
       "                 r15_mxtime-12       r15_mxtime-13       r15_mxtime-14  \\\n",
       "Datetime                                                                 \n",
       "2010-01-01 2010-01-01 22:45:00 2010-01-01 00:45:00 2010-01-01 00:15:00   \n",
       "2010-01-02                 NaT                 NaT                 NaT   \n",
       "2010-01-03                 NaT 2010-01-03 07:00:00                 NaT   \n",
       "2010-01-04                 NaT 2010-01-04 12:45:00                 NaT   \n",
       "2010-01-05                 NaT                 NaT 2010-01-05 10:15:00   \n",
       "\n",
       "                 r15_mxtime-15       r15_mxtime-16       r15_mxtime-21  \n",
       "Datetime                                                                \n",
       "2010-01-01 2010-01-01 05:00:00 2010-01-01 00:45:00 2010-01-01 23:00:00  \n",
       "2010-01-02                 NaT                 NaT                 NaT  \n",
       "2010-01-03                 NaT                 NaT                 NaT  \n",
       "2010-01-04                 NaT                 NaT                 NaT  \n",
       "2010-01-05                 NaT                 NaT 2010-01-05 10:30:00  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r15_timemx = rain15.groupby(pd.TimeGrouper('D')).idxmax()\n",
    "r15_timemx = rename_cols(r15_timemx, 'r15_mxtime')\n",
    "r15_timemx = filter_max_rain_time_dfs(rain_daily15, r15_timemx)\n",
    "r15_timemx.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Rain prev 3 days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>r3d-19</th>\n",
       "      <th>r3d-20</th>\n",
       "      <th>r3d-1</th>\n",
       "      <th>r3d-2</th>\n",
       "      <th>r3d-7</th>\n",
       "      <th>r3d-11</th>\n",
       "      <th>r3d-12</th>\n",
       "      <th>r3d-13</th>\n",
       "      <th>r3d-14</th>\n",
       "      <th>r3d-15</th>\n",
       "      <th>r3d-16</th>\n",
       "      <th>r3d-21</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Datetime</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",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2010-01-01</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-02</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-03</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-04</th>\n",
       "      <td>0.01</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.06</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-05</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.11</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            r3d-19  r3d-20  r3d-1  r3d-2  r3d-7  r3d-11  r3d-12  r3d-13  \\\n",
       "Datetime                                                                  \n",
       "2010-01-01     NaN     NaN    NaN    NaN    NaN     NaN     NaN     NaN   \n",
       "2010-01-02     NaN     NaN    NaN    NaN    NaN     NaN     NaN     NaN   \n",
       "2010-01-03     NaN     NaN    NaN    NaN    NaN     NaN     NaN     NaN   \n",
       "2010-01-04    0.01    0.01    0.0    0.0   0.05    0.04    0.06    0.04   \n",
       "2010-01-05    0.00    0.00    0.0    0.0   0.10    0.11    0.00    0.13   \n",
       "\n",
       "            r3d-14  r3d-15  r3d-16  r3d-21  \n",
       "Datetime                                    \n",
       "2010-01-01     NaN     NaN     NaN     NaN  \n",
       "2010-01-02     NaN     NaN     NaN     NaN  \n",
       "2010-01-03     NaN     NaN     NaN     NaN  \n",
       "2010-01-04    0.01    0.01    0.02    0.03  \n",
       "2010-01-05    0.00    0.00    0.00    0.00  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rain_prev_3_days = rain_daily_comb_no_name.shift(1).rolling(window=3).sum()\n",
    "rain_prev_3_days = rename_cols(rain_prev_3_days, 'r3d')\n",
    "rain_prev_3_days.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series([], Freq: D, Name: rd-14, dtype: float64)"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rain_daily_comb_named['rd-14'][rain_daily_comb_named['rd-14']<0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>SiteID</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>7</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "      <th>15</th>\n",
       "      <th>16</th>\n",
       "      <th>21</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Datetime</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>2014-06-24 00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 00:01:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:02:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:03:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:04:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:05:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:06:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:07:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:08:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:09:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:10:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:11:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:12:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:13:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:14:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:15:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 00:16:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:17:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:18:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:19:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:20:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:21:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:22:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:23:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:24:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:25:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:26:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:27:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:28:00</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 00:29:00</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>0.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-24 16:30:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 16:45:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 17:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 17:15:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 17:30:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 17:45:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 18:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 18:15:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 18:30:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 18:45:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 19:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 19:15:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 19:30:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 19:45:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 20:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 20:15:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 20:30:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 20:45:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 21:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 21:15:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 21:30:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 21:45:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 22:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 22:15:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 22:30:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 22:45:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 23:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 23:15:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 23:30:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>2014-06-24 23:45:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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>531 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "SiteID                1   2    7    11   12   13   14   15   16   21\n",
       "Datetime                                                            \n",
       "2014-06-24 00:00:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 00:01:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:02:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:03:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:04:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:05:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:06:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:07:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:08:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:09:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:10:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:11:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:12:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:13:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:14:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:15:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 00:16:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:17:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:18:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:19:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:20:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:21:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:22:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:23:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:24:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:25:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:26:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:27:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:28:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "2014-06-24 00:29:00  NaN NaN  NaN  NaN  NaN  NaN  0.0  NaN  NaN  NaN\n",
       "...                  ...  ..  ...  ...  ...  ...  ...  ...  ...  ...\n",
       "2014-06-24 16:30:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 16:45:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 17:00:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 17:15:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 17:30:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 17:45:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 18:00:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 18:15:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 18:30:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 18:45:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 19:00:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 19:15:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 19:30:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 19:45:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 20:00:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 20:15:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 20:30:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 20:45:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 21:00:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 21:15:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 21:30:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 21:45:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 22:00:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 22:15:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 22:30:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 22:45:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 23:00:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 23:15:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 23:30:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "2014-06-24 23:45:00  0.0 NaN  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0\n",
       "\n",
       "[531 rows x 10 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rain15.loc['2014-06-24']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "ename": "ImportError",
     "evalue": "matplotlib is required for plotting.",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mImportError\u001b[0m                               Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-60-4e3ddb34dde4>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mrain_prev_3_days\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mplot\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbox\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/pandas/plotting/_core.py\u001b[0m in \u001b[0;36mbox\u001b[0;34m(self, by, **kwds)\u001b[0m\n\u001b[1;32m   3219\u001b[0m             \u001b[0;34m>>\u001b[0m\u001b[0;34m>\u001b[0m \u001b[0max\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mplot\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbox\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3220\u001b[0m         \"\"\"\n\u001b[0;32m-> 3221\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkind\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'box'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mby\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mby\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwds\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3222\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3223\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mhist\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mby\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mbins\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m10\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwds\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/pandas/plotting/_core.py\u001b[0m in \u001b[0;36m__call__\u001b[0;34m(self, x, y, kind, ax, subplots, sharex, sharey, layout, figsize, use_index, title, grid, legend, style, logx, logy, loglog, xticks, yticks, xlim, ylim, rot, fontsize, colormap, table, yerr, xerr, secondary_y, sort_columns, **kwds)\u001b[0m\n\u001b[1;32m   2940\u001b[0m                           \u001b[0mfontsize\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mfontsize\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcolormap\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcolormap\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtable\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mtable\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2941\u001b[0m                           \u001b[0myerr\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0myerr\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mxerr\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mxerr\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msecondary_y\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0msecondary_y\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2942\u001b[0;31m                           sort_columns=sort_columns, **kwds)\n\u001b[0m\u001b[1;32m   2943\u001b[0m     \u001b[0m__call__\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__doc__\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mplot_frame\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__doc__\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2944\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/pandas/plotting/_core.py\u001b[0m in \u001b[0;36mplot_frame\u001b[0;34m(data, x, y, kind, ax, subplots, sharex, sharey, layout, figsize, use_index, title, grid, legend, style, logx, logy, loglog, xticks, yticks, xlim, ylim, rot, fontsize, colormap, table, yerr, xerr, secondary_y, sort_columns, **kwds)\u001b[0m\n\u001b[1;32m   1971\u001b[0m                  \u001b[0myerr\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0myerr\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mxerr\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mxerr\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1972\u001b[0m                  \u001b[0msecondary_y\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0msecondary_y\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msort_columns\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0msort_columns\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1973\u001b[0;31m                  **kwds)\n\u001b[0m\u001b[1;32m   1974\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1975\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/pandas/plotting/_core.py\u001b[0m in \u001b[0;36m_plot\u001b[0;34m(data, x, y, subplots, ax, kind, **kwds)\u001b[0m\n\u001b[1;32m   1797\u001b[0m                     \u001b[0mdata\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumns\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mlabel_name\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1798\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1799\u001b[0;31m         \u001b[0mplot_obj\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mklass\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msubplots\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0msubplots\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0max\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0max\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkind\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mkind\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwds\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1800\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1801\u001b[0m     \u001b[0mplot_obj\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgenerate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/pandas/plotting/_core.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, data, return_type, **kwargs)\u001b[0m\n\u001b[1;32m   1570\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1571\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreturn_type\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mreturn_type\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1572\u001b[0;31m         \u001b[0mMPLPlot\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__init__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1573\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1574\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_args_adjust\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/pandas/plotting/_core.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, data, kind, by, subplots, sharex, sharey, use_index, figsize, grid, legend, rot, ax, fig, title, xlim, ylim, xticks, yticks, sort_columns, fontsize, secondary_y, colormap, table, layout, **kwds)\u001b[0m\n\u001b[1;32m     96\u001b[0m                  table=False, layout=None, **kwds):\n\u001b[1;32m     97\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 98\u001b[0;31m         \u001b[0m_raise_if_no_mpl\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     99\u001b[0m         \u001b[0m_converter\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_WARN\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mFalse\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    100\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdata\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/pandas/plotting/_core.py\u001b[0m in \u001b[0;36m_raise_if_no_mpl\u001b[0;34m()\u001b[0m\n\u001b[1;32m     46\u001b[0m     \u001b[0;31m# TODO(mpl_converter): remove once converter is explicit\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     47\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0m_HAS_MPL\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 48\u001b[0;31m         \u001b[0;32mraise\u001b[0m \u001b[0mImportError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"matplotlib is required for plotting.\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     49\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     50\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mImportError\u001b[0m: matplotlib is required for plotting."
     ]
    }
   ],
   "source": [
    "#rain_prev_3_days.plot.box()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  Groundwater"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "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>gw_av-4</th>\n",
       "      <th>gw_av-5</th>\n",
       "      <th>gw_av-6</th>\n",
       "      <th>gw_av-8</th>\n",
       "      <th>gw_av-9</th>\n",
       "      <th>gw_av-10</th>\n",
       "      <th>gw_av-11</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Datetime</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>2010-01-01</th>\n",
       "      <td>0.181510</td>\n",
       "      <td>3.924140</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.050129</td>\n",
       "      <td>2.858511</td>\n",
       "      <td>3.167374</td>\n",
       "      <td>3.509806</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-02</th>\n",
       "      <td>0.051690</td>\n",
       "      <td>3.548121</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.990354</td>\n",
       "      <td>2.745979</td>\n",
       "      <td>3.238457</td>\n",
       "      <td>3.637924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-03</th>\n",
       "      <td>-0.075826</td>\n",
       "      <td>3.103997</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.910525</td>\n",
       "      <td>2.650683</td>\n",
       "      <td>3.127058</td>\n",
       "      <td>3.511233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-04</th>\n",
       "      <td>-0.186996</td>\n",
       "      <td>2.882649</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.770411</td>\n",
       "      <td>2.579872</td>\n",
       "      <td>2.990989</td>\n",
       "      <td>3.348661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-05</th>\n",
       "      <td>-0.251922</td>\n",
       "      <td>2.772379</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.648158</td>\n",
       "      <td>2.538736</td>\n",
       "      <td>2.873044</td>\n",
       "      <td>3.232544</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             gw_av-4   gw_av-5  gw_av-6   gw_av-8   gw_av-9  gw_av-10  \\\n",
       "Datetime                                                                \n",
       "2010-01-01  0.181510  3.924140      NaN  6.050129  2.858511  3.167374   \n",
       "2010-01-02  0.051690  3.548121      NaN  5.990354  2.745979  3.238457   \n",
       "2010-01-03 -0.075826  3.103997      NaN  5.910525  2.650683  3.127058   \n",
       "2010-01-04 -0.186996  2.882649      NaN  5.770411  2.579872  2.990989   \n",
       "2010-01-05 -0.251922  2.772379      NaN  5.648158  2.538736  2.873044   \n",
       "\n",
       "            gw_av-11  \n",
       "Datetime              \n",
       "2010-01-01  3.509806  \n",
       "2010-01-02  3.637924  \n",
       "2010-01-03  3.511233  \n",
       "2010-01-04  3.348661  \n",
       "2010-01-05  3.232544  "
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gw_df = daily_pivot_table('shallow_well_depth', np.mean, 'gw_av')\n",
    "gw_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  Tide"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Average daily tide"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>td_av-17</th>\n",
       "      <th>td_av-18</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Datetime</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2010-01-01</th>\n",
       "      <td>0.675800</td>\n",
       "      <td>0.679987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-02</th>\n",
       "      <td>0.604608</td>\n",
       "      <td>0.707200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-03</th>\n",
       "      <td>-0.608692</td>\n",
       "      <td>-0.579467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-04</th>\n",
       "      <td>-0.515733</td>\n",
       "      <td>-0.522700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-05</th>\n",
       "      <td>0.020037</td>\n",
       "      <td>0.017379</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            td_av-17  td_av-18\n",
       "Datetime                      \n",
       "2010-01-01  0.675800  0.679987\n",
       "2010-01-02  0.604608  0.707200\n",
       "2010-01-03 -0.608692 -0.579467\n",
       "2010-01-04 -0.515733 -0.522700\n",
       "2010-01-05  0.020037  0.017379"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tide_df = daily_pivot_table('six_min_tide', np.mean, 'td_av')\n",
    "tide_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  Tide when rain is at max"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/erica/miniconda3/lib/python3.7/site-packages/ipykernel_launcher.py:13: FutureWarning: \n",
      "Passing list-likes to .loc or [] with any missing label will raise\n",
      "KeyError in the future, you can use .reindex() as an alternative.\n",
      "\n",
      "See the documentation here:\n",
      "https://pandas.pydata.org/pandas-docs/stable/indexing.html#deprecate-loc-reindex-listlike\n",
      "  del sys.path[0]\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>r15-1_td-17</th>\n",
       "      <th>r15-1_td-18</th>\n",
       "      <th>r15-2_td-17</th>\n",
       "      <th>r15-2_td-18</th>\n",
       "      <th>r15-7_td-17</th>\n",
       "      <th>r15-7_td-18</th>\n",
       "      <th>r15-11_td-17</th>\n",
       "      <th>r15-11_td-18</th>\n",
       "      <th>r15-12_td-17</th>\n",
       "      <th>r15-12_td-18</th>\n",
       "      <th>r15-13_td-17</th>\n",
       "      <th>r15-13_td-18</th>\n",
       "      <th>r15-14_td-17</th>\n",
       "      <th>r15-14_td-18</th>\n",
       "      <th>r15-15_td-17</th>\n",
       "      <th>r15-15_td-18</th>\n",
       "      <th>r15-16_td-17</th>\n",
       "      <th>r15-16_td-18</th>\n",
       "      <th>r15-21_td-17</th>\n",
       "      <th>r15-21_td-18</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Datetime</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",
       "      <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>2010-01-01</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.814333</td>\n",
       "      <td>2.148</td>\n",
       "      <td>-0.246</td>\n",
       "      <td>-0.092</td>\n",
       "      <td>1.8375</td>\n",
       "      <td>2.0505</td>\n",
       "      <td>-0.567500</td>\n",
       "      <td>-0.503500</td>\n",
       "      <td>-0.2460</td>\n",
       "      <td>-0.092</td>\n",
       "      <td>-0.049333</td>\n",
       "      <td>-0.431</td>\n",
       "      <td>-0.5675</td>\n",
       "      <td>-0.5035</td>\n",
       "      <td>1.836333</td>\n",
       "      <td>1.962000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-02</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>0.912</td>\n",
       "      <td>1.273</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-03</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>-0.869333</td>\n",
       "      <td>-1.160333</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>2010-01-04</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.729333</td>\n",
       "      <td>0.919</td>\n",
       "      <td>0.901</td>\n",
       "      <td>1.135</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.968000</td>\n",
       "      <td>1.240500</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>2010-01-05</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",
       "      <td>NaN</td>\n",
       "      <td>0.6005</td>\n",
       "      <td>0.530</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.772000</td>\n",
       "      <td>0.740333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            r15-1_td-17  r15-1_td-18  r15-2_td-17  r15-2_td-18  r15-7_td-17  \\\n",
       "Datetime                                                                      \n",
       "2010-01-01          NaN          NaN          NaN          NaN     1.814333   \n",
       "2010-01-02          NaN          NaN          NaN          NaN          NaN   \n",
       "2010-01-03          NaN          NaN          NaN          NaN          NaN   \n",
       "2010-01-04          NaN          NaN          NaN          NaN     0.729333   \n",
       "2010-01-05          NaN          NaN          NaN          NaN          NaN   \n",
       "\n",
       "            r15-7_td-18  r15-11_td-17  r15-11_td-18  r15-12_td-17  \\\n",
       "Datetime                                                            \n",
       "2010-01-01        2.148        -0.246        -0.092        1.8375   \n",
       "2010-01-02          NaN         0.912         1.273           NaN   \n",
       "2010-01-03          NaN           NaN           NaN           NaN   \n",
       "2010-01-04        0.919         0.901         1.135           NaN   \n",
       "2010-01-05          NaN           NaN           NaN           NaN   \n",
       "\n",
       "            r15-12_td-18  r15-13_td-17  r15-13_td-18  r15-14_td-17  \\\n",
       "Datetime                                                             \n",
       "2010-01-01        2.0505     -0.567500     -0.503500       -0.2460   \n",
       "2010-01-02           NaN           NaN           NaN           NaN   \n",
       "2010-01-03           NaN     -0.869333     -1.160333           NaN   \n",
       "2010-01-04           NaN      0.968000      1.240500           NaN   \n",
       "2010-01-05           NaN           NaN           NaN        0.6005   \n",
       "\n",
       "            r15-14_td-18  r15-15_td-17  r15-15_td-18  r15-16_td-17  \\\n",
       "Datetime                                                             \n",
       "2010-01-01        -0.092     -0.049333        -0.431       -0.5675   \n",
       "2010-01-02           NaN           NaN           NaN           NaN   \n",
       "2010-01-03           NaN           NaN           NaN           NaN   \n",
       "2010-01-04           NaN           NaN           NaN           NaN   \n",
       "2010-01-05         0.530           NaN           NaN           NaN   \n",
       "\n",
       "            r15-16_td-18  r15-21_td-17  r15-21_td-18  \n",
       "Datetime                                              \n",
       "2010-01-01       -0.5035      1.836333      1.962000  \n",
       "2010-01-02           NaN           NaN           NaN  \n",
       "2010-01-03           NaN           NaN           NaN  \n",
       "2010-01-04           NaN           NaN           NaN  \n",
       "2010-01-05           NaN      0.772000      0.740333  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "td_r15mx = tide_when_rain_max(r15_timemx)\n",
    "td_r15mx.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/erica/miniconda3/lib/python3.7/site-packages/ipykernel_launcher.py:13: FutureWarning: \n",
      "Passing list-likes to .loc or [] with any missing label will raise\n",
      "KeyError in the future, you can use .reindex() as an alternative.\n",
      "\n",
      "See the documentation here:\n",
      "https://pandas.pydata.org/pandas-docs/stable/indexing.html#deprecate-loc-reindex-listlike\n",
      "  del sys.path[0]\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>rhr-1_td-17</th>\n",
       "      <th>rhr-1_td-18</th>\n",
       "      <th>rhr-2_td-17</th>\n",
       "      <th>rhr-2_td-18</th>\n",
       "      <th>rhr-7_td-17</th>\n",
       "      <th>rhr-7_td-18</th>\n",
       "      <th>rhr-11_td-17</th>\n",
       "      <th>rhr-11_td-18</th>\n",
       "      <th>rhr-12_td-17</th>\n",
       "      <th>rhr-12_td-18</th>\n",
       "      <th>rhr-13_td-17</th>\n",
       "      <th>rhr-13_td-18</th>\n",
       "      <th>rhr-14_td-17</th>\n",
       "      <th>rhr-14_td-18</th>\n",
       "      <th>rhr-15_td-17</th>\n",
       "      <th>rhr-15_td-18</th>\n",
       "      <th>rhr-16_td-17</th>\n",
       "      <th>rhr-16_td-18</th>\n",
       "      <th>rhr-21_td-17</th>\n",
       "      <th>rhr-21_td-18</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Datetime</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",
       "      <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>2010-01-01</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.837500</td>\n",
       "      <td>2.0505</td>\n",
       "      <td>-0.246</td>\n",
       "      <td>-0.092</td>\n",
       "      <td>1.836333</td>\n",
       "      <td>1.962</td>\n",
       "      <td>-0.567500</td>\n",
       "      <td>-0.503500</td>\n",
       "      <td>-0.2460</td>\n",
       "      <td>-0.092</td>\n",
       "      <td>-0.049333</td>\n",
       "      <td>-0.431</td>\n",
       "      <td>-0.5675</td>\n",
       "      <td>-0.5035</td>\n",
       "      <td>1.836333</td>\n",
       "      <td>1.962000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-02</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>0.912</td>\n",
       "      <td>1.273</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-03</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>-0.869333</td>\n",
       "      <td>-1.160333</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>2010-01-04</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.729333</td>\n",
       "      <td>0.9190</td>\n",
       "      <td>0.901</td>\n",
       "      <td>1.135</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.968000</td>\n",
       "      <td>1.240500</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>2010-01-05</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",
       "      <td>NaN</td>\n",
       "      <td>0.6005</td>\n",
       "      <td>0.530</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.772000</td>\n",
       "      <td>0.740333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            rhr-1_td-17  rhr-1_td-18  rhr-2_td-17  rhr-2_td-18  rhr-7_td-17  \\\n",
       "Datetime                                                                      \n",
       "2010-01-01          NaN          NaN          NaN          NaN     1.837500   \n",
       "2010-01-02          NaN          NaN          NaN          NaN          NaN   \n",
       "2010-01-03          NaN          NaN          NaN          NaN          NaN   \n",
       "2010-01-04          NaN          NaN          NaN          NaN     0.729333   \n",
       "2010-01-05          NaN          NaN          NaN          NaN          NaN   \n",
       "\n",
       "            rhr-7_td-18  rhr-11_td-17  rhr-11_td-18  rhr-12_td-17  \\\n",
       "Datetime                                                            \n",
       "2010-01-01       2.0505        -0.246        -0.092      1.836333   \n",
       "2010-01-02          NaN         0.912         1.273           NaN   \n",
       "2010-01-03          NaN           NaN           NaN           NaN   \n",
       "2010-01-04       0.9190         0.901         1.135           NaN   \n",
       "2010-01-05          NaN           NaN           NaN           NaN   \n",
       "\n",
       "            rhr-12_td-18  rhr-13_td-17  rhr-13_td-18  rhr-14_td-17  \\\n",
       "Datetime                                                             \n",
       "2010-01-01         1.962     -0.567500     -0.503500       -0.2460   \n",
       "2010-01-02           NaN           NaN           NaN           NaN   \n",
       "2010-01-03           NaN     -0.869333     -1.160333           NaN   \n",
       "2010-01-04           NaN      0.968000      1.240500           NaN   \n",
       "2010-01-05           NaN           NaN           NaN        0.6005   \n",
       "\n",
       "            rhr-14_td-18  rhr-15_td-17  rhr-15_td-18  rhr-16_td-17  \\\n",
       "Datetime                                                             \n",
       "2010-01-01        -0.092     -0.049333        -0.431       -0.5675   \n",
       "2010-01-02           NaN           NaN           NaN           NaN   \n",
       "2010-01-03           NaN           NaN           NaN           NaN   \n",
       "2010-01-04           NaN           NaN           NaN           NaN   \n",
       "2010-01-05         0.530           NaN           NaN           NaN   \n",
       "\n",
       "            rhr-16_td-18  rhr-21_td-17  rhr-21_td-18  \n",
       "Datetime                                              \n",
       "2010-01-01       -0.5035      1.836333      1.962000  \n",
       "2010-01-02           NaN           NaN           NaN  \n",
       "2010-01-03           NaN           NaN           NaN  \n",
       "2010-01-04           NaN           NaN           NaN  \n",
       "2010-01-05           NaN      0.772000      0.740333  "
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "td_rhrmx = tide_when_rain_max(rhr_timemx)\n",
    "td_rhrmx.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## HI/LOs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "hilos = []\n",
    "for v in ['high_tide', 'high_high_tide', 'low_tide', 'low_low_tide']:\n",
    "    hilos.append(daily_pivot_table(v, np.mean, \"\".join(w[0] for w in v.split('_'))))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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>ht-17</th>\n",
       "      <th>ht-18</th>\n",
       "      <th>hht-17</th>\n",
       "      <th>hht-18</th>\n",
       "      <th>lt-17</th>\n",
       "      <th>lt-18</th>\n",
       "      <th>llt-17</th>\n",
       "      <th>llt-18</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Datetime</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>2010-01-01</th>\n",
       "      <td>1.952</td>\n",
       "      <td>2.241</td>\n",
       "      <td>2.493</td>\n",
       "      <td>2.7490</td>\n",
       "      <td>-0.797</td>\n",
       "      <td>-1.043</td>\n",
       "      <td>-1.198</td>\n",
       "      <td>-1.430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-02</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.939</td>\n",
       "      <td>2.2395</td>\n",
       "      <td>-0.604</td>\n",
       "      <td>-0.840</td>\n",
       "      <td>-1.056</td>\n",
       "      <td>-1.132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-03</th>\n",
       "      <td>0.830</td>\n",
       "      <td>1.096</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.841</td>\n",
       "      <td>-2.024</td>\n",
       "      <td>-2.516</td>\n",
       "      <td>-2.694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-04</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.102</td>\n",
       "      <td>1.3190</td>\n",
       "      <td>-1.693</td>\n",
       "      <td>-1.949</td>\n",
       "      <td>-2.349</td>\n",
       "      <td>-2.566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-05</th>\n",
       "      <td>1.355</td>\n",
       "      <td>1.654</td>\n",
       "      <td>1.480</td>\n",
       "      <td>1.7680</td>\n",
       "      <td>-1.253</td>\n",
       "      <td>-1.453</td>\n",
       "      <td>-1.306</td>\n",
       "      <td>-1.522</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            ht-17  ht-18  hht-17  hht-18  lt-17  lt-18  llt-17  llt-18\n",
       "Datetime                                                              \n",
       "2010-01-01  1.952  2.241   2.493  2.7490 -0.797 -1.043  -1.198  -1.430\n",
       "2010-01-02    NaN    NaN   1.939  2.2395 -0.604 -0.840  -1.056  -1.132\n",
       "2010-01-03  0.830  1.096     NaN     NaN -1.841 -2.024  -2.516  -2.694\n",
       "2010-01-04    NaN    NaN   1.102  1.3190 -1.693 -1.949  -2.349  -2.566\n",
       "2010-01-05  1.355  1.654   1.480  1.7680 -1.253 -1.453  -1.306  -1.522"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hilo_df = pd.concat(hilos, axis=1)\n",
    "hilo_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  Wind"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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>WDF2-19</th>\n",
       "      <th>WDF2-20</th>\n",
       "      <th>WSF2-19</th>\n",
       "      <th>WSF2-20</th>\n",
       "      <th>AWDR-19</th>\n",
       "      <th>AWND-19</th>\n",
       "      <th>AWND-20</th>\n",
       "      <th>WGF6-18</th>\n",
       "      <th>AWND-18</th>\n",
       "      <th>AWDR-18</th>\n",
       "      <th>AWND-3</th>\n",
       "      <th>AWDR-3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Datetime</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",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2010-01-01</th>\n",
       "      <td>360.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>23.9</td>\n",
       "      <td>353.0</td>\n",
       "      <td>6.3</td>\n",
       "      <td>7.2</td>\n",
       "      <td>6.798833</td>\n",
       "      <td>4.384917</td>\n",
       "      <td>201.898458</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-02</th>\n",
       "      <td>340.0</td>\n",
       "      <td>310.0</td>\n",
       "      <td>23.9</td>\n",
       "      <td>30.0</td>\n",
       "      <td>321.0</td>\n",
       "      <td>13.9</td>\n",
       "      <td>19.9</td>\n",
       "      <td>18.064142</td>\n",
       "      <td>11.356569</td>\n",
       "      <td>337.813724</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-03</th>\n",
       "      <td>290.0</td>\n",
       "      <td>290.0</td>\n",
       "      <td>17.9</td>\n",
       "      <td>28.0</td>\n",
       "      <td>295.0</td>\n",
       "      <td>11.2</td>\n",
       "      <td>16.6</td>\n",
       "      <td>16.935208</td>\n",
       "      <td>10.734083</td>\n",
       "      <td>325.094250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-04</th>\n",
       "      <td>330.0</td>\n",
       "      <td>310.0</td>\n",
       "      <td>16.1</td>\n",
       "      <td>21.0</td>\n",
       "      <td>306.0</td>\n",
       "      <td>7.2</td>\n",
       "      <td>9.8</td>\n",
       "      <td>9.317000</td>\n",
       "      <td>6.280500</td>\n",
       "      <td>324.969875</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-05</th>\n",
       "      <td>310.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>292.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>8.5</td>\n",
       "      <td>8.925750</td>\n",
       "      <td>6.063000</td>\n",
       "      <td>317.637125</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            WDF2-19  WDF2-20  WSF2-19  WSF2-20  AWDR-19  AWND-19  AWND-20  \\\n",
       "Datetime                                                                    \n",
       "2010-01-01    360.0    340.0     23.0     23.9    353.0      6.3      7.2   \n",
       "2010-01-02    340.0    310.0     23.9     30.0    321.0     13.9     19.9   \n",
       "2010-01-03    290.0    290.0     17.9     28.0    295.0     11.2     16.6   \n",
       "2010-01-04    330.0    310.0     16.1     21.0    306.0      7.2      9.8   \n",
       "2010-01-05    310.0    300.0     15.0     17.0    292.0      6.0      8.5   \n",
       "\n",
       "              WGF6-18    AWND-18     AWDR-18  AWND-3  AWDR-3  \n",
       "Datetime                                                      \n",
       "2010-01-01   6.798833   4.384917  201.898458     NaN     NaN  \n",
       "2010-01-02  18.064142  11.356569  337.813724     NaN     NaN  \n",
       "2010-01-03  16.935208  10.734083  325.094250     NaN     NaN  \n",
       "2010-01-04   9.317000   6.280500  324.969875     NaN     NaN  \n",
       "2010-01-05   8.925750   6.063000  317.637125     NaN     NaN  "
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wind_dfs = []\n",
    "for v in ['WDF2', 'WSF2', 'AWDR', 'AWND', 'WGF6', 'WSF6', 'WDF6', 'WS2min', 'WD2min']:\n",
    "    if v == 'WSF6':\n",
    "        abbr = 'AWND'\n",
    "    elif v == 'WDF6':\n",
    "        abbr = 'AWDR'\n",
    "    elif v == 'WS2min':\n",
    "        abbr = 'AWND'\n",
    "    elif v == 'WD2min':\n",
    "        abbr = 'AWDR'\n",
    "    else:\n",
    "        abbr = v\n",
    "    wind_dfs.append(daily_pivot_table(v, np.mean, abbr))\n",
    "all_wind = pd.concat(wind_dfs, axis=1)\n",
    "all_wind.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>WDF2-19</th>\n",
       "      <th>WDF2-20</th>\n",
       "      <th>WSF2-19</th>\n",
       "      <th>WSF2-20</th>\n",
       "      <th>AWDR-19</th>\n",
       "      <th>AWND-19</th>\n",
       "      <th>AWND-20</th>\n",
       "      <th>WGF6-18</th>\n",
       "      <th>AWND-18</th>\n",
       "      <th>AWDR-18</th>\n",
       "      <th>...</th>\n",
       "      <th>r3d-1</th>\n",
       "      <th>r3d-2</th>\n",
       "      <th>r3d-7</th>\n",
       "      <th>r3d-11</th>\n",
       "      <th>r3d-12</th>\n",
       "      <th>r3d-13</th>\n",
       "      <th>r3d-14</th>\n",
       "      <th>r3d-15</th>\n",
       "      <th>r3d-16</th>\n",
       "      <th>r3d-21</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Datetime</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",
       "      <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>2010-09-15</th>\n",
       "      <td>140.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>119.0</td>\n",
       "      <td>5.1</td>\n",
       "      <td>5.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.200000e-01</td>\n",
       "      <td>2.400000e-01</td>\n",
       "      <td>2.600000e-01</td>\n",
       "      <td>2.200000e-01</td>\n",
       "      <td>3.100000e-01</td>\n",
       "      <td>2.400000e-01</td>\n",
       "      <td>3.600000e-01</td>\n",
       "      <td>2.400000e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-09-16</th>\n",
       "      <td>220.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>25.9</td>\n",
       "      <td>19.9</td>\n",
       "      <td>210.0</td>\n",
       "      <td>15.2</td>\n",
       "      <td>9.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000e-02</td>\n",
       "      <td>-3.053113e-16</td>\n",
       "      <td>-2.220446e-16</td>\n",
       "      <td>-3.608225e-16</td>\n",
       "      <td>3.330669e-16</td>\n",
       "      <td>-1.110223e-15</td>\n",
       "      <td>-1.554312e-15</td>\n",
       "      <td>-5.551115e-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-09-17</th>\n",
       "      <td>220.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>16.1</td>\n",
       "      <td>41.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>9.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1.351350e-15</td>\n",
       "      <td>-3.053113e-16</td>\n",
       "      <td>-2.220446e-16</td>\n",
       "      <td>-3.608225e-16</td>\n",
       "      <td>3.330669e-16</td>\n",
       "      <td>-1.110223e-15</td>\n",
       "      <td>-1.554312e-15</td>\n",
       "      <td>-5.551115e-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-09-18</th>\n",
       "      <td>30.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>16.1</td>\n",
       "      <td>18.1</td>\n",
       "      <td>37.0</td>\n",
       "      <td>8.5</td>\n",
       "      <td>9.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1.351350e-15</td>\n",
       "      <td>-3.053113e-16</td>\n",
       "      <td>-2.220446e-16</td>\n",
       "      <td>-3.608225e-16</td>\n",
       "      <td>3.330669e-16</td>\n",
       "      <td>-1.110223e-15</td>\n",
       "      <td>-1.554312e-15</td>\n",
       "      <td>-5.551115e-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-09-19</th>\n",
       "      <td>40.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.1</td>\n",
       "      <td>12.1</td>\n",
       "      <td>58.0</td>\n",
       "      <td>4.3</td>\n",
       "      <td>4.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1.351350e-15</td>\n",
       "      <td>-3.053113e-16</td>\n",
       "      <td>-2.220446e-16</td>\n",
       "      <td>-3.608225e-16</td>\n",
       "      <td>3.330669e-16</td>\n",
       "      <td>-1.110223e-15</td>\n",
       "      <td>-1.554312e-15</td>\n",
       "      <td>-5.551115e-17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 113 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            WDF2-19  WDF2-20  WSF2-19  WSF2-20  AWDR-19  AWND-19  AWND-20  \\\n",
       "Datetime                                                                    \n",
       "2010-09-15    140.0    110.0     13.0     15.0    119.0      5.1      5.6   \n",
       "2010-09-16    220.0    200.0     25.9     19.9    210.0     15.2      9.6   \n",
       "2010-09-17    220.0    200.0     21.0     16.1     41.0     11.0      9.4   \n",
       "2010-09-18     30.0     30.0     16.1     18.1     37.0      8.5      9.4   \n",
       "2010-09-19     40.0     10.0     10.1     12.1     58.0      4.3      4.7   \n",
       "\n",
       "            WGF6-18  AWND-18  AWDR-18  ...  r3d-1  r3d-2         r3d-7  \\\n",
       "Datetime                               ...                               \n",
       "2010-09-15      NaN      NaN      NaN  ...    0.0    0.0  2.200000e-01   \n",
       "2010-09-16      NaN      NaN      NaN  ...    0.0    0.0  1.000000e-02   \n",
       "2010-09-17      NaN      NaN      NaN  ...    0.0    0.0 -1.351350e-15   \n",
       "2010-09-18      NaN      NaN      NaN  ...    0.0    0.0 -1.351350e-15   \n",
       "2010-09-19      NaN      NaN      NaN  ...    0.0    0.0 -1.351350e-15   \n",
       "\n",
       "                  r3d-11        r3d-12        r3d-13        r3d-14  \\\n",
       "Datetime                                                             \n",
       "2010-09-15  2.400000e-01  2.600000e-01  2.200000e-01  3.100000e-01   \n",
       "2010-09-16 -3.053113e-16 -2.220446e-16 -3.608225e-16  3.330669e-16   \n",
       "2010-09-17 -3.053113e-16 -2.220446e-16 -3.608225e-16  3.330669e-16   \n",
       "2010-09-18 -3.053113e-16 -2.220446e-16 -3.608225e-16  3.330669e-16   \n",
       "2010-09-19 -3.053113e-16 -2.220446e-16 -3.608225e-16  3.330669e-16   \n",
       "\n",
       "                  r3d-15        r3d-16        r3d-21  \n",
       "Datetime                                              \n",
       "2010-09-15  2.400000e-01  3.600000e-01  2.400000e-01  \n",
       "2010-09-16 -1.110223e-15 -1.554312e-15 -5.551115e-17  \n",
       "2010-09-17 -1.110223e-15 -1.554312e-15 -5.551115e-17  \n",
       "2010-09-18 -1.110223e-15 -1.554312e-15 -5.551115e-17  \n",
       "2010-09-19 -1.110223e-15 -1.554312e-15 -5.551115e-17  \n",
       "\n",
       "[5 rows x 113 columns]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_df = pd.concat([all_wind, hilo_df, td_r15mx, td_rhrmx, tide_df, gw_df, r15_mx, rhr_mx, rain_daily_comb_named, rain_prev_3_days], axis=1)\n",
    "feature_df = feature_df.loc['2010-09-15':'2016-10-15']\n",
    "feature_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "### Save Daily Observations to DB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "# con = sqlite3.connect(db_filename)\n",
    "# feature_df.to_sql(con=con, name=\"nor_daily_observations\", if_exists=\"replace\")\n",
    "feature_df.to_csv('nor_daily_observations_standalone.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}