ECHO_modules_Tutorials.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "4TDSu6HIiYbb"
   },
   "source": [
    "| ![EEW logo](https://github.com/edgi-govdata-archiving/EEW-Image-Assets/blob/main/Jupyter%20instructions/eew.jpg?raw=true) | ![EDGI logo](https://github.com/edgi-govdata-archiving/EEW-Image-Assets/blob/main/Jupyter%20instructions/edgi.png?raw=true) |\n",
    "|---|---|\n",
    "\n",
    "\n",
    "This notebook is licensed under GPL 3.0. Please visit our Github [repo](https://github.com/edgi-govdata-archiving/ECHO_modules) for more information.\n",
    "\n",
    "The notebook was collaboratively authored by the Environmental Data & Governance Initiative (EDGI) following [our authorship protocol](https://docs.google.com/document/d/1CtDN5ZZ4Zv70fHiBTmWkDJ9mswEipX6eCYrwicP66Xw/)\n",
    "\n",
    "For more information about this project, visit [our website](https://www.environmentalenforcementwatch.org/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "rNdbczHwih64"
   },
   "source": [
    "This is a Jupyter Notebook - a way to organize Python computer programming code. Hosting the notebook on Google Colab allows you to access and visualize data without actually needing to do any coding! The code is left visible for individuals with a knowledge of Python or for those wondering how this site was put together. This allows for a more interactive user experience.\n",
    "\n",
    "Be sure to read the instructions in \"How to Run\" (below)  and the notes above each cell for important tips and context!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "uSApSqhdiofd"
   },
   "source": [
    "# ECHO_modules\n",
    "*ECHO_modules* is a Python package repository for analyzing a copy of the US Environmental Protection Agency's (EPA) Enforcement and Compliance History Online (ECHO) database.\n",
    "\n",
    "### Background\n",
    "The US EPA collects a wide variety of data concerning environmental pollution and makes this available through several portals. The ECHO database collates information on industrial facilities' compliance with environmental protection laws like the Clean Water Act and regulatory agencies' enforcement of those laws. ECHO records reported violations, agency inspections of facilities, penalties paid by companies for infractions. It also incorporates information from other sources, such as reported pollutant releases from the Toxics Release Inventory and socio-economic information from EJScreen.\n",
    "\n",
    "Unfortunately, both the web portal for ECHO (echo.epa.gov) and its API have a number of limitations. First, EPA generally only makes the past 3-5 years worth of data available through these services. Second, EPA typically does not allow aggregating information into meaningful views, such as reports of inspections by Census tract or ZIP code. Instead, searches on echo.epa.gov are usually facility-oriented. This makes it hard to understand the state of environmental enforcement and compliance across an entire geography, company, or industry sector.\n",
    "\n",
    "In response, we make regular copies of the full set of historical records in ECHO by scraping echo.epa.gov [here](https://echo.epa.gov/files/echodownloads/). We load a number of specific tables into a [Postgresql database](https://github.com/sunggheel/edgipgdb) hosted at Stony Brook University (SBU). These tables are then linked with one another through various lookups and materialized views. For instance, the table that contains summary information about facilities (ECHO_EXPORTER) is linked to the table with detailed records on Clean Water Act violations through the `NPDES_ID` key.\n",
    "\n",
    "`ECHO_modules` provides convenient dataset definitions and pre-defined queries that enable users to retrieve information from the SBU database and to visualize it as tables, maps, and charts. It also supports user-defined queries. With `ECHO_modules`, users can easily access summaries of EPA's records for specific geographies (e.g. a set of ZIP codes) and examine these records in relation to EPA's measures of environmental inequalities (from EJScreen).\n",
    "\n",
    "Learn more about the SBU copy of ECHO and how it is used by ECHO_modules [here](https://github.com/edgi-govdata-archiving/ECHO_modules/blob/main/SBU-db.md). EDGI's Environmental Enforcement Watch (EEW) works with `ECHO_modules` extensively. For more on the EEW project, visit [here](https://environmentalenforcementwatch.org/) or check out project-specific repositories in the EDGI organization on GitHub.\n",
    "\n",
    "### Interpreting ECHO Data\n",
    "The ECHO database is notoriously incomplete and biased. EDGI EEW's own research, based on `ECHO_modules` and published [here](https://envirodatagov.org/wp-content/uploads/2022/09/Gaps_and_Disparities_Report.pdf), has found the following:\n",
    "\n",
    "> * Over 19,000 facilities regulated under foundational environmental protection laws are missing basic information such as their latitude and longitude. Nearly all — 19,657 out of 19,675 (99.9%) — of these are SDWA-regulated facilities.\n",
    "> * Data needed for basic EJ assessments, such as the percent minority population surrounding a facility or the Census block it resides in, is missing for 14% of the facilities in EPA’s most public-facing database. This increases to 83% of facilities regulated under SDWA.\n",
    "> * Nationally, the typical facility regulated under each of these environmental protection laws is missing:\n",
    ">   * 86% of CWA-specific information\n",
    ">   * 86% of RCRA-specific information\n",
    ">   * 71% of CAA-specific information\n",
    ">   * 40% of SDWA-specific information\n",
    "> * Facilities in majority-minority communities have somewhat worse data quality scores than facilities in majority-white communities, for all acts except SDWA.\n",
    "> * Data missingness is substantially worse for facilities in areas already screened by EPA to be of particular concern for environmental injustices and majority-minority areas when looking at Clean Water Act inspections in particular.\n",
    "> * 78% of all facilities regulated under the CWA are missing inspection counts, but only 75% of facilities in majority white areas, rising to 83% of facilities in majority-minority areas.\n",
    "> * Western states including Texas, New Mexico, Colorado, Utah, and Nevada are much worse when it comes to inspection data completeness for facilities in majority-minority communities.\n",
    "\n",
    "Simply put, ECHO records are flawed because they rely on industry and state self-reporting. For instance, the emissions levels industry provides to EPA are typically estimates rather than direct measurements. ProPublica [has found](https://www.propublica.org/article/whats-polluting-the-air-not-even-the-epa-can-say) that these estimates actually tend to be *overestimates* - industry knows EPA lacks the will and capacity to look into large emitters, while submitting lower numbers might raise suspicion.\n",
    "\n",
    "ECHO records also reflect a flawed governance system. Determining that a facility is in violation of its permit to pollute typically requires either accurate and honest self-reporting of emissions, or regulatory inspections of the facility. However, facility inspections have been in decline since at least the Obama administration. Thus, \"true\" violations are unlikely to be noticed and recorded. In other words, the ECHO database is rife with type II statistical errors (\"false negatives\").\n",
    "\n",
    "Even when violations are flagged, it is important to keep in mind that these represent emissions above and beyond permitted thresholds (if they are not paperwork violations), but whether or not these permitted thresholds are adequate is entirely different question. Just because a facility is *not* in violation of its permit does not mean that it is inconsequential to human and environmental health, since most permits to pollute do not account for the cumulative effects of multiple polluters in a region or the synergistic effects of multiple pollutants.\n",
    "\n",
    "[According to](https://global.oup.com/academic/product/next-generation-compliance-9780197656747) former EPA director of enforcement and compliance assurance Cynthia Giles (2020), records related to the Clean Water Act's National Pollutant Discharge Elimination System (NPDES) tend to be the most reliable, in part because of federal requirements that all regulated facilities submit digitized records straight to US EPA.\n",
    "\n",
    "Any interpretations you make of ECHO data accessed through `ECHO_modules` should keep all the above in mind. For instance, EEW prefers to use language such as \"reported violations\" and \"estimated emissions\" when discussing findings.\n",
    "\n",
    "### How to Run this Notebook\n",
    "![Instructions for running a Jupyter Notebook](https://github.com/edgi-govdata-archiving/EEW-Image-Assets/blob/main/overall_instructions.png?raw=true)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ejSvfrPwrXhh"
   },
   "source": [
    "# Install ECHO_modules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "pX7L_dBIqcWJ"
   },
   "outputs": [],
   "source": [
    "!pip install ECHO_modules &>/dev/null; # ECHO_modules"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "SJBe9ujdrZyU"
   },
   "source": [
    "# Basic Usage\n",
    "## Analyze Currently Active Facilities in a County\n",
    "\n",
    "In the following example, we retrieve all of the currently active facilities (according to EPA) in Erie County in New York."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 875
    },
    "id": "aj2Bo1oYj08s",
    "outputId": "e9488012-3632-4f2d-842f-cb77fb4736a9"
   },
   "outputs": [],
   "source": [
    "from ECHO_modules.utilities import get_active_facilities # Use the get_active_facilities function\n",
    "\n",
    "erie = get_active_facilities(\"NY\", \"County\", [\"ERIE\"])\n",
    "erie"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "xSNqQ-DXpY_B"
   },
   "source": [
    "## Save this Data to CSV Format\n",
    "\n",
    "This will export the above dataframe as a CSV. If running this notebook in Google Colab, the CSV can be found by clicking on the folder on the left-hand side of the screen and then opening the CSV sub-folder, right-clicking on the \"Facilities-NY-County-Erie.csv\" file and choosing Download.\n",
    "\n",
    "First you are able to customize the name of the file we will be creating."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## You may enter a name to use for the file, or use the one provided.\n",
    "from ECHO_modules.utilities import dataset_filename, write_dataset\n",
    "\n",
    "filename_widget = dataset_filename(base='Facilities',\n",
    "               type='County', state='NY', regions=['Erie'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "...and now you can write the file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "9PFRJC0IpbIQ",
    "outputId": "203177d8-7a54-43eb-ddb3-1834ebc189ec"
   },
   "outputs": [],
   "source": [
    "from ECHO_modules.utilities import write_dataset\n",
    "write_dataset(erie, filename_widget.value)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "io3mYPYKm-l2"
   },
   "source": [
    "## Find and Chart the 10 Facilities Least Compliant with the Resource and Conservation Recovery Act (RCRA) over the Past 12 Quarters in this County\n",
    "\n",
    "EPA provides summary data on inspections, violations, and penalties under various environmental protection laws. In the following example, we access that summary data for RCRA-regulated facilities in Erie County, sort it, and then chart it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 676
    },
    "id": "bptBvDOonJPs",
    "outputId": "3fbdba73-e286-4e34-ba10-4d7362f48542",
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Next, we enter the dataframe of Erie County facilities into the get_top_violators() function, then use chart_top_violators to visualize the result\n",
    "# Use get_top_violators and chart_top_violators\n",
    "from ECHO_modules.utilities import get_top_violators, chart_top_violators\n",
    "\n",
    "erie_top_violators = get_top_violators( erie, flag = 'RCRA_FLAG',\n",
    "                                       noncomp_field = 'RCRA_3YR_COMPL_QTRS_HISTORY',\n",
    "                                        action_field = 'RCRA_FORMAL_ACTION_COUNT',\n",
    "                                        num_fac=10 )\n",
    "chart_top_violators(erie_top_violators, state = 'NY', selections = \"Erie County\",\n",
    "                    epa_pgm = \"RCRA\" )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "hypdEH6ZzDgy"
   },
   "source": [
    "## Map these Top 10 RCRA Violators"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 558
    },
    "id": "NcbnlLIMzHCy",
    "outputId": "7cf680f2-c22d-45df-f8aa-7955457dd326"
   },
   "outputs": [],
   "source": [
    "import geopandas # Import a Python package for creating spatial dataframes\n",
    "from ECHO_modules.get_data import get_spatial_data # Module for getting spatial data from the SBU database\n",
    "from ECHO_modules.geographies import spatial_tables # Variables that support spatial queries\n",
    "from ECHO_modules.utilities import bivariate_map, map_style # Use this function and variable to make our map\n",
    "\n",
    "# Query and return spatial data\n",
    "county, state = get_spatial_data( region_type = \"County\", states = [\"NY\"],\n",
    "                                 spatial_tables = spatial_tables, region_filter = \"Erie\")\n",
    "bivariate_map(regions = county,\n",
    "              points = geopandas.GeoDataFrame(erie_top_violators,\n",
    "                                              geometry=geopandas.points_from_xy(\n",
    "                                                  erie_top_violators['FAC_LONG'],\n",
    "                                                  erie_top_violators['FAC_LAT']),\n",
    "                                                  crs=4269\n",
    "                                              )\n",
    "              ) # Use Geopandas to create a spatial version of this dataframe so that it can be mapped"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create DataSet templates for all the types of ECHO records\n",
    "\n",
    "EPA not only produces summary information on environmental enforcement and compliance programs (in its ECHO_EXPORTER records), it provides access to \"raw\" historical records. These can be accessed through our DataSet collections. Here we will create all of the types of DataSet. These won't hold any records yet. They are just the containers for the types of data. We will populate some of these data_sets later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from ECHO_modules.make_data_sets import make_data_sets\n",
    "\n",
    "## List of datasets to choose from\n",
    "data_sets = make_data_sets([\n",
    "    \"RCRA Violations\",\n",
    "    \"RCRA Inspections\",\n",
    "    \"RCRA Penalties\",\n",
    "    \"CAA Violations\",\n",
    "    \"CAA Penalties\",\n",
    "    \"CAA Inspections\",\n",
    "    \"Combined Air Emissions\",\n",
    "    \"Greenhouse Gas Emissions\",\n",
    "    \"Toxic Releases\",\n",
    "    \"CWA Violations\",\n",
    "    \"CWA Inspections\",\n",
    "    \"CWA Penalties\",\n",
    "    \"SDWA Site Visits\",\n",
    "    \"SDWA Enforcements\",\n",
    "    \"SDWA Public Water Systems\",\n",
    "    \"SDWA Violations\",\n",
    "    \"SDWA Serious Violators\",\n",
    "    \"2022 Discharge Monitoring\",\n",
    "    \"Effluent Violations\",\n",
    "])\n",
    "## These are described in more detail here: https://github.com/edgi-govdata-archiving/ECHO_modules/blob/main/ECHO_modules/data_set_presets.py\n",
    "## and here: https://echo.epa.gov/tools/data-downloads#downloads\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "416Pxv19sMJF",
    "jupyter": {
     "source_hidden": true
    }
   },
   "source": [
    "## Get Historical Records of RCRA Violations in this County\n",
    "\n",
    "Here we will populate one of the DataSet containers, the one for \"RCRA Violations\", with a request for specific data. We use the DataSet's store_results() function to get the data from the database and keep it in a DatSetResults container owned by the DataSet.\n",
    "\n",
    "EPA says records prior to 2001 are unreliable. First we'll let you select your years of interest..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from ECHO_modules.utilities import show_year_range_widget\n",
    "## Slide the endpoints to the desired years\n",
    "year_range = show_year_range_widget()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "...and now we can populate the RCRA Violations DataSet for those years."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 858
    },
    "id": "hzDIsHajsSb8",
    "outputId": "cbc84298-c013-40b1-b0a4-4d3d1a5f78aa"
   },
   "outputs": [],
   "source": [
    "# Store results for this DataSet as a DataSetResults object\n",
    "erie_rcra_violations = data_sets[\"RCRA Violations\"].store_results(\n",
    "    region_type=\"County\", region_value=[\"ERIE\"], state=\"NY\", years=year_range.value)\n",
    "erie_rcra_violations.dataframe # Show the results as a dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "33d0TQLuuYQF"
   },
   "source": [
    "## Show RCRA Violations Over Time in a Chart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 516
    },
    "id": "HHMx1a6Yuoz4",
    "outputId": "ddcf4099-5f44-42a2-afc2-76ac8e78ce2f"
   },
   "outputs": [],
   "source": [
    "erie_rcra_violations.show_chart()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "qMr8Ad4ruwd9"
   },
   "source": [
    "## Map Facilities in this County with Recorded RCRA Violations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 613
    },
    "id": "WNKUJkhLu6a7",
    "outputId": "044bdddc-0fea-4c4b-b5d2-4df122adf905"
   },
   "outputs": [],
   "source": [
    "from ECHO_modules.utilities import aggregate_by_facility, point_mapper # Import relevant modules\n",
    "\n",
    "erie_rcra_violations.region_value=[\"ERIE\"] # (re)set the region_value as a list\n",
    "# Aggregate each entry using this function. In the case of RCRA violations,\n",
    "# it will summarize each type of violation (permit, schedule, effluent, etc.)\n",
    "# and then aggregate them for each facility over time.\n",
    "# By setting other_records to True, we also get RCRA-regulated facilities in the\n",
    "# county without records of violations.\n",
    "aggregated_results = aggregate_by_facility(\n",
    "    erie_rcra_violations, erie_rcra_violations.dataset.name, other_records=True)\n",
    "# Map each facility as a point, the size of which corresponds to the number of reported violations since 2001.\n",
    "point_mapper(aggregated_results[\"data\"], aggregated_results[\"aggregator\"],\n",
    "             quartiles=True, other_fac=aggregated_results[\"diff\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "CnkBUm7rxV9M"
   },
   "source": [
    "# Advanced Usage"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "qyRQO_p5avvj"
   },
   "source": [
    "## Select an area of interest using a map\n",
    "Administrative boundaries like counties are only so meaningful when it comes to understanding environmental pollution, enforcement, and compliance trends near you. You might want to draw your own neighborhood - this utility lets you do that, and then retrieve records for facilities within those boundaries.\n",
    "\n",
    "Run the following cell, use the tools in the left part of the map to create a shape, and then run the cell that follows to retrieve Clean Air Act violations for the area."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 439,
     "referenced_widgets": [
      "7074e529ba9d4453beef2469c963295e",
      "9e7ee67b334a471b81ff1496548fee92",
      "5f95b11a96014d04b4d236ce2fdd815c",
      "21e6c046057f44dfa17e69a5ae8d0a0c",
      "34122ce4fe5648afa06519247c5f1232",
      "09a1174f58cc46e98050235ba2a9c581",
      "01e2895d7c6a434db7993b1e06416e5e",
      "db8a2f40b7db4e38a0bcb46a03643494",
      "f04d59bd9120420aa2b0361db4015c88"
     ]
    },
    "id": "wvo33lbhbKjG",
    "outputId": "1e41b7f7-005d-41e2-e15c-b51035f8e415",
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from ECHO_modules.utilities import polygon_map\n",
    "area_of_interest = polygon_map()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 1000
    },
    "id": "3_FAtBH6b5uT",
    "outputId": "16b454b8-4f2b-4f10-d3fe-c63788cad297"
   },
   "outputs": [],
   "source": [
    "# Populate the CAA Violations DataSet that was created earlier.\n",
    "# Store results for this DataSet as a DataSetResults object\n",
    "try:\n",
    "    aoi_caa_violations = data_sets[\"CAA Violations\"].store_results( region_type=\"Neighborhood\",\n",
    "                            region_value=list(area_of_interest)[0], years=[2020,2024] )\n",
    "except EmptyDataError:\n",
    "    print(\"There are no records in that region for this data set.\")\n",
    "aoi_caa_violations.dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "i6MS8nSbaWJV"
   },
   "source": [
    "## Watersheds\n",
    "Many people may not know the formal name of the watershed they live in (and since watersheds are nested within each other, people live in several watersheds of various sizes, each of which likely has a different name, compounding the challenge).\n",
    "\n",
    "Most people do, however, know which ZIP code or county they live in. To get ECHO data on a watershed basis, first we query the database for the watersheds intersecting a more well-known geography. (Note: technically, we look up watersheds *within* a more well-known geography. Unfortunately, this means some watersheds will never be selected because they are not fully contained by a state. This is an issue to be fixed.)\n",
    "\n",
    "In the following example, we get the watersheds that intersect with a state and then look up \"serious violators' of the Safe Drinking Water Act within one of those watersheds.\n",
    "\n",
    "Note: Watershed geographies do not require setting the `state` variable to retrieve data/store results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 1000
    },
    "id": "wHstF41qn6jp",
    "outputId": "4beb5a33-4616-411d-f822-783c9124b384",
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from ECHO_modules.get_data import get_spatial_data\n",
    "from ECHO_modules.geographies import spatial_tables\n",
    "from ECHO_modules.utilities import show_regions\n",
    "\n",
    "# We look up intersecting watersheds on a state(s) basis.\n",
    "watersheds, state = get_spatial_data(region_type = \"Watershed\", states = [\"NY\"],\n",
    "                                     spatial_tables = spatial_tables)\n",
    "# Map out the watersheds to make it clearer which one we want\n",
    "show_regions(regions = watersheds, states = state, region_type = \"Watershed\",\n",
    "             spatial_tables = spatial_tables)\n",
    "\n",
    "watershed = watersheds.loc[watersheds[\"name\"] == \"Seneca\"] # Filter to the watershed we're interested in\n",
    "ds = make_data_sets([\"SDWA Serious Violators\"]) # Create a DataSet for handling that watershed's data\n",
    "# Store results for this DataSet as a DataSetResults object.\n",
    "# In some cases we have to add a \"0\" back on to the watershed id when it gets\n",
    "# convereted to an integer.\n",
    "seneca_sdwa = ds[\"SDWA Serious Violators\"].store_results(\n",
    "    region_type=\"Watershed\", region_value=[\"0\"+str(watershed[\"huc8\"].iloc[0])])\n",
    "seneca_sdwa.dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ways of Selecting Records on Facilities\n",
    "\n",
    "A user may have facility IDs obtained, perhaps, from outside of the ECHO data. As examples, RCRA data uses a field called ID_NUMBER and CAA uses PGM_. The database has mapped program-specific IDs to the REGISTRY_IDs that are used in the ECHO_EXPORTER table. Because of that mapping, we can generally work with the REGISTRY_ID to identify a facility. But we could also get to the same data, in the case of RCRA, by asking for the facilities by their ID_NUMBER.\n",
    "\n",
    "Here we show the two ways of requesting data on facilities, again using RCRA Violations as our example.\n",
    "\n",
    "### Select Records by Facility Program IDs\n",
    "\n",
    "We'll use some data we acquired earlier, erie_rcra_violations, to get some of the RCRA program-specific ID_NUMBERs. Then we'll ask the database for records using those ID_NUMBERS and show that the number of records is the same"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f'The RCRA Violations data contains {len(erie_rcra_violations.dataframe)} records.')\n",
    "\n",
    "## Even though we already have the same data, we'll get the list of ID_NUMBER values\n",
    "## and get the data another way. We should get the same data.\n",
    "ids = erie_rcra_violations.dataframe.index.unique().to_list()\n",
    "## Pretend we were given this list of facility ID_NUMBERS and asked to get the RCRA Violation\n",
    "## records for the selected years.\n",
    "second_results = data_sets['RCRA Violations'].store_results_by_ids(ids, region_type=\"Facilities\", \n",
    "                                              use_registry_id=False, years=year_range.value)\n",
    "print(f'second_results contains {len(second_results.dataframe)} records.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Select Records by REGISTRY_ID\n",
    "\n",
    "Now we'll go the other direction--we'll take the REGISTRY_IDs from our second_results and ask the database for the matching records. We should again get the same records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ids = second_results.dataframe['REGISTRY_ID'].unique().tolist()\n",
    "## This time, pretend we were given this list of facility REGISTRY_IDs and asked to get the RCRA Violation\n",
    "## records for the selected years.\n",
    "third_results = data_sets['RCRA Violations'].store_results_by_ids(ids, region_type=\"Facilities\", \n",
    "                                              use_registry_id=True, years=year_range.value)\n",
    "print(f'third_results contains {len(third_results.dataframe)} records.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "7jdewSqJysuG"
   },
   "source": [
    "## Multiple Geographies\n",
    "A DataSetResults object can only store one kind of geography (e.g. ZIP codes OR Counties) but it can store multiple values of that geography (e.g. ZIPs: 53703, 52358, 04345, etc.)\n",
    "\n",
    "Notes: ZIP Code geographies do not require setting the `state` variable to retrieve data/store results. Also, unlike with counties, which we look retrieve data for using a list like `region_value = [\"ERIE\"]`, for ZIP codes and watersheds we provide values in a string like `region_value = '14201,14202,14203'`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 593
    },
    "id": "sK8FSImsyuPi",
    "outputId": "4da717b4-7794-4ea7-9722-9ec1379d29df"
   },
   "outputs": [],
   "source": [
    "from ECHO_modules.make_data_sets import make_data_sets # Import relevant module\n",
    "from ECHO_modules.utilities import aggregate_by_facility, point_mapper # Import relevant modules\n",
    "\n",
    "ds = make_data_sets([\"CWA Inspections\"]) # Create a DataSet for handling the data\n",
    "# Store results for this DataSet as a DataSetResults object\n",
    "buffalo_cwa_inspections = ds[\"CWA Inspections\"].store_results(\n",
    "    region_type=\"Zip Code\", region_value='14201,14202,14203')\n",
    "aggregated_results = aggregate_by_facility(\n",
    "    records = buffalo_cwa_inspections,\n",
    "    program = buffalo_cwa_inspections.dataset.name,\n",
    "    other_records=True) # Aggregate each entry using this function\n",
    "# Map each facility as a point, the size of which corresponds to the number of reported violations since 2001.\n",
    "point_mapper(aggregated_results[\"data\"], aggregated_results[\"aggregator\"],\n",
    "             quartiles=True, other_fac=aggregated_results[\"diff\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "-STR7ln6Vj5-"
   },
   "source": [
    "## Multiple Programs\n",
    "\n",
    "We can load as many programs as we like for each set of geographies (in DataSetResults objects).\n",
    "\n",
    "Information about available programs can be found in [here](https://github.com/edgi-govdata-archiving/ECHO_modules/blob/v0-1-0/SBU-db.md).\n",
    "\n",
    "The following produces charts that summarize inspections, violations, and penalties under the Clean Air Act for two of New York's Congressional Districts - #25 and #26."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 1000
    },
    "id": "kgeYczbAkB7T",
    "outputId": "1d58d2e2-2cc3-4471-a667-192e44d092a1"
   },
   "outputs": [],
   "source": [
    "from ECHO_modules.make_data_sets import make_data_sets # Import relevant modules\n",
    "from ECHO_modules.utilities import aggregate_by_facility, point_mapper\n",
    "\n",
    "ny_cds_caa_inspections = data_sets[\"CAA Inspections\"].store_results(\n",
    "    region_type=\"Congressional District\", region_value=[\"25\", \"26\"], state = \"NY\",\n",
    "    years=[2016,2024])\n",
    "ny_cds_caa_violations = data_sets[\"CAA Violations\"].store_results(\n",
    "    region_type=\"Congressional District\", region_value=[\"25\", \"26\"], state = \"NY\",\n",
    "    years=[2016,2024])\n",
    "ny_cds_caa_penalties = data_sets[\"CAA Penalties\"].store_results(\n",
    "    region_type=\"Congressional District\", region_value=[\"25\", \"26\"], state = \"NY\",\n",
    "    years=[2016,2024])\n",
    "\n",
    "ny_cds_caa_inspections.show_chart()\n",
    "ny_cds_caa_violations.show_chart()\n",
    "ny_cds_caa_penalties.show_chart()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "3BxIp8NfnTfG"
   },
   "source": [
    "## GHGs and other Air Emissions\n",
    "Beyond enforcement and compliance information, the ECHO database - and our copy of it at Stony Brook University - contains records of industry's self-report releases of various pollutants. These records originate from the Greenhouse Gas Reporting Program and the Toxics Release Inventory (TRI).\n",
    "\n",
    "The following code returns these records for New York state. If you are interested in a specific GHG or TRI pollutant, some analysis would have to be written outside existing ECHO_modules - we haven't developed specific code to filter these tables to a pollutant(s). An example of this extra code is shown below, however - `ny_tri.dataframe.loc[ny_tri.dataframe['POLLUTANT_NAME'].str.lower().str.contains(\"mercury\")]`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 1000
    },
    "id": "P1qab-sEoC-u",
    "outputId": "6f77ca46-b6c7-4e9b-f96c-1301f0b18d24"
   },
   "outputs": [],
   "source": [
    "from ECHO_modules.make_data_sets import make_data_sets # Import relevant modules\n",
    "from ECHO_modules.utilities import aggregate_by_facility, point_mapper\n",
    "\n",
    "ny_ghg = data_sets[\"Greenhouse Gas Emissions\"].store_results(\n",
    "    region_type=\"State\", region_value = \"NY\", state = \"NY\",\n",
    "    years=[2016,2024])\n",
    "ny_ghg.show_chart() # Total reported emissions in lbs (normalized to CO2e)\n",
    "ny_tri = data_sets[\"Toxic Releases\"].store_results(\n",
    "    region_type=\"State\", region_value = \"NY\", state = \"NY\",\n",
    "    years=[2016,2024])\n",
    "ny_tri.show_chart() # Total emissions in lbs\n",
    "\n",
    "# Filter NY_TRI records to just ones where the pollutant is mercury\n",
    "ny_tri.dataframe = ny_tri.dataframe.loc[\n",
    "    ny_tri.dataframe['POLLUTANT_NAME'].str.lower().str.contains(\"mercury\")]\n",
    "ny_tri.show_chart() # Chart total mercury emissions in lbs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ncL4gZ2EwmWC"
   },
   "source": [
    "## Discharge Monitoring Reports (DMRs)\n",
    "\n",
    "Facilities regulated under the Clean Water Act are required to submit monitoring reports directly to EPA. These represent extensive records of levels of pollutants discharged into waterbodies. We currently provide access to reports from Fiscal Year 2022.\n",
    "\n",
    "The following code maps facilities with DMRs across two watersheds (note: watershed IDs currently have to be looked up separately. See \"Watersheds\" section above)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 593
    },
    "id": "vqLHFLzCyFHB",
    "outputId": "f1b0e6bc-f4f3-490a-ad45-2acaa15b483f"
   },
   "outputs": [],
   "source": [
    "from ECHO_modules.get_data import get_spatial_data # Module for getting spatial data from the SBU database\n",
    "from ECHO_modules.geographies import spatial_tables # Variables that support spatial queries\n",
    "from ECHO_modules.utilities import bivariate_map, map_style # Use this function and variable to make our map\n",
    "\n",
    "dmrs = data_sets[\"2022 Discharge Monitoring\"].store_results(region_type=\"Watershed\",\n",
    "                                                     region_value = '04120103, 04120102')\n",
    "# The facilities in this watershed\n",
    "dmrs.dataframe = dmrs.dataframe.drop_duplicates(subset=[\"FAC_NAME\"])\n",
    "# Query and return spatial data\n",
    "watersheds, state = get_spatial_data(region_type = \"Watershed\", states = [\"NY\"],\n",
    "                                     spatial_tables = spatial_tables,\n",
    "                                     region_filter = [\"04120103\", \"04120102\"])\n",
    "# Map each unique DMR-reporting facility in these watersheds\n",
    "# We only keep the columns we need (name, geometry) and use them for pop-ups\n",
    "# with the fields/aliases parameters\n",
    "bivariate_map(regions = watersheds[[\"name\", \"geometry\"]],\n",
    "              points = geopandas.GeoDataFrame(dmrs.dataframe,\n",
    "                                              geometry=geopandas.points_from_xy(\n",
    "                                                  dmrs.dataframe['FAC_LONG'],\n",
    "                                                  dmrs.dataframe['FAC_LAT']),\n",
    "                                                  crs=4269\n",
    "                                              )[[\"FAC_NAME\", \"geometry\"]],\n",
    "              region_fields=[\"name\",],\n",
    "              region_aliases=[\"Watershed Name: \",],\n",
    "              points_fields=[\"FAC_NAME\",],\n",
    "              points_aliases=[\"Facility Name: \"],\n",
    "              )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "cH4Pcae1BQYQ"
   },
   "source": [
    "## Mapping\n",
    "We can symbolize inspections, violations, and so on for areas such as ZIP Codes and Congressional Districts using the `choropleth()` function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 593
    },
    "id": "_xur2XH4Bba7",
    "outputId": "a7c737ce-1a1f-45da-d03f-a8bb5db783a6"
   },
   "outputs": [],
   "source": [
    "# Function for aggregating data by spatial unit and mapping data values by that unit (e.g. ZIP code)\n",
    "from ECHO_modules.utilities import aggregate_by_geography, choropleth\n",
    "from ECHO_modules.get_data import get_spatial_data # Function for getting zip code boundaries\n",
    "from ECHO_modules.geographies import spatial_tables # Variables that support spatial queries\n",
    "\n",
    "zips = '14201, 14202, 14203, 14204, 14205, 14206, 14207, 14208, 14209, 14210, 14211, \\\n",
    "14212, 14213, 14214, 14215, 14216, 14217, 14218, 14219, 14220, 14221, 14222, \\\n",
    "14223, 14224, 14225, 14226, 14227, 14228, 14231, 14233, 14240, 14241, 14260, \\\n",
    "14261, 14263, 14264, 14265, 14267, 14269, 14270, 14272, 14273, 14276, 14280'\n",
    "# Create a duplicate but differently formatted list for the get_spatial_data function\n",
    "zips_list = [str(z) for z in zips.split(\", \")]\n",
    "\n",
    "# Get attribute data\n",
    "ny_zips_cwa_inspections = data_sets[\"CWA Violations\"].store_results(region_type=\"Zip Code\",\n",
    "                                                             region_value=zips, state = \"NY\",\n",
    "                                                             years=[2020,2024]) # Store results for this DataSet as a DataSetResults object\n",
    "\n",
    "\n",
    "# Aggregate attribute data\n",
    "ny_zips_aggregated = aggregate_by_geography(ny_zips_cwa_inspections,\n",
    "                                            agg_type=\"sum\",\n",
    "                                            spatial_tables=spatial_tables)\n",
    "# Reset the index to make the zip codes available to the choropleth function\n",
    "ny_zips_aggregated.reset_index(inplace=True)\n",
    "\n",
    "# Map\n",
    "choropleth(polygons = ny_zips_aggregated,\n",
    "           attribute = \"NUME90Q\",\n",
    "           key_id = \"FAC_ZIP\",\n",
    "           legend_name = \"Map\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "pn85N-N5--GC"
   },
   "source": [
    "## Custom Queries: EJScreen\n",
    "\n",
    "Records from EPA's EJScreen (2021) are available through custom SQL queries.\n",
    "\n",
    "For example, the following returns EJScreen information for the state of New York and where the % of the population defined as a racial minority is greater than 75% and the % of the population defined as low-income is greater than 50%.\n",
    "\n",
    "For more information about EJScreen, see the [documentation](https://gaftp.epa.gov/EJScreen/2021/2021_EJSCREEEN_columns-explained.xlsx) (.xlsx file)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 964
    },
    "id": "LwV-Z_gv4ZSo",
    "outputId": "75765a27-c175-4db6-dede-5490f0074651"
   },
   "outputs": [],
   "source": [
    "from ECHO_modules.get_data import get_echo_data\n",
    "\n",
    "# This query selects Census Block Group records from EJScreen for the state of New York and where the % of the population defined as a racial minority is greater than 75% and the % of the population defined as low-income is greater than 50%\n",
    "sql = 'SELECT * FROM \"EJSCREEN_2021_USPR\" WHERE \"ST_ABBREV\" = \\'NY\\' ' +\\\n",
    "'AND \"MINORPCT\" > .75 AND \"LOWINCPCT\" > .5'\n",
    "results = get_echo_data(sql)\n",
    "results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "provenance": []
  },
  "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.12.7"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}