Lead Sampling Results at WHO's recommended 10 ppb limit

Overview

Michigan's governor has proposed dropping the over-rate limit for lead in drinking water from 15 parts per billion to 10 parts per billion. An AP analysis has found if that rule were to go into place nationwide, more than 2,500 water systems serving 18 million Americans would have violated the standard from the beginning of 2013 through 2015. That's a significant increase from the 1,477 water systems found over-limit during that time period under the current rule.

Included Power BI reports

Top level story: shows a top level story and can be filtered to a state level. This is useful for a national story where you want your readers to interact with the visual and explore state-by-state

Is your child's school safe?: this report is designed to help you find an emotional story in the data. Use this to get a easy overview of how bad things are in your area

Water stations with the highest concentration of lead?: use this report to drill down to find a water station in your area and get an in-person story

About the data set

The data set, which now includes results of sampling tests through December 31, 2015, summarizes lead sampling results from the EPA's Safe Drinking Water Information System. Be aware that these results are self-reported by water systems and there are some extreme outliers in the data which may have resulted from input errors. The AP analyzed 25 years of sampling data reported by roughly 75,000 drinking water systems that are subject to the lead rule, which went into effect in 1991.

While no lead exposure is considered safe, the current federal rule calls for water systems to keep lead levels below 15 parts per billion. World Health Organization officials recommend levels below 10 parts per billion, a standard adopted by many other countries across the world, including China, Canada and the members of the European Union.

How lead sampling works: Water officials collect anywhere from 5 to over 100 samples from taps across their system. The lead sampling result is reported as the 90th percentile of those samples -- meaning that 10 percent of tap samples are above the reported level, and 90 percent are below. If this result is above the federal 15 ppb level, water officials need to inform customers about the problem and take steps to address it, such as by adding corrosion control treatment to keep lead from service lines from leeching into the water. Samples are taken at taps at residential, commercial and government sites - the number of samples required is based on how many customers the water system serves.

Our analysis reports the singular 90th percentile result of a lead sampling period, and not the individual samples collected from taps. We deleted duplicate records of samples that covered the exact same sampling period with the same results and corrected known reporting errors for about two dozen systems within the data set.

Included assets

State-by-State count: The first spreadsheet counts the number of water systems in each state that would have been over-limit in the past three years if the WHO's recommendation of a level of 10 parts per billion level had been in place. It also includes a count of how many water systems had action exceedance levels during that time period under the current federal limit of 15 ppb.

Download the state count file: here

Over-limit water systems at 10 ppb: The second spreadsheet provides data on each water system that has had a lead sampling result over 10 parts per billion from Jan. 1, 2013 to December 31, 2015. This includes the name of the water system, the dates of the most recent sampling period, the sample results at the 90 percentile benchmark, and the total number of sampling periods where a limit of 10 ppb has been exceeded since 2013 and since sampling began in late 1991. Extremely high results (above 50 ppb) should be double-checked with the agency for possible errors in reporting. It also includes a flag if the system in the past three years has had a sampling period over the current federal limit of 15 ppb.

Download the over-limit sample file: here

IMPORTANT NOTES ABOUT THE DATA SET

The EPA's SDWIS lookup tool for Lead and Copper samples is here. You can use this tool to check on all sampling results from your local water system -- enter your local system's Pwsid from the attached lead sample file into the "PWS ID" field and delete the Sample End Date, then click on "View Reports" to see all samples taken at your local system. PLEASE NOTE: The samples reported on the EPA's website are expressed in milligrams, not micrograms (parts per billion). To get the proper ppb level, multiply the EPA site's results by 1,000. If this number is over 15, it is over the action limit. (This should also then match the spreadsheet numbers, which are properly recorded in parts per billion).

This data is reported to the EPA by state enivornmental agencies. There are some issues with misplaced decimals in the sampling data, and extremely high results should be regarded with suspicion. A spot-check of some of the largest outliers in the dataset found that most had a decimal-point error. In the cases where the AP found an error and received the actual sampling results, the data has been corrected (these changes were made in the case of roughly two dozen water systems). Please call the water system involved to double check before reporting results that are many times over the federal limit.

The state and city is where the administrator of the water system is located. In nearly all cases, these are the same as where the water system is located. In some cases, a water system may serve customers in multiple states. And in the case of multi-state companies - or very small systems - the administrator may be off-site. The PWS_location field is the state where the system actually is, and should be used for counting the number of systems by state.

The Associated Press analysis on the original story published on April 9, 2016 used a data set only current through the end of September 2015. This analysis includes data through the end of the year.

Power BI Model fields

State: The state where the water system is located
Stardard Federal Region: The standard federal region where the water system is located
Census Division: The census division where the water system is located
Census Region: The census region where the water system is located
Circuit Court: The circuit court number where the water system is located
State: The state where the water system is located
10ppb: The number of distinct water systems that were over the 10 parts per billion recommendation set by the WHO between Jan. 1, 2013 and the end of 2015.
15ppb: The number of distinct water systems that were over the EPA's 15 parts per billion action limit between Jan. 1, 2013 and the end of 2015.
Individual water system: The name of the water system.
Administrator city: City address for the water system administrator. Usually the same as the location, especially for municipal and larger water systems.
Administrator state: State of the water system administrator. Usually the same as the actual location.
Operational status: Whether water system is operational as of Dec. 2015
Ownership: Whether system is privately or publicly owned.
Primary source of water: Source of water in system.
School or Daycare: School or a daycare
Population server violating current limit (15ppb): How many people are served by water systems that violate current limit (15ppb)
Population server violating current limit (10ppb): How many people are served by water systems that violate current limit (10ppb)
Beginning of most recent sampling period: Beginning of most recent sample period when system was found over limit
Most recent result violating proposed limit (10ppb): The last over-limit sample period results, reported at the 90th percentile, in parts per billion (anything over 15 is considered over the federal action limit; we considered anything over the WHO's recommendation of 10 ppb)
Sample periods violating proposed limit (10ppb): How many sample periods in the history of the system have results over 10 parts per billion
Sample periods violating proposed limit (10ppb) since 2013: How many sample periods since Jan. 1, 2013 have results over 10 parts per billion
Sample periods violating proposed limit (10ppb) since 2010: How many sample periods since Jan. 1, 2010 have results over 10 parts per billion
Sample periods: How many sample periods have occurred in the history of the system
Lowest result ever taken: The lowest result (in parts per billion) ever taken in the life of the system
Highest result ever taken: The highest result (in parts per billion) ever taken in the life of the system
Beginning of the first samplinp period: The date of the beginning of the first sampling period on the system
Beginning of most recent sampling period: The date of the beginning of the most recent sampling period on the system.
Most recent sampling period date end: The date of the end of the most recent sampling period
Affected: Indicates if the water system violates the current limit or the proposed limit
Scope: A text label that shows the currently filtered state, region or "National" if nothing is filtered

Excel Column Definitions, State Count

State: The state where the water system is located
WHO_10ppb_overlimit_count: The number of distinct water systems that were over the 10 parts per billion recommendation set by the WHO between Jan. 1, 2013 and the end of 2015.
EPA_15pbb_overlimit_count: The number of distinct water systems that were over the EPA's 15 parts per billion action limit between Jan. 1, 2013 and the end of 2015.

Column Definitions, Over-Limit Water Systems

pwsid: Unique identifier for water system - use it to look up other tests in the EPA's lookup tool (see link above).
pws_name: The name of the water system.
pws_location: The state the water system is located in. Use this to find the water systems in your state.
mailing_city: City address for the water system administrator. Usually the same as the location, especially for municipal and larger water systems.
mailing_state: State of the water system administrator. Usually the same as the actual location.
activity_code: Whether water system is operational as of Dec. 2015. A=Active; I=Inactive; N=Unknown
owner_type: Whether system is privately or publicly owned.
primary_source: Source of water in system.
school_or_daycare: Y= yes, it's a school or a daycare
population_served_count: How many people are served by this system
most_recent_over_limit_date_started: Beginning of most recent sample period when system was found over limit
most_recent_over_limit_result: The last over-limit sample period results, reported at the 90th percentile, in parts per billion (anything over 15 is considered over the federal action limit; we considered anything over the WHO's recommendation of 10 ppb)
total_results_over_limit: How many sample periods in the history of the system have results over 10 parts per billion
total_over_since_2013: How many sample periods since Jan. 1, 2013 have results over 10 parts per billion
total_over_since_2010: How many sample periods since Jan. 1, 2010 have results over 10 parts per billion
number_sampling_periods: How many sample periods have occurred in the history of the system
lowest_result: The lowest result (in parts per billion) ever taken in the life of the system
highest_result: The highest result (in parts per billion) ever taken in the life of the system
date_first_sampling_period: The date of the beginning of the first sampling period on the system
date_most_recent_sampling_period_started: The date of the beginning of the most recent sampling period on the system.
date_most_recent_sampling_period_ended: The date of the end of the most recent sampling period
over_limit_epa_15_ppb: Y/N: Whether the water system has had a sampling period result over the federal limit of 15 ppb since Jan. 1, 2013

All data collected and prepared by AP Data Journalist Meghan Hoyer.

Power BI support

We think Power BI is transformational and defines next-level data journalism. If you are not already using this amazing tool talk to us and we'll help get you setup and on your way.

As a member you also have access to in-person priority support for Power BI:

Power BI website:: https://powerbi.microsoft.com
Power BI for data journalism website:: https://powerbi.microsoft.com/en-us/datajournalism/
View Power BI publish to web in action:: https://powerbi.microsoft.com/en-us/publishtoweb
Visualization Principles for the Graphic Presentation of Data (webinar at top of page):: https://powerbi.microsoft.com/en-us/datajournalism/
Data Visualization & Storytelling Courses (on-demand learning videos & step-by-step tutorials):: https://powerbi.microsoft.com/en-us/datajournalism/courses/
Create Impactful Reports with Power BI Desktop (video):: https://www.youtube.com/watch?v=d2bZpNZ6uIA
Tell Data Stories with Power BI publish to web (webinar):: https://info.microsoft.com/CO-PowerBI-WBNR-FY16-06Jun-14-PowerBI-Publish-to-Web-Registration.html?Is=Website
Power BI Data Stories Gallery:: https://community.powerbi.com/t5/Data-Stories-Gallery/bd-p/DataStoriesGallery
Data Storytelling Discussion Forum in Power BI Community:: https://aka.ms/djcommunity
Guided Learning:: https://aka.ms/djcommunity