random_variables.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b20f1e00-970d-4510-a05b-4b558af0335b",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "# Random variables\n",
    "\n",
    "The water monitoring project at the Montreux Jazz has been going on since 2016. The data has been collected and treated by a variety of people since then. \n",
    "\n",
    "__Objective:__ Standardize the nomenclature from the different sampling years. Provide a model for storing and collecting data in the future.\n",
    "\n",
    "__Purpose:__ Define the probability that a survey will exceed a threshold value within the period of the year defined by the survey results.\n",
    "\n",
    "## Definitions\n",
    "\n",
    "* colony: a circular growth of individual bacteria from a water sample\n",
    "* colony-count: the number of colonies of the same color for a given media type\n",
    "* media/medium: the provided nutrients and substrates of a microbial plate or card\n",
    "* color: the observed color of the colony\n",
    "* label: the assumed category of the color:\n",
    "  * Bioindicator\n",
    "  * Coliform\n",
    "  * Other\n",
    "* coef:  the correction factor applied, to allow reporting of colony counts per 100ml of the original water sample.\n",
    " \n",
    "The purpose of the sampling is to identify colonies that appear in the media and classify them as one of the possible labels. The label of interest is _Bioindicators_, this represents the bacteria that are issue from the organism of interest. The organism in this case is people, the _Bioindicator_ is issue from fecal contaminants.\n",
    "\n",
    "## Methods\n",
    "\n",
    "The process requires collaborating with the data-manager(s) from the different project years and ensuring that the data from each year can be combined and interpreted together. The data for this collaboration is stored in the _componentdata_ folder.\n",
    "\n",
    "The relationship of previous label <---> new label is stored in a dictionary or an array for the different possibilities of medium, color, label and coefficient. The new labels are applied to a data-frame.\n",
    "\n",
    "The finsihed data (the result of the collaboration) is stored in the _end_ folder\n",
    "\n",
    "## Sample data\n",
    "\n",
    "The sample data is an example of the desired output per year. This includes the following parameters:\n",
    "\n",
    "1. colony-count\n",
    "2. label\n",
    "3. location\n",
    "4. coefficient*count\n",
    "5. week number\n",
    "6. day of year\n",
    "7. is-jazz: boolean\n",
    "8. rain fall in millimeters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c3cbf469-7cbe-4704-ad37-466afd0f5d2c",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": [
     "remove-input"
    ]
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import datetime as dt\n",
    "import numpy as np\n",
    "\n",
    "project = \"Hackuarium do it together water quality sampling\"\n",
    "site_markers = {\"SVT\":\"o\", \"VNX\":\"D\", \"MRD\":\"X\"}\n",
    "species_colors = { \"Bioindicator\":\"dodgerblue\", \"Coliform\":\"magenta\"}\n",
    "marker_colors = {\"SVT\":\"black\", \"VNX\":\"green\", \"MRD\":\"goldenrod\"}\n",
    "sites = [\"SVT\", \"VNX\", \"MRD\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "486b4419-ca13-4853-8c4d-c8fa9f851abe",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "## Survey data\n",
    "\n",
    "The format of the survey data after collaboration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "fca288ab-6c1e-4837-ab27-9ee4f4f4cc76",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": [
     "remove-input"
    ]
   },
   "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>sample</th>\n",
       "      <th>date_sample</th>\n",
       "      <th>event</th>\n",
       "      <th>before event</th>\n",
       "      <th>after event</th>\n",
       "      <th>medium</th>\n",
       "      <th>label</th>\n",
       "      <th>count</th>\n",
       "      <th>coef</th>\n",
       "      <th>week</th>\n",
       "      <th>doy</th>\n",
       "      <th>year</th>\n",
       "      <th>color</th>\n",
       "      <th>image</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-07-05</td>\n",
       "      <td>MRD</td>\n",
       "      <td>MRD1</td>\n",
       "      <td>('2016-07-05', 'MRD1')</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>EasyGel</td>\n",
       "      <td>Bioindicator</td>\n",
       "      <td>0.0</td>\n",
       "      <td>250</td>\n",
       "      <td>27</td>\n",
       "      <td>187</td>\n",
       "      <td>2016</td>\n",
       "      <td>big_blue</td>\n",
       "      <td>none</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-07-12</td>\n",
       "      <td>MRD</td>\n",
       "      <td>MRD1</td>\n",
       "      <td>('2016-07-12', 'MRD1')</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>EasyGel</td>\n",
       "      <td>Bioindicator</td>\n",
       "      <td>22.0</td>\n",
       "      <td>25</td>\n",
       "      <td>28</td>\n",
       "      <td>194</td>\n",
       "      <td>2016</td>\n",
       "      <td>big_blue</td>\n",
       "      <td>none</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-07-19</td>\n",
       "      <td>MRD</td>\n",
       "      <td>MRD1</td>\n",
       "      <td>('2016-07-19', 'MRD1')</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>EasyGel</td>\n",
       "      <td>Bioindicator</td>\n",
       "      <td>8.0</td>\n",
       "      <td>25</td>\n",
       "      <td>29</td>\n",
       "      <td>201</td>\n",
       "      <td>2016</td>\n",
       "      <td>big_blue</td>\n",
       "      <td>none</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-06-21</td>\n",
       "      <td>MRD</td>\n",
       "      <td>MRD1</td>\n",
       "      <td>('2016-06-21', 'MRD1')</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>EasyGel</td>\n",
       "      <td>Bioindicator</td>\n",
       "      <td>2.0</td>\n",
       "      <td>100</td>\n",
       "      <td>25</td>\n",
       "      <td>173</td>\n",
       "      <td>2016</td>\n",
       "      <td>big_blue</td>\n",
       "      <td>none</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-06-28</td>\n",
       "      <td>MRD</td>\n",
       "      <td>MRD1</td>\n",
       "      <td>('2016-06-28', 'MRD1')</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>EasyGel</td>\n",
       "      <td>Bioindicator</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25</td>\n",
       "      <td>26</td>\n",
       "      <td>180</td>\n",
       "      <td>2016</td>\n",
       "      <td>big_blue</td>\n",
       "      <td>none</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date location sample             date_sample  event  before event  \\\n",
       "0 2016-07-05      MRD   MRD1  ('2016-07-05', 'MRD1')   True         False   \n",
       "1 2016-07-12      MRD   MRD1  ('2016-07-12', 'MRD1')   True         False   \n",
       "2 2016-07-19      MRD   MRD1  ('2016-07-19', 'MRD1')  False         False   \n",
       "3 2016-06-21      MRD   MRD1  ('2016-06-21', 'MRD1')  False          True   \n",
       "4 2016-06-28      MRD   MRD1  ('2016-06-28', 'MRD1')  False          True   \n",
       "\n",
       "   after event   medium         label  count  coef  week  doy  year     color  \\\n",
       "0        False  EasyGel  Bioindicator    0.0   250    27  187  2016  big_blue   \n",
       "1        False  EasyGel  Bioindicator   22.0    25    28  194  2016  big_blue   \n",
       "2         True  EasyGel  Bioindicator    8.0    25    29  201  2016  big_blue   \n",
       "3        False  EasyGel  Bioindicator    2.0   100    25  173  2016  big_blue   \n",
       "4        False  EasyGel  Bioindicator    0.0    25    26  180  2016  big_blue   \n",
       "\n",
       "  image  \n",
       "0  none  \n",
       "1  none  \n",
       "2  none  \n",
       "3  none  \n",
       "4  none  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stddf = pd.read_csv(\"data/end/2016_2023_MRD_VNX_SVT.csv\")\n",
    "stddf['date'] = pd.to_datetime(stddf[\"date\"])\n",
    "\n",
    "stddf.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ccce7bd1-732a-449c-aeb1-dba99f22a830",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "### Current data to process\n",
    "\n",
    "None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a6702e71-8d42-4ff3-bff4-20330c5d1ed5",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": [
     "remove-input"
    ]
   },
   "outputs": [],
   "source": [
    "# new_df16 = pd.read_csv(\"data/componentdata/2016_Data.csv\")\n",
    "\n",
    "# colors_2016 = [\n",
    "#     'P1_24h_big_blue', 'P1_24h_med_blue',\n",
    "#     'P1_24h_other', 'P1_24h_pink', 'P1_24h_turq', 'P1_qty_sample',\n",
    "#     'P2_24h_big_blue', 'P2_24h_med_blue', 'P2_24h_other', 'P2_24h_pink',\n",
    "#     'P2_24h_turq', 'P3_24h_big_blue', 'P3_24h_med_blue', 'P3_24h_other',\n",
    "#     'P3_24h_pink', 'P3_24h_turq']\n",
    "# colors_slug = {x:f'{x[1:3]}{x[7:]}' for x in colors_2016}\n",
    "\n",
    "# df16 = new_df16.rename(columns=colors_slug)\n",
    "# df16.drop(\"Unnamed: 0\", axis=1, inplace=True)\n",
    "# df16 = pd.melt(df16, value_vars=colors_slug.values(), id_vars=[\"Date\", \"Location\"])\n",
    "# df16[\"plate\"] = df16.variable.apply(lambda x: x[0])\n",
    "# df16[\"sample\"] = df16.Location + df16.plate\n",
    "# df16[\"colour\"] = df16.variable.apply(lambda x: x[2:])\n",
    "# df16.drop(\"variable\", inplace=True, axis=1)\n",
    "# df16.rename(columns={\"value\":\"count\", \"Date\":\"date\", \"Location\":\"location\"}, inplace=True)\n",
    "# df16[\"date\"] = pd.to_datetime(df16[\"date\"])\n",
    "# df16[\"image\"] = None\n",
    "# df16[\"coef\"] = 100"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4408e1cf-84a2-4dba-93bb-2f023f9dcd3e",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "The data from 2017 will require quite a bit of formatting:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b161a56d-e2b2-47c2-8493-b2fbe1dd45e9",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": [
     "remove-input"
    ]
   },
   "outputs": [],
   "source": [
    "# new_df17 = pd.read_csv(\"data/componentdata/2017_Data.csv\")\n",
    "# new_df17.rename(columns={\"p3_fluo_halo_colonies\":'P3_fluo_halo_colonies', 'p3_fluo_other':'P3_fluo_other'}, inplace=True)\n",
    "# new_df17.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e92614d3-183f-4daf-97a5-a73e7c1e4c55",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": [
     "remove-input"
    ]
   },
   "outputs": [],
   "source": [
    "\n",
    "# columns_d17 = [\n",
    "#     'P1_fluo_halo_colonies', \n",
    "#     'P1_fluo_other',\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', \n",
    "#     'P2_qty_sample',\n",
    "#     'P2_fluo_halo_colonies',\n",
    "#     'P2_fluo_other',\n",
    "#     'P2_48h_big_blue',\n",
    "#     'P2_48h_med_blue',\n",
    "#     'P2_48h_green',\n",
    "#     'P2_48h_turq', \n",
    "#     'P2_48h_pink',\n",
    "#     'P2_48h_other', \n",
    "#     'p3_fluo_halo_colonies',\n",
    "#     'p3_fluo_other',\n",
    "#     'P3_48h_big_blue',\n",
    "#     'P3_48h_med_blue',\n",
    "#     'P3_48h_green',\n",
    "#     'P3_48h_turq',\n",
    "#     'P3_48h_pink',\n",
    "#     'P3_48h_other',\n",
    "# ]\n",
    "\n",
    "# ad_cols = ['date', 'location', 'medium', 'Samples', 'P1_qty_sample', 'P3_qty_sample', 'P2_qty_sample']\n",
    "\n",
    "# plate_one = [\n",
    "#     'P1_fluo_halo_colonies', \n",
    "#     'P1_fluo_other',\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',\n",
    "#     'P1_qty_sample',\n",
    "#     'Plate_one_48h_image',\n",
    "#     'date',\n",
    "#     'location',\n",
    "#     'medium'\n",
    "# ]\n",
    "\n",
    "# plate_two = [*[f'P2_{x[3:]}' for x in plate_one[:-4]], 'Plate_two_48h_image', *plate_one[-3:]]\n",
    "# plate_three = [*[f'P3_{x[3:]}' for x in plate_one[:-4]], 'Plate_three_48h_image', *plate_one[-3:]]\n",
    "    \n",
    "\n",
    "# df17 = new_df17.copy()\n",
    "# df17 = df17[df17.Location.isin([\"MRD\", \"SVT\", \"VNX\"])]\n",
    "# df17.rename(columns={\"Date\":\"date\", \"Location\":\"location\"}, inplace=True)\n",
    "# df17[\"date\"] = pd.to_datetime(df17[\"date\"], format=\"%d.%m.%y\")\n",
    "# df17[\"doy\"] = df17[\"date\"].dt.dayofyear\n",
    "# df17[\"week\"] = df17[\"date\"].dt.isocalendar().week\n",
    "# df17[\"year\"] = df17[\"date\"].dt.year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "82e1220a-edd6-45d2-b87e-cc08c510f7a5",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": [
     "remove-input"
    ]
   },
   "outputs": [],
   "source": [
    "# data2 = df17[[*plate_two, \"doy\", \"week\", \"year\"]].copy()\n",
    "\n",
    "# plate_number = \"P2\"\n",
    "# image_name = \"Plate_two_48h_image\"\n",
    "# sample_quantity = \"P2_qty_sample\"\n",
    "\n",
    "# def make_new_dfs(data, plate_number, image_name, sample_quantity):\n",
    "#     new_col_names = {x:x[7:] for x in data.columns[2:8]}\n",
    "#     data.rename(columns={f\"{plate_number}_fluo_halo_colonies\":\"fluo_halo\", f\"{plate_number}_fluo_other\":\"fluo_other\"}, inplace=True)\n",
    "#     data.rename(columns=new_col_names, inplace=True)\n",
    "#     data.rename(columns={image_name: \"image\"}, inplace=True)\n",
    "#     data.rename(columns={sample_quantity:\"coef\"}, inplace=True)\n",
    "    \n",
    "#     return data\n",
    "# p_two = make_new_dfs(data2, plate_number, image_name, sample_quantity)\n",
    "# p_two[\"plate\"] = \"2\"\n",
    "\n",
    "# data1 = df17[[*plate_one, \"doy\", \"week\", \"year\"]].copy()\n",
    "\n",
    "# plate_number = \"P1\"\n",
    "# image_name = \"Plate_one_48h_image\"\n",
    "# sample_quantity = \"P1_qty_sample\"\n",
    "\n",
    "# p_one = make_new_dfs(data1, plate_number, image_name, sample_quantity)\n",
    "# p_one[\"plate\"] = \"1\"\n",
    "\n",
    "# data3 = df17[[*plate_three, \"doy\", \"week\", \"year\"]].copy()\n",
    "\n",
    "# plate_number = \"P3\"\n",
    "# image_name = \"Plate_three_48h_image\"\n",
    "# sample_quantity = \"P3_qty_sample\"\n",
    "\n",
    "# p_three =  make_new_dfs(data3, plate_number, image_name, sample_quantity)\n",
    "# p_three[\"plate\"] = \"3\"\n",
    "# df2017 = pd.concat([p_one, p_two, p_three])\n",
    "# df2017 = df2017.dropna()\n",
    "# df2017 = pd.melt(df2017, id_vars=[\"coef\", \"image\", \"date\", \"plate\", \"location\", \"medium\", \"doy\", \"week\", \"year\"])\n",
    "# df2017[\"sample\"] = df2017[\"location\"] + df2017[\"plate\"]\n",
    "# df2017.rename(columns={\"variable\":\"color\", \"value\":\"count\"}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6b81e7b6-a60e-425a-b62d-554c73b49c28",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": [
     "remove-input"
    ]
   },
   "outputs": [],
   "source": [
    "# k = df17.P2_qty_sample.values\n",
    "# ki = [x[0] for x in k]\n",
    "# df17 = df17.drop(\"P2_qty_sample\", axis=1)\n",
    "# df17[\"P2_qty_sample\"] = ki\n",
    "# df17[df17.P2_qty_sample != 4]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27d25acf-642a-425b-a3ad-af22a55d473f",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "### Applying labels\n",
    "\n",
    "The colors that were used for the observations can be placed into three broad categories. \n",
    "\n",
    "1. Bioindicator\n",
    "2. Coliforms\n",
    "3. Other\n",
    "\n",
    "The microbiologist determines the correct label for the recorded color based on the specifics of the media/medium used to grow the culture.\n",
    "\n",
    "The colors appropriate to each label are stored in an array. The color for each record is tested for membership in one of the arrays. If it is in one of the arrays, the name of that array is returned. If the color is not in any array the original value is returned. The result is added to the data-frame.\n",
    "\n",
    "```python\n",
    "bioindicators = [\"Dark Blue\", \"Blue\", \"Turquoise fast\", \"metallic_green\", \"green_met\", \"fluo_halo\", \"big_blue\"]\n",
    "coliforms = [\"Pink\", \"pink\", \"purple\", \"med_blue\"]\n",
    "other = [\"Turquoise\", \"Turquoise slow\", \"other\", \"mauve\", \"fluo_other\", \"green\"]\n",
    "\n",
    "def translate_colors(x, bioindicators, coliforms, other):\n",
    "    if x in bioindicators:\n",
    "        return \"Bioindicator\"\n",
    "    elif x in coliforms:\n",
    "        return \"Coliform\"\n",
    "    elif x in other:\n",
    "        return \"Other\"\n",
    "    else:\n",
    "        return x\n",
    "\n",
    "stddf [\"label\"] = stddf .color.apply(lambda x: translate_colors(x, bioindicators, coliforms, other))\n",
    "```\n",
    "\n",
    "We do the same for the media/medium except we use a dictionary to store that information\n",
    "\n",
    "```python\n",
    "media_names =  {\n",
    "    \"ECC-A Card\":\"ECC-A\",\n",
    "    \"new ECCA\":\"ECC-A\",\n",
    "    \"E-coli side\": \"E coli\",\n",
    "    \"Double side E coli\": \"E coli\",\n",
    "    \"ECC-side\":\"ECC\",\n",
    "    \"Double side ECC\":\"ECC\",\n",
    "    \"selective\":\"Levine\",\n",
    "    \"media\":\"EasyGel\",\n",
    "    \"plus uv\":\"EasyGelPlus\",\n",
    "    \"UVplus\":\"EasyGelPlus\",\n",
    "    \"non-restrictive\":\"LB\",\n",
    "    \"levine\": \"Levine\",\n",
    "    \"easy_gel\":\"EasyGel\",\n",
    "    \"unil_kitchen\":\"LB\",\n",
    "    \"micrology_card\": \"ECC\"\n",
    "}\n",
    "\n",
    "def translate_media(x, media_names):\n",
    "    if x in media_names.keys():\n",
    "        return media_names[x]\n",
    "    else:\n",
    "        return x\n",
    "\n",
    "\n",
    "stddf [\"medium\"] = stddf .media.apply(lambda x: translate_media(x, media_names))\n",
    "\n",
    "```\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff8df02f-d3f2-4c12-b278-64408f35a2d4",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "### Labeling the date range of interest\n",
    "\n",
    "Voici les dates de Jazz pour toutes les années de prélèvement :\n",
    "\n",
    "* 2016:  2016-07-01 - 2016-07-16\n",
    "* 2017: 2017-06-30 - 2017-07-15\n",
    "* 2020: 2020-07-03 - 2020-07-18\n",
    "* 2022: 2022-07-01 - 2022-07-16\n",
    "* 2023: 2023-06-30 - 2023-07-15\n",
    "\n",
    "__before event:__ samples before the begining of the event of interest\n",
    "\n",
    "__after event:__ samples after the end of the event"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "1c8effb7-ab5a-48cc-aba1-6ed2d8a6c4dd",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": [
     "remove-input"
    ]
   },
   "outputs": [],
   "source": [
    "# mask the date ranges\n",
    "import datetime as dt\n",
    "\n",
    "def make_date_object(x):\n",
    "    return dt.datetime.strptime(x, \"%Y-%m-%d\")\n",
    "\n",
    "# # stddf[\"before event\"] = False\n",
    "# # stddf[\"after event\"] = False\n",
    "# df[\"event\"] = False\n",
    "# event_mask = (df['date'] >= pd.Timestamp(\"2020-07-01\")) & (df['date'] > pd.Timestamp(\"2020-07-16\"))\n",
    "# df.loc[event_mask, \"event\"] = True\n",
    "\n",
    "# df[\"before event\"] = False\n",
    "# df[\"after event\"] = False\n",
    "\n",
    "# df.loc[ (df['date'] < pd.Timestamp(\"2020-07-01\")), \"before event\"] = True\n",
    "# df.loc[ (df['date'] > pd.Timestamp(\"2020-07-16\")), \"after event\"] = True\n",
    "\n",
    "# stddf[\"year\"] = stddf[\"year\"].astype(\"str\")\n",
    "# stddf.rename(columns={\"isjazz\": \"event\"}, inplace=True)\n",
    "\n",
    "# stddf.loc[(stddf[\"year\"] == \"2022\") & (stddf['date'] < pd.Timestamp(\"2022-07-01\")), \"before event\"] = True\n",
    "# stddf.loc[(stddf[\"year\"] == \"2023\") & (stddf['date'] < pd.Timestamp(\"2023-06-30\")), \"before event\"] = True\n",
    "# stddf.loc[(stddf[\"year\"] == \"2022\") & (stddf['date'] > pd.Timestamp(\"2022-07-16\")), \"after event\"] = True\n",
    "# stddf.loc[(stddf[\"year\"] == \"2023\") & (stddf['date'] > pd.Timestamp(\"2023-07-15\")), \"after event\"] = True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "e4387571-78ae-46e6-a7eb-78e443d47fd3",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": [
     "remove-input"
    ]
   },
   "outputs": [],
   "source": [
    "# df2017[\"before event\"] = False\n",
    "# df2017[\"after event\"] = False\n",
    "# df2017[\"event\"] = False\n",
    "# event_mask = (df2017['date'] >= pd.Timestamp(\"2017-06-30\")) & (df2017['date'] <= pd.Timestamp(\"2017-07-15\"))\n",
    "# df2017.loc[event_mask, \"event\"] = True\n",
    "\n",
    "# df2017.loc[ (df2017['date'] < pd.Timestamp(\"2017-06-30\")), \"before event\"] = True\n",
    "# df2017.loc[ (df2017['date'] > pd.Timestamp(\"2017-07-15\")), \"after event\"] = True\n",
    "# df2017.rename(columns={\"variable\":\"color\", \"value\":\"count\"}, inplace=True)\n",
    "# df2017.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "06f7c489-4d99-44a1-bc46-5b1d29fa39f9",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": [
     "remove-input"
    ]
   },
   "outputs": [],
   "source": [
    "# df2017[\"d\"] = df2017[\"date\"].dt.strftime('%Y-%m-%d')\n",
    "# df2017[\"date_sample\"] = list(zip(df2017[\"d\"], df2017[\"sample\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "00d90774-4cf3-4fd0-928d-f3e0d26a3df7",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": [
     "remove-input"
    ]
   },
   "outputs": [],
   "source": [
    "# translate colors\n",
    "def translate_colors(x, bioindicators, coliforms, other):\n",
    "    if x in bioindicators:\n",
    "        return \"Bioindicator\"\n",
    "    elif x in coliforms:\n",
    "        return \"Coliform\"\n",
    "    elif x in other:\n",
    "        return \"Other\"\n",
    "    else:\n",
    "        return x\n",
    "\n",
    "bioindicators = [\"Dark Blue\", \"Blue\", \"Turquoise fast\", \"metallic_green\", \"green_met\", \"fluo_halo\", \"big_blue\"]\n",
    "coliforms = [\"Pink\", \"pink\", \"purple\", \"med_blue\"]\n",
    "other = [\"Turquoise\", \"Turquoise slow\", \"other\", \"mauve\", \"fluo_other\", \"green\", \"turq\", 'sample']\n",
    "\n",
    "# df[\"label\"] = df.color.apply(lambda x: translate_colors(x, bioindicators, coliforms, other))\n",
    "\n",
    "def translate_media(x, media_names):\n",
    "    if x in media_names.keys():\n",
    "        return media_names[x]\n",
    "    else:\n",
    "        return x\n",
    "\n",
    "media_names =  {\n",
    "\"ECC-A Card\":\"ECC-A\",\n",
    "\"new ECCA\":\"ECC-A\",\n",
    "\"E-coli side\": \"E coli\",\n",
    "\"Double side E coli\": \"E coli\",\n",
    "\"ECC-side\":\"ECC\",\n",
    "\"Double side ECC\":\"ECC\",\n",
    "\"selective\":\"Levine\",\n",
    "\"media\":\"EasyGel\",\n",
    "\"plus uv\":\"EasyGelPlus\",\n",
    "\"UVplus\":\"EasyGelPlus\",\n",
    "\"non-restrictive\":\"LB\",\n",
    "\"levine\": \"Levine\",\n",
    "\"easy_gel\":\"EasyGel\",\n",
    "\"unil_kitchen\":\"LB\",\n",
    "\"micrology_card\": \"ECC\"\n",
    "}\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c7e3a4e-89ef-4bf4-a5a0-a07c51344261",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "# Rain fall\n",
    "\n",
    "Expected format of rain data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "3d2aa36f-1191-44f4-9668-abb489f89a63",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": [
     "remove-input"
    ]
   },
   "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>mm</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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date    mm\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"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_data = pd.read_csv(\"data/end/rain_data_2016.csv\")\n",
    "sample_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "47bd05d6-f059-4bef-ad7d-28f58b92643c",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": [
     "remove-input"
    ]
   },
   "outputs": [],
   "source": [
    "merge_columns = [\n",
    "    \"date\",\n",
    "    \"location\",\n",
    "    \"sample\",\n",
    "    \"date_sample\",\n",
    "    \"event\",\n",
    "    \"before event\",\n",
    "    \"after event\",\n",
    "    \"medium\",\n",
    "    \"label\",\n",
    "    \"count\",\n",
    "    \"coef\",\n",
    "    \"week\",\n",
    "    \"doy\",\n",
    "    \"year\",\n",
    "    \"color\",\n",
    "    \"image\",\n",
    "    \n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "28dca34d-38e0-43c7-a4a2-fb48774cd50d",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8cdd006-c719-4a2c-94ba-b68a58ef8192",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.17"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}