chicago / clear-water

Forecasting elevated levels of E. coli at Chicago beaches to provide proper warning to beach-goers.

Clear Water

The City of Chicago's Clear Water project brings an innovative approach to beach water quality monitoring. It uses a machine learning prediction technique to better forecast the bacteria levels at Chicago beaches. The model works by interpreting patterns in the results of DNA tests at a handful of beaches across the City, which are then extrapolated to forecast the water quality at other, untested beaches. This method provides a new way for beach managers to save money on expensive rapid water quality tests.

Initial evaluation of the model has shown a significant improvement over current methods of predicting beach water quality. Testing is ongoing, and the 2017 beach season is being analyzed to further improve and evaluate the model's performance.

Getting started with R

This project uses R, which you can download here. We recommend you also install RStudio. You can open this project in RStudio by opening the clear-water.Rproj file.

If you are new to R, check out some basics here.

Running the model

To generate the model, open the Master.R file. Inside the file, you will see settings that you can tweak to change the predictors and other facets of the model. Once you're ready, run all the code in the file. If you've successfully generated the model, you'll see ROC and Precision/Recall plots appear in RStudio. You'll also have a Data Frame in R called model_summary that contains the results of the model evaluation.

Running the model in production

This repo is one of two GitHub repos that make up the Clear Water project. The other one can be found here and is an application that automatically generates water quality predictions based on daily DNA test results that are published on Chicago's Data Portal.

Contributing

If you are interested in contributing to this project, see our Contribution Guide.

Notes

Collaboration with the Civic Tech Community

This project originated as a breakout group at Chi Hack Night.

Resources

• Repository: https://github.com/Chicago/clear-water
• Issue tracker: https://github.com/Chicago/clear-water/issues
• Project management board: https://waffle.io/Chicago/clear-water
• Documentation and notes: https://github.com/Chicago/clear-water/wiki

LICENSE

Copyright (c) 2015 City of Chicago

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

clear-water's Issues

How are disagreements between reading 1 and reading 2 handled?

Sometimes there is significant disagreement between reading 1 and reading 2, at times, one reading is near zero and another is near the maximum reading. How does Park District handle these numbers? Is the maximum value taken or the minimum? Are the geometric means used?

Reorganize code

I'm conducting some housekeeping on the code as found in a9fd8b3. Namely:

• Create a split_stack.R function that will split and clean the raw data
• Call the split_stack.R function from the analysis.R function.
• Conduct some of the data organization and cleaning in the analysis.R function.

Get a description of all variables in current CPD model

The current model has several variables listed, but it is unclear how those variables crosswalk to the variables in the lab data.

Need to determine the variables and their meaning.

Normalize the name of beaches

When are beach predictions determined?

What time does CPD determine when to issue an advisory for beaches?

Relationship between Reading.1 and Reading.2 with predictions

Reading.1 and Reading.2 has a significant level of disagreement. Need to investigate if a large amount of disagreement between the two readings can predict elevated levels of e. coli.

Identify potential variables

E. Coli readings

• E. Coli beach readings from two days ago
• E. Coli beach readings from previous day

Beach Water readings

• Solar radiation
• Water temperature
• Turbidity
• Wave height
• Wave period

Beach Weather Stations

• Air temperature
• Rain intensity
• Rain interval
• Wind

Nearby Actions

• Open locks
• Park events
  Calendar-based data
• Day of week
• Holidays
• Forecasted e. coli from current model

Reading 1 and Reading 2 are blank, but geometric means are provided

Why does this happen?

Look at missing data

Look at:

• Missing Reading.1
• Missing Reading.2
• Missing e.coli levels

Historic data from drek beach

• Actual
• Historic
• Action (Ban or Adivsory)

forecast weather data for days without reading

Even for days where E. coli readings were not taken (e.g., most weekend days), it is still possible to get the weather data. This data should be downloaded and added to the data-set so that models that rely on the previous day's weather can be better informed.

Question: can there be beach advisories for reasons besides water quality?

Gather river lock openings

The dates of when the lock between the beach and river is open

Reconcile Python and R code semantics and names

Merge DrekBeach (advisory) data with lab data

The DrekBeach source in data/DrekBeach should be merged with cleaned lab data.

Test harness

Compare performance on various models against benchmarks.

Inquire if the forecasted values are available

Ask Park district if EPA/Michigan State model has the forecasted values for the past 10 years.

Create a train/test split for benchmarking

It would probably be nice to get a training and test dataset split for comparing new models, similar to the way Kaggle selects a winning model. One thing we'll have to look out for is making sure the training and testing datasets are balanced w.r.t the number of positives and negatives.

Shorten the name of beaches

Per @beckeroobonsai, should shorten the name of beaches to a single word or something more manageable within R/Python.

Sheets with out-of-order header columns

Three sheets with TWO header columns with incorrect column order (7/6/06, 7/8/06, 7/9/06)

Revisit naming conventions

Reading = Drek_Reading
Prediction = Drek_Prediction
SwimStatus = Drek_WorstSwimStatus

e.coli.geomean = e.coli.geomean.calculated

Remove significant outlier

A value over 6,000 is in the data set. It should be removed.

Add Beach Groupings

Add a North/South beach flag (relative Navy Pier).h

Add several other clusters for the beaches. 4? 5? 6?
North-North, North-South, South-South, South-North

Follow-up with Parks on threshold

Does over 1,000 parts lead to beach ban?

Aggregate past lab result data

Aggregate Excel files provided by CPD into a single file for further analytics

Double-check historical forecast.io weather (#58)

Remove ">" from data

When the test results are the highest levels (2420), the data contains a ">" and needs to be removed.

Investigate the time-series aspect of the model

Explore if there are moving average patterns to the e. coli levels over a period of time to see if there is high or low periods.

Filling in NaN values

How should NaN values be filled in (if at all)?

Using the city-wide mean for a given day might not be appropriate for filling in NaNs on the weekends, since the beaches that are sampled on the weekend are only sampled because they had a high reading on Friday (and maybe on Thursday? -- don't know the exact resampling rule) possibly causing bias in those readings.

Using a moving average could similarly be biased if the weekend samples are not excluded from the moving average.

Start simple model

• Autogressive with
• Day of week
• Holiday
• Beach
• Their forecast levels
• Levels in other beaches

Are E. coli levels predicted on the weekends?

Expand GBM to include other predictors

Being led by @beckeroobonsai -- expand GBM by including additional predictors (e.g., weather) for the applicable periods.

Include beach weather data into Python code

Use data mining to help identify useful variables

@nicklucius is using PCA to determine some predictors.

Autoregressive model in R

Create autoregressive model to benchmark

Improve the read-in function Python code to generalize "lag" data

There are a couple of options on reading-in data, such as the definition of "lag". Namely, whether lag means "previous reading" or "previous date"

DEFINEDNAME repeated

When running the data-cleaning code, the following message gets repeated quite a bit. Is this something that can be prevented?

DEFINEDNAME: 20 00 00 01 0b 00 00 00 01 00 00 00 00 00 00 07 3b 00 00 00 00 ff ff 00 00 00 00

This message specifically happens for some of the calls to split_sheets. Specifically, it happens with

df2006 <- split_sheets("data/ChicagoParkDistrict/raw/Standard 18 hr Testing/2006 Lab Results.xls", 2006)

Acquire historical weather data at the beaches

Historical forecast data

Despite the name, it appears as if forecast.io does not have historical forecasts, but only the observed values (e.g., we can not tell what the the weather on 6/2/2014 was forecasted to be on 6/1/2014). What other sources could we draw from to get the historical forecasts?

Some sheets have more than 2 readings

I found a few sheets that have more than 2 readings. It looks like the R code does not handle these (it silently renames the extra reading columns). This could possibly related to the large outlier? Could also be causing the few instances where we observed a larger-than-1 difference between our calculated geometric mean and the geometric mean we pulled from the excel file (i.e. by renaming the column Reading 3 to Escherichia.coli)

Found these dates on a first-pass through:

July 31 2006
August 1 2006
August 2 2006
August 3 2006
August 4 2006
August 9 2006
August 2 2007
August 2 2008

Create binary variable for lab results exceeding acceptable threshold

According to the notes, the threshold for unsafe levels of bacteria is equal to, or greater than, 235 ppm.

Naive Model, Today like yesterday

Create naive model to benchmark

Obtain current Park water quality model

Describe beach variables

Port the descriptions of the beach variables to https://github.com/Chicago/e-coli-beach-predictions/wiki#variables

"Shift" data to prior day or previous observations

The data is currently organized so each row represents a lab test result at a beach on a given day. The remainder of the row then shows all of the information associated with that day.

However, to look at the impact of previous weather and other variables from prior days, need to organize the data set to make it easier to run analysis against historical data.

Test harness

Compare performance on various models against benchmarks.

Are beaches systematically sampled?

The beach readings also report the time-of-day when they were taken. Is there a systematic difference in the sampling time for beaches (e.g., does the sampler always go from the north side to the south side?)

Test prediction threshold other than 250

E.coli thresholds above 250 are what trigger beach advisories. We can try to predict levels other than 250 to see if it has better real-life performance.

Describe weather variables

Port some of the weather descriptions from forecast.io to https://github.com/Chicago/e-coli-beach-predictions/wiki#variables

Merge Lab Results data

Merge the files in https://github.com/tomschenkjr/e-coli-beach-predictions/tree/master/data/ChicagoParkDistrict/raw into a single file that shows lab readings for each summer day.