qc_1000_lakes.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime as dt\n",
    "import imp\n",
    "\n",
    "import altair as alt\n",
    "import geopandas as gpd\n",
    "import nivapy3 as nivapy\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "import aquamonitor as am"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "am.host = \"https://test-aquamonitor.niva.no/\"\n",
    "am.aqua_site = \"admin\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Login"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Please enter your Aquamonitor username and password:\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Username:  ···\n",
      "Password:  ··············\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Log-in successful.\n"
     ]
    }
   ],
   "source": [
    "token = am.jhub_login()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Get all Labware \"projects\" for 1000 Lakes Survey\n",
    "\n",
    "Note that a \"project\" in Labware is not the same as a \"project\" in AM."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [404]>\n"
     ]
    },
    {
     "ename": "JSONDecodeError",
     "evalue": "Expecting value: line 1 column 1 (char 0)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mJSONDecodeError\u001b[0m                           Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-4-6de322247e4e>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# Get Labware projects\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mproj_df\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mam\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_labware_projects\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtoken\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'190091;3'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      3\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0mproj_df\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/development/Aquamonitor-Python/aquamonitor.py\u001b[0m in \u001b[0;36mget_labware_projects\u001b[0;34m(token, proj_code)\u001b[0m\n\u001b[1;32m    327\u001b[0m         \u001b[0;34m\"query\"\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0;34m\"query getProjects($nr: String) {projects(projectNr: $nr){name,status,closed}}\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    328\u001b[0m         \"variables\": {\n\u001b[0;32m--> 329\u001b[0;31m             \u001b[0;34m\"nr\"\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mproj_code\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    330\u001b[0m         }\n\u001b[1;32m    331\u001b[0m     })\n",
      "\u001b[0;32m~/development/Aquamonitor-Python/aquamonitor.py\u001b[0m in \u001b[0;36mqueryGraph\u001b[0;34m(token, document)\u001b[0m\n\u001b[1;32m    310\u001b[0m                          cookies=dict(aqua_key=token))\n\u001b[1;32m    311\u001b[0m     \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresp\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 312\u001b[0;31m     \u001b[0;32mreturn\u001b[0m \u001b[0mresp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mjson\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'data'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    313\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    314\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mget_labware_projects\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtoken\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mproj_code\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/lib/python3.7/site-packages/requests/models.py\u001b[0m in \u001b[0;36mjson\u001b[0;34m(self, **kwargs)\u001b[0m\n\u001b[1;32m    896\u001b[0m                     \u001b[0;31m# used.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    897\u001b[0m                     \u001b[0;32mpass\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 898\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mcomplexjson\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mloads\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtext\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    899\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    900\u001b[0m     \u001b[0;34m@\u001b[0m\u001b[0mproperty\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/lib/python3.7/site-packages/simplejson/__init__.py\u001b[0m in \u001b[0;36mloads\u001b[0;34m(s, encoding, cls, object_hook, parse_float, parse_int, parse_constant, object_pairs_hook, use_decimal, **kw)\u001b[0m\n\u001b[1;32m    523\u001b[0m             \u001b[0mparse_constant\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mNone\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mobject_pairs_hook\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    524\u001b[0m             and not use_decimal and not kw):\n\u001b[0;32m--> 525\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0m_default_decoder\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdecode\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0ms\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    526\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0mcls\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    527\u001b[0m         \u001b[0mcls\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mJSONDecoder\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/lib/python3.7/site-packages/simplejson/decoder.py\u001b[0m in \u001b[0;36mdecode\u001b[0;34m(self, s, _w, _PY3)\u001b[0m\n\u001b[1;32m    368\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0m_PY3\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0ms\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mbytes\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    369\u001b[0m             \u001b[0ms\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mstr\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0ms\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mencoding\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 370\u001b[0;31m         \u001b[0mobj\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mend\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraw_decode\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0ms\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    371\u001b[0m         \u001b[0mend\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_w\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0ms\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mend\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    372\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mend\u001b[0m \u001b[0;34m!=\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0ms\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/lib/python3.7/site-packages/simplejson/decoder.py\u001b[0m in \u001b[0;36mraw_decode\u001b[0;34m(self, s, idx, _w, _PY3)\u001b[0m\n\u001b[1;32m    398\u001b[0m             \u001b[0;32melif\u001b[0m \u001b[0mord0\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;36m0xef\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0ms\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0midx\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0midx\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0;36m3\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m'\\xef\\xbb\\xbf'\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    399\u001b[0m                 \u001b[0midx\u001b[0m \u001b[0;34m+=\u001b[0m \u001b[0;36m3\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 400\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mscan_once\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0ms\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0midx\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0m_w\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0ms\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0midx\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mJSONDecodeError\u001b[0m: Expecting value: line 1 column 1 (char 0)"
     ]
    }
   ],
   "source": [
    "# Get Labware projects\n",
    "proj_df = am.get_labware_projects(token, \"190091;3\")\n",
    "\n",
    "proj_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Get all samples for projects "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "samp_df = am.get_labware_project_samples(token, proj_df[\"name\"])\n",
    "\n",
    "samp_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Get results for samples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "res_df = am.get_labware_sample_results(token, samp_df[\"sampleNumber\"])\n",
    "\n",
    "res_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Combine Labware data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Tidy\n",
    "samp_df2 = samp_df[\n",
    "    [\n",
    "        \"sampleNumber\",\n",
    "        \"station_id\",\n",
    "        \"station_name\",\n",
    "        \"station_type\",\n",
    "        \"sampledDate\",\n",
    "        \"sampleDepthUpper\",\n",
    "        \"sampleDepthLower\",\n",
    "    ]\n",
    "]\n",
    "\n",
    "samp_df2.columns = [\n",
    "    \"sample_id\",\n",
    "    \"station_id\",\n",
    "    \"station_name\",\n",
    "    \"station_type\",\n",
    "    \"sample_date\",\n",
    "    \"depth1\",\n",
    "    \"depth2\",\n",
    "]\n",
    "\n",
    "res_df2 = res_df[[\"sample_id\", \"name\", \"status\", \"loq\", \"numericEntry\", \"units\"]]\n",
    "res_df2.columns = [\"sample_id\", \"parameter\", \"status\", \"loq\", \"value\", \"units\"]\n",
    "\n",
    "# Join\n",
    "lw_df = pd.merge(res_df2, samp_df2, how=\"left\", on=\"sample_id\")\n",
    "\n",
    "# Add verbose status codes\n",
    "res_status = pd.read_csv(\"labware_result_status_codes.csv\", sep=\";\")\n",
    "lw_df = pd.merge(lw_df, res_status, how=\"left\", on=\"status\")\n",
    "del lw_df[\"status\"]\n",
    "lw_df.rename({\"description\": \"status\"}, axis=\"columns\", inplace=True)\n",
    "\n",
    "# Tidy\n",
    "lw_df = lw_df[\n",
    "    [\n",
    "        \"sample_id\",\n",
    "        \"station_id\",\n",
    "        \"station_name\",\n",
    "        \"station_type\",\n",
    "        \"sample_date\",\n",
    "        \"depth1\",\n",
    "        \"depth2\",\n",
    "        \"parameter\",\n",
    "        \"status\",\n",
    "        \"loq\",\n",
    "        \"value\",\n",
    "        \"units\",\n",
    "    ]\n",
    "]\n",
    "\n",
    "# Get only surface samples\n",
    "lw_df = lw_df.query(\"(depth1==0) and (depth2==0)\")\n",
    "del lw_df[\"depth1\"], lw_df[\"depth2\"]\n",
    "\n",
    "# Reclassify 'Nitrat' as 'Nitritt + nitrat'\n",
    "lw_df[\"parameter\"].replace({\"Nitrat\": \"Nitritt + nitrat\"}, inplace=True)\n",
    "\n",
    "# Add verbose status codes\n",
    "res_status = pd.read_csv(\"labware_result_status_codes.csv\", sep=\";\")\n",
    "\n",
    "# Drop duplicates\n",
    "lw_df.drop_duplicates(inplace=True)\n",
    "\n",
    "lw_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(lw_df.query('parameter == \"Nitritt + nitrat\"'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Get data from 1995"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Download all data for project\n",
    "excel_file = \"am1000sjoer.xlsx\"\n",
    "am.Query(\"project_id=\" + str(12433), token).makeArchive(\"excel\", excel_file).download(\n",
    "    \"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read downloaded data\n",
    "chem_df = pd.read_excel(\n",
    "    excel_file,\n",
    "    sheet_name=\"WaterChemistry\",\n",
    "    header=[0, 1],\n",
    ")\n",
    "chem_df.columns = chem_df.columns.map(\"_\".join)\n",
    "chem_df.columns = [\n",
    "    i.split(\"_\")[-1] if i[:7] == \"Unnamed\" else i for i in chem_df.columns\n",
    "]\n",
    "\n",
    "# Parse dates, filter to 1995 and tidy\n",
    "chem_df[\"SampleDate\"] = pd.to_datetime(\n",
    "    chem_df[\"SampleDate\"], format=\"%d.%m.%Y %H.%M.%S\"\n",
    ")\n",
    "chem_df = chem_df[chem_df[\"SampleDate\"].dt.year == 1995]\n",
    "chem_df.drop(\n",
    "    [\"ProjectId\", \"ProjectName\", \"StationCode\", \"StationName\", \"SampleDate\"],\n",
    "    axis=1,\n",
    "    inplace=True,\n",
    ")\n",
    "\n",
    "# Restructure\n",
    "chem_df = chem_df.melt(id_vars=[\"StationId\", \"Depth1\", \"Depth2\"], var_name=\"par_unit\")\n",
    "\n",
    "# AM export seems to mix '.' and ',' as the decimal separator\n",
    "chem_df[\"value\"] = chem_df[\"value\"].astype(str).str.replace(\",\", \".\")\n",
    "\n",
    "# Convert LOD to value\n",
    "chem_df[\"value\"] = chem_df[\"value\"].str.strip(\"<\").astype(float)\n",
    "\n",
    "# Average duplicates\n",
    "chem_df = (\n",
    "    chem_df.groupby([\"StationId\", \"Depth1\", \"Depth2\", \"par_unit\"]).mean().reset_index()\n",
    ")\n",
    "\n",
    "chem_df[\"parameter\"], chem_df[\"units\"] = chem_df[\"par_unit\"].str.split(\"_\", 1).str\n",
    "del chem_df[\"par_unit\"]\n",
    "\n",
    "# Tidy\n",
    "chem_df = chem_df[[\"StationId\", \"Depth1\", \"Depth2\", \"parameter\", \"value\", \"units\"]]\n",
    "chem_df.columns = [\"station_id\", \"depth1\", \"depth2\", \"parameter\", \"value\", \"units\"]\n",
    "chem_df.dropna(how=\"any\", inplace=True)\n",
    "\n",
    "# Get only surface samples\n",
    "chem_df = chem_df.query(\"(depth1==0) and (depth2==0)\")\n",
    "del chem_df[\"depth1\"], chem_df[\"depth2\"]\n",
    "\n",
    "# Link labware param names\n",
    "par_map = pd.read_csv(\"am_labware_par_map.csv\", sep=\";\")\n",
    "\n",
    "chem_df = pd.merge(\n",
    "    chem_df, par_map, how=\"left\", left_on=\"parameter\", right_on=\"am_parameter\"\n",
    ")\n",
    "\n",
    "# Convert units\n",
    "chem_df[\"value\"] = chem_df[\"value\"] * chem_df[\"factor\"]\n",
    "\n",
    "# Only keep values that can be matched in Labware\n",
    "del chem_df[\"parameter\"], chem_df[\"am_parameter\"], chem_df[\"factor\"], chem_df[\"units\"]\n",
    "chem_df.dropna(subset=[\"labware_parameter\"], inplace=True)\n",
    "\n",
    "chem_df.rename({\"labware_parameter\": \"parameter\"}, axis=\"columns\", inplace=True)\n",
    "\n",
    "chem_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Link 1995 and 2019 datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Combine\n",
    "df = pd.merge(\n",
    "    lw_df, chem_df, how=\"left\", on=[\"station_id\", \"parameter\"], suffixes=[\"_lw\", \"_am\"]\n",
    ")\n",
    "\n",
    "df.dropna(subset=[\"value_am\", \"value_lw\"], inplace=True)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Additional filtering and processing requested by Atle\n",
    "\n",
    " * **Added 25.11.2019:** Remove Åsmundvatnet as sample is obviously contaminated and it skews the plots. After some checking by Atle, it seems this site is brackish/estuarine."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.query('station_name != \"Åsmundvatnet\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " * **Added 07.01.2019:** Include Ca/Mg ratios for \"Authorised\" values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_list = []\n",
    "\n",
    "for src in [\"lw\", \"am\"]:\n",
    "    # Get authorised Ca and Mg data\n",
    "    df2 = df[[\"sample_id\", \"parameter\", \"status\", f\"value_{src}\"]]\n",
    "    df2 = df2.query(\n",
    "        \"(parameter in ('Kalsium', 'Magnesium')) and (status == 'Authorised')\"\n",
    "    )\n",
    "    del df2[\"status\"]\n",
    "\n",
    "    # Restructure\n",
    "    df2 = df2.groupby([\"sample_id\", \"parameter\"]).mean()\n",
    "    df2 = df2.unstack(\"parameter\")\n",
    "    df2.columns = [\"Kalsium\", \"Magnesium\"]\n",
    "\n",
    "    # Calc Ca/Mg ratio\n",
    "    df2[f\"value_{src}\"] = df2[\"Kalsium\"] / df2[\"Magnesium\"]\n",
    "    del df2[\"Kalsium\"], df2[\"Magnesium\"]\n",
    "\n",
    "    df_list.append(df2)\n",
    "\n",
    "# Concatenate\n",
    "df2 = pd.concat(df_list, axis=1)\n",
    "df2[\"parameter\"] = \"Ca/Mg\"\n",
    "df2[\"status\"] = \"Authorised\"\n",
    "df2[\"units\"] = \"None\"\n",
    "df2.reset_index(inplace=True)\n",
    "\n",
    "# Join metadata\n",
    "df3 = df[\n",
    "    [\"sample_id\", \"station_id\", \"station_name\", \"station_type\", \"sample_date\"]\n",
    "].copy()\n",
    "df3.drop_duplicates(subset=[\"sample_id\"], inplace=True)\n",
    "df2 = pd.merge(df2, df3, how=\"left\", on=\"sample_id\")\n",
    "\n",
    "# Add to main df\n",
    "df = pd.concat([df, df2], axis=0, sort=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save data\n",
    "df.to_csv(\"1000_lakes_lw_am.csv\", index=False, encoding=\"utf-8\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Build data visualisation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read data for speed\n",
    "df = pd.read_csv(\"1000_lakes_lw_am.csv\")\n",
    "df = df.query(\"value_am > 0\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read station co-ords\n",
    "stn_df = pd.read_excel(\"am1000sjoer.xlsx\", sheet_name=\"StationPoint\")\n",
    "stn_df = stn_df[[\"StationId\", \"Latitude\", \"Longitude\"]]\n",
    "stn_df.columns = [\"station_id\", \"lat\", \"lon\"]\n",
    "\n",
    "df = pd.merge(df, stn_df, how=\"left\", on=\"station_id\")\n",
    "\n",
    "# Calc ratio 2019:1995\n",
    "df[\"ratio_2019:1995\"] = df[\"value_lw\"] / df[\"value_am\"]\n",
    "df[\"log(ratio_2019:1995)\"] = np.log10(df[\"ratio_2019:1995\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get Norway coastline\n",
    "fylker_path = \"/home/jovyan/dstoolkit_examples/data/gis/vector/fylker.geojson\"\n",
    "gdf = gpd.read_file(fylker_path)\n",
    "\n",
    "# Dissolve to single polygon\n",
    "gdf[\"dissolvefield\"] = 1\n",
    "gdf = gdf.dissolve(by=\"dissolvefield\")\n",
    "\n",
    "# Build Altair dataset\n",
    "nor = alt.InlineData(\n",
    "    values=gdf.to_json(),  # geopandas to geojson string\n",
    "    # root object type is \"FeatureCollection\" but we need its features\n",
    "    format=alt.DataFormat(property=\"features\", type=\"json\"),\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* **Updated 06.01.2020** to share x and y scales for histograms. This gives some slightly odd-looking x-scales for some parameters with large outliers\n",
    "\n",
    "* **Updated 07.01.2020** to include the map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save data as JSON\n",
    "alt.data_transformers.enable(\"json\")\n",
    "\n",
    "# Build drop-down list\n",
    "par_list = [\"None\"] + sorted(df[\"parameter\"].unique())\n",
    "input_dropdown = alt.binding_select(options=par_list)\n",
    "selection = alt.selection_single(\n",
    "    fields=[\"parameter\"], bind=input_dropdown, name=\"Select\"\n",
    ")\n",
    "\n",
    "# Scatter plot\n",
    "scatter = (\n",
    "    alt.Chart(df, height=400, width=800)\n",
    "    .mark_point()\n",
    "    .encode(\n",
    "        x=alt.X(\"value_am:Q\", title=\"1995 value in Aquamonitor\"),\n",
    "        y=alt.Y(\"value_lw:Q\", title=\"2019 value in Labware\"),\n",
    "        tooltip=[\n",
    "            \"station_id:N\",\n",
    "            \"station_name:N\",\n",
    "            \"sample_date:T\",\n",
    "            \"status:N\",\n",
    "            \"ratio_2019:1995:Q\",\n",
    "            \"log(ratio_2019:1995):Q\",\n",
    "        ],\n",
    "        color=\"status:N\",\n",
    "    )\n",
    "    .add_selection(selection)\n",
    "    .transform_filter(selection)\n",
    "    .interactive()\n",
    ")\n",
    "\n",
    "# 1:1 line\n",
    "line = (\n",
    "    alt.Chart(df, height=400, width=800)\n",
    "    .mark_line()\n",
    "    .encode(\n",
    "        x=alt.X(\"value_am:Q\", title=\"\"),\n",
    "        y=alt.Y(\"value_am:Q\", title=\"\"),\n",
    "    )\n",
    "    .transform_filter(selection)\n",
    ")\n",
    "\n",
    "# Labware histogram\n",
    "lw_hist = (\n",
    "    alt.Chart(df, height=250, width=350)\n",
    "    .mark_area(interpolate=\"step\")\n",
    "    .encode(\n",
    "        x=alt.X(\"value_lw:Q\", bin=alt.Bin(maxbins=100), title=\"2019 value in Labware\"),\n",
    "        y=alt.Y(\"count()\", title=\"Frequency\"),\n",
    "    )\n",
    "    .transform_filter(selection)\n",
    ")\n",
    "\n",
    "# AM histogram\n",
    "am_hist = (\n",
    "    alt.Chart(df, height=250, width=350)\n",
    "    .mark_area(interpolate=\"step\")\n",
    "    .encode(\n",
    "        x=alt.X(\n",
    "            \"value_am:Q\", bin=alt.Bin(maxbins=100), title=\"1995 value in Aquamonitor\"\n",
    "        ),\n",
    "        y=alt.Y(\"count()\", title=\"\"),\n",
    "    )\n",
    "    .transform_filter(selection)\n",
    ")\n",
    "\n",
    "hists = alt.hconcat(lw_hist, am_hist,).resolve_scale(\n",
    "    y=\"shared\",\n",
    "    x=\"shared\",\n",
    ")\n",
    "\n",
    "# Basemap\n",
    "basemap = (\n",
    "    alt.Chart(nor)\n",
    "    .mark_geoshape(stroke=\"black\")\n",
    "    .encode(\n",
    "        color=alt.value(\"white\"),\n",
    "    )\n",
    "    .properties(width=600, height=800)\n",
    ")\n",
    "\n",
    "# Plot locations\n",
    "locs = (\n",
    "    alt.Chart(df)\n",
    "    .mark_circle()\n",
    "    .encode(\n",
    "        longitude=alt.X(\"lon:Q\", title=\"\"),\n",
    "        latitude=alt.Y(\"lat:Q\", title=\"\"),\n",
    "        tooltip=[\n",
    "            \"station_id:N\",\n",
    "            \"station_name:N\",\n",
    "            \"sample_date:T\",\n",
    "            \"status:N\",\n",
    "            \"ratio_2019:1995:Q\",\n",
    "            \"log(ratio_2019:1995):Q\",\n",
    "        ],\n",
    "        color=alt.Color(\n",
    "            \"log(ratio_2019:1995)\",\n",
    "            sort=\"descending\",\n",
    "            scale=alt.Scale(scheme=\"redyellowblue\", domain=[-1, 1]),\n",
    "        ),\n",
    "    )\n",
    "    .add_selection(selection)\n",
    "    .transform_filter(selection)\n",
    ")\n",
    "\n",
    "# Layout\n",
    "chart = ((scatter + line) & hists) | (basemap + locs)\n",
    "chart.save(\"qc_1000_lakes.json\")\n",
    "\n",
    "chart"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Update `index.html`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Build new text\n",
    "today = dt.datetime.today()\n",
    "today = today.strftime(\"%d.%m.%Y\")\n",
    "new_text = f\"Labware results were last updated {today}\"\n",
    "\n",
    "# Update HTML. See https://stackoverflow.com/a/42882971/505698\n",
    "soup = BeautifulSoup(open(\"index.html\"), \"html.parser\")\n",
    "h3 = soup.find(\"h3\")\n",
    "h3.string.replace_with(new_text)\n",
    "\n",
    "with open(\"index.html\", \"w\") as file:\n",
    "    file.write(str(soup))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Parameter status by station\n",
    "\n",
    "See e-mail from Atle received 27.11.2019 at 16.24."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "par_status_df = (\n",
    "    lw_df[[\"parameter\", \"station_id\", \"status\"]]\n",
    "    .drop_duplicates()\n",
    "    .groupby([\"parameter\", \"status\"])\n",
    "    .count()\n",
    "    .sort_index()\n",
    "    .reset_index()\n",
    ")\n",
    "par_status_df.columns = [\"parameter\", \"status\", \"sample_count\"]\n",
    "\n",
    "par_status_df.to_csv(\"par_status_by_station.csv\", index=False, encoding=\"utf-8\")"
   ]
  }
 ],
 "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.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}