Compile Ambient Water Quality Data from 2005

[fgregg]

From http://www.mwrd.org/irj/servlet/prt/portal/prtroot/pcd!3aportal_content!2fMWRD!2fMWRDInternet!2fRoles!2fReports!2fTechnical!2fWaterReclamationPlantData!2fwater_quality_monitoring#1

• Download the yearly "Waterway Ambient Water Quality" spreadsheets going back to 2005
• Extract the monthly data from the excel tabs
• Align and compile all the observations into one data time series csv

[andreweskeclarke]

I only added the fec_col and e coli counts. Is there other data that we should use? Much of it did not seem connected to sewer overflows. Possibly turbidity, dissolved oxygen, etc. Much of it seemed related to manufacturing pollution, for instance Benzene and Ethylbenzene measurements.

Also, out of curiosity, I ended up manually copying the columns from all the sheets into one document. Is there a better way to consolidate that data?

Some Notes: Missing E Coli from Oct 2011 onwards. They started collecting time data in 2012, ignored that in this summary.

[andreweskeclarke]

Added the summary mentioned above in commit 6f5eed82e6e855c0e7e3f76557b831bd43cc7408

[fgregg]

This is awesome! Probably the right way to do this more automatically is with a excel parsing library http://stackoverflow.com/questions/7372716/parsing-excel-documents-with-python

On Saturday, January 11, 2014, andreweskeclarke wrote:

Added the summary mentioned above in commit 6f5eed8https://github.com/open-city/chicago-river-sewage/commit/6f5eed82e6e855c0e7e3f76557b831bd43cc7408

— Reply to this email directly or view it on GitHubhttps://github.com/open-city/chicago-river-sewage/issues/10#issuecomment-32107893 .

773.888.2718 2231 N. Monticello Ave Chicago, IL 60647

[derekeder]

@fgregg is this file enough for you to get started on a model?

https://github.com/open-city/chicago-river-sewage/blob/master/data/waterways_quality_summary.csv

[fgregg]

Nope. need more variables.

On Thu, Jan 16, 2014 at 1:08 PM, Derek Eder notifications@github.comwrote:

@fgregg https://github.com/fgregg is this file enough for you to get started on a model?

https://github.com/open-city/chicago-river-sewage/blob/master/data/waterways_quality_summary.csv

— Reply to this email directly or view it on GitHubhttps://github.com/open-city/chicago-river-sewage/issues/10#issuecomment-32512838 .

773.888.2718 2231 N. Monticello Ave Chicago, IL 60647

[andreweskeclarke]

Combined all years and all variables. dc22c193aaf1c07745274af3a156147d998b3b7a

Notes:

• Any columns that were missing headers I removed, ultimately ~40 columns had to be removed. In each case, they represented only one month's worth of data. Most likely typos where someone forgot to enter the column header when entering that month's data.
• Some data was not collected on every month, but is present in the combined csv.

[fgregg]

Awesome!