search

edgi-govdata-archiving / ECHO-Cross-Program

Jupyter Notebooks for ECHO that use data from multiple EPA programs
https://colab.research.google.com/github/edgi-govdata-archiving/ECHO-Cross-Program/blob/master/ECHO-Cross-Programs.ipynb
GNU General Public License v3.0
8 stars 5 forks source link

Fix Air Inspections Materialized View #85

Closed ericnost closed 3 years ago

ericnost commented 3 years ago

Hi @shansen5 I'm wondering if we can ask Paul to reconfigure the Air Inspections Materialized View. Currently, I'm not sure which table it's drawing from - I think ICIS_FEC_EPA_INSPECTIONS - but it should be drawing from ICIS-AIR_FCES_PCES. ICIS_FEC_EPA_INSPECTIONS includes all US EPA inspections for ALL programs, not just CAA. ICIS-AIR_FCES_PCES is CAA inspections, both state agency and US EPA.

ericnost commented 3 years ago

The id field is pgm_sys_id, date column is ACTUAL_END_DATE, agg_col is STATE_EPA_FLAG, agg_type is count.

shansen5 commented 3 years ago

We do already have a view for the ICIS-AIR_FCES_PCES table. It is AIR_COMPLIANCE_MVIEW, and is available in make_data_sets.py as 'CAA Compliance', which is currently not included in the data sets made available in AllPrograms.ipynb.
So we could do a couple of things:

  1. Add 'CAA Compliance' to the data sets in AllPrograms cell #3, in place of 'CAA Inspections". Use the 'CAA Compliance' data set to generate the answers currently being provided by 'CAA Inspections'.
  2. Change make_data_sets.py so that 'CAA Inspections' uses AIR_COMPLIANCE_MVIEW instead of AIR_INSPECTIONS_MVIEW.
    I would propose implementing option 1.
    (The index, date field and agg field and type are in agreement in make_data_sets.py.)
ericnost commented 3 years ago

Ok! Let's go with option 1. We'll just want to keep in mind that CAA Compliance = CAA Inspections to Determine Compliance