PreparingMicroBioAndRainData.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Checking and cleaning the data from summer microbiology surveys\n",
    "\n",
    "#### !Edit the orginal data for the 2017 rainfall was incorrect this has been updated.\n",
    "\n",
    "#### There are two consecutive years of data. Stored in different excel tables and in diferent formats. The data needs to be aggregated and transformed in the following ways:\n",
    "\n",
    "1. Ensure date formats are consistent in both sets\n",
    "2. Identify and separate incubation times\n",
    "3. Identify unique sample days per year\n",
    "4. Ensure the precipitation data is consistent in format with the micro-biology data\n",
    "5. Develop categories and grouping methods to analyse and present the results\n",
    "5. Store and separate the results in a proper directory structure\n",
    "\n",
    "\n",
    "#### The data should be usable either as .csv or JSON format for a variety of tasks.\n",
    "\n",
    "1. The results maybe transformed further along the chain (grouping/aggregating/summarising)\n",
    "2. This should be considered the \"starting point\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import json\n",
    "import re\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Make a function to write JSON files\n",
    "\n",
    "#### We will be pushing most of our work to JSON or csv\n",
    "\n",
    "1. The built in pandas function will not always work\n",
    "2. Most of what is pushed is already in dictionary format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_json(a, b):\n",
    "    with open(a, 'wb') as f:\n",
    "        f.write(json.dumps(b).encode('utf-8'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import the data and clean it up\n",
    "\n",
    "1. Format the dates\n",
    "2. Fix column names (ensure they all have the same syntax)\n",
    "3. Identify any records that need to be excluded for other reasons (nan values, weather, etc..)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get the data\n",
    "a = pd.read_csv('data/2017_Data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Location</th>\n",
       "      <th>medium</th>\n",
       "      <th>Samples</th>\n",
       "      <th>Sampling_Notes</th>\n",
       "      <th>Water_temp</th>\n",
       "      <th>Plating_notes</th>\n",
       "      <th>Temp_incubation</th>\n",
       "      <th>P1_qty_sample</th>\n",
       "      <th>Image_24h_fluo_plate_one</th>\n",
       "      <th>...</th>\n",
       "      <th>P3_48h_big_blue</th>\n",
       "      <th>P3_48h_med_blue</th>\n",
       "      <th>P3_48h_green</th>\n",
       "      <th>P3_48h_turq</th>\n",
       "      <th>P3_48h_pink</th>\n",
       "      <th>P3_48h_other</th>\n",
       "      <th>Comments_p3_48h</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>Location_Image</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>26.06.17</td>\n",
       "      <td>Echandens</td>\n",
       "      <td>easy_gel</td>\n",
       "      <td>1</td>\n",
       "      <td>No comments</td>\n",
       "      <td>18</td>\n",
       "      <td>plate one is Easy Gel</td>\n",
       "      <td>37</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Filter-16_55_45.JPG</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>No comments</td>\n",
       "      <td>46.534677</td>\n",
       "      <td>6.539600</td>\n",
       "      <td>none</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>26.06.17</td>\n",
       "      <td>Echandens</td>\n",
       "      <td>easy_gel</td>\n",
       "      <td>1</td>\n",
       "      <td>No comments</td>\n",
       "      <td>18</td>\n",
       "      <td>Plate one is easygel</td>\n",
       "      <td>37</td>\n",
       "      <td>0.5</td>\n",
       "      <td>Cplus-16_50_50.JPG</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>No comments</td>\n",
       "      <td>46.535602</td>\n",
       "      <td>6.552227</td>\n",
       "      <td>none</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>26.06.17</td>\n",
       "      <td>Echandens</td>\n",
       "      <td>unil_kitchen</td>\n",
       "      <td>1</td>\n",
       "      <td>No comments</td>\n",
       "      <td>18</td>\n",
       "      <td>moved unil kitchen data here, out of plate 2 slot</td>\n",
       "      <td>37</td>\n",
       "      <td>0.5</td>\n",
       "      <td>none</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>No comments</td>\n",
       "      <td>46.534677</td>\n",
       "      <td>6.539600</td>\n",
       "      <td>none</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 71 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date   Location        medium  Samples Sampling_Notes  Water_temp  \\\n",
       "12  26.06.17  Echandens      easy_gel        1    No comments          18   \n",
       "15  26.06.17  Echandens      easy_gel        1    No comments          18   \n",
       "62  26.06.17  Echandens  unil_kitchen        1    No comments          18   \n",
       "\n",
       "                                        Plating_notes  Temp_incubation  \\\n",
       "12                             plate one is Easy Gel                37   \n",
       "15                              Plate one is easygel                37   \n",
       "62  moved unil kitchen data here, out of plate 2 slot               37   \n",
       "\n",
       "    P1_qty_sample Image_24h_fluo_plate_one      ...        P3_48h_big_blue  \\\n",
       "12            4.0      Filter-16_55_45.JPG      ...                    0.0   \n",
       "15            0.5       Cplus-16_50_50.JPG      ...                    0.0   \n",
       "62            0.5                     none      ...                    0.0   \n",
       "\n",
       "    P3_48h_med_blue P3_48h_green  P3_48h_turq  P3_48h_pink  P3_48h_other  \\\n",
       "12              0.0          0.0          0.0          0.0           0.0   \n",
       "15              0.0          0.0          0.0          0.0           0.0   \n",
       "62              0.0          0.0          0.0          0.0           0.0   \n",
       "\n",
       "    Comments_p3_48h   latitude  longitude Location_Image  \n",
       "12      No comments  46.534677   6.539600           none  \n",
       "15      No comments  46.535602   6.552227           none  \n",
       "62      No comments  46.534677   6.539600           none  \n",
       "\n",
       "[3 rows x 71 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a.loc[(a.Date == \"26.06.17\") & (a.Location == \"Echandens\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this is a duplicate value in the DF, the data was moved and then added as a record)\n",
    "# so we need to get rid of that:\n",
    "a.drop(12, inplace=True)\n",
    "# there is a column name that doesn't match the others\n",
    "a.rename(columns={'p3_fluo_halo_colonies':'P3_fluo_halo_colonies'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Format dates 2017 data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# format the 2017 dates\n",
    "# for whatever reason the pd.datetime did not recognize the date 17.06.12\n",
    "# it flipped it to : 17.12.06 ---- which won't work\n",
    "# so use some list index and str properties to make this right\n",
    "\n",
    "b = a['Date'].copy()\n",
    "new_dates = []\n",
    "new_dic = {}\n",
    "for x in b:\n",
    "    year = '20'+str(x[6:])\n",
    "    day = x[:2]\n",
    "    month = x[3:5]\n",
    "    new_d = year + '/' + str(month) + '/' + str(day)\n",
    "    new_dates.append({x:new_d})\n",
    "    new_dic.update({x:new_d})\n",
    "new_dates[:10]\n",
    "\n",
    "a['Date'] = a['Date'].map(new_dic)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The dates were put into a \"string\" format\n",
    "# This is good for reading but not for doing calculations\n",
    "# Use to_datetime to convert to a timestamp\n",
    "a['Date'] = pd.to_datetime(a['Date'],format='%Y/%m/%d' )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Location</th>\n",
       "      <th>medium</th>\n",
       "      <th>Samples</th>\n",
       "      <th>Sampling_Notes</th>\n",
       "      <th>Water_temp</th>\n",
       "      <th>Plating_notes</th>\n",
       "      <th>Temp_incubation</th>\n",
       "      <th>P1_qty_sample</th>\n",
       "      <th>Image_24h_fluo_plate_one</th>\n",
       "      <th>...</th>\n",
       "      <th>P3_48h_big_blue</th>\n",
       "      <th>P3_48h_med_blue</th>\n",
       "      <th>P3_48h_green</th>\n",
       "      <th>P3_48h_turq</th>\n",
       "      <th>P3_48h_pink</th>\n",
       "      <th>P3_48h_other</th>\n",
       "      <th>Comments_p3_48h</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>Location_Image</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>2017-07-24</td>\n",
       "      <td>MRD</td>\n",
       "      <td>easy_gel</td>\n",
       "      <td>3</td>\n",
       "      <td>Waves at sight, sampling not effective</td>\n",
       "      <td>0</td>\n",
       "      <td>No comments</td>\n",
       "      <td>37</td>\n",
       "      <td>4.0</td>\n",
       "      <td>none</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>No comments</td>\n",
       "      <td>46.431662</td>\n",
       "      <td>6.908962</td>\n",
       "      <td>1500895656638.jpg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 71 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date Location    medium  Samples  \\\n",
       "45 2017-07-24      MRD  easy_gel        3   \n",
       "\n",
       "                            Sampling_Notes  Water_temp Plating_notes  \\\n",
       "45  Waves at sight, sampling not effective           0   No comments   \n",
       "\n",
       "    Temp_incubation  P1_qty_sample Image_24h_fluo_plate_one  \\\n",
       "45               37            4.0                     none   \n",
       "\n",
       "          ...          P3_48h_big_blue  P3_48h_med_blue P3_48h_green  \\\n",
       "45        ...                      0.0              0.0          0.0   \n",
       "\n",
       "    P3_48h_turq  P3_48h_pink  P3_48h_other  Comments_p3_48h   latitude  \\\n",
       "45          0.0          0.0           0.0      No comments  46.431662   \n",
       "\n",
       "    longitude     Location_Image  \n",
       "45   6.908962  1500895656638.jpg  \n",
       "\n",
       "[1 rows x 71 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a[(a.Location == 'MRD') & (a.Date == '2017-07-24')]\n",
    "# this record needs to go or we need to reset the sample number\n",
    "# check the sampling notes to see why\n",
    "# so even though the sampling was ineffective there is still a value in the samples column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# go ahead and change that now\n",
    "a.loc[(a.Location == 'MRD') & (a.Date == '2017-07-24'), \"Samples\"] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "a.loc[(a.Location == 'MRD') & (a.Date == '2017-07-24'), \"Sampling_Notes\"] = \"Waves at site, sampling not effective\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query that record to make sure it is ok"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Location</th>\n",
       "      <th>medium</th>\n",
       "      <th>Samples</th>\n",
       "      <th>Sampling_Notes</th>\n",
       "      <th>Water_temp</th>\n",
       "      <th>Plating_notes</th>\n",
       "      <th>Temp_incubation</th>\n",
       "      <th>P1_qty_sample</th>\n",
       "      <th>Image_24h_fluo_plate_one</th>\n",
       "      <th>...</th>\n",
       "      <th>P3_48h_big_blue</th>\n",
       "      <th>P3_48h_med_blue</th>\n",
       "      <th>P3_48h_green</th>\n",
       "      <th>P3_48h_turq</th>\n",
       "      <th>P3_48h_pink</th>\n",
       "      <th>P3_48h_other</th>\n",
       "      <th>Comments_p3_48h</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>Location_Image</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>2017-07-24</td>\n",
       "      <td>MRD</td>\n",
       "      <td>easy_gel</td>\n",
       "      <td>0</td>\n",
       "      <td>Waves at site, sampling not effective</td>\n",
       "      <td>0</td>\n",
       "      <td>No comments</td>\n",
       "      <td>37</td>\n",
       "      <td>4.0</td>\n",
       "      <td>none</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>No comments</td>\n",
       "      <td>46.431662</td>\n",
       "      <td>6.908962</td>\n",
       "      <td>1500895656638.jpg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 71 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date Location    medium  Samples  \\\n",
       "45 2017-07-24      MRD  easy_gel        0   \n",
       "\n",
       "                           Sampling_Notes  Water_temp Plating_notes  \\\n",
       "45  Waves at site, sampling not effective           0   No comments   \n",
       "\n",
       "    Temp_incubation  P1_qty_sample Image_24h_fluo_plate_one  \\\n",
       "45               37            4.0                     none   \n",
       "\n",
       "          ...          P3_48h_big_blue  P3_48h_med_blue P3_48h_green  \\\n",
       "45        ...                      0.0              0.0          0.0   \n",
       "\n",
       "    P3_48h_turq  P3_48h_pink  P3_48h_other  Comments_p3_48h   latitude  \\\n",
       "45          0.0          0.0           0.0      No comments  46.431662   \n",
       "\n",
       "    longitude     Location_Image  \n",
       "45   6.908962  1500895656638.jpg  \n",
       "\n",
       "[1 rows x 71 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a[(a.Location == 'MRD') & (a.Date == '2017-07-24')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The data will be served in JSON\n",
    "# convert here and read back to make sure everything is ok\n",
    "a.to_json('data/JSON/2017ColonyCounts.json', orient='index')\n",
    "a.to_csv('data/CSV/2017ColonyCounts.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# reading the data back in should produce no errors:\n",
    "a = pd.read_json('data/JSON/2017ColonyCounts.json', orient='index')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### This is \"record data\" each row corresponds to an event:\n",
    "#### Each event is a water sample from a location on a particular day\n",
    "\n",
    "1. For each event there are three samples\n",
    "2. Those three samples each get their own selective medium plate\n",
    "3. Each plate has a subtotal \"colony count\"\n",
    "4. The average of the subtotals is the \"Total colony count\" for that day and location\n",
    "5. The bacterial species are identified by the color of the colony\n",
    "6. The colonies are counted at 48h and 24h\n",
    "\n",
    "### Initially we need to be able to develop a method to \"call\" individual values:\n",
    "\n",
    "1. Per location\n",
    "2. Per date\n",
    "3. Per species\n",
    "4. Per incubation time\n",
    "\n",
    "#### Then we can chart and compare results across locations and from one year to another.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There are two incubation times for each sample - 24 and 48 hours:\n",
    "\n",
    "1. Seperate the records\n",
    "2. Export the data as a csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Date groupings, by week 2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# group the dates by week number\n",
    "# first get a sorted list by date\n",
    "b = list(a['Date'].unique())\n",
    "\n",
    "# then convert the dataes to a timestamp that can be used by python/numpy\n",
    "b = pd.to_datetime(b, format='%Y/%m/%d')\n",
    "\n",
    "# week number is how we will measure the \"distance\" from the \"Jazz Event\"\n",
    "week_number = ['Week one', 'Week two', 'Week three', 'Week four', 'Week five', 'Week six', 'Week seven', 'Week eight']\n",
    "\n",
    "# pair the week number with the corresponding date combine in a dictionary:\n",
    "weeks = dict(zip(week_number, b))\n",
    "\n",
    "# the out put looks like this:\n",
    "# {'Week one': Timestamp('2017-06-12 00:00:00'),...'Week eight': Timestamp('2017-07-31 00:00:00')}\n",
    "\n",
    "# inverse the key:value pairs:\n",
    "weeks_2 = dict(zip(b, week_number))\n",
    "# !! Attention:\n",
    "# the time stamp works here but it is a hassel in js\n",
    "# and we can allways use == to group by date strings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The date in string format will be used in JSON output\n",
    "# Easy to parse in JS with Date.parse method:\n",
    "\n",
    "def convert_to_string(a):\n",
    "    d = {}\n",
    "    for key, value in a.items():\n",
    "        b = value.strftime('%Y-%m-%d')\n",
    "        c = {key:b}\n",
    "        d.update(c)\n",
    "    return d\n",
    "    \n",
    "weeks_2017 = convert_to_string(weeks)\n",
    "dates_2017 = {value:key for key, value in weeks_2017.items()}\n",
    "\n",
    "# the output looks like this:\n",
    "#{'2017-06-12': 'Week one',...'2017-07-31': 'Week eight'}\n",
    "\n",
    "# write both versions to JSON and save them as utitlities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "make_json('data/utilities/dateWeek2017JsonObj.json',dates_2017)\n",
    "make_json('data/utilities/weekDate2017JsonObj.json',weeks_2017)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get and check 2016 data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "a16 = pd.read_csv('data/2016_Data.csv')\n",
    "a16.drop('Unnamed: 0', axis=1, inplace=True)\n",
    "a16.to_json('data/JSON/2016ColonyCounts.json', orient='index')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Date groupings, by week 2016\n",
    "\n",
    "This is the same process as the dates for 2017, for an explanation see blocks above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "b_2016 = a16['Date'].unique()\n",
    "b_2016.sort()\n",
    "b_2016 = pd.to_datetime(b_2016,format='%Y-%m-%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "weeks_2016 = dict(zip(week_number, b_2016))\n",
    "dates_2016 = dict(zip(b_2016, week_number))\n",
    "week_date16 = convert_to_string(weeks_2016)\n",
    "date_week16 = {value:key for key, value in week_date16.items()}\n",
    "make_json('data/utilities/weekDate2016JsonObj.json',week_date16)\n",
    "make_json('data/utilities/dateWeek2016JsonObj.json',date_week16 )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Mediums used to culture bacteria\n",
    "\n",
    "1. Create a list of the different culture mediums that were used"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# a list of mediums is important\n",
    "mediums = list(a['medium'].unique().copy())\n",
    "# push that to JSON\n",
    "make_json('data/utilities/mediums2017.json', mediums)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Locations of interest\n",
    "\n",
    "1. Create a list of the sample locations to be compared"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "places_48 = ['MRD', 'VNX', 'SVT']\n",
    "make_json('data/utilities/locations.json',places_48)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Link column names to colony color\n",
    "\n",
    "1. indifferent of platenumber\n",
    "2. used to label charts \n",
    "3. Easier to remember"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create plate variables\n",
    "p_one_48 = ['P1_fluo_halo_colonies','P1_48h_big_blue','P1_48h_med_blue','P1_48h_green',\n",
    "            'P1_48h_turq','P1_48h_pink','P1_48h_other']\n",
    "p_two_48 = ['P2_fluo_halo_colonies','P2_48h_big_blue','P2_48h_med_blue',\n",
    "            'P2_48h_green','P2_48h_turq','P2_48h_pink','P2_48h_other']\n",
    "p_three_48 = ['P3_fluo_halo_colonies','P3_48h_big_blue','P3_48h_med_blue',\n",
    "              'P3_48h_green','P3_48h_turq','P3_48h_pink','P3_48h_other']\n",
    "# create key for charting\n",
    "q = re.compile('fluo_halo', re.IGNORECASE)\n",
    "def make_keys(a):\n",
    "    f={}\n",
    "    for h in a:\n",
    "        for b, c in enumerate(h):\n",
    "            if q.search(h[b]):\n",
    "                d = 'UV Fluo'\n",
    "                e = {h[0]:d}\n",
    "                f.update(e)\n",
    "            else:\n",
    "                d = h[b][7:]\n",
    "                e = {h[b]:d}\n",
    "                f.update(e)\n",
    "    return f\n",
    "colony_map = make_keys([p_one_48, p_two_48, p_three_48])\n",
    "make_json('data/utilities/colony_map.json', colony_map)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'P1_fluo_halo_colonies': 'UV Fluo',\n",
       " 'P1_48h_big_blue': 'big_blue',\n",
       " 'P1_48h_med_blue': 'med_blue',\n",
       " 'P1_48h_green': 'green',\n",
       " 'P1_48h_turq': 'turq',\n",
       " 'P1_48h_pink': 'pink',\n",
       " 'P1_48h_other': 'other',\n",
       " 'P2_fluo_halo_colonies': 'UV Fluo',\n",
       " 'P2_48h_big_blue': 'big_blue',\n",
       " 'P2_48h_med_blue': 'med_blue',\n",
       " 'P2_48h_green': 'green',\n",
       " 'P2_48h_turq': 'turq',\n",
       " 'P2_48h_pink': 'pink',\n",
       " 'P2_48h_other': 'other',\n",
       " 'P3_fluo_halo_colonies': 'UV Fluo',\n",
       " 'P3_48h_big_blue': 'big_blue',\n",
       " 'P3_48h_med_blue': 'med_blue',\n",
       " 'P3_48h_green': 'green',\n",
       " 'P3_48h_turq': 'turq',\n",
       " 'P3_48h_pink': 'pink',\n",
       " 'P3_48h_other': 'other'}"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "colony_map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### This create user friendly names mapped to orignal dataframe column names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'big_blue'"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the 'map' can be used to convert\n",
    "# column names to something more concise\n",
    "# can use index location or counts to call labels\n",
    "# so that means that\n",
    "colony_map[p_two_48[1]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Test that the same index number calls the same color group across all plates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# to check that the same color group is called for each plate\n",
    "# grab a random number and use it to\n",
    "# index the lists created from the columns\n",
    "# these will be used as labels later to call data out\n",
    "# the output  should be the same for all values x[i] where i = np.random.choice(len(list of names))\n",
    "plates_four_eight =  [p_one_48, p_two_48, p_three_48]\n",
    "plates_2four_eight = [p_one_48, p_two_48]\n",
    "plates_1four_eight = [p_one_48]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['P1_fluo_halo_colonies',\n",
       "  'P1_48h_big_blue',\n",
       "  'P1_48h_med_blue',\n",
       "  'P1_48h_green',\n",
       "  'P1_48h_turq',\n",
       "  'P1_48h_pink',\n",
       "  'P1_48h_other']]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "plates_1four_eight"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calling color groups\n",
    "\n",
    "1. We need to be able to call color groups by plate number and/or incubation time\n",
    "2. this needs to be passed on in JSON format also\n",
    "\n",
    "#### Ensure that with one index call all the results for a particular species can be collected"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['P1_48h_med_blue', 'P2_48h_med_blue', 'P3_48h_med_blue']"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# use the above property to make color groups\n",
    "# each color represents a type/family of bacteria\n",
    "# we need two calculate the results per color group\n",
    "# in the preceding step they are grouped by plate\n",
    "a_number = np.arange(len(plates_four_eight[0]))\n",
    "def make_groups(x, q):\n",
    "    b=[]\n",
    "    for s in x:\n",
    "        a = []\n",
    "        for y in q:\n",
    "            a.append(y[s])\n",
    "        b.append(a)\n",
    "    return b\n",
    "three_plate_48 = make_groups(a_number, plates_four_eight)\n",
    "two_plate_48 = make_groups(np.arange(len(plates_2four_eight[0])), plates_2four_eight)\n",
    "one_plate_48 = make_groups(np.arange(len(plates_1four_eight[0])), plates_1four_eight)\n",
    "\n",
    "# take a look\n",
    "# they should all be the same group:\n",
    "three_plate_48[2]#< ---------- this is good\n",
    "# perfect now I can call a color group by the number of samples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['P1_48h_big_blue', 'P2_48h_big_blue', 'P3_48h_big_blue']"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "three_plate_48[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 24 hour groups\n",
    "### Re-use the functions from the 48 hour group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "p_one_24 = ['P1_fluo_halo_colonies', 'P1_24h_big_blue','P1_24h_med_blue',\n",
    "            'P1_24h_green', 'P1_24h_turq', 'P1_24h_pink', 'P1_24h_other']\n",
    "p_two_24 = ['P2_fluo_halo_colonies','P2_24h_big_blue','P2_24h_med_blue',\n",
    "            'P2_24h_green', 'P2_24h_turq', 'P2_24h_pink','P2_24h_other',]\n",
    "p_three_24 = ['P3_fluo_halo_colonies', 'P3_24h_big_blue', 'P3_24h_med_blue',\n",
    "              'P3_24h_green', 'P3_24h_turq', 'P3_24h_pink','P3_24h_other']\n",
    "\n",
    "colony_map_24 = make_keys([p_one_24, p_two_24, p_three_24] )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "plates_two_four = [p_one_24, p_two_24, p_three_24]\n",
    "plates_2four_four = [p_one_24, p_two_24]\n",
    "plates_1four_four = [p_one_24]\n",
    "# check_indexes(plates_two_four)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['P1_24h_med_blue', 'P2_24h_med_blue']"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "three_plate_24 = make_groups(np.arange(len(plates_two_four[0])), plates_two_four)\n",
    "two_plate_24 = make_groups(np.arange(len(plates_2four_four[0])), plates_2four_four)\n",
    "one_plate_24 = make_groups(np.arange(len(plates_1four_four[0])), plates_1four_four)\n",
    "\n",
    "# take a look\n",
    "# they should all be the same group:\n",
    "two_plate_24[2]\n",
    "# perfect now I can call a color group by the number of samples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['P1_24h_med_blue', 'P2_24h_med_blue', 'P3_24h_med_blue']"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "three_plate_24[2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "make_json('data/utilities/threeP2417.json', three_plate_24)\n",
    "make_json('data/utilities/twoP2417.json', two_plate_24)\n",
    "make_json('data/utilities/oneP2417.json', one_plate_24)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2016 groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "p_one_2016 = ['P1_24h_big_blue','P1_24h_med_blue','P1_24h_turq', 'P1_24h_pink', 'P1_24h_other']\n",
    "p_two_2016 = ['P2_24h_big_blue','P2_24h_med_blue','P2_24h_turq', 'P2_24h_pink','P2_24h_other',]\n",
    "p_three_2016 = ['P3_24h_big_blue', 'P3_24h_med_blue','P3_24h_turq', 'P3_24h_pink','P3_24h_other']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# make some keys for the 2016 data\n",
    "# there is no fluo\n",
    "def make_keys_16(a):\n",
    "    f={}\n",
    "    for h in a:\n",
    "        for b, c in enumerate(h):\n",
    "            d = h[b][7:]\n",
    "            e = {h[b]:d}\n",
    "            f.update(e)\n",
    "    return f"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "colony_map_16 = make_keys_16([p_one_2016, p_two_2016, p_three_2016])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'P1_24h_big_blue': 'big_blue',\n",
       " 'P1_24h_med_blue': 'med_blue',\n",
       " 'P1_24h_turq': 'turq',\n",
       " 'P1_24h_pink': 'pink',\n",
       " 'P1_24h_other': 'other',\n",
       " 'P2_24h_big_blue': 'big_blue',\n",
       " 'P2_24h_med_blue': 'med_blue',\n",
       " 'P2_24h_turq': 'turq',\n",
       " 'P2_24h_pink': 'pink',\n",
       " 'P2_24h_other': 'other',\n",
       " 'P3_24h_big_blue': 'big_blue',\n",
       " 'P3_24h_med_blue': 'med_blue',\n",
       " 'P3_24h_turq': 'turq',\n",
       " 'P3_24h_pink': 'pink',\n",
       " 'P3_24h_other': 'other'}"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "colony_map_16"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "plates_16 = [p_one_2016, p_two_2016, p_three_2016]\n",
    "three_plate_16 = make_groups(np.arange(len(plates_16[0])), plates_16)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'med_blue'"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "colony_map_16[p_two_2016[1]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['P1_24h_other', 'P2_24h_other', 'P3_24h_other']"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "three_plate_16[4] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "make_json('data/utilities/threeP2416.json', three_plate_16)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "# okay good to go"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Rain data 2017"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Edit \n",
    "\n",
    "The 2017 rain data that was provided was incorrect. This relflects the correct values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12.06.17</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13.06.17</td>\n",
       "      <td>2.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14.06.17</td>\n",
       "      <td>6.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15.06.17</td>\n",
       "      <td>3.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16.06.17</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>17.06.17</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>18.06.17</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>19.06.17</td>\n",
       "      <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>20.06.17</td>\n",
       "      <td>1.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>21.06.17</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>22.06.17</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>23.06.17</td>\n",
       "      <td>5.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>24.06.17</td>\n",
       "      <td>9.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>25.06.17</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>26.06.17</td>\n",
       "      <td>4.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>27.06.17</td>\n",
       "      <td>6.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>28.06.17</td>\n",
       "      <td>19.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>29.06.17</td>\n",
       "      <td>6.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>30.06.17</td>\n",
       "      <td>2.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>01.07.17</td>\n",
       "      <td>5.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>02.07.17</td>\n",
       "      <td>2.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>03.07.17</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>04.07.17</td>\n",
       "      <td>0.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>05.07.17</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>06.07.17</td>\n",
       "      <td>0.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>07.07.17</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>08.07.17</td>\n",
       "      <td>6.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>09.07.17</td>\n",
       "      <td>8.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>10.07.17</td>\n",
       "      <td>7.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>11.07.17</td>\n",
       "      <td>2.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>12.07.17</td>\n",
       "      <td>2.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>13.07.17</td>\n",
       "      <td>0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>14.07.17</td>\n",
       "      <td>2.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>15.07.17</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>16.07.17</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>17.07.17</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>18.07.17</td>\n",
       "      <td>10.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>19.07.17</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>20.07.17</td>\n",
       "      <td>6.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>21.07.17</td>\n",
       "      <td>13.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>22.07.17</td>\n",
       "      <td>4.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>23.07.17</td>\n",
       "      <td>3.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>24.07.17</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>25.07.17</td>\n",
       "      <td>10.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>26.07.17</td>\n",
       "      <td>3.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>27.07.17</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>28.07.17</td>\n",
       "      <td>0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>29.07.17</td>\n",
       "      <td>3.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>30.07.17</td>\n",
       "      <td>1.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>31.07.17</td>\n",
       "      <td>4.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           0     1\n",
       "0   12.06.17   5.3\n",
       "1   13.06.17   2.6\n",
       "2   14.06.17   6.5\n",
       "3   15.06.17   3.7\n",
       "4   16.06.17   1.5\n",
       "5   17.06.17   0.0\n",
       "6   18.06.17   0.0\n",
       "7   19.06.17   1.2\n",
       "8   20.06.17   1.9\n",
       "9   21.06.17   5.3\n",
       "10  22.06.17   3.0\n",
       "11  23.06.17   5.7\n",
       "12  24.06.17   9.8\n",
       "13  25.06.17   8.0\n",
       "14  26.06.17   4.5\n",
       "15  27.06.17   6.9\n",
       "16  28.06.17  19.3\n",
       "17  29.06.17   6.3\n",
       "18  30.06.17   2.2\n",
       "19  01.07.17   5.6\n",
       "20  02.07.17   2.3\n",
       "21  03.07.17   0.3\n",
       "22  04.07.17   0.2\n",
       "23  05.07.17   0.3\n",
       "24  06.07.17   0.2\n",
       "25  07.07.17   1.0\n",
       "26  08.07.17   6.3\n",
       "27  09.07.17   8.3\n",
       "28  10.07.17   7.4\n",
       "29  11.07.17   2.1\n",
       "30  12.07.17   2.9\n",
       "31  13.07.17   0.8\n",
       "32  14.07.17   2.8\n",
       "33  15.07.17   0.3\n",
       "34  16.07.17   0.0\n",
       "35  17.07.17   1.5\n",
       "36  18.07.17  10.6\n",
       "37  19.07.17   5.0\n",
       "38  20.07.17   6.4\n",
       "39  21.07.17  13.2\n",
       "40  22.07.17   4.2\n",
       "41  23.07.17   3.9\n",
       "42  24.07.17  11.0\n",
       "43  25.07.17  10.5\n",
       "44  26.07.17   3.4\n",
       "45  27.07.17   2.0\n",
       "46  28.07.17   0.8\n",
       "47  29.07.17   3.8\n",
       "48  30.07.17   1.7\n",
       "49  31.07.17   4.1"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we need to add rain values for the sampling period\n",
    "# let's see what we got from the CSO\n",
    "aa = pd.read_csv('/home/mw-shovel/dev/water_quality/water-quality-2016-2017/data/CSV/2017correctrain_date_mm.csv', header=None)\n",
    "aa"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "b = aa[0].copy()\n",
    "new_dates = []\n",
    "new_dic = {}\n",
    "for x in b:\n",
    "    year = '20'+str(x[6:])\n",
    "    day = x[:2]\n",
    "    month = x[3:5]\n",
    "    new_d = year + '/' + str(month) + '/' + str(day)\n",
    "    new_dates.append({x:new_d})\n",
    "    new_dic.update({x:new_d})\n",
    "new_dates[:10]\n",
    "\n",
    "aa[0] = aa[0].map(new_dic)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2017/06/12'"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# first rename the column\n",
    "aa.rename(columns={0:'Date', 1:\"Rain\"}, inplace=True)\n",
    "# check again before we go down some long road\n",
    "aa['Date'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2017-06-12 00:00:00')"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# see how this converts using datetime method\n",
    "pd.to_datetime(aa['Date'][0])\n",
    "# if i am lucky it will give me the year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2017-06-12 00:00:00')"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aa['Date'] = pd.to_datetime(aa['Date'])\n",
    "aa['Date'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "r_2017 = aa.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "r_2017['Date']=r_2017['Date'].dt.strftime(\"%Y-%m-%d\")\n",
    "r_2017.to_json('data/JSON/rain2017.json', orient='index')\n",
    "r_2017.to_csv('data/CSV/rain2017.csv')"
   ]
  },
  {
   "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>Date</th>\n",
       "      <th>Rain</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-06-12</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-06-13</td>\n",
       "      <td>2.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-06-14</td>\n",
       "      <td>6.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-06-15</td>\n",
       "      <td>3.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-06-16</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2017-06-17</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2017-06-18</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2017-06-19</td>\n",
       "      <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2017-06-20</td>\n",
       "      <td>1.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2017-06-21</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2017-06-22</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2017-06-23</td>\n",
       "      <td>5.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2017-06-24</td>\n",
       "      <td>9.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2017-06-25</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2017-06-26</td>\n",
       "      <td>4.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2017-06-27</td>\n",
       "      <td>6.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2017-06-28</td>\n",
       "      <td>19.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2017-06-29</td>\n",
       "      <td>6.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2017-06-30</td>\n",
       "      <td>2.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2017-07-01</td>\n",
       "      <td>5.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2017-07-02</td>\n",
       "      <td>2.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2017-07-03</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2017-07-04</td>\n",
       "      <td>0.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2017-07-05</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2017-07-06</td>\n",
       "      <td>0.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2017-07-07</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2017-07-08</td>\n",
       "      <td>6.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2017-07-09</td>\n",
       "      <td>8.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2017-07-10</td>\n",
       "      <td>7.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2017-07-11</td>\n",
       "      <td>2.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2017-07-12</td>\n",
       "      <td>2.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2017-07-13</td>\n",
       "      <td>0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>2017-07-14</td>\n",
       "      <td>2.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2017-07-15</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2017-07-16</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>2017-07-17</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2017-07-18</td>\n",
       "      <td>10.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>2017-07-19</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>2017-07-20</td>\n",
       "      <td>6.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>2017-07-21</td>\n",
       "      <td>13.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>2017-07-22</td>\n",
       "      <td>4.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>2017-07-23</td>\n",
       "      <td>3.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>2017-07-24</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>2017-07-25</td>\n",
       "      <td>10.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>2017-07-26</td>\n",
       "      <td>3.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>2017-07-27</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>2017-07-28</td>\n",
       "      <td>0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>2017-07-29</td>\n",
       "      <td>3.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>2017-07-30</td>\n",
       "      <td>1.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>2017-07-31</td>\n",
       "      <td>4.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date  Rain\n",
       "0   2017-06-12   5.3\n",
       "1   2017-06-13   2.6\n",
       "2   2017-06-14   6.5\n",
       "3   2017-06-15   3.7\n",
       "4   2017-06-16   1.5\n",
       "5   2017-06-17   0.0\n",
       "6   2017-06-18   0.0\n",
       "7   2017-06-19   1.2\n",
       "8   2017-06-20   1.9\n",
       "9   2017-06-21   5.3\n",
       "10  2017-06-22   3.0\n",
       "11  2017-06-23   5.7\n",
       "12  2017-06-24   9.8\n",
       "13  2017-06-25   8.0\n",
       "14  2017-06-26   4.5\n",
       "15  2017-06-27   6.9\n",
       "16  2017-06-28  19.3\n",
       "17  2017-06-29   6.3\n",
       "18  2017-06-30   2.2\n",
       "19  2017-07-01   5.6\n",
       "20  2017-07-02   2.3\n",
       "21  2017-07-03   0.3\n",
       "22  2017-07-04   0.2\n",
       "23  2017-07-05   0.3\n",
       "24  2017-07-06   0.2\n",
       "25  2017-07-07   1.0\n",
       "26  2017-07-08   6.3\n",
       "27  2017-07-09   8.3\n",
       "28  2017-07-10   7.4\n",
       "29  2017-07-11   2.1\n",
       "30  2017-07-12   2.9\n",
       "31  2017-07-13   0.8\n",
       "32  2017-07-14   2.8\n",
       "33  2017-07-15   0.3\n",
       "34  2017-07-16   0.0\n",
       "35  2017-07-17   1.5\n",
       "36  2017-07-18  10.6\n",
       "37  2017-07-19   5.0\n",
       "38  2017-07-20   6.4\n",
       "39  2017-07-21  13.2\n",
       "40  2017-07-22   4.2\n",
       "41  2017-07-23   3.9\n",
       "42  2017-07-24  11.0\n",
       "43  2017-07-25  10.5\n",
       "44  2017-07-26   3.4\n",
       "45  2017-07-27   2.0\n",
       "46  2017-07-28   0.8\n",
       "47  2017-07-29   3.8\n",
       "48  2017-07-30   1.7\n",
       "49  2017-07-31   4.1"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r_2017"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2016 rain data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "rain_2016 = pd.read_csv('data/CSV/correctedrain2016.csv', header=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     21.06.16\n",
       "1     22.06.16\n",
       "2     23.06.16\n",
       "3     24.06.16\n",
       "4     25.06.16\n",
       "5     26.06.16\n",
       "6     27.06.16\n",
       "7     28.06.16\n",
       "8     29.06.16\n",
       "9     30.06.16\n",
       "10    01.07.16\n",
       "11    02.07.16\n",
       "12    03.07.16\n",
       "13    04.07.16\n",
       "14    05.07.16\n",
       "15    06.07.16\n",
       "16    07.07.16\n",
       "17    08.07.16\n",
       "18    09.07.16\n",
       "19    10.07.16\n",
       "20    11.07.16\n",
       "21    12.07.16\n",
       "22    13.07.16\n",
       "23    14.07.16\n",
       "24    15.07.16\n",
       "25    16.07.16\n",
       "26    17.07.16\n",
       "27    18.07.16\n",
       "28    19.07.16\n",
       "29    20.07.16\n",
       "30    21.07.16\n",
       "31    22.07.16\n",
       "32    23.07.16\n",
       "33    24.07.16\n",
       "34    25.07.16\n",
       "35    26.07.16\n",
       "36    27.07.16\n",
       "37    28.07.16\n",
       "38    29.07.16\n",
       "39    30.07.16\n",
       "40    31.07.16\n",
       "41    01.08.16\n",
       "42    02.08.16\n",
       "43    03.08.16\n",
       "44    04.08.16\n",
       "45    05.08.16\n",
       "46    06.08.16\n",
       "47    07.08.16\n",
       "48    08.08.16\n",
       "49    09.08.16\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rain_2016[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "b = rain_2016[0].copy()\n",
    "new_dates = []\n",
    "new_dic = {}\n",
    "for x in b:\n",
    "    year = '20'+str(x[6:])\n",
    "    day = x[:2]\n",
    "    month = x[3:5]\n",
    "    new_d = year + '/' + str(month) + '/' + str(day)\n",
    "    new_dates.append({x:new_d})\n",
    "    new_dic.update({x:new_d})\n",
    "new_dates[:10]\n",
    "\n",
    "rain_2016[0] = rain_2016[0].map(new_dic)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "rain_2016.rename(columns={0:'Date', 1:'Rain'}, inplace=True)\n",
    "rain_2016['Date'] = pd.to_datetime(rain_2016['Date'])\n",
    "\n",
    "rain_2016.to_json('data/JSON/rain2016.json', orient='index')\n",
    "rain_2016.to_csv('data/CSV/rain2016.csv')"
   ]
  },
  {
   "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>Date</th>\n",
       "      <th>Rain</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-06-21</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-06-22</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-06-23</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-06-24</td>\n",
       "      <td>13.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-06-25</td>\n",
       "      <td>9.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2016-06-26</td>\n",
       "      <td>1.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2016-06-27</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2016-06-28</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2016-06-29</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2016-06-30</td>\n",
       "      <td>10.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2016-07-01</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2016-07-02</td>\n",
       "      <td>9.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2016-07-03</td>\n",
       "      <td>0.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2016-07-04</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2016-07-05</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2016-07-06</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2016-07-07</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2016-07-08</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2016-07-09</td>\n",
       "      <td>0.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2016-07-10</td>\n",
       "      <td>1.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2016-07-11</td>\n",
       "      <td>7.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2016-07-12</td>\n",
       "      <td>13.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2016-07-13</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2016-07-14</td>\n",
       "      <td>6.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2016-07-15</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2016-07-16</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2016-07-17</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2016-07-18</td>\n",
       "      <td>2.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2016-07-19</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2016-07-20</td>\n",
       "      <td>4.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2016-07-21</td>\n",
       "      <td>3.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2016-07-22</td>\n",
       "      <td>4.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>2016-07-23</td>\n",
       "      <td>3.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2016-07-24</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2016-07-25</td>\n",
       "      <td>2.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>2016-07-26</td>\n",
       "      <td>1.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2016-07-27</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>2016-07-28</td>\n",
       "      <td>0.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>2016-07-29</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>2016-07-30</td>\n",
       "      <td>2.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>2016-07-31</td>\n",
       "      <td>11.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>2016-08-01</td>\n",
       "      <td>0.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>2016-08-02</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>2016-08-03</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>2016-08-04</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>2016-08-05</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>2016-08-06</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>2016-08-07</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>2016-08-08</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>2016-08-09</td>\n",
       "      <td>9.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  Rain\n",
       "0  2016-06-21   4.0\n",
       "1  2016-06-22   0.6\n",
       "2  2016-06-23   0.9\n",
       "3  2016-06-24  13.1\n",
       "4  2016-06-25   9.8\n",
       "5  2016-06-26   1.3\n",
       "6  2016-06-27   0.3\n",
       "7  2016-06-28   0.6\n",
       "8  2016-06-29   2.5\n",
       "9  2016-06-30  10.4\n",
       "10 2016-07-01   1.4\n",
       "11 2016-07-02   9.8\n",
       "12 2016-07-03   0.2\n",
       "13 2016-07-04   0.1\n",
       "14 2016-07-05   6.0\n",
       "15 2016-07-06   0.3\n",
       "16 2016-07-07   0.0\n",
       "17 2016-07-08   2.5\n",
       "18 2016-07-09   0.7\n",
       "19 2016-07-10   1.7\n",
       "20 2016-07-11   7.1\n",
       "21 2016-07-12  13.4\n",
       "22 2016-07-13   5.5\n",
       "23 2016-07-14   6.9\n",
       "24 2016-07-15   0.1\n",
       "25 2016-07-16   0.0\n",
       "26 2016-07-17   0.0\n",
       "27 2016-07-18   2.7\n",
       "28 2016-07-19   3.0\n",
       "29 2016-07-20   4.2\n",
       "30 2016-07-21   3.7\n",
       "31 2016-07-22   4.7\n",
       "32 2016-07-23   3.8\n",
       "33 2016-07-24   4.0\n",
       "34 2016-07-25   2.6\n",
       "35 2016-07-26   1.7\n",
       "36 2016-07-27   2.0\n",
       "37 2016-07-28   0.7\n",
       "38 2016-07-29   0.6\n",
       "39 2016-07-30   2.9\n",
       "40 2016-07-31  11.3\n",
       "41 2016-08-01   0.7\n",
       "42 2016-08-02   0.5\n",
       "43 2016-08-03   0.1\n",
       "44 2016-08-04  19.0\n",
       "45 2016-08-05  14.0\n",
       "46 2016-08-06   0.0\n",
       "47 2016-08-07   0.0\n",
       "48 2016-08-08   1.8\n",
       "49 2016-08-09   9.8"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rain_2016"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "hide_input": false,
  "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}