modules/NEON/scripts/build/notebooks/NEON_doc_discharge

{ "cells": [ { "cell_type": "markdown", "id": "a5afd6ac-7c2b-40b6-b07e-f39028eb59bf", "metadata": {}, "source": [ "# Query and analyze NEON discharge and water chemistry data from BigQuery" ] }, { "cell_type": "markdown", "id": "3c7d170b-1a23-408d-b5fc-8b2cc0cea152", "metadata": {}, "source": [ "Funded by the National Science Foundation (NSF) and proudly operated by Battelle, the [National Ecological Observatory Network (NEON)](https://www.neonscience.org/) program provides open, continental-scale data across the United States that characterize and quantify complex, rapidly changing ecological processes. The Observatory’s comprehensive design supports greater understanding of ecological change and enables forecasting of future ecological conditions. NEON collects and processes data from field sites located across the continental U.S., Puerto Rico, and Hawaii over a 30-year timeframe. NEON provides free and open data that characterize plants, animals, soil, nutrients, freshwater, and the atmosphere. These data may be combined with external datasets or data collected by individual researchers to support the study of continental-scale ecological change.\n", "\n", "This notebook is a demonstration of accessing and querying NEON data stored in Google Cloud BigQuery. Only a small subset of NEON data are currently available in this format, but more may be added in the future. The example datasets used are [Continuous discharge (DP4.00130.001)](https://data.neonscience.org/data-products/DP4.00130.001/RELEASE-2022) and [Chemical properties of surface water (DP1.20093.001)](https://data.neonscience.org/data-products/DP1.20093.001/RELEASE-2022). The RELEASE-2022 versions of both data products are available in BigQuery tables via the Analytics Hub. In both cases, we'll be focusing on the critical data table (csd_continuousDischarge and swc_externalLabDataByAnalyte, respectively) but other data and metadata tables are also available in the Analytics Hub listing. For more details about the two data products, see the product details pages at the links above, and the variables tables in each Analytics Hub listing.\n", "\n", "These two data products can be used together to estimate nutrient fluxes in stream ecosystems. In this notebook we first demonstrate a query on each data product independently, then a query that brings the two together to estimate dissolved organic carbon fluxes across NEON's stream sites.\n", "\n", "### Setup\n", "\n", "Install the Plotly Express module for visualization, and import other needed libraries." ] }, { "cell_type": "code", "execution_count": null, "id": "a523e0ab-2885-49c1-9670-dbd1da0740f8", "metadata": {}, "outputs": [], "source": [ "pip install plotly_express" ] }, { "cell_type": "code", "execution_count": null, "id": "c1457f62-894d-44d8-a71d-967241f2bf7d", "metadata": {}, "outputs": [], "source": [ "import matplotlib.pyplot as plt\n", "import pandas\n", "import requests\n", "import json\n", "import plotly.express as px\n", "import os" ] }, { "cell_type": "markdown", "id": "f20e8c31-f148-449c-9001-d58844a91e1d", "metadata": { "tags": [] }, "source": [ "### Query continuous discharge data\n", "\n", "Query for Continuous discharge (DP4.00130.001) data from the RELEASE-2022 dataset in BigQuery. `%%bigquery` is the \"magic\" command that designates the entire cell as one query command. `csd_core` is the name of the object to write the output to, and the query itself is written in SQL.\n", "\n", "Here, we'll download the site, date, discharge rate, and the two-standard-deviation range for all data in RELEASE-2022, filtering out any records with the final quality flag raised, or where a discharge value is not populated." ] }, { "cell_type": "code", "execution_count": null, "id": "f6af6055-18b9-47c6-b77f-be2d77c01753", "metadata": {}, "outputs": [], "source": [ "%%bigquery csd_core\n", "SELECT\n", " siteID,\n", " endDate,\n", " maxpostDischarge,\n", " withRemnUncQLower2Std,\n", " withRemnUncQUpper2Std\n", "FROM\n", " `neon_continuous_discharge.csd_continuousDischarge`\n", "WHERE\n", " dischargeFinalQF = 0 AND \n", " maxpostDischarge IS NOT NULL\n", "ORDER BY\n", " siteID, endDate;" ] }, { "cell_type": "markdown", "id": "2fe32af2-43cc-4d81-aa10-a7ae3de17a86", "metadata": {}, "source": [ "Take a look at the first 10 rows to make sure the data look as expected:" ] }, { "cell_type": "code", "execution_count": null, "id": "a743f572-68c9-43e4-af0a-2a4cc158cf54", "metadata": {}, "outputs": [], "source": [ "csd_core[0:9]" ] }, { "cell_type": "markdown", "id": "f04557d1-4fc4-438f-983b-d28b6d2777ae", "metadata": {}, "source": [ "The numeric data are imported from BigQuery as type \"decimal\", which `pandas` doesn't work with correctly. Convert the format." ] }, { "cell_type": "code", "execution_count": null, "id": "7723d8e6-6953-42bd-88b7-d1faf0b637f3", "metadata": {}, "outputs": [], "source": [ "csd_core.maxpostDischarge = csd_core.maxpostDischarge.astype(\"float\")\n", "csd_core.withRemnUncQLower2Std = csd_core.withRemnUncQLower2Std.astype(\"float\")\n", "csd_core.withRemnUncQUpper2Std = csd_core.withRemnUncQUpper2Std.astype(\"float\")" ] }, { "cell_type": "markdown", "id": "f216a120-aa41-4e27-9e07-0487e25e92b2", "metadata": {}, "source": [ "Start by subsetting to a single site, to explore the data before proceeding to larger analyses. Let's look at Como Creek (COMO), in Colorado." ] }, { "cell_type": "code", "execution_count": null, "id": "a2a3a9ad-8612-420a-b131-ad1d64be542b", "metadata": {}, "outputs": [], "source": [ "csd_COMO = csd_core[csd_core[\"siteID\"] == \"COMO\"]" ] }, { "cell_type": "markdown", "id": "88871f22-e12b-4e7a-9f07-598165b1d477", "metadata": {}, "source": [ "Start by plotting the data for all time at COMO, including the upper and lower uncertainty bounds." ] }, { "cell_type": "code", "execution_count": null, "id": "460652a8-8d53-4908-93ac-da50f9b28dca", "metadata": {}, "outputs": [], "source": [ "plt.plot(csd_COMO.endDate, \n", " csd_COMO.maxpostDischarge,\n", " linewidth=0.25, color=\"black\")\n", "plt.fill_between(csd_COMO.endDate, \n", " csd_COMO.withRemnUncQLower2Std, \n", " csd_COMO.withRemnUncQUpper2Std,\n", " color=\"gray\", alpha=0.2)" ] }, { "cell_type": "markdown", "id": "aa3e1659-e0c1-440a-933e-2d0f3651f555", "metadata": {}, "source": [ "There are three years of data for COMO in RELEASE-2022. This stream is in the Rocky Mountains, and we can see clearly that there is a long period each winter when flow is extremely low or zero, with almost all the flow happening in a few months. NEON covers a very broad range of ecosystem types, so we expect to see very different patterns at different sites.\n", "\n", "### Query Chemical properties of surface water data\n", "\n", "Now let's get the water chemistry data. For this demo, we'll be focusing on dissolved organic carbon (DOC), so we can select just that analyte from the RELEASE-2022 dataset in BigQuery." ] }, { "cell_type": "code", "execution_count": null, "id": "51f8425b-cb09-4a61-88b2-c75c3167169b", "metadata": {}, "outputs": [], "source": [ "%%bigquery swc_doc\n", "SELECT\n", " siteID,\n", " collectDate,\n", " analyteConcentration AS docConcentration\n", "FROM\n", " `neon_chemical_properties_of_surface_water.swc_externalLabDataByAnalyte`\n", "WHERE\n", " analyte = \"DOC\"\n", "ORDER BY\n", " siteID, collectDate;" ] }, { "cell_type": "markdown", "id": "f91f0703-0cf4-4f77-bdcb-d48f623ff75c", "metadata": {}, "source": [ "Again, check the first few rows:" ] }, { "cell_type": "code", "execution_count": null, "id": "c74f04ea-4824-4d2f-9ef0-3b5cdba60665", "metadata": {}, "outputs": [], "source": [ "swc_doc[0:9]" ] }, { "cell_type": "code", "execution_count": null, "id": "23385e94-966c-4ebb-82d9-a76deb8ee021", "metadata": {}, "outputs": [], "source": [ "swc_doc.docConcentration = swc_doc.docConcentration.astype(\"float\")" ] }, { "cell_type": "markdown", "id": "56dd9f5f-a52b-41e4-b551-a4dfcdfad70e", "metadata": {}, "source": [ "And plot DOC concentration for COMO on the right-hand axis along with the discharge data:" ] }, { "cell_type": "code", "execution_count": null, "id": "176f3ca1-bba2-4e60-b6e9-ccd026b223ab", "metadata": {}, "outputs": [], "source": [ "doc_COMO = swc_doc[swc_doc[\"siteID\"] == \"COMO\"]" ] }, { "cell_type": "code", "execution_count": null, "id": "3d156090-af1e-4cbf-9a1b-24d5f535d212", "metadata": {}, "outputs": [], "source": [ "fig, ax1 = plt.subplots()\n", "ax2 = ax1.twinx()\n", "ax1.plot(csd_COMO.endDate, \n", " csd_COMO.maxpostDischarge,\n", " linewidth=0.25, color=\"black\")\n", "ax1.fill_between(csd_COMO.endDate, \n", " csd_COMO.withRemnUncQLower2Std, \n", " csd_COMO.withRemnUncQUpper2Std,\n", " color=\"gray\", alpha=0.2)\n", "ax2.scatter(doc_COMO.collectDate,\n", " doc_COMO.docConcentration,\n", " marker=\"o\", color=\"blue\", s=0.5)" ] }, { "cell_type": "markdown", "id": "73c8b052-7e60-41cc-9fe9-8bb87d7831e9", "metadata": {}, "source": [ "We learn a couple of things from this figure: first, DOC concentrations rise and fall in parallel with discharge rate, and second, there are chemistry samples collected from time periods when discharge estimates aren't available. Of course, since discharge estimates are made every minute, there are also many discharge estimates that don't have corresponding DOC samples.\n", "\n", "In order to calculate the flux of DOC, we need to do some alignment of the two datasets. The end result we want is a series of sites and dates, each with a corresponding discharge value and DOC value. To get there, we need to:\n", "* Subset the chemistry data to only the sites contained in the discharge data (no discharge at lake sites)\n", "* Match the dates between the chemistry and discharge data\n", "* Calculate a discharge value for the time period represented by each DOC collection\n", "\n", "### Query both data products together and join\n", "\n", "We could do this by transforming the `pandas` data frames we already have, and that would be a reasonable approach. But we're here to explore the capabilities of BigQuery. It enables us to calculate the average discharge on each day of each year and join the data to the DOC data from the days it was collected, using SQL. Using this approach, we only access the subsetted, aggregated data, rather than downloading the entirety of both datasets." ] }, { "cell_type": "code", "execution_count": null, "id": "1b3f1a9e-52e9-4511-8d65-277ccbf0622d", "metadata": {}, "outputs": [], "source": [ "%%bigquery flux\n", "SELECT\n", "*\n", "FROM\n", " (SELECT\n", " siteID,\n", " EXTRACT(YEAR FROM endDate) AS yr,\n", " EXTRACT(DAYOFYEAR FROM endDate) AS doy,\n", " AVG(maxpostDischarge) AS dischargeMean\n", " FROM\n", " `neon_continuous_discharge.csd_continuousDischarge`\n", " WHERE\n", " dischargeFinalQF = 0 AND \n", " maxpostDischarge IS NOT NULL\n", " GROUP BY\n", " siteID, \n", " EXTRACT(YEAR FROM endDate),\n", " EXTRACT(DAYOFYEAR FROM endDate)) csd\n", "JOIN\n", " (SELECT\n", " siteID,\n", " EXTRACT(YEAR FROM collectDate) AS yr,\n", " EXTRACT(DAYOFYEAR FROM collectDate) AS doy,\n", " analyteConcentration AS doc\n", " FROM\n", " `neon_chemical_properties_of_surface_water.swc_externalLabDataByAnalyte`\n", " WHERE\n", " analyte = \"DOC\") swc\n", "ON\n", " csd.siteID = swc.siteID AND\n", " csd.yr = swc.yr AND\n", " csd.doy = swc.doy\n", "ORDER BY\n", " csd.siteID, csd.yr, csd.doy" ] }, { "cell_type": "code", "execution_count": null, "id": "52dc7f87-5ec9-4bbc-8d2e-aafb3b06757e", "metadata": {}, "outputs": [], "source": [ "flux[0:9]" ] }, { "cell_type": "markdown", "id": "8e0663a3-77f1-4b2c-9771-dd58c6ad2662", "metadata": {}, "source": [ "The table `flux` contains one record for each day water chemistry was sampled, and the mean discharge rate from that day. Let's calculate the flux of DOC for each of those days:" ] }, { "cell_type": "code", "execution_count": null, "id": "d6569fa5-c8fa-4a75-b046-543fcb8d8e1a", "metadata": {}, "outputs": [], "source": [ "flux[\"flux\"] = flux.dischargeMean*flux.doc\n", "flux.flux = flux.flux.astype(\"float\")" ] }, { "cell_type": "markdown", "id": "2c60e70c-84f6-4b98-b51f-347afec73643", "metadata": {}, "source": [ "Now we have an estimate of the flux of DOC for a couple of dozen days per site per year. There are many directions an analysis could go at this point, let's pick something that can make a simple visualization. We'll find the maximum daily flux of DOC from each site across the dataset:" ] }, { "cell_type": "code", "execution_count": null, "id": "7715acc3-de66-40dc-b8ce-8ae3cf70d773", "metadata": {}, "outputs": [], "source": [ "flux_max = []\n", "for i in list(dict.fromkeys(flux.siteID)):\n", " flux_max.append([i, max(flux.flux[flux[\"siteID\"] == i])])\n", "fluxmax = pandas.DataFrame(flux_max)\n", "fluxmax.columns = [\"siteID\",\"docFlux\"]" ] }, { "cell_type": "markdown", "id": "634b89ef-e8e8-4af9-88cb-9ba00c199405", "metadata": {}, "source": [ "Using site coordinates retrieved from NEON's public API, and the `Plotly Express` module, we can map maximum daily DOC flux at NEON stream sites across the country:" ] }, { "cell_type": "code", "execution_count": null, "id": "2d9afb8e-8577-47c1-8ac8-3430cd4c6a08", "metadata": {}, "outputs": [], "source": [ "utmlist = []\n", "for i in fluxmax.siteID:\n", " site_request = requests.get(\"https://data.neonscience.org/api/v0/locations/\" + i)\n", " site_list = site_request.json()\n", " utmlist.append([i, site_list[\"data\"][\"locationDecimalLatitude\"], \n", " site_list[\"data\"][\"locationDecimalLongitude\"]])\n", "utms = pandas.DataFrame(utmlist)\n", "utms.columns = [\"siteID\",\"lat\",\"lon\"]" ] }, { "cell_type": "code", "execution_count": null, "id": "6a3b40cd-af5b-4ea3-be57-feed0c343bc7", "metadata": {}, "outputs": [], "source": [ "utms[\"docFlux\"] = fluxmax.docFlux/100\n", "docUtm = utms[pandas.notna(utms[\"docFlux\"])]" ] }, { "cell_type": "code", "execution_count": null, "id": "d0408160-f7c1-4c77-a0fe-969fcd9188b5", "metadata": {}, "outputs": [], "source": [ "fig = px.scatter_geo(docUtm, lat=\"lat\", lon=\"lon\", \n", " size=\"docFlux\", scope=\"usa\",\n", " projection=\"albers usa\",\n", " color=\"siteID\", size_max=150,\n", " opacity=0.6)\n", "fig.show()" ] }, { "cell_type": "markdown", "id": "616d8cc5-8d31-4cfd-9759-8f9b0950fa36", "metadata": {}, "source": [ "The two large rivers in the southeast dominate the map; let's take a look without them:" ] }, { "cell_type": "code", "execution_count": null, "id": "0252fd0c-6352-4d2f-8a4b-840e22b411f4", "metadata": {}, "outputs": [], "source": [ "streamDoc = docUtm.drop(docUtm[(docUtm.siteID == 'BLWA') | (docUtm.siteID == 'FLNT')].index)" ] }, { "cell_type": "code", "execution_count": null, "id": "c5c4f582-691f-4964-b578-b4baba7cea3e", "metadata": {}, "outputs": [], "source": [ "fig = px.scatter_geo(streamDoc, lat=\"lat\", lon=\"lon\", \n", " size=\"docFlux\", scope=\"usa\",\n", " projection=\"albers usa\",\n", " color=\"siteID\", size_max=50,\n", " opacity=0.6)\n", "fig.show()" ] }, { "cell_type": "markdown", "id": "9e65cf69-08f5-4d16-9e5c-2513c42a3f68", "metadata": {}, "source": [ "We can see the distribution of maximum DOC flux across the country.\n", "\n", "### Final thoughts\n", "\n", "A few notes on this demo. This was a simple exercise for illustration purposes, not a rigorous analysis. We calculated the DOC flux on each day when DOC was sampled, but the continuous discharge data are available on a 1-minute resolution. More typical for this type of analysis would be to build a model estimating the flux as a function of discharge, and use the high-resolution discharge data to extrapolate a total flux estimate for the entire year.\n", "\n", "Similarly, there are many possible pathways to take regarding how much aggregation and data cleaning to carry out in the query step, as opposed to querying broadly and doing the data wrangling in the analysis phase. Choosing what to do in which part of the process depends on the nature of the analysis, and on familiarity with the data and data structures. In general, applying extensive data cleaning in the query step makes processing and analysis faster, by taking advantage of BigQuery's high speed performance and capabilities, but at the risk of missing critical nuance in the data. For example, if we didn't know to retain only the records where `dischargeFinalQF=0`, the daily mean discharge estimate from the query would include data that had been flagged as erroneous. As with all analyses, explore the data carefully before choosing an analytical approach." ] }, { "cell_type": "code", "execution_count": null, "id": "bdef8a5e-aff7-4984-9ffe-d5483c2e29a4", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3.7.4 64-bit", "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.4" }, "vscode": { "interpreter": { "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49" } } }, "nbformat": 4, "nbformat_minor": 5 }

modules/NEON/scripts/build/notebooks/NEON_doc_discharge_demo.ipynb (512 lines of code) (raw):