ECHO_National.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Ylalhm0wbt2p"
      },
      "source": [
        "| ![EEW logo](https://github.com/edgi-govdata-archiving/EEW-Image-Assets/blob/master/Jupyter%20instructions/eew.jpg?raw=true) | ![EDGI logo](https://github.com/edgi-govdata-archiving/EEW-Image-Assets/blob/master/Jupyter%20instructions/edgi.png?raw=true) |\n",
        "|---|---|\n",
        "\n",
        "#### This notebook is licensed under GPL 3.0. Please visit our Github repo for more information:  https://github.com/edgi-govdata-archiving/ECHO-Cross-Program\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",
        "#### For more information about this project, visit https://www.environmentalenforcementwatch.org/"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xwpXPiNLbt2x"
      },
      "source": [
        "## How to Run this Notebook\n",
        "* If you click on a gray **code** cell, a little “play button” arrow appears on the left. If you click the play button, it will run the code in that cell (“**running** a cell”). The button will animate. When the animation stops, the cell has finished running.\n",
        "![Where to click to run the cell](https://github.com/edgi-govdata-archiving/EEW-Image-Assets/blob/master/Jupyter%20instructions/pressplay.JPG?raw=true)\n",
        "* You may get a warning that the notebook was not authored by Google. We know, we authored them! It’s okay. Click “Run Anyway” to continue.\n",
        "![Error Message](https://github.com/edgi-govdata-archiving/EEW-Image-Assets/blob/master/Jupyter%20instructions/warning-message.JPG?raw=true)\n",
        "* Run all of the cells in a Notebook to make a complete report. Please feel free to look at and **learn about each result as you create it**!\n",
        "\n",
        "---"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vU4AqThS-37L"
      },
      "source": [
        "# Nationwide statistics about environmental enforcement and compliance trends"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "czia5t5ebt20"
      },
      "source": [
        "## Setup\n",
        "Here we load some helper code to get us going."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "vF3iTaeCbt23"
      },
      "outputs": [],
      "source": [
        "# Import code libraries\n",
        "!pip install git+https://github.com/edgi-govdata-archiving/ECHO_modules &>/dev/null;\n",
        "!pip install geopandas &>/dev/null;\n",
        "\n",
        "import urllib.parse\n",
        "import pandas as pd\n",
        "import numpy as np\n",
        "import matplotlib.pyplot as plt\n",
        "%matplotlib inline\n",
        "import requests\n",
        "import csv\n",
        "import datetime\n",
        "import folium\n",
        "from folium.plugins import FastMarkerCluster\n",
        "import ipywidgets as widgets\n",
        "from IPython.core.display import display, HTML\n",
        "from pandas.errors import EmptyDataError\n",
        "from ECHO_modules.get_data import get_echo_data\n",
        "\n",
        "def formatter(value):\n",
        "  return \"{:0.2f}\".format(value)\n",
        "\n",
        "print(\"Done!\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "F7ZY4G6kVwLj"
      },
      "source": [
        "Here we set up some code to help us store and eventually export the metrics."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "aDnEBqa9Vvuc"
      },
      "outputs": [],
      "source": [
        "inspections = dict()\n",
        "violations = dict()\n",
        "enforcements = dict()\n",
        "penalties = dict()\n",
        "emissions = dict()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "su7_wvKWbt3Z"
      },
      "source": [
        "## Start getting data\n",
        "First, get summary data from the ECHO_EXPORTER table."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "cBkHsLPtbt3c"
      },
      "outputs": [],
      "source": [
        "from ECHO_modules.get_data import get_echo_data\n",
        "\n",
        "# Get everything we will need from ECHO_EXPORTER in a single DB query.\n",
        "# We can then use the full dataframe to specialize views of it.\n",
        "full_echo_data = None\n",
        "column_mapping = {\n",
        "    '\"REGISTRY_ID\"': str,\n",
        "    '\"FAC_NAME\"': str,\n",
        "    '\"FAC_LAT\"': float,\n",
        "    '\"FAC_LONG\"': float,\n",
        "    '\"AIR_IDS\"': str,\n",
        "    '\"NPDES_IDS\"': str,\n",
        "    '\"RCRA_IDS\"': str,\n",
        "    '\"DFR_URL\"': str,\n",
        "    '\"AIR_FLAG\"': str,\n",
        "    '\"NPDES_FLAG\"': str,\n",
        "    '\"GHG_FLAG\"': str,\n",
        "    '\"RCRA_FLAG\"': str,\n",
        "    '\"FAC_ACTIVE_FLAG\"': str\n",
        "}\n",
        "column_names = list( column_mapping.keys() )\n",
        "columns_string = ','.join( column_names )\n",
        "sql = 'select ' + columns_string + ' from \"ECHO_EXPORTER\" where \"AIR_FLAG\" = \\'Y\\' or \"NPDES_FLAG\" = \\'Y\\' or \"GHG_FLAG\" = \\'Y\\' or \"RCRA_FLAG\" = \\'Y\\''\n",
        "try:\n",
        "    # Don't index.\n",
        "    full_echo_data = get_echo_data( sql )\n",
        "except EmptyDataError:\n",
        "    print(\"\\nThere are no EPA facilities for this query.\\n\")\n",
        "full_echo_data"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Fnla1jRuQu75"
      },
      "source": [
        "## Number of Currently Regulated Facilities Per Program"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "mEcRLg9YRHZI"
      },
      "outputs": [],
      "source": [
        "air_fac = full_echo_data.loc[(full_echo_data[\"AIR_FLAG\"]==\"Y\") & (full_echo_data[\"FAC_ACTIVE_FLAG\"]==\"Y\")].shape[0]\n",
        "water_fac = full_echo_data.loc[(full_echo_data[\"NPDES_FLAG\"]==\"Y\") & (full_echo_data[\"FAC_ACTIVE_FLAG\"]==\"Y\")].shape[0]\n",
        "waste_fac = full_echo_data.loc[(full_echo_data[\"RCRA_FLAG\"]==\"Y\") & (full_echo_data[\"FAC_ACTIVE_FLAG\"]==\"Y\")].shape[0]\n",
        "ghg_fac = full_echo_data.loc[(full_echo_data[\"GHG_FLAG\"]==\"Y\") & (full_echo_data[\"FAC_ACTIVE_FLAG\"]==\"Y\")].shape[0]\n",
        "\n",
        "display(HTML(\"<h3>There are \"+ str(air_fac) + \" facilities currently regulated under the Clean Air Act.</h3>\"))\n",
        "display(HTML(\"<h3>There are \"+ str(water_fac) + \" facilities currently regulated under the Clean Water Act.</h3>\"))\n",
        "display(HTML(\"<h3>There are \"+ str(waste_fac) + \" facilities currently regulated under RCRA (hazardous waste).</h3>\"))\n",
        "display(HTML(\"<h3>There are \"+ str(ghg_fac) + \" facilities currently reporting greenhouse gas emissions.</h3>\"))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TUf6AQfPbt3z"
      },
      "source": [
        "## Clean Air Act inspections in 2022"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "rGzitWnObt33"
      },
      "outputs": [],
      "source": [
        "# Use SQL to search for and select the data about air stack tests\n",
        "air_inspections = None\n",
        "try:\n",
        "    sql = 'select * from \\\"ICIS-AIR_FCES_PCES\\\" where \\\"ACTUAL_END_DATE\\\" like \\'__-__-2022\\''\n",
        "\n",
        "    # Download the data from that URL\n",
        "    air_inspections = get_echo_data( sql, 'pgm_sys_id' )\n",
        "except EmptyDataError:\n",
        "    print( \"No data found\")\n",
        "\n",
        "air_inspections"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "lZfbWeMnK7Yk"
      },
      "outputs": [],
      "source": [
        "# Number of inspections in 2022 per 1000 regulated facilities\n",
        "air_inspections_metric = formatter((air_inspections.shape[0] / air_fac) * 1000) # Divide by regulated facilities and multiply by desired rate (per 1000)\n",
        "inspections[\"CAA\"] = air_inspections_metric\n",
        "display(HTML(\"<h3>\"+ air_inspections_metric +\" inspections per 1000 facilities</h3>\"))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RMjxybzbbt5X"
      },
      "source": [
        "## Violations of the Clean Air Act in 2022\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "z1N2CuKTbt5Z"
      },
      "outputs": [],
      "source": [
        "air_violations = None\n",
        "try:\n",
        "    sql = 'select * from \"ICIS-AIR_VIOLATION_HISTORY\" where \"EARLIEST_FRV_DETERM_DATE\" like \\'__-__-2022\\' or \"HPV_DAYZERO_DATE\" like \\'__-__-2022\\''\n",
        "\n",
        "    air_violations = get_echo_data( sql, \"pgm_sys_id\" )\n",
        "\n",
        "    # Optional: remove \"FACIL\" violations, which are paperwork violations according to: https://19january2017snapshot.epa.gov/sites/production/files/2013-10/documents/frvmemo.pdf\n",
        "    # air_violations = air_violations.loc[(air_violations[\"POLLUTANT_DESCS\"]!=\"FACIL\")]\n",
        "except EmptyDataError:\n",
        "    print( \"No data found\")\n",
        "air_violations"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "cellView": "both",
        "id": "KUQs-_I045oc"
      },
      "outputs": [],
      "source": [
        "# Number of high priority and federally reportable violations per 1000 regulated facilities\n",
        "air_violations_metric = formatter((air_violations.shape[0] / air_fac) * 1000) # Divide by regulated facilities and multiply by desired rate (per 1000)\n",
        "violations[\"CAA\"] = air_violations_metric\n",
        "display(HTML(\"<h3>\"+air_violations_metric+\" violations per 1000 facilities </h3>\"))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DGdQDifqo3Bg"
      },
      "source": [
        "## Formal Enforcement Actions and Penalties under the Clean Air Act in 2022"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "vKHjSEl6o0tp"
      },
      "outputs": [],
      "source": [
        "air_enforcements = None\n",
        "try:\n",
        "    sql = 'select * from \"ICIS-AIR_FORMAL_ACTIONS\" where \"SETTLEMENT_ENTERED_DATE\" like \\'__/__/2022\\''\n",
        "\n",
        "    air_enforcements = get_echo_data( sql, \"pgm_sys_id\" )\n",
        "except EmptyDataError:\n",
        "    print( \"No data found\")\n",
        "air_enforcements"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "3LYD5_dd6bub"
      },
      "outputs": [],
      "source": [
        "# Number of formal actions in 2022 per violation\n",
        "air_enforcements_metric = formatter(air_enforcements.shape[0]/air_violations.shape[0]) # Formal actions divided by number of violations\n",
        "enforcements[\"CAA\"] = air_enforcements_metric\n",
        "display(HTML(\"<h3>\"+air_enforcements_metric +\" formal enforcement actions per violation</h3>\"))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Z7ykKG7V7G4b"
      },
      "outputs": [],
      "source": [
        "# Penalties each year per violating facility\n",
        "air_penalties = air_enforcements.loc[air_enforcements[\"PENALTY_AMOUNT\"]>0]\n",
        "air_penalties_metric = formatter(sum(air_penalties[\"PENALTY_AMOUNT\"]) / len(air_violations[\"PGM_SYS_ID\"].unique())) #Divide the sum of penalties by number of violating facilities\n",
        "air_penalties_max = formatter(max(air_penalties[\"PENALTY_AMOUNT\"]))\n",
        "air_penalties_min = formatter(min(air_penalties[\"PENALTY_AMOUNT\"]))\n",
        "penalties[\"CAA\"] = air_penalties_metric\n",
        "display(HTML(\"<h3>$\"+air_penalties_metric +\" per facility in violation</h3>\"))\n",
        "display(HTML(\"<h3>Max: $\"+air_penalties_max +\"</h3>\"))\n",
        "display(HTML(\"<h3>Min: $\"+air_penalties_min +\"</h3>\"))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bevtiFu2bt6M"
      },
      "source": [
        "---"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hOmeJnXgnzOC"
      },
      "source": [
        "## Clean Water Act inspections in 2022"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "gHXwXWcqnyNO"
      },
      "outputs": [],
      "source": [
        "# Find facilities with pollutant exceedences\n",
        "water_inspections = None\n",
        "try:\n",
        "    sql = 'select \"NPDES_ID\", \"REGISTRY_ID\", \"ACTUAL_END_DATE\", \"STATE_EPA_FLAG\"' + \\\n",
        "        ' from \"NPDES_INSPECTIONS\" where \"ACTUAL_END_DATE\" like \\'__/__/2022\\''\n",
        "\n",
        "    water_inspections = get_echo_data( sql, \"NPDES_ID\" )\n",
        "except EmptyDataError:\n",
        "    print( \"No data found\")\n",
        "water_inspections"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "YoBlWh8V8BD-"
      },
      "outputs": [],
      "source": [
        "# Number of inspections in 2022 per 1000 regulated facilities\n",
        "water_inspections_metric = formatter((water_inspections.shape[0] / water_fac) * 1000) #Divide by regulated facilities and multiply by desired rate (per 1000)\n",
        "inspections[\"CWA\"] = water_inspections_metric\n",
        "display(HTML(\"<h3>\"+water_inspections_metric +\" inspections per 1000 facilities</h3>\"))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "C6ntT917bt6N"
      },
      "source": [
        "## Violations of the Clean Water Act in 2022"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "FmsI5gJAbt6P"
      },
      "outputs": [],
      "source": [
        "# Find facilities with water permit violations\n",
        "water_violations = None\n",
        "try:\n",
        "    sql = 'select * from \"NPDES_QNCR_HISTORY\" where \"YEARQTR\" = 20221 or \"YEARQTR\" = 20222 or \"YEARQTR\" = 20223 or \"YEARQTR\" = 20224'\n",
        "    water_violations = get_echo_data( sql, \"NPDES_ID\" )\n",
        "except EmptyDataError:\n",
        "    print( \"No data found\")\n",
        "water_violations"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "EhmXcwIQ8kXA"
      },
      "outputs": [],
      "source": [
        "# Number of violations each year per 1000 regulated facilities\n",
        "# Sum violations\n",
        "water_violations[\"Sum\"] = water_violations[\"NUME90Q\"]\t+ water_violations[\"NUMCVDT\"] + water_violations[\"NUMSVCD\"]\t+ water_violations[\"NUMPSCH\"]\n",
        "water_violations_metric = formatter((np.sum(water_violations[\"Sum\"]) / water_fac) * 1000) #Divide by regulated facilities and multiply by desired rate (per 1000)\n",
        "violations[\"CWA\"] = water_violations_metric\n",
        "display(HTML(\"<h3>\"+water_violations_metric+\" violations per 1000 facilities</h3>\"))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8rMriPuXpkA_"
      },
      "source": [
        "## Enforcement Actions and Penalties under the Clean Water Act in 2022"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "g2fIBDtppjeI"
      },
      "outputs": [],
      "source": [
        "# Find facilities with pollutant exceedences\n",
        "water_enforcements = None\n",
        "try:\n",
        "    sql = 'select \"NPDES_ID\", \"AGENCY\", \"ENF_TYPE_DESC\", \"SETTLEMENT_ENTERED_DATE\", \"FED_PENALTY_ASSESSED_AMT\", \"STATE_LOCAL_PENALTY_AMT\"' + \\\n",
        "        ' from \"NPDES_FORMAL_ENFORCEMENT_ACTIONS\" where \"SETTLEMENT_ENTERED_DATE\" like \\'__/__/2022\\''\n",
        "\n",
        "    water_enforcements = get_echo_data( sql, \"NPDES_ID\" )\n",
        "except EmptyDataError:\n",
        "    print( \"No data found\")\n",
        "water_enforcements"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "v7OKOgCz85l2"
      },
      "outputs": [],
      "source": [
        "# Number of formal actions in 2022 per violation\n",
        "water_enforcements_metric = formatter(water_enforcements.shape[0]/water_violations.shape[0]) # Formal actions divided by number of violations\n",
        "enforcements[\"CWA\"] = water_enforcements_metric\n",
        "display(HTML(\"<h3>\"+water_enforcements_metric +\" formal enforcement actions per violation</h3>\"))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "DCFkWo0N9XHl"
      },
      "outputs": [],
      "source": [
        "# Penalties each year per violating facility\n",
        "# Find violating facilities (not all in NPDES QNCR are violating...)\n",
        "water_violators = water_violations.loc[water_violations[\"Sum\"]>0]\n",
        "water_violators = len(water_violators.index.unique())\n",
        "water_enforcements[\"StateLocalFedFines\"] = water_enforcements[\"FED_PENALTY_ASSESSED_AMT\"].fillna(0) + water_enforcements[\"STATE_LOCAL_PENALTY_AMT\"].fillna(0)\n",
        "water_penalties = water_enforcements.loc[water_enforcements[\"StateLocalFedFines\"]>0]\n",
        "water_penalties_metric = formatter(sum(water_penalties[\"StateLocalFedFines\"]) / water_violators) #Divide the sum of penalties by number of penalized facilities\n",
        "water_penalties_max = formatter(max(water_penalties[\"StateLocalFedFines\"]))\n",
        "water_penalties_min = formatter(min(water_penalties[\"StateLocalFedFines\"]))\n",
        "penalties[\"CWA\"] = water_penalties_metric\n",
        "display(HTML(\"<h3>$\"+water_penalties_metric +\" per facility in violation</h3>\"))\n",
        "display(HTML(\"<h3>Max: $\"+water_penalties_max +\"</h3>\"))\n",
        "display(HTML(\"<h3>Min: $\"+water_penalties_min +\"</h3>\"))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kDFULgy2qPSD"
      },
      "source": [
        "## RCRA inspections in 2022"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "0Kq8C8oFqRKe"
      },
      "outputs": [],
      "source": [
        "# Find facilities with pollutant exceedences\n",
        "waste_inspections = None\n",
        "try:\n",
        "    sql = 'select * from \"RCRA_EVALUATIONS\" where \"EVALUATION_START_DATE\" like \\'__/__/2022\\''\n",
        "\n",
        "    waste_inspections = get_echo_data( sql, \"ID_NUMBER\" )\n",
        "except EmptyDataError:\n",
        "    print( \"No data found\")\n",
        "waste_inspections"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "HasmoprP9o0Z"
      },
      "outputs": [],
      "source": [
        "# Number of inspections in 2022 per 1000 regulated facilities\n",
        "waste_inspections_metric = formatter((waste_inspections.shape[0] / waste_fac) * 1000) #Divide by regulated facilities and multiply by desired rate (per 1000)\n",
        "inspections[\"RCRA\"] = waste_inspections_metric\n",
        "display(HTML(\"<h3>\"+waste_inspections_metric+\" inspections per 1000 facilities</h3>\"))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3ycMCo9WqRT_"
      },
      "source": [
        "## Violations of RCRA in 2022"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Jj-LlSbnqS3m"
      },
      "outputs": [],
      "source": [
        "# Find facilities with pollutant exceedences\n",
        "waste_violations = None\n",
        "try:\n",
        "    sql = 'select * from \"RCRA_VIOLATIONS\" where \"DATE_VIOLATION_DETERMINED\" like \\'__/__/2022\\''\n",
        "\n",
        "    waste_violations = get_echo_data( sql, \"ID_NUMBER\" )\n",
        "except EmptyDataError:\n",
        "    print( \"No data found\")\n",
        "waste_violations"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "gALP4P5y-ASz"
      },
      "outputs": [],
      "source": [
        "# Number of violations in 2022 per 1000 regulated facilities\n",
        "waste_violations_metric = formatter((waste_violations.shape[0] / waste_fac) * 1000) #Divide by regulated facilities and multiply by desired rate (per 1000)\n",
        "violations[\"RCRA\"] = waste_violations_metric\n",
        "display(HTML(\"<h3>\"+waste_violations_metric+\" violations per 1000 facilities</h3>\"))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HdGcQI-gqTBi"
      },
      "source": [
        "## Enforcement Actions and Penalties under RCRA in 2022"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "M8s_Z0M0qUfT"
      },
      "outputs": [],
      "source": [
        "# Find facilities with enforcement actions\n",
        "waste_enforcements = None\n",
        "try:\n",
        "    sql = 'select * from \"RCRA_ENFORCEMENTS\" where \"ENFORCEMENT_ACTION_DATE\" like \\'__/__/2022\\''\n",
        "\n",
        "    waste_enforcements = get_echo_data( sql, \"ID_NUMBER\" )\n",
        "except EmptyDataError:\n",
        "    print( \"No data found\")\n",
        "waste_enforcements"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Qif-FilM-bns"
      },
      "outputs": [],
      "source": [
        "# Number of enforcement actions each year per violation\n",
        "waste_enforcements_metric = formatter(waste_enforcements.shape[0] / waste_violations.shape[0])\n",
        "enforcements[\"RCRA\"] = waste_enforcements_metric\n",
        "display(HTML(\"<h3>\"+waste_enforcements_metric+\" enforcement actions per violation</h3>\"))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "MAPXMRZt-r-3"
      },
      "outputs": [],
      "source": [
        "# Penalties each year per violating facility\n",
        "waste_penalties = waste_enforcements.loc[waste_enforcements[\"FMP_AMOUNT\"]>0]\n",
        "waste_penalties_metric = formatter(sum(waste_penalties[\"FMP_AMOUNT\"]) / len(waste_violations.index.unique())) #Divide by penalized facilities\n",
        "waste_penalties_max = formatter(max(waste_penalties[\"FMP_AMOUNT\"]))\n",
        "waste_penalties_min = formatter(min(waste_penalties[\"FMP_AMOUNT\"]))\n",
        "penalties[\"RCRA\"] = waste_penalties_metric\n",
        "display(HTML(\"<h3>$\"+waste_penalties_metric +\" per facility in violation</h3>\"))\n",
        "display(HTML(\"<h3>Max: $\"+waste_penalties_max +\"</h3>\"))\n",
        "display(HTML(\"<h3>Min: $\"+waste_penalties_min +\"</h3>\"))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YQY2458NqUp9"
      },
      "source": [
        "## Greenhouse Gas Emissions in 2021 (latest data available)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "DN8EVg29qW1k"
      },
      "outputs": [],
      "source": [
        "# Find GHG emissions\n",
        "ghg_emissions = None\n",
        "try:\n",
        "    sql = 'select * from \"POLL_RPT_COMBINED_EMISSIONS\" where \"REPORTING_YEAR\" = \\'2021\\' and \"PGM_SYS_ACRNM\" = \\'E-GGRT\\''\n",
        "\n",
        "    ghg_emissions = get_echo_data( sql)\n",
        "except EmptyDataError:\n",
        "    print( \"No data found\")\n",
        "ghg_emissions"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "WYm9skUM_OV7"
      },
      "outputs": [],
      "source": [
        "# Emissions in 2021 per facility\n",
        "ghg_emissions_metric = formatter(np.nansum(ghg_emissions[\"ANNUAL_EMISSION\"]) / len(ghg_emissions[\"REGISTRY_ID\"].unique())) #Divide by reporting facility\n",
        "ghg_emissions_fac = ghg_emissions.groupby(\"PGM_SYS_ID\")[[\"ANNUAL_EMISSION\"]].sum() # Group by facility\n",
        "ghg_emissions_max = formatter(np.nanmax(ghg_emissions_fac[\"ANNUAL_EMISSION\"]))\n",
        "ghg_emissions_min = formatter(np.nanmin(ghg_emissions_fac.loc[ghg_emissions_fac[\"ANNUAL_EMISSION\"]>0][\"ANNUAL_EMISSION\"]))\n",
        "emissions[\"GHG\"] = ghg_emissions_metric\n",
        "display(HTML(\"<h3>\"+ghg_emissions_metric+\" MTCO2e (metric tons of carbon dioxide equivalent) emissions per reporting facility</h3>\"))\n",
        "display(HTML(\"<h3>Max: \"+ghg_emissions_max+\" MTCO2e (metric tons of carbon dioxide equivalent) emissions</h3>\"))\n",
        "display(HTML(\"<h3>Min: \"+ghg_emissions_min+\" MTCO2e (metric tons of carbon dioxide equivalent) emissions</h3>\"))\n",
        "ghg_emissions_fac"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2ybjI0eGllRg"
      },
      "source": [
        "# Data Export"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "EGHkxhYzlrKp"
      },
      "outputs": [],
      "source": [
        "data = [inspections,\n",
        "violations,\n",
        "enforcements,\n",
        "penalties,\n",
        "emissions]\n",
        "\n",
        "units = [\"#inspections per 1000\",\n",
        "\"#violations per 1000\",\n",
        "\"#actions per facility in violation\",\n",
        "\"$ per facility in violation\",\n",
        "\"amount of emissions (metric tons)\"]\n",
        "\n",
        "short_units = [\"inspectionsper1000\",\n",
        "\"violationsper1000\",\n",
        "\"enforcementsperviolatingfacility\",\n",
        "\"penaltiesperviolatingfacility\",\n",
        "\"emissions2021\"]\n",
        "\n",
        "for index, program in enumerate(data):\n",
        "    # create dataframe\n",
        "    df = pd.DataFrame(program, index=[0]).T\n",
        "    df = df.rename(columns={0: units[index]})\n",
        "    filename= short_units[index]+\"_All_USA_pg4_2022.csv\"\n",
        "    df.to_csv(filename)\n",
        "    print(df)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ZPm-PEDiJyMH"
      },
      "outputs": [],
      "source": []
    }
  ],
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "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.8.5"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}