Author
|
Post |
|
|
BillPayer
F L I N T O I D
|
With a draw down of state services coming near I've decided to look at the public lead sample data again. I 'sanitized' the data and wrote a little app to facilitate this, but so far I've only looked at a few things:
# Samples Collected: 29,067
# Unique addresses: 19,412
# of unique sites with a sample above:
5 PPB: 3518 (18.1%)
10 PPB: 2165 (11.2%)
15 PPB: 1651 (8.5%)
* It's been said that there are 30,000 water customers. With only 19,412 unique addresses that would imply 65% of customers have tested their water. HOWEVER: many apartment complexes have tested ALL of their units. These count as unique addresses, however for the 30k customer stats I believe they should only count as 1. So this 65% statistic could be very misleading.
* Only 819 (49.6%) of the unique addresses with a sample above 15 PPB tested their water more than once.
More to come. If you are interested in anything specific feel free to ask. |
|
|
Fri Jun 30, 2017 2:32 pm |
|
|
BillPayer
F L I N T O I D
|
Monthly analysis from public data. Total number of samples for each month as well as percentages below 5ppb and percentages greater than or equal to 5, 10, and 15 ppb.
code:
period #samples < 5ppb 5+ ppb 10+ ppb 15+ ppb
---------------------------------------------------------
9/2015 40 62.50% 37.50% 20.00% 12.50%
10/2015 330 73.64% 26.36% 11.21% 7.88%
11/2015 126 81.75% 18.25% 7.14% 7.14%
12/2015 176 75.57% 24.43% 13.07% 7.39%
1/2016 4980 83.07% 16.93% 9.34% 6.65%
2/2016 7900 80.46% 19.54% 11.04% 8.16%
3/2016 4916 76.87% 23.13% 13.67% 9.64%
4/2016 2478 77.44% 22.56% 12.39% 9.32%
5/2016 1421 77.41% 22.59% 12.32% 8.87%
6/2016 804 72.76% 27.24% 16.17% 12.06%
7/2016 686 75.51% 24.49% 13.85% 9.48%
8/2016 589 78.10% 21.90% 13.75% 9.34%
9/2016 609 76.68% 23.32% 11.49% 7.88%
10/2016 336 84.82% 15.18% 8.93% 5.65%
11/2016 589 86.93% 13.07% 5.26% 3.57%
12/2016 296 88.51% 11.49% 6.76% 5.41%
1/2017 289 88.58% 11.42% 5.19% 3.81%
2/2017 618 87.22% 12.78% 6.63% 4.53%
3/2017 668 88.47% 11.53% 6.59% 3.89%
4/2017 457 88.84% 11.16% 6.56% 5.03%
5/2017 462 91.77% 8.23% 4.98% 3.68%
6/2017 297 90.91% 9.09% 5.05% 3.37%
|
|
|
Mon Jul 03, 2017 8:17 am |
|
|
BillPayer
F L I N T O I D
|
There is a large anomaly in the public residential lead test result data. A very large number of zip codes associated with results are incorrect. The number of incorrect zip codes is over 20%, and this is not a simple data entry error. That's not to say it's intentional, but this data has not been corrected in over a year.
Examples found in the data are:
For examples:
LG60791,7/20/2016,Lead,,,,0,Copper,,,,0,1606,PARK ST,FLINT,48506
LG67868,8/18/2016,Lead,,,,0,Copper,,,,0,1606,PARK ST,FLINT,48503
LG75353,9/21/2016,Lead,,,,0,Copper,,,,0,1606,PARK ST,FLINT,48504
LG60779,7/20/2016,Lead,,,,1,Copper,,,,0,1206,BLANCHARD AVE,FLINT,48529
LG87429,11/17/2016,Lead,,,,3,Copper,,,,60,1206,BLANCHARD AVE,FLINT,48507
LG75383,9/21/2016,Lead,,,,7,Copper,,,,70,1206,BLANCHARD AVE,FLINT,48507
LG24217,3/16/2016,Lead,,,,31,Copper,,,,70,1206,BLANCHARD AVE,FLINT,48503
LG29818,3/30/2016,Lead,,,,12,Copper,,,,60,1206,BLANCHARD AVE,FLINT,48503
Since there are such a large number of errors in the zip code portion, how trust worthy is the rest of the information? |
|
|
Mon Jul 03, 2017 6:45 pm |
|
|
BillPayer
F L I N T O I D
|
What do the zip code errors do to the data?
48506: Percentage of samples at or above the action limit was 2.53% higher than state data reflected.
48507: Percentage of samples at or above the action limit was 2.92% lower than states data reflects.
The following table has a row for each target zip code. The columns are broken up into two sets of data. 2017 samples with corrected zip codes, and 2017 data with the raw data from the states spreadsheets. Each section contains the number of samples in that zip code and the percentage at or above the action limit.
The last column indicates the change to the percentage of samples at or above the action limit. A positive percentage indicates the incorrect zip codes ADDED to the number of samples at or above the limit. A negative value indicates the zip code errors REMOVED samples at or above the action limit.
code:
[=Corrected=====] [=Raw Data=======]
Zip Code # Samples % AL+ # Samples % AL+ Diff.
48502: 154 9.09% => 175 9.14% +0.05%
48503: 1573 6.04% => 1600 5.44% -0.6%
48504: 1028 7.20% => 1077 5.57% -1.63%
48505: 588 6.46% => 588 7.99% +1.53%
48506: 471 7.43% => 490 4.90% -2.53%
48507: 741 5.53% => 769 8.45% +2.92%
The samples used in this are a subset of the full 2017 tests. They are only the samples in the 2017 data which had a tests on their block before june 30th 2016. This is because the pre-june 30th data has correct zip codes. I cross referenced street names and block numbers with this data to verify correct zip codes in the 2017 data. Only samples which could be verified/corrected were used. |
|
|
Wed Jul 05, 2017 2:04 am |
|
|
|