# Dataset url on GitHub repository.
data_url<-"https://raw.githubusercontent.com/renastechschool/Python_tutorials/main/Lead_Testing_in_School_Drinking_Water_Sampling_and_Results_Compliance_Year_2016_formated.csv?token=GHSAT0AAAAAACNH7S3BJGTQXNGH4UPQCJI6ZNQB3VA"
# Read dataset. Input data wrapped by url mothod. This allows to read data from a url.
school_lead_df<-read_csv(url(data_url))NYS School Water Quality: Exposure to Lead
Lead Testing in NYS School Drinking Water
Overview
This use case explores the risks of exposure to lead via drinking water. Lead contamination is a serious issue that poses severe health risks and requires remedial action. In this lesson, we will analyze data on lead levels in NY State schools collected from 2016, 2017, 2018, and 2019 and compare it with population characteristics at the county level in New York State (NYS) to understand its impact. No “safe” levels of lead have been established, but we will discuss what level of lead can be detected. The lesson examines the sources of lead exposure and its adverse effects. The review also discusses the importance of data transparency, public participation, visualizing contamination as maps/graphs, and estimating population risks to address water quality issues.
Learning Objectives
- Learn about lead as a drinking water contaminant and its health impacts.
- Visualize lead contamination data geographically and statistically.
- Discuss the importance of safe drinking water in the context of lead contamination.
- Understand and apply open data principles in the context of lead contamination data.
- Explore different data classification methods (equal, quantile, natural breaks, standard deviation).
Introduction
Access to clean and safe drinking water is significant to ensure public health (Environmental Health Sciences, n.d.). Drinking water contaminants may have both short-term and long-term negative health impacts. One such contaminant that can have detrimental effects is lead, which is particularly harmful to a child’s development (Levallois et al. 2018). Children’s central nervous systems and cognitive function have been linked to harm from lead exposure, even at low levels (Lanphear et al. 2005). Therefore, it is essential to address lead-contaminated water in schools and homes.
Lead exposure has no safe threshold; therefore, safety depends on ensuring that lead levels in water are below the legal thresholds set by the World Health Organization (WHO) and the US Environmental Protection Agency (EPA) (EPA 2024). According to guidelines issued by the WHO, lead concentrations in drinking water should not exceed 10 ppb (Parts Per Billion) (WHO 2022), and 15 ppb is the action level set by the EPA (EPA 2024).
It is crucial to highlight that the degree of lead able to be detected depends upon many variables (Schock 1990). The main source of lead in drinking water is the corrosion of lead-containing plumbing materials (EPA 2024). Older plumbing systems with lead pipes and solder may leak lead into drinking water, particularly in regions with acidic water (EPA 2024). Furthermore, runoff from contaminated soil and industrial discharges can also introduce lead into water systems. Developing effective preventive and remedial strategies requires understanding the factors that contribute to lead contamination.
LEAD IN NEW YORK SCHOOLS
New York State (NYS) Lead Testing in School Drinking Water dataset shows the school drinking water lead sampling and results information reported by each NYS public school and Boards of Cooperative Educational Services (BOCES) (NYS Department of Health). More information on the NYS dataset sampling is available here.
Analysis of the dataset reveals that as of 2022, 1,864 schools had lead outlets testing higher than 15 ppb (NYS 2016). While 527 schools finished their remediation, 1,851 schools reported taking remedial action. There are now 12 schools with outlets exceeding 15 ppb in operation, indicating possible continuous exposure. However, there are gaps in following up and documenting the corrective measures. More transparency is necessary for schools with high exposure to lead to address the hazards of lead contamination and implement improved repeated testing protocols. The New York State Department of Health provides guidelines, rules, and resources on lead testing and remediation in schools (Health 2023). However, it seems that there is currently a lack of financial and technical support for schools to handle lead hazards.
Lead in the News
Thousands of people were exposed to dangerously high lead levels in their drinking water when the Flint water crisis broke out in 2014. A study by Virginia Tech researchers, through their resident-organized sampling to testing data of 252 homes, revealed that lead levels in the city had increased (Council, n.d.). Over 17% of samples tested higher than the federal “action level” of 15 ppb, which calls for the need for corrective action. More than 40% had lead readings higher than 5 ppb, which the researchers deemed indicative of a “very serious” issue.
Even years after the crisis began, elevated lead levels remained in Flint’s schools. An article by The New York Times discusses how, in 2019, drinking water samples from 30 Flint school buildings still exhibit excessive lead levels. The elevated levels demonstrate remaining problems with a prolonged impact on children’s health and development. Schools have an obligation to supply their pupils with clean drinking water. The Flint water crisis brought to light the long-term consequences of prolonged exposure to lead, especially for vulnerable groups such as children.
Lead exposure is cumulative, as noted in the article by the Tampa Bay Times. The duration of lead exposure in Flint has lasting impacts on the public, not only affecting physical health but also leading to psychological consequences of communities not being able to trust their drinking water (Brooks and Patel 2021). Rather than waiting for concerns to arise, schools can detect contamination issues early and take corrective action by implementing a lead testing program. Better learning outcomes are made possible by shielding children from lead exposure. The Flint water crisis made clear how crucial it is to conduct proactive lead testing, monitor the situation, and take prompt, corrective action. It also made clear how important it is to be transparent, involve the community, and address barriers to clean drinking water access.
In 2021, the Biden-Harris administration announced an ambitious Lead Pipe and Paint Action Plan. This comprehensive $15 billion effort intends to promptly replace all lead service lines and pipes that are contaminating drinking water systems across the country. The plan has a provision of providing a lead remediation grant of $9 billion to disadvantaged communities through the Water Infrastructure Improvements for the Nation Act (WIIN) program, including for schools and childcare centers at EPA.
Read data
To work with the NYS data, first we will read the NYS school lead testing results from 2016 to 2019. The dataset is hosted on a GitHub repository and we will read the dataset by using the dataset URL.
Preparing the lead dataset for the analysis
All datasets require some pre-cleaning and formatting. In the section below, we will format field names. R does not like field names with spaces, so we need to convert space to an underscore “_”. Also, we need to extract the year from the date field for the next step of our work.
# There are empty spaces in the field names which R does not like.
# Replace empty space with "_".
names(school_lead_df) <- names(school_lead_df) %>% stringr::str_replace_all("\\s","_")
# Extract the year from the date field. We are using Date_Results_Updated for the date.
school_lead_df<-school_lead_df %>% mutate(year=format(as.Date(Date_Results_Updated, format="%d/%m/%Y"),"%Y"))
# Data reports lead level by outlets if a outlet lead level is above or under 15ppb.
# The line below categorized the schools if they have any outlet above or under 15ppb.
school_lead_df<-school_lead_df %>% mutate(lead_summary_by_school=case_when(
Number_of_Outlets_above_15_ppb ==0~ "has lead < 15ppb",
Number_of_Outlets_above_15_ppb >0~ "has lead >15ppb",
TRUE ~ "no data"))Get familiar with the dataset
Getting familiar with the dataset is the first step of an analysis. To understand the attributes, we will query data by geographic region (county) and different attributes (fields).
The code below creates a Shiny app that allows users to select a county and specific fields from a data frame (school_lead_df), and then it displays the corresponding data table based on the user’s selections.
Coding Review
A Shiny app is a web application framework for R programming language that allows you to create interactive web applications directly from R code. It’s part of the RStudio ecosystem and is widely used for creating interactive data visualizations, dashboards, and web-based tools without needing to know HTML, CSS, or JavaScript.
#| panel: fill
fluidPage(
fluidRow(style = "padding-bottom: 30px;background-color:#f1f2f3;",
select_county,select_fields4),
fluidRow(column(12, DT::dataTableOutput("table"))))#| context: server
output$table<- DT::renderDataTable({
if (input$county=="All Counties")
{school_lead_df %>% dplyr::select(input$fields) }
else(school_lead_df %>% filter(County==input$county)%>% dplyr::select(input$fields))
})Converting from tabular data to geospatial data
A dataset needs to have a geometry attribute to plot the data on a map or to conduct different spatial analyses. The NYS dataset has xy coordinates of schools. The xy coordinates will allow us to convert the tabular dataset to a spatial dataset.
We first need to address that the xy coordinates are not properly formatted. The coordinates are currently stored with school addresses, for example: 31-02 67 AVENUE Queens, NY 11364(40.74779141700003, -73.74551716499997). We need to extract the coordinates (40.74779141700003, -73.74551716499997) and store the value on each side of the comma as a separate field. The first number refers to the y coordinate (latitude), and the second number refers to the x coordinate (longitude).
While converting the data, we also need to know the projection of xy coordinates. XY coordinates can be in different projection systems. Projection information is typically stored in the metadata of a dataset. However, in the NYS dataset, there is not any metadata attached to the dataset.
The most commonly used geographic coordinate system is the WORLD GEODETIC SYSTEM 1984 (WGS 84). We will use the WGS84 projection to convert the NYS dataset to spatial data.
# In the datasets xy coordinates of schools are combined with school addresses
# and recorded under the location field.
# For example: 231-02 67 AVENUEQueens, NY 11364(40.74779141700003, -73.74551716499997)
# The xy coordinates, (40.74779141700003, -73.74551716499997), need to be extracted,
# sparated by a ",", and saved under different fields names.
# First, we will extract the xy coordinates and save under "lat","long" fields.
school_lead_df<-school_lead_df %>% mutate(location_temp=str_extract(Location,"(?<=\\().*(?=\\))")) %>%
separate(location_temp,c("lat","long"),sep=",")
# Next, we will create geometry attributes by using lat long fields and the geographic coordinate system.
# This step allows us to map the school locations and do geospatial anaylysis.
school_locations<-school_lead_df %>% filter(!is.na(lat)) %>%
# Convert the dataset to spatial dataset by using WGS84 projection.
st_as_sf(coords = c("long", "lat"),crs = "+proj=longlat +datum=WGS84 +ellps=WGS84 +towgs84=0,0,0") Mapping the dataset
We will now map the school locations in New York State from the dataset.
#|panel: fill
fluidPage(
fluidRow(style = "padding-bottom: 30px;background-color:#f1f2f3;",
select_county),
fluidRow(column(12,leafletOutput("map"))))school_loc<-reactive({school_locations %>% filter(County==input$county)})
output$map<-renderLeaflet({
# if (input$county=="All Counties"){school_loc <-school_locations
# tmap<-tm_basemap(leaflet::providers$Esri.WorldImagery)+
# tm_shape(school_loc, name="school locations") +
# tm_dots(id="",col="County",palette="magma",
# popup.vars=c("School name: "="School" ),
# legend.show = FALSE)
# tmap_leaflet(tmap)
# }
#
# else{school_loc <-school_locations %>% filter(County==input$county)
tmap<-tm_basemap(leaflet::providers$Esri.WorldImagery)+
tm_shape(school_loc(), name="school locations") +
tm_dots(id="",col="County",palette="magma",
popup.vars=c("School name: "="School" ),
legend.show = FALSE)
tmap_leaflet(tmap)
})Obtain population data from the US Census Bureau
We will next pull population data from the US Census Bureau by using the Census Bureau’s Application Programming Interface (API).
#|panel: fill
fluidPage(
fluidRow(style = "padding-bottom: 20px;background-color:#f1f2f3;",
select_state,select_fields1, select_fields2, select_classification_method),
fluidRow(column(6, plotOutput("plot1")),
column(6, plotOutput("plot2"))),
fluidRow(column(6, leafletOutput("map1")),
column(6,leafletOutput("map2"))))# get total population from census 2020
county_census<-reactive({
req(input$state)
get_census_data(input$state) })
output$plot1<-renderPlot({
county_census()%>%st_drop_geometry() %>%
dplyr::select(contains("total")) %>% gather(key,value) %>%
group_by(key) %>% summarise(Count=sum(value)) %>%
filter(key!="total_pop") %>%
mutate(percent = prop.table(Count), prc=paste0( "%",round(percent*100, 1)," \n(", comma(Count/1000),"K)")) %>%
ggplot(aes(x=key,y=Count,fill=key))+
geom_bar(stat="identity")+
geom_text(aes(label = prc),size=5, hjust=0.7,
position=position_stack(0.9))+guides(fill=FALSE)+
labs(x="",y=" ", fill="", title = "Total Population by Race")+
coord_flip()+theme
})
output$plot2<-renderPlot({
county_census() %>%st_drop_geometry() %>%
dplyr::select("white_5_17","black_5_17","hispanic_5_17","asian_5_17") %>%
gather(key,value) %>%
group_by(key) %>% summarise(Count=sum(value)) %>%
mutate(percent = prop.table(Count), prc=paste0( "%",round(percent*100, 1)," \n(", comma(Count/1000),"K)")) %>%
ggplot(aes(x=key,y=Count,fill=key))+
geom_bar(stat="identity")+
geom_text(aes(label = prc),size=5, hjust=0.7,
position=position_stack(0.9))+guides(fill=FALSE)+
labs(x="",y=" ", fill="", title = "Total Populaiton age 5-17 by Race")+
coord_flip()+theme
})
output$map1<-renderLeaflet({
tmap<-tm_basemap(leaflet::providers$Esri.WorldImagery)+
tmap::tm_shape(county_census(), name="NYS counties") +
tm_polygons(col=input$field1,style=input$classification, palette="RdYlGn")
tmap_leaflet(tmap)
})
output$map2<-renderLeaflet({
tmap<-tm_basemap(leaflet::providers$Esri.WorldImagery)+
tmap::tm_shape(county_census(), name="NYS counties") +
tm_polygons(col=input$field2,style=input$classification, n=6,palette="RdYlGn")
tmap_leaflet(tmap)
})Explore your data spatially
fluidPage(
fluidRow(style = "padding-bottom: 20px;background-color:#f1f2f3;",
select_state,select_county),
fluidRow(column(5,"", plotOutput("plot")),
column(7,"", leafletOutput("map"))))counties_bry <- reactive({req(input$state)
get_census_data(input$state) })
school_loc <- reactive({school_locations %>%filter(County==input$county)})
output$plot<-renderPlot({
if (input$county=="All Counties"){school_lead_df_<-school_lead_df}
else{school_lead_df_<-school_lead_df %>% filter(County==input$county)}
school_lead_df_ %>%
group_by(lead_summary_by_school) %>% summarize(Count=n()) %>%
mutate(percent = prop.table(Count), prc=paste0( " %",round(percent*100, 1)," (", comma(Count),")")) %>%
ggplot(aes(x=lead_summary_by_school,
y=Count,fill=lead_summary_by_school))+
geom_bar(stat="identity")+
geom_text(aes(label = prc),size=5,
position=position_stack(0.9))+guides(fill=FALSE)+
scale_fill_manual(values=c("green3","red","grey"))+
labs(x="",y="Count of School", fill="", title = "Count of schools based on lead status",
caption="has lead < 15ppb: None of the outlets have lead over 15ppb\nhas lead >15ppb: At least a outlet has lead over 15ppb")+theme
})
output$map<-renderLeaflet({
tmap<-tm_basemap(leaflet::providers$Esri.WorldImagery)+
tmap::tm_shape(counties_bry() , name="NYS counties") +tm_polygons("black",alpha=0,popup.vars=c("County Name : "="county"))+
tm_shape(counties_bry() ,name="NYS counties ") + tm_borders("black", lwd = 2) +
tm_shape(school_loc(), name="School locations") +
tm_dots(id="something",col="lead_summary_by_school",palette=c("has lead < 15ppb"="green","has lead < 15ppb"="red"),
popup.vars=c("School: "="School" ),
legend.show = FALSE)
tmap_leaflet(tmap)
})Aggregate school locations in county boundary and compare with county population
Lastly, we will aggregate points into county boundaries.
fluidPage(
fluidRow(style = "padding-bottom: 20px;background-color:#f1f2f3;",
select_state,select_fields1,select_fields3, select_classification_method),
fluidRow(column(6,"", leafletOutput("map1")),
column(6,"", leafletOutput("map2"))))# get total population from census 2020
county_bry<-reactive({
req(input$state)
get_census_data(input$state)})
output$map1<-renderLeaflet({
county_bry<- st_transform(county_bry(), crs(school_locations))
school_locations_join<-st_join(school_locations,county_bry,join = st_intersects) %>%
group_by(GEOID,county) %>% summarise(school_count=n(),
outlets_under_15_ppb=sum(Number_of_Outlets_under_15_ppb, na.rm =TRUE),
outlets_above_15_ppb=sum(Number_of_Outlets_above_15_ppb, na.rm=TRUE)) %>%
dplyr::select(GEOID,county,school_count,outlets_under_15_ppb,outlets_above_15_ppb) %>% st_drop_geometry()
county_with_school<-county_bry%>% left_join(school_locations_join, by="GEOID")
tmap<-tm_basemap(leaflet::providers$Esri.WorldImagery)+
tmap::tm_shape(county_with_school, name="county boundaries") +
tm_polygons(col=input$field1,style=input$classification, n=6,palette="YlOrRd")
tmap_leaflet(tmap)
})
output$map2<-renderLeaflet({
county_bry<- st_transform(county_bry(), crs(school_locations))
school_locations_join<-st_join(school_locations,county_bry,join = st_intersects) %>%
group_by(GEOID,county) %>% summarise(school_count=n(),
outlets_under_15_ppb=sum(Number_of_Outlets_under_15_ppb, na.rm =TRUE),
outlets_above_15_ppb=sum(Number_of_Outlets_above_15_ppb, na.rm=TRUE)) %>%
dplyr::select(GEOID,county,school_count,outlets_under_15_ppb,outlets_above_15_ppb) %>% st_drop_geometry()
county_with_school<-county_bry%>% left_join(school_locations_join, by="GEOID")
tmap<-tm_basemap(leaflet::providers$Esri.WorldImagery)+
tmap::tm_shape(county_with_school, name="county boundaries") +
tm_polygons(col=input$field3,style=input$classification, n=6,palette="YlOrRd")
tmap_leaflet(tmap)
})ENSURING SAFE DRINKING WATER
Access to safe drinking water is a fundamental human right and a pillar of public health (Li and Carpenter 2023). However, the cases discussed underscore the critical need to address the risk of lead contamination. From New York to Flint, Michigan, these examples serve as reminders of the need for action, supported by transparent and accessible data.
A balance is needed between short and long-term action - promptly addressing risks in the short term by closing down contaminated water sources, improving monitoring, and transparency in reporting water quality issues (EPA 2024). In the long term, managing the costs of major infrastructure changes required to prevent such crises and exposures, such as replacing lead service lines and school plumbing (EPA 2024). Community collaboration, precise surveillance databases, and flexible adaptation strategies are critical in ensuring access to safe drinking water.
Maintaining transparency and exchanging information with communities regarding the outcomes of lead testing and mitigation strategies is essential; empowering the public with knowledge and access to data can catalyze action and foster leadership within communities to address these challenges. Educating individuals about water quality issues, both globally and locally, is paramount.
In this lesson, you learned…
Congratulations! Now you should be able to:
- Read into R to analyze a dataset.
- Convert survey dataset to spatial data using xy coordinates.
- Plot locations on a map.
- Obtain population data from US Census Bureau by using census API.
- Aggregate a dataset to a boundary.
- Create your own map.
Explore the data
References
Brooks, Samantha K, and Sonny S Patel. 2021. “Psychological Consequences of the Flint Water Crisis: A Scoping Review.” Disaster Medicine and Public Health Preparedness 16 (3): 1259–69. https://doi.org/10.1017/dmp.2021.41.
Council, National Resource Defense. n.d. “Flint Water Crisis: Everything You Need to Know.” https://www.nrdc.org/stories/flint-water-crisis-everything-you-need-know#summary.
Environmental Health Sciences, National Institute of. n.d. “Safe Water and Your Health.” https://www.niehs.nih.gov/health/topics/agents/water-poll.
EPA, United States Environmental Protection Agency. 2024. “Basic Information about Lead in Drinking Water.” January 25, 2024. https://www.epa.gov/ground-water-and-drinking-water/basic-information-about-lead-drinking-water.
Health, NYS Department of. 2023. “Lead Testing of School Drinking Water.” NYS Department of Health. https://www.health.ny.gov/environmental/water/drinking/lead/lead_testing_of_school_drinking_water.htm.
Lanphear, Bruce P., Richard Hornung, Jane Khoury, Kimberly Yolton, Peter Baghurst, David C. Bellinger, Richard L. Canfield, et al. 2005. “Low-Level Environmental Lead Exposure and Children’s Intellectual Function: An International Pooled Analysis.” Environmental Health Perspectives 113 (7): 894–99. https://doi.org/10.1289/ehp.7688.
Levallois, Patrick, Prabjit Barn, Mathieu Valcke, Denis Gauvin, and Tom Kosatsky. 2018. “Public Health Consequences of Lead in Drinking Water.” Current Environmental Health Reports 5 (2): 255–62. https://doi.org/10.1007/s40572-018-0193-0.
Li, Samuel, and Adam Carpenter. 2023. “The Human Right to Water: UN Definitions, Implications, and Effects.” Journal AWWA 115 (10): 50–55. https://doi.org/10.1002/awwa.2199.
NYS, New York State. 2016. “Lead Testing in School Drinking Water Sampling and Results Compliance Year 2016.” September 22, 2016. https://health.data.ny.gov/Health/Lead-Testing-in-School-Drinking-Water-Sampling-and/rkyy-fsv9/about_data.
Schock, Michael R. 1990. “Causes of Temporal Variability of Lead in Domestic Plumbing Systems.” Environmental Monitoring and Assessment 15 (1): 59–82. https://doi.org/10.1007/bf00454749.
WHO, The World Health Organization. 2022. “Guidelines for Drinking-Water Quality: Fourth Edition Incorporating the First and Second Addenda.” World Health Organization. https://www.who.int/publications/i/item/9789240045064.