D5-Exmaple_Data_Sources.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# D5 - Example Data Sources\n",
    "\n",
    "Feedback: https://forms.gle/Le3RAsMEcYqEyswEA\n",
    "\n",
    "**Index**:\n",
    "* API Keys\n",
    "* Water Data\n",
    "  * CIMIS\n",
    "  * NWIS Waterdata via web\n",
    "  * NWIS data via module\n",
    "\n",
    "\n",
    "## API Keys\n",
    "When you get an API key for a website, it's associated with your account and not something you want to share.  So, it's **not** a good idea to keep API keys in your scripts if you're going to share them, post them on github, etc.  \n",
    "\n",
    "A good strategy is to put them in an environment variable so that you can get the key from the env variable inside your script.  In the examples below you'll see checks for os.environ - this is looking for an environment variable with your key.\n",
    "\n",
    "In linux, you can set an environment variable by adding a line to your ~/.bashrc file like follows, and the log back into your computer:\n",
    "\n",
    "    export CIMIS_API_KEY=\"your key here\"\n",
    "\n",
    "And in windows, you can run the following in a cmd window:\n",
    "\n",
    "    setx OPENAI_API_KEY “<yourkey>”\n",
    "\n",
    "Theres more info on this stuff here: https://help.openai.com/en/articles/5112595-best-practices-for-api-key-safety"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cimis Data\n",
    "Create an account at https://cimis.water.ca.gov.  Once you log, click the \"Account\" button in the top right corner. Scroll down and you'll see an API key that you'll need for queries.\n",
    "\n",
    "We can query the Cimis data from: https://et.water.ca.gov...  \n",
    "* There are example queries at: https://et.water.ca.gov/Rest/Index\n",
    "\n",
    "Here's some example code for querying it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Request url is:  https://et.water.ca.gov/api/data?appKey=46f5b53b-3adc-4a4d-a660-474c010a26f4&startDate=2010-01-01&endDate=2010-01-05&targets=2,8,127\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "import os\n",
    "import requests\n",
    "import pandas as pd\n",
    "\n",
    "if 'CIMIS_API_KEY' in os.environ:\n",
    "    api_key = os.environ['CIMIS_API_KEY']\n",
    "else:\n",
    "    api_key = 'your_api_key_here'\n",
    "url_base = 'https://et.water.ca.gov/api'\n",
    "options = [f'appKey={api_key}', 'startDate=2010-01-01', 'endDate=2010-01-05', 'targets=2,8,127']\n",
    "options = '&'.join(options)\n",
    "url = f'{url_base}/data?{options}'\n",
    "\n",
    "print(\"Request url is: \", url)\n",
    "response = requests.get(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "response_data = json.loads(response.text)\n",
    "# for record in response['Data']['Providers'][0]['Records']:\n",
    "#     print(record)\n",
    "df = pd.DataFrame(response_data['Data']['Providers'][0]['Records'])\n",
    "# print(df.info())\n",
    "# print(df.head())\n",
    "\n",
    "# The values all have this dictionary format:\n",
    "# DayAirTmpAvg: {'Value': '39', 'Qc': ' ', 'Unit': '(F)'}\n",
    "\n",
    "# So we need to break them out into separate columns:\n",
    "value_cols = [c for c in df.columns if c.startswith('Day')]\n",
    "for c in value_cols:\n",
    "    df[f'{c}_Units'] = df[c].apply(lambda x: x['Unit'])\n",
    "    # You may also want to preserve the Qc value\n",
    "    df[c] = df[c].apply(lambda x: x['Value'])\n",
    "\n",
    "print(df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## USGS NWIS Waterdata via Web\n",
    "https://waterdata.usgs.gov/\n",
    "\n",
    "Example site data: https://waterdata.usgs.gov/nwis/uv?site_no=05056241&legacy=1\n",
    "There's a new version page at: https://waterdata.usgs.gov/monitoring-location/05056241/\n",
    "This one has download data button -> select primary time series -> retreive, and this opens another page with this url:\n",
    "https://waterservices.usgs.gov/nwis/iv/?sites=05056241&startDT=2024-09-19T20:57:15.477-05:00&endDT=2024-09-26T20:57:15.477-05:00&parameterCd=00065&format=rdb\n",
    "\n",
    "Change `format=rdb` to `format=json` and we get some easy to work with data.  View it in the browser...  usually there's a pretty print check box at the top of the browser for json data like this that will make it easier to read.  \n",
    "\n",
    "The data returned has a lot of meta-data and the time series data we're interested in: \n",
    "\n",
    "    data['value']['timeSeries'][0]['values'][0]['value']\n",
    "\n",
    "Look through the meta data as some of it is useful... time zone, query info, and meaning of the pcodes in the output data:\n",
    "\n",
    "        \"variable\": {\n",
    "          \"variableCode\": [\n",
    "            {\n",
    "              \"value\": \"00065\",\n",
    "              \"network\": \"NWIS\",\n",
    "              \"vocabulary\": \"NWIS:UnitValues\",\n",
    "              \"variableID\": 45807202,\n",
    "              \"default\": true\n",
    "            }\n",
    "          ],\n",
    "          \"variableName\": \"Gage height, ft\",\n",
    "          \"variableDescription\": \"Gage height, feet\",\n",
    "          \"valueType\": \"Derived Value\",\n",
    "          \"unit\": {\n",
    "            \"unitCode\": \"ft\"\n",
    "          },\n",
    "          \"options\": {\n",
    "            \"option\": [\n",
    "              {\n",
    "                \"name\": \"Statistic\",\n",
    "                \"optionCode\": \"00000\"\n",
    "              }\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#https://waterservices.usgs.gov/nwis/iv/?sites=05056241&startDT=2024-09-19T20:57:15.477-05:00&endDT=2024-09-26T20:57:15.477-05:00&parameterCd=00065&format=rdb\n",
    "# midnight to midnight\n",
    "startDT = '2024-09-19T00:00:00-05:00'\n",
    "endDt = '2024-09-26T00:00:00-05:00'\n",
    "# I suspect the 05:00 is the time zone offset\n",
    "sites = '05056241' # presumably this could be a comma separated list\n",
    "parameterCd = '00065' # discharge in cubic feet per second\n",
    "format = 'json'\n",
    "base_url = 'https://waterservices.usgs.gov/nwis/iv'\n",
    "params = [f'sites={sites}', f'startDT={startDT}', f'endDT={endDt}', f'parameterCd={parameterCd}', f'format={format}']\n",
    "params = '&'.join(params)\n",
    "url = f'{base_url}/?{params}'\n",
    "print(url)\n",
    "response = requests.get(url)\n",
    "data = json.loads(response.text)\n",
    "# print(response.text)\n",
    "df = pd.DataFrame(data['value']['timeSeries'][0]['values'][0]['value'])\n",
    "print(df.head())\n",
    "print(df.info())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Often, you'll be limited in the date range, number of sites, or number of parameters you can query at once, so you can play around and see what works and what gives an error.  The json output should have an error code or explanation.  Once you know the limits, use a loop to increment the dates and collect all of the data needed:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "chunk_size_days = 7\n",
    "from datetime import datetime, timedelta\n",
    "start_date = datetime(2024, 1, 1)\n",
    "end_date = datetime(2024, 3, 31)\n",
    "delta = timedelta(days=chunk_size_days)\n",
    "working_date = start_date\n",
    "dataframes = []\n",
    "while working_date <= end_date:\n",
    "    end_working_date = working_date + delta\n",
    "    if end_working_date > end_date:\n",
    "        end_working_date = end_date\n",
    "    startDT = working_date.strftime('%Y-%m-%dT00:00:00-05:00')\n",
    "    endDT = end_working_date.strftime('%Y-%m-%dT00:00:00-05:00')\n",
    "    params = [f'sites={sites}', f'startDT={startDT}', f'endDT={endDT}', f'parameterCd={parameterCd}', f'format={format}']\n",
    "    params = '&'.join(params)\n",
    "    url = f'{base_url}/?{params}'\n",
    "    print(url)\n",
    "    response = requests.get(url)\n",
    "    data = json.loads(response.text)\n",
    "    df = pd.DataFrame(data['value']['timeSeries'][0]['values'][0]['value'])\n",
    "    dataframes.append(df)\n",
    "    working_date = end_working_date + timedelta(days=1)\n",
    "df = pd.concat(dataframes)\n",
    "\n",
    "df.to_csv(f'site_{sites}.csv', index=False)\n",
    "print(df.info())\n",
    "print(df.head())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## NWIS Data\n",
    "This data uses PCodes.  You'll need to look up "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "%pip install hydrofunctions\n",
    "import hydrofunctions as hf\n",
    "\n",
    "#Example Sites:\n",
    "# 'CCH':'11455350', # old CCH41\n",
    "# 'CCH41':'11455385', # now called RYF\n",
    "# 'CFL':'11455508', # now called CONFL\n",
    "# 'DEC':'11455478', # old TOL\n",
    "# 'TOL': '11455485',\n",
    "\n",
    "startDT = '2022-01-01'\n",
    "endDT = '2024-02-02'\n",
    "site_name = 'CCH41'\n",
    "site_code = '11455385'\n",
    "\n",
    "NWIS_request = hf.NWIS(site_code,'iv',startDT,endDT)\n",
    "        \n",
    "df = NWIS_request.df()\n",
    "\n",
    "headers = {}\n",
    "lines = str(NWIS_request).split('\\n') #treates NWIS_request as a string - this gives us one long string of original col names\n",
    "for line in lines[1:-2]: #ignore the first line  (which is the site name) and the last two lines (are the strt and end dates)\n",
    "    line = line.strip() #remove white space\n",
    "    identifier = line.split(':')[0].strip() \n",
    "    headers[identifier] = line #put this into the dictionary as the identifier\n",
    "\n",
    "cols = list(df.columns)  #creates a list of original column names\n",
    "new_cols = []\n",
    "\n",
    "for column in cols:\n",
    "    qual = 'qualifier' in column #creates a bool based on whether the column is a qualifer column, as opposed to a data column\n",
    "    scol = column.split(':')\n",
    "\n",
    "df['site_code'] = site_code\n",
    "df['site_name'] = site_name\n",
    "\n",
    "df = df.reset_index()\n",
    "\n",
    "df['datetimeUTC'] = pd.to_datetime(df['datetimeUTC'], format='%Y-%m-%d %H:%M:%S')\n",
    "df['TS Timestamp (PST)'] = df['datetimeUTC'].dt.strftime('%Y-%m-%d %H:%M:%S')\n",
    "df['TS Timestamp (PST)'] = pd.to_datetime(df['TS Timestamp (PST)'], format='%Y-%m-%d %H:%M:%S') - pd.Timedelta(hours = 8)\n",
    "\n",
    "df = df.set_index('TS Timestamp (PST)')\n",
    "\n",
    "df.columns = [col.split(':')[2] if ':' in col else col for col in df.columns]\n",
    "df.columns = [col.split('-')[0] if '-' in col else col for col in df.columns]\n",
    "\n",
    "print(df.columns)\n",
    "print(df.head())\n",
    "\n",
    "#fname = f'TS_{field_id}_{site}.csv'   \n",
    "fname = f'TS_{site_code}_{site_name}.csv'\n",
    "print('saving to', fname)\n",
    "df.to_csv(fname)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv",
   "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.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}