data-exploration.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data exploration of data in timestream db\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Initialization\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Imports\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-06-21T07:21:43.316637Z",
     "iopub.status.busy": "2022-06-21T07:21:43.315931Z",
     "iopub.status.idle": "2022-06-21T07:21:44.852819Z",
     "shell.execute_reply": "2022-06-21T07:21:44.852143Z"
    }
   },
   "outputs": [],
   "source": [
    "import datetime\n",
    "from itertools import repeat\n",
    "from os import makedirs\n",
    "from typing import Optional, Sequence\n",
    "\n",
    "import awswrangler as wr\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import plotly.graph_objects as go\n",
    "import scipy.signal as signal\n",
    "from dotenv import load_dotenv\n",
    "from numpy.polynomial import Polynomial\n",
    "\n",
    "load_dotenv()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data retrieval\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-06-21T07:21:44.892715Z",
     "iopub.status.busy": "2022-06-21T07:21:44.892172Z",
     "iopub.status.idle": "2022-06-21T07:21:47.060044Z",
     "shell.execute_reply": "2022-06-21T07:21:47.058889Z"
    }
   },
   "outputs": [],
   "source": [
    "df: pd.DataFrame = wr.timestream.query('SELECT * FROM \"when-to-water\".\"sensor-data\"')\n",
    "print(f\"Retrieved {len(df)} records\")\n",
    "df_origin = df.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Constants\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-06-21T07:21:47.065369Z",
     "iopub.status.busy": "2022-06-21T07:21:47.064946Z",
     "iopub.status.idle": "2022-06-21T07:21:47.070139Z",
     "shell.execute_reply": "2022-06-21T07:21:47.069195Z"
    }
   },
   "outputs": [],
   "source": [
    "SENSOR_PLANT_MAPPING: dict = {\n",
    "    \"PWS_1\": \"Goldfruchtpalme\",\n",
    "    \"PWS_2\": \"Pilea\",\n",
    "    \"PWS_3\": \"Drachenbaum\",\n",
    "}\n",
    "PLANTS: tuple[str, ...] = tuple(SENSOR_PLANT_MAPPING.values())\n",
    "COLORS: tuple[str, ...] = (\n",
    "    \"#E69F00\",\n",
    "    \"#56B4E9\",\n",
    "    \"#009E73\",\n",
    "    \"#F0E442\",\n",
    "    \"#0072B2\",\n",
    "    \"#D55E00\",\n",
    "    \"#CC79A7\",\n",
    ")\n",
    "MINIMUM_MOISTURE: dict = {\n",
    "    \"Goldfruchtpalme\": 10,\n",
    "    \"Pilea\": 20,\n",
    "    \"Drachenbaum\": 30,\n",
    "}\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transformations\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### General\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-06-21T07:21:47.074561Z",
     "iopub.status.busy": "2022-06-21T07:21:47.074161Z",
     "iopub.status.idle": "2022-06-21T07:21:47.190779Z",
     "shell.execute_reply": "2022-06-21T07:21:47.189929Z"
    }
   },
   "outputs": [],
   "source": [
    "# set types and rename\n",
    "df[\"time\"] = pd.to_datetime(df[\"time\"])\n",
    "df.rename(\n",
    "    columns={\"measure_value::double\": \"value\", \"sensor_name\": \"plant\"}, inplace=True\n",
    ")\n",
    "df[\"value\"] = df[\"value\"].astype(float)\n",
    "\n",
    "df[\"plant\"] = df[\"plant\"].map(SENSOR_PLANT_MAPPING)\n",
    "\n",
    "# drop n/a rows\n",
    "df.dropna(inplace=True)\n",
    "\n",
    "# drop power\n",
    "df = df[df[\"measure_name\"] != \"power\"]\n",
    "\n",
    "# add unit to measurement name\n",
    "df[\"measure_name\"] = df[\"measure_name\"].str.replace(\"_\", \" \") + \" in \" + df[\"unit\"]\n",
    "measurements = df[\"measure_name\"].unique().tolist()\n",
    "\n",
    "# drop unit\n",
    "df.drop(columns=[\"unit\"], inplace=True)\n",
    "\n",
    "# remove 0 moisture\n",
    "df = df[~((df[\"measure_name\"] == \"soil moisture in %\") & (df[\"value\"] == 0))]\n",
    "\n",
    "# Resample df to hourly measures\n",
    "df.set_index(\"time\", inplace=True)\n",
    "df = df.groupby([\"plant\", \"measure_name\"]).resample(\"H\").mean().reset_index()\n",
    "\n",
    "# transpose measure_name \n",
    "df.set_index([\"time\", \"plant\", \"measure_name\"], inplace=True)\n",
    "df = df.unstack().reset_index()\n",
    "df.columns = [\" \".join(col).strip().replace(\"value \", \"\") for col in df.columns.values]\n",
    "df.reset_index(drop=True, inplace=True)\n",
    "\n",
    "# interpolate missing values\n",
    "df.set_index(\"time\", inplace=True)\n",
    "for plant in PLANTS:\n",
    "    df[df[\"plant\"] == plant] = df[df[\"plant\"] == plant].interpolate(method=\"time\")\n",
    "\n",
    "df.reset_index(inplace=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Trasformed data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "transformed_figure = go.Figure(\n",
    "    layout=go.Layout(\n",
    "        title=\"Transformed values\",\n",
    "        width=1280,\n",
    "        height=720,\n",
    "        template=\"plotly_white\",\n",
    "        font=dict(family=\"Times New Roman\", color=\"#000000\"),\n",
    "    )\n",
    ")\n",
    "\n",
    "for index, plant in enumerate(PLANTS):\n",
    "    transformed_figure.add_trace(\n",
    "        go.Scatter(\n",
    "            x=df[df[\"plant\"] == plant][\"time\"],\n",
    "            y=df[df[\"plant\"] == plant][\"soil moisture in %\"],\n",
    "            mode=\"lines\",\n",
    "            name=plant,\n",
    "            opacity=0.7,\n",
    "            marker=dict(color=COLORS[index], size=3),\n",
    "        )\n",
    "    )\n",
    "\n",
    "transformed_figure.update_scenes(\n",
    "    dict(\n",
    "        xaxis_title=\"Time\",\n",
    "        yaxis_title=\"soil moisture in %\",\n",
    "    )\n",
    ")\n",
    "\n",
    "transformed_figure.update_layout(legend=dict(\n",
    "    orientation=\"h\",\n",
    "    yanchor=\"bottom\",\n",
    "    y=1,\n",
    "    xanchor=\"center\",\n",
    "    x=0.5,\n",
    "    itemsizing='constant'\n",
    "))\n",
    "\n",
    "makedirs(\"./dist\", exist_ok=True)\n",
    "transformed_figure.write_html(\"./dist/1_transformed.html\", include_plotlyjs=\"directory\", full_html=True)\n",
    "\n",
    "transformed_figure.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Identify valleys/peaks in moisture\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-06-21T07:21:47.196188Z",
     "iopub.status.busy": "2022-06-21T07:21:47.195520Z",
     "iopub.status.idle": "2022-06-21T07:21:47.208780Z",
     "shell.execute_reply": "2022-06-21T07:21:47.208015Z"
    }
   },
   "outputs": [],
   "source": [
    "DISTANCE = 3\n",
    "PROMINENCE = 2\n",
    "\n",
    "all_peaks: list = []\n",
    "all_valleys: list = []\n",
    "\n",
    "for plant in PLANTS:\n",
    "    df_plant = df[df[\"plant\"] == plant]\n",
    "    peaks = signal.find_peaks(\n",
    "        df_plant[\"soil moisture in %\"],\n",
    "        distance=DISTANCE,\n",
    "        prominence=PROMINENCE,\n",
    "    )[0]\n",
    "    valleys = signal.find_peaks(\n",
    "        -df_plant[\"soil moisture in %\"],\n",
    "        distance=DISTANCE,\n",
    "        prominence=PROMINENCE,\n",
    "    )[0]\n",
    "    # translate row to index\n",
    "    all_peaks += [df_plant.index[peak] for peak in peaks]\n",
    "    all_valleys += [df_plant.index[valley] for valley in valleys]\n",
    "\n",
    "df[\"peak\"] = df.index.isin(all_peaks)\n",
    "df[\"valley\"] = df.index.isin(all_valleys)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Plot for valleys/peaks\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "peak_valley_figure = go.Figure(\n",
    "    layout=go.Layout(\n",
    "        title=\"Peaks/Valleys\",\n",
    "        width=1280,\n",
    "        height=720,\n",
    "        template=\"plotly_white\",\n",
    "        font=dict(family=\"Times New Roman\", color=\"#000000\"),\n",
    "    )\n",
    ")\n",
    "\n",
    "for index, plant in enumerate(PLANTS):\n",
    "    peak_valley_figure.add_trace(\n",
    "        go.Scatter(\n",
    "            x=df[df[\"plant\"] == plant][\"time\"],\n",
    "            y=df[df[\"plant\"] == plant][\"soil moisture in %\"],\n",
    "            mode=\"lines\",\n",
    "            legendgrouptitle_text=plant,\n",
    "            legendgroup=plant,\n",
    "            name=\"values\",\n",
    "            opacity=0.7,\n",
    "            marker=dict(color=COLORS[index], size=3),\n",
    "        )\n",
    "    )\n",
    "    peak_valley_figure.add_trace(\n",
    "        go.Scatter(\n",
    "            x=df[(df[\"plant\"] == plant) & (df[\"peak\"] == True)][\"time\"],\n",
    "            y=df[(df[\"plant\"] == plant) & (df[\"peak\"] == True)][\"soil moisture in %\"],\n",
    "            mode=\"markers\",\n",
    "            legendgroup=plant,\n",
    "            name=\"peaks\",\n",
    "            opacity=1,\n",
    "            marker=dict(color=COLORS[index], size=16, symbol=\"arrow-bar-down\"),\n",
    "        )\n",
    "    )\n",
    "    peak_valley_figure.add_trace(\n",
    "        go.Scatter(\n",
    "            x=df[(df[\"plant\"] == plant) & (df[\"valley\"] == True)][\"time\"],\n",
    "            y=df[(df[\"plant\"] == plant) & (df[\"valley\"] == True)][\"soil moisture in %\"],\n",
    "            mode=\"markers\",\n",
    "            legendgroup=plant,\n",
    "            name=\"valleys\",\n",
    "            opacity=1,\n",
    "            marker=dict(color=COLORS[index], size=16, symbol=\"arrow-bar-up\"),\n",
    "        )\n",
    "    )\n",
    "\n",
    "peak_valley_figure.update_scenes(\n",
    "    dict(\n",
    "        xaxis_title=\"Time\",\n",
    "        yaxis_title=\"soil moisture in %\",\n",
    "    )\n",
    ")\n",
    "\n",
    "peak_valley_figure.update_layout(legend=dict(\n",
    "    orientation=\"h\",\n",
    "    yanchor=\"bottom\",\n",
    "    y=1,\n",
    "    xanchor=\"center\",\n",
    "    x=0.5,\n",
    "    itemsizing='constant'\n",
    "))\n",
    "\n",
    "makedirs(\"./dist\", exist_ok=True)\n",
    "peak_valley_figure.write_html(\"./dist/2_peak_valley.html\", include_plotlyjs=\"directory\", full_html=True)\n",
    "\n",
    "peak_valley_figure.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pick descends and normalize soil moisture\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-06-21T07:21:47.214125Z",
     "iopub.status.busy": "2022-06-21T07:21:47.213643Z",
     "iopub.status.idle": "2022-06-21T07:21:47.370418Z",
     "shell.execute_reply": "2022-06-21T07:21:47.369547Z"
    }
   },
   "outputs": [],
   "source": [
    "decending_dfs: list[pd.DataFrame] = []\n",
    "for plant in PLANTS:\n",
    "    last_peak = -1\n",
    "    last_valley = -1\n",
    "    df_plant = df[df[\"plant\"] == plant].copy()\n",
    "    df_plant.reset_index(drop=True, inplace=True)\n",
    "    for row in df_plant.itertuples():\n",
    "        if row.peak:\n",
    "            last_peak = row.Index\n",
    "        if row.valley:\n",
    "            last_valley = row.Index\n",
    "            if last_peak > -1 and last_peak < last_valley:\n",
    "                df_candidate = df_plant.iloc[last_peak:last_valley].copy()\n",
    "                mininmum_dt = df_candidate[\"time\"].min()\n",
    "                # offset in days\n",
    "                df_candidate[\"offset\"] = (\n",
    "                    (df_candidate[\"time\"] - mininmum_dt).dt.total_seconds() / 3600 / 24\n",
    "                )\n",
    "                if (\n",
    "                    df_candidate.iloc[0][\"soil moisture in %\"]\n",
    "                    < df_candidate.iloc[-1][\"soil moisture in %\"]\n",
    "                ):\n",
    "                    continue\n",
    "\n",
    "                if df_candidate.empty:\n",
    "                    continue\n",
    "\n",
    "                # normalize\n",
    "                df_candidate[\"soil moisture in %\"] = df_candidate[\n",
    "                    \"soil moisture in %\"\n",
    "                ] + (100 - df_candidate[\"soil moisture in %\"].max())\n",
    "\n",
    "                decending_dfs.append(df_candidate)\n",
    "\n",
    "    all_decending_dfs = pd.concat(decending_dfs)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Regression\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-06-21T07:21:47.375269Z",
     "iopub.status.busy": "2022-06-21T07:21:47.374828Z",
     "iopub.status.idle": "2022-06-21T07:21:47.389040Z",
     "shell.execute_reply": "2022-06-21T07:21:47.388213Z"
    }
   },
   "outputs": [],
   "source": [
    "polyfits: dict = {}\n",
    "for plant in PLANTS:\n",
    "    polyfits[plant] = Polynomial.fit(\n",
    "        all_decending_dfs[all_decending_dfs[\"plant\"] == plant][\"offset\"],\n",
    "        all_decending_dfs[all_decending_dfs[\"plant\"] == plant][\"soil moisture in %\"],\n",
    "        1,\n",
    "    )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Plot regression\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-06-21T07:21:47.394056Z",
     "iopub.status.busy": "2022-06-21T07:21:47.393601Z",
     "iopub.status.idle": "2022-06-21T07:21:47.871769Z",
     "shell.execute_reply": "2022-06-21T07:21:47.870822Z"
    }
   },
   "outputs": [],
   "source": [
    "regression_figure = go.Figure(\n",
    "    layout=go.Layout(\n",
    "        title=\"Moisture Regression\",\n",
    "        width=1280,\n",
    "        height=720,\n",
    "        template=\"plotly_white\",\n",
    "        font=dict(family=\"Times New Roman\", color=\"#000000\"),\n",
    "    )\n",
    ")\n",
    "\n",
    "for index, plant in enumerate(PLANTS):\n",
    "    regression_figure.add_trace(\n",
    "        go.Scatter(\n",
    "            x=all_decending_dfs[all_decending_dfs[\"plant\"] == plant][\"offset\"],\n",
    "            y=all_decending_dfs[all_decending_dfs[\"plant\"] == plant][\n",
    "                \"soil moisture in %\"\n",
    "            ],\n",
    "            mode=\"markers\",\n",
    "            name=plant,\n",
    "            opacity=0.5,\n",
    "            marker=dict(color=COLORS[index], size=3),\n",
    "        )\n",
    "    )\n",
    "    poly_x, poly_y = polyfits[plant].linspace(100)\n",
    "    regression_figure.add_trace(\n",
    "        go.Scatter(\n",
    "            x=poly_x,\n",
    "            y=poly_y,\n",
    "            mode=\"lines\",\n",
    "            name=f\"regression {plant}\",\n",
    "            opacity=1,\n",
    "            line=dict(color=COLORS[index], width=2, dash=\"longdash\"),\n",
    "        )\n",
    "    )\n",
    "\n",
    "regression_figure.update_scenes(\n",
    "    dict(\n",
    "        xaxis_title=\"Time after last watering in days\",\n",
    "        yaxis_title=\"soil moisture in %\",\n",
    "    )\n",
    ")\n",
    "\n",
    "regression_figure.update_layout(\n",
    "    legend=dict(\n",
    "        orientation=\"h\",\n",
    "        yanchor=\"bottom\",\n",
    "        y=1,\n",
    "        xanchor=\"center\",\n",
    "        x=0.5,\n",
    "        itemsizing='constant',\n",
    "    )\n",
    ")\n",
    "\n",
    "makedirs(\"./dist\", exist_ok=True)\n",
    "regression_figure.write_html(\"./dist/3_regression.html\", include_plotlyjs=\"directory\", full_html=True)\n",
    "\n",
    "regression_figure.show()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Find next watering time\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for plant in PLANTS:\n",
    "    newest_time = df[df[\"plant\"] == plant][\"time\"].max()\n",
    "    newest_moisture = df[(df[\"plant\"] == plant) & (df[\"time\"] == newest_time)][\n",
    "        \"soil moisture in %\"\n",
    "    ]\n",
    "    root_current = (polyfits[plant] - newest_moisture).roots()[0]\n",
    "    root_minimum = (polyfits[plant] - MINIMUM_MOISTURE[plant]).roots()[0]\n",
    "    days_after_last_measurement = root_minimum - root_current\n",
    "    days_after_now = days_after_last_measurement - (datetime.datetime.now() - newest_time).total_seconds() / 3600 / 24\n",
    "    print(\n",
    "        f\"{plant} will reach {MINIMUM_MOISTURE[plant]} % moisture in {days_after_now} days\"\n",
    "    )"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.5 ('.venv': pipenv)",
   "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.10.5"
  },
  "vscode": {
   "interpreter": {
    "hash": "ba573d71f289a056abe6419ea7784e90080280ef3a94fc6fe612ae1840708d14"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}