{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Chicago Lead Analysis \n", "\n", "An APM Reports analysis of extensive lead testing data in Chicago shows that EPA sampling protocols fail to capture the highest levels of lead present in a water system.\n", "\n", "In the city's [2018 Water Quality Report](https://www.chicago.gov/content/dam/city/depts/water/ConsumerConfidenceReports/ChicagoWaterQuality2018.PDF), Chicago reported that their 90th-percentile lead level was 9.1 ppb, well under the federal action level of 15 ppb. This was the result of testing the first liter of water drawn from 50 sites over a period of three years, the least amount of testing a city of Chicago's size is required to do. The city reported that none of the sites tested had lead levels above the federal action level. \n", "\n", "In 2016, Thomas H. Powers, then commissioner of the Chicago Department of Water Management [wrote in a letter](https://www.chicagotribune.com/opinion/letters/ct-chicago-isn-t-the-next-flint-20160209-story.html) to the Chicago Tribune that, “Chicago's water is safe and pure, exceeding all standards set by the U.S. Environmental Protection Agency, the Illinois EPA and the drinking water industry,” The letter disputed a Tribune story, based on an EPA study, that claimed that Chicago had high lead levels. \n", "\n", "But that same year, as a part of a water quality study, Chicago began conducting lead testing for any customer that requested it. In the past four years, thousands of customers have requested the city test their water. Homes with eleveated lead levels in their first test then received more extensive testing. In these select number of homes, the city tested each of the first 10 liters out of the tap. \n", "\n", "This data represents some of the most extensive lead sampling in a major American city and demonstrates the weaknesses of relying on EPA mandated first-draw sampling. This analysis looks at the Chicago sampling data under various proposals and regulations to show the impact that different testing protocols have on the 90th-percentile lead level. \n", "\n", "#### Table of Contents\n", "* [Data Overview](#Data-Overview)\n", "* [Current LCR Sampling Protocol](#Current-LCR-Sampling-Protocol) \n", "* [Proposed LCR Revision](#LCR-Proposed-Revision)\n", "* [Michigan's Lead and Copper Rule](#Michigan-LCR) \n", "* [Highest sample](#Highest-of-the-1st-10th-Liters) \n", "* [CDC health-based action level](#CDC-Health-Based-Action-Level)\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Data Overview" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The data can be downloaded from [this website](http://chicagowaterquality.org/home#results). Each row in the dataset is a site that had 10 liters of water tested. All values are measured in parts per billion. " ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
date_sampledaddress1st_draw2nd_draw3rd_draw4th_draw5th_draw6th_draw7th_draw8th_draw9th_draw10th_draw
02016-02-2827XX N Wilton Ave**22.325.729.833.53839.938.335.231.326.2
12016-03-0454XX S Harper Ave**13.72122.819.314.510.79.337.446.665.79
22016-03-0481XX S Euclid Ave1.18<1<1<1<1<1<1<1<1<1
32016-03-1611XX W George St8.018.510.899.4210.310.59.286.195.1
42016-03-1693XX S Bennett Ave4.84.417.929.229.8410.611.811.910.910.1
\n", "
" ], "text/plain": [ " date_sampled address 1st_draw 2nd_draw 3rd_draw 4th_draw \\\n", "0 2016-02-28 27XX N Wilton Ave** 22.3 25.7 29.8 33.5 \n", "1 2016-03-04 54XX S Harper Ave** 13.7 21 22.8 19.3 \n", "2 2016-03-04 81XX S Euclid Ave 1.18 <1 <1 <1 \n", "3 2016-03-16 11XX W George St 8.01 8.5 10.8 9 \n", "4 2016-03-16 93XX S Bennett Ave 4.8 4.41 7.92 9.22 \n", "\n", " 5th_draw 6th_draw 7th_draw 8th_draw 9th_draw 10th_draw \n", "0 38 39.9 38.3 35.2 31.3 26.2 \n", "1 14.5 10.7 9.33 7.44 6.66 5.79 \n", "2 <1 <1 <1 <1 <1 <1 \n", "3 9.42 10.3 10.5 9.28 6.19 5.1 \n", "4 9.84 10.6 11.8 11.9 10.9 10.1 " ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import os\n", "import pandas as pd\n", "import altair as alt\n", "\n", "data_dir = os.path.join(os.getcwd(), 'data/source')\n", "data_out = os.path.join(os.getcwd(), 'data/processed')\n", "chicago_xlsx = os.path.join(data_dir, 'chicago_sampling_results.xlsx')\n", "\n", "chicago_sampling = pd.read_excel(chicago_xlsx, sheet_name='Sequential', skiprows=2, skipfooter=7, usecols=list(range(0,12)))\n", "\n", "columns = { 'Date Sampled': 'date_sampled',\n", " 'Address': 'address',\n", " '1st Draw': '1st_draw',\n", " '2nd Draw': '2nd_draw',\n", " '3rd Draw': '3rd_draw',\n", " '4th Draw': '4th_draw',\n", " '5th Draw': '5th_draw',\n", " '6th Draw': '6th_draw',\n", " '7th Draw': '7th_draw',\n", " '8th Draw': '8th_draw',\n", " '9th Draw': '9th_draw',\n", " '10th Draw': '10th_draw'}\n", "\n", "chicago_sampling = chicago_sampling.rename(columns=columns)\n", "\n", "chicago_sampling.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The Chicago sampling data reports all values lower than 1 ppb as '<1'. For our purposes, we will treat that as 0. Since the action level is a percentile, we are safe in making this assumption. " ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "# cast all values below 1ppb to 0\n", "chicago_sampling = chicago_sampling.replace({'<1.00': 0, '<1': 0})\n", "\n", "# There typos in some cells \n", "chicago_sampling.loc[chicago_sampling['5th_draw']=='22..1', '5th_draw'] = 22.1\n", "chicago_sampling.loc[chicago_sampling['6th_draw']=='7.49.', '6th_draw'] = 7.49\n", "\n", "\n", "# Set dtype of number columns\n", "num_cols = ['1st_draw','2nd_draw','3rd_draw','4th_draw','5th_draw','6th_draw','7th_draw','8th_draw','9th_draw', '10th_draw']\n", "chicago_sampling[num_cols] = chicago_sampling[num_cols].astype('float64')\n", "\n", "# Add year and month columns to df \n", "chicago_sampling['year'] = chicago_sampling.apply(lambda x: x['date_sampled'].year, axis=1)\n", "chicago_sampling['month'] = chicago_sampling.apply(lambda x: x['date_sampled'].month, axis=1)\n", "chicago_sampling['1st_5th_highest'] = chicago_sampling[['1st_draw', '5th_draw']].max(axis=1)\n", "chicago_sampling['highest_sample'] = chicago_sampling[num_cols].max(axis=1)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "There are likely 569 unique addresses in the dataset. There may be fewer individual homes since the data is anonymized at the block level. If two homes on the same block were both tested, they would have the same address in the data. \n", "\n", "The sampling covers the last four years. The earliest sample date is Feb 28, 2016 and the most recent is Sept. 16, 2019. " ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Unique addresses: 569\n", "Earliest sampled: 2016-02-28 00:00:00\n", "Most recent sampled: 2019-09-16 00:00:00\n" ] } ], "source": [ "print(f'Unique addresses: {len(chicago_sampling.address.unique())}')\n", "print(f'Earliest sampled: {chicago_sampling.date_sampled.min()}')\n", "print(f'Most recent sampled: {chicago_sampling.date_sampled.max()}')\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Most sampling was conducted in June, July, and August. Sampling was conducted in each month." ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", "" ], "text/plain": [ "alt.Chart(...)" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "sample_dates = chicago_sampling.date_sampled\n", "\n", "months = sample_dates.apply(lambda x: x.month).value_counts().reset_index(name='count').rename(columns={'index':'month'})\n", "\n", "display(alt.Chart(months.sort_values(by='month'), width=500).mark_bar().encode(\n", " x='month:O',\n", " y=alt.Y('count', axis=alt.Axis(title='Number of samples'))\n", "))" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", "" ], "text/plain": [ "alt.Chart(...)" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "avg_per_month = chicago_sampling.copy()\n", "avg_per_month['site_average'] = avg_per_month[num_cols].mean(axis=1)\n", "\n", "avg_month = avg_per_month.groupby('month')['site_average'].mean().reset_index()\n", "\n", "alt.Chart(avg_month, width=800, title='Average lead level per month, all samples').mark_line(point=True).encode(\n", " x=alt.X('month:O',sort=[1,2,3,4,5,6,7,8,9,10,11,12]),\n", " y=alt.Y('site_average', axis=alt.Axis(title='Avg lead concentration (ppb)', format='.1f')),\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Visualizing the lead concentration of each liter at every site shows that there is a huge variation. Even with that huge variation, we can see that there are some sites with very, very high lead levels. " ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", "" ], "text/plain": [ "alt.LayerChart(...)" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "\n", "alt.data_transformers.enable('default')\n", "alt.data_transformers.disable_max_rows()\n", "\n", "indexed_sampling = chicago_sampling.copy()\n", "indexed_sampling['id'] = indexed_sampling.index\n", "\n", "\n", "\n", "df= pd.melt(indexed_sampling,\n", " id_vars=['id'],\n", " value_vars=num_cols,\n", " var_name = 'draw_#',\n", " value_name = 'lead_concentration')\n", "\n", "base = alt.Chart(df, width=800, height=1000, title='Lead Profiles with lead action level').mark_line(point=True).encode(\n", " x=alt.X('draw_#:O',sort=num_cols, title='Liter number'),\n", " y=alt.Y('lead_concentration', axis=alt.Axis(format='.1f'),title='Lead Concentration (ppb)'),\n", " color=alt.Color('id:O', title='Site ID')\n", ")\n", "\n", "dummy = pd.DataFrame([{'draw_#': draw,'lead_concentration': 15} for draw in num_cols])\n", "\n", "line = alt.Chart(dummy).mark_line(color='red').encode(\n", " x=alt.X('draw_#:O',sort=num_cols),\n", " y=alt.Y('lead_concentration'),\n", ")\n", "\n", "base+line" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The chart below is the average lead concentration in each separate liter. The average for each liter is higher than the liter before, up to the 9th liter. The average lead concentration in 9th liter samples is a little over 14ppb, just under the federal action level. " ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", "" ], "text/plain": [ "alt.Chart(...)" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "avg_lead = df.groupby('draw_#')['lead_concentration'].mean().reset_index()\n", "\n", "alt.Chart(avg_lead, width=800, title='Average lead levels per liter number').mark_line(point=True).encode(\n", " x=alt.X('draw_#:O',sort=num_cols),\n", " y=alt.Y('lead_concentration', axis=alt.Axis(format='.1f', title='Avg lead concentration (ppb)')),\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Most individual samples in the dataset are below 50 ppb. 27% of the samples are above the action level:" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "There are 6409 samples below 50ppb\n", "There are 58 samples above 50ppb\n", "There are 1767 samples above 15ppb\n", "There are 6467 individual samples in the dataset\n", "Highest sample: 491.0 ppb\n" ] } ], "source": [ "print(f\"There are {len(df[df.lead_concentration <= 50])} samples below 50ppb\")\n", "print(f\"There are {len(df[df.lead_concentration > 50])} samples above 50ppb\")\n", "print(f\"There are {len(df[df.lead_concentration > 15])} samples above 15ppb\")\n", "print(f\"There are {len(df[~df.lead_concentration.isna()])} individual samples in the dataset\")\n", "print(f\"Highest sample: {df['lead_concentration'].max()} ppb\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Nearly a thousand samples are between 4 and 6 ppb. The chart below only looks at all samples below 50 ppb. " ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", "" ], "text/plain": [ "alt.LayerChart(...)" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "base = alt.Chart(width=800, title='Number of Samples with a given lead concentration').mark_bar().encode(\n", " x=alt.X('lead_concentration',bin=alt.Bin(maxbins=25), title='lead concentration (ppb), lead action level'),\n", " y=alt.Y('count()', axis=alt.Axis(title='Count of samples'))\n", ")\n", "\n", "vertline = alt.Chart().mark_rule(color='red').encode(\n", " x='a:Q'\n", ")\n", "\n", "\n", "alt.layer(\n", " base, vertline,\n", " data = df[df.lead_concentration <= 50]\n", ").transform_calculate(\n", " a='15'\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The charts below clearly illustrate one of the big problems with the LCR -- the lowest lead levels are found in the first liter.\n", "\n", "Each chart is a histogram of the lead concentrations of each liter drawn from the tap. The first and second liter skew low, but lead concentrations increase as water from deeper within the plumbing is tested. " ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", "" ], "text/plain": [ "alt.FacetChart(...)" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "\n", "base = alt.Chart(width=600).mark_bar().encode(\n", " x=alt.X('lead_concentration',bin=alt.Bin(maxbins=25), title='lead concentration (ppb), lead action level'),\n", " y=alt.Y('count()', axis=alt.Axis(title='Count of samples'))\n", ")\n", "\n", "alt.layer(\n", " base, vertline,\n", " data = df[df.lead_concentration <= 50]\n", ").transform_calculate(\n", " a='15'\n", ").facet(\n", " row=alt.Row('draw_#', sort=num_cols)\n", ").resolve_scale(\n", " x='independent'\n", ")\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The 90th-percentile value is what the federal government uses to determine whether a utility is in compliance with the LCR. The 90th-percentile means that 10% of samples are above the 90th-percentile value and 90% of samples are below. So if the 90th-percentile value is 10 ppb, that means that 10% of lead samples were above 10 ppb. \n", "\n", "The chart below looks at the 90th-percentile value for each liter drawn from the tap. The 8th liter and 10th liter samples have the highest 90th-percentile values at 28ppb, nearly double the action level. \n", "\n", "The first liter has the lowest 90th-percentile lead level, so as long as cities use the first-liter sampling protocol, they may be using the lowest lead levels in all of their public policy work. " ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
sample90th_percentile
01st_draw16.0
12nd_draw18.5
23rd_draw21.0
34th_draw24.9
45th_draw25.8
56th_draw26.4
67th_draw26.0
78th_draw28.0
89th_draw27.8
910th_draw28.0
\n", "
" ], "text/plain": [ " sample 90th_percentile\n", "0 1st_draw 16.0\n", "1 2nd_draw 18.5\n", "2 3rd_draw 21.0\n", "3 4th_draw 24.9\n", "4 5th_draw 25.8\n", "5 6th_draw 26.4\n", "6 7th_draw 26.0\n", "7 8th_draw 28.0\n", "8 9th_draw 27.8\n", "9 10th_draw 28.0" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", "
\n", "" ], "text/plain": [ "alt.LayerChart(...)" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ninetieth_per_sample = pd.DataFrame(data = {\"sample\": num_cols,\n", " \"90th_percentile\": [df[df['draw_#'] == draw]['lead_concentration'].quantile(.9, interpolation='nearest') for draw in num_cols]})\n", "\n", "horiline = alt.Chart().mark_rule(color='red').encode(\n", " y='a:Q'\n", ")\n", "\n", "base = alt.Chart(width=600, height=400,title='90th Percentile Value for all samples per liter').mark_line(point=True).encode(\n", " x=alt.X('sample:O',sort=num_cols, title='Liter Number'),\n", " y=alt.Y('90th_percentile:Q', axis=alt.Axis(tickCount=10, format='.1f'), title='90th-Percentile Lead Level (ppb)')\n", ")\n", "\n", "\n", "display(ninetieth_per_sample)\n", "alt.layer(\n", " base, horiline,\n", " data=ninetieth_per_sample\n", ").transform_calculate(a='15')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Chicago and Michigan Comparison \n", "Michigan enacted first-and-fifth liter compliance sampling. An APM Reports analysis of individual samples from Michigan found that fifth liter samples were 44% higher on average than first liter samples. In Chicago, the average lead concentration in the first draw sample was 9.4 ppb and the average lead concentration of the fifth draw was 11.9 ppb, a 27% increase. " ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
1st_draw5th_draw
count650.000000648.000000
mean9.36692211.880000
std17.95890410.791019
min0.0000000.000000
25%3.6800005.132500
50%6.0000008.650000
75%9.42750015.000000
max232.00000094.800000
\n", "
" ], "text/plain": [ " 1st_draw 5th_draw\n", "count 650.000000 648.000000\n", "mean 9.366922 11.880000\n", "std 17.958904 10.791019\n", "min 0.000000 0.000000\n", "25% 3.680000 5.132500\n", "50% 6.000000 8.650000\n", "75% 9.427500 15.000000\n", "max 232.000000 94.800000" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "chicago_1st_5th = chicago_sampling[['1st_draw', '5th_draw']].copy()\n", "\n", "chicago_1st_5th.describe()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Sampling Protocols\n", "\n", "The crux of the debate on lead sampling is over which liter utilities should use when calculating their 90th-percentile lead concentration. The liter that utilities use for measuring compliance has a huge impact on whether a utility passes or fails the lead action level. It also has significant implications for how a city manages its water supply\n", "\n", "There are two things that are important to keep in mind. \n", "\n", "The point of measuring lead in the water is for the utility to tweak how they control water corrosion. The intent of the LCR is for lead sampling to inform how well corrosion control is working at keeping lead levels down. The first requirement after exceeding the action level is to optomize corrosion control to try and better reduce lead levels. If the utility is consistently missing the highest lead levels, they are operating with limited data and will not be effective at reducing lead at the tap. \n", "\n", "The other thing we need to keep in mind is that utilities are supposed to be testing water at the highest risk sites. The Lead and Copper Rule has a few requirements for choosing compliance sample sites because the EPA's intent with the law was for utilities to use the sites that are most at-risk for high lead levels to inform how effective a utility's corrosion control is working. \n", "\n", "A lack of accurate lead testing data starts a cascading effect of policies and decisions based on a misleading information. Utilities are testing water with the lowest lead levels, which leads to corrosion control that is not optomized to reduce high lead levels. Utilities then reduce the amount of testing they do as they spend time under the action level, which leaves lead lines in the ground of years. \n", "\n", "To show the impact that different sampling protocols have on the 90th-percentile lead level, APM Reports analyzed the Chicago sampling data according to different rules and regulations. \n", "\n", "We break down the data four different ways for each proposal, according to different monitoring periods. Utilities have different monitoring periods based on whether they have been under the action level in previous monitoring periods. The most frequently utilities need to monitor is every 6 months. Utilities that are under the action level can go to annual and eventually tri-annual testing. \n", "\n", "We tested the data under four different monitoring periods:\n", "1. The 90th-percentile value for the entire dataset, covering 4 years of sampling. \n", "2. The 90th-percentile value for each year in the dataset, using samples gathered year-round. \n", "3. The 90th-percentile value for each year in the dataset, but only using samples from June-Sept. When utilities go on reduced monitoring, they only sample in the summer, when research shows lead levels are highest. \n", "4. The 90th-percentile value for each six-month period in the data. \n" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [], "source": [ "\n", "def pass_fail(value, rule):\n", " if rule == 'cdc': \n", " return 'passes' if value <=7.5 else 'fails' \n", " \n", " elif rule=='revision':\n", " if value >= 10 and value <=15.4:\n", " return 'fails the trigger level but passes'\n", " elif value > 15.4:\n", " return 'fails'\n", " elif value < 10:\n", " return 'passes'\n", " \n", " else:\n", " return 'passes' if value<=15.4 else 'fails'\n", "\n", "\n", "def sampling_results(rule, df=chicago_sampling):\n", " # 4 rules, 4 time periods in each rule \n", " \n", " # format rule names in plain english\n", " rule_name = {\n", " 'lcr': 'Original Lead and Copper Rule',\n", " 'revision': 'Proposed Lead and Copper Rule Revision',\n", " 'mi_lcr': 'Michigan Lead and Copper Rule',\n", " 'highest_sample': 'Highest of the 1st-10th liters',\n", " 'cdc' : 'CDC Health-Based Action Level'\n", " }\n", " \n", " # Different year masks\n", " mask_2016 = df['year'] == 2016\n", " mask_2017 = df['year'] == 2017\n", " mask_2018 = df['year'] == 2018\n", " mask_2019 = df['year'] == 2019\n", " \n", " # Different months for testing \n", " annual_testing_months = [6,7,8,9]\n", " first_half = [1,2,3,4,5,6]\n", " second_half = [7,8,9,10,11,12]\n", " \n", " mask_annual_testing = df['month'].isin(annual_testing_months)\n", " mask_first_half = df['month'].isin(first_half)\n", " mask_second_half = df['month'].isin(second_half)\n", " \n", " if rule == 'lcr' or rule == 'cdc' or rule == 'revision':\n", " sample = '1st_draw'\n", " interpolation = 'nearest'\n", " \n", " elif rule == 'mi_lcr':\n", " sample = '1st_5th_highest'\n", " interpolation = 'linear'\n", " \n", " elif rule == 'highest_sample':\n", " sample = 'highest_sample'\n", " interpolation = 'nearest'\n", " \n", "\n", " chi_tot = df[sample].quantile(.9, interpolation=interpolation)\n", "\n", " chi_2016_tot = df[mask_2016][sample].quantile(.9, interpolation=interpolation)\n", " chi_2017_tot = df[mask_2017][sample].quantile(.9, interpolation=interpolation) \n", " chi_2018_tot = df[mask_2018][sample].quantile(.9, interpolation=interpolation)\n", " chi_2019_tot = df[mask_2019][sample].quantile(.9, interpolation=interpolation) \n", "\n", " chi_2016_annual = df[(mask_2016)&(mask_annual_testing)][sample].quantile(.9, interpolation=interpolation) \n", " chi_2017_annual = df[(mask_2017)&(mask_annual_testing)][sample].quantile(.9, interpolation=interpolation) \n", " chi_2018_annual = df[(mask_2018)&(mask_annual_testing)][sample].quantile(.9, interpolation=interpolation)\n", " chi_2019_annual = df[(mask_2019)&(mask_annual_testing)][sample].quantile(.9, interpolation=interpolation)\n", "\n", " chi_2016_pt1 = df[(mask_2016)&(mask_first_half)][sample].quantile(.9, interpolation=interpolation) \n", " chi_2017_pt1 = df[(mask_2017)&(mask_first_half)][sample].quantile(.9, interpolation=interpolation) \n", " chi_2018_pt1 = df[(mask_2018)&(mask_first_half)][sample].quantile(.9, interpolation=interpolation)\n", " chi_2019_pt1 = df[(mask_2019)&(mask_first_half)][sample].quantile(.9, interpolation=interpolation)\n", "\n", " chi_2016_pt2 = df[(mask_2016)&(mask_second_half)][sample].quantile(.9, interpolation=interpolation) \n", " chi_2017_pt2 = df[(mask_2017)&(mask_second_half)][sample].quantile(.9, interpolation=interpolation) \n", " chi_2018_pt2 = df[(mask_2018)&(mask_second_half)][sample].quantile(.9, interpolation=interpolation)\n", " chi_2019_pt2 = df[(mask_2019)&(mask_second_half)][sample].quantile(.9, interpolation=interpolation)\n", " \n", " # Length of each dataset \n", " chi_2016_tot_len = df[mask_2016][sample].count() #len(df[mask_2016][sample])\n", " chi_2017_tot_len = df[mask_2017][sample].count() #len(df[mask_2017][sample])\n", " chi_2018_tot_len = df[mask_2018][sample].count() #len(df[mask_2018][sample])\n", " chi_2019_tot_len = df[mask_2019][sample].count() #len(df[mask_2019][sample])\n", " \n", " chi_2016_annual_len = df[(mask_2016)&(mask_annual_testing)][sample].count() #len(df[(mask_2016)&(mask_annual_testing)][sample])\n", " chi_2017_annual_len = df[(mask_2017)&(mask_annual_testing)][sample].count() #len(df[(mask_2017)&(mask_annual_testing)][sample])\n", " chi_2018_annual_len = df[(mask_2018)&(mask_annual_testing)][sample].count() #len(df[(mask_2018)&(mask_annual_testing)][sample])\n", " chi_2019_annual_len = df[(mask_2019)&(mask_annual_testing)][sample].count() #len(df[(mask_2019)&(mask_annual_testing)][sample])\n", " \n", " chi_2016_pt1_len = df[(mask_2016)&(mask_first_half)][sample].count() #len(df[(mask_2016)&(mask_first_half)][sample])\n", " chi_2017_pt1_len = df[(mask_2017)&(mask_first_half)][sample].count() #len(df[(mask_2017)&(mask_first_half)][sample])\n", " chi_2018_pt1_len = df[(mask_2018)&(mask_first_half)][sample].count() #len(df[(mask_2018)&(mask_first_half)][sample])\n", " chi_2019_pt1_len = df[(mask_2019)&(mask_first_half)][sample].count() #len(df[(mask_2019)&(mask_first_half)][sample])\n", " \n", " chi_2016_pt2_len = df[(mask_2016)&(mask_second_half)][sample].count() #len(df[(mask_2016)&(mask_second_half)][sample])\n", " chi_2017_pt2_len = df[(mask_2017)&(mask_second_half)][sample].count() #len(df[(mask_2017)&(mask_second_half)][sample])\n", " chi_2018_pt2_len = df[(mask_2018)&(mask_second_half)][sample].count() #len(df[(mask_2018)&(mask_second_half)][sample])\n", " chi_2019_pt2_len = df[(mask_2019)&(mask_second_half)][sample].count() #len(df[(mask_2019)&(mask_second_half)][sample])\n", " \n", " print(f\"\"\"\n", " Sampling Protocol: {rule_name[rule]}\n", " - 90th-percentile value for the entire dataset: {chi_tot}, This {pass_fail(chi_tot, rule) +' the AL'}\n", "\n", " - 90th-percentile value for each year, for the entire year:\n", " -- 2016: {round(chi_2016_tot,2)} ppb, This {pass_fail(chi_2016_tot, rule) +' the AL'} (n= {chi_2016_tot_len})\n", " -- 2017: {round(chi_2017_tot,2)} ppb, This {pass_fail(chi_2017_tot, rule) +' the AL'} (n= {chi_2017_tot_len})\n", " -- 2018: {round(chi_2018_tot,2)} ppb, This {pass_fail(chi_2018_tot, rule) +' the AL'} (n= {chi_2018_tot_len})\n", " -- 2019: {round(chi_2019_tot,2)} ppb, This {pass_fail(chi_2019_tot, rule) +' the AL'} (n= {chi_2019_tot_len})\n", "\n", " - 90th-percentile value for each year, for the annual federal testing months of June-Sept:\n", " -- 2016: {round(chi_2016_annual,2)} ppb, This {pass_fail(chi_2016_annual, rule) +' the AL'} (n= {chi_2016_annual_len})\n", " -- 2017: {round(chi_2017_annual,2)} ppb, This {pass_fail(chi_2017_annual, rule) +' the AL'} (n= {chi_2017_annual_len})\n", " -- 2018: {round(chi_2018_annual,2)} ppb, This {pass_fail(chi_2018_annual, rule) +' the AL'} (n= {chi_2018_annual_len})\n", " -- 2019: {round(chi_2019_annual,2)} ppb, This {pass_fail(chi_2019_annual, rule) +' the AL'} (n= {chi_2019_annual_len})\n", "\n", " - 90th-percentile value for each six-month period\n", " -- Jan - June 2016: {round(chi_2016_pt1,2)} ppb, This {pass_fail(chi_2016_pt1, rule) +' the AL'} (n= {chi_2016_pt1_len})\n", " -- July - Dec 2016: {round(chi_2016_pt2,2)} ppb, This {pass_fail(chi_2016_pt2, rule) +' the AL'} (n= {chi_2016_pt2_len})\n", "\n", " -- Jan - June 2017: {round(chi_2017_pt1,2)} ppb, This {pass_fail(chi_2017_pt1, rule) +' the AL'} (n= {chi_2017_pt1_len})\n", " -- July - Dec 2017: {round(chi_2017_pt2,2)} ppb, This {pass_fail(chi_2017_pt2, rule) +' the AL'} (n= {chi_2017_pt2_len})\n", "\n", " -- Jan - June 2018: {round(chi_2018_pt1,2)} ppb, This {pass_fail(chi_2018_pt1, rule) +' the AL'} (n= {chi_2018_pt1_len})\n", " -- July - Dec 2018: {round(chi_2018_pt2,2)} ppb, This {pass_fail(chi_2018_pt2, rule) +' the AL'} (n= {chi_2018_pt2_len})\n", "\n", " -- Jan - June 2019: {round(chi_2019_pt1,2)} ppb, This {pass_fail(chi_2019_pt1, rule) +' the AL'} (n= {chi_2019_pt1_len})\n", " -- July - Dec 2016: {round(chi_2019_pt2,2)} ppb, This {pass_fail(chi_2019_pt2, rule) +' the AL'} (n= {chi_2019_pt2_len})\n", " \"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Current LCR Sampling Protocol\n", "\n", "The current LCR's testing protocol requires a utility test the first liter of water drawn from a tap after the water has sat motionless in the pipes for at least six hours. This testing protocol routinely misses the highest levels of lead because plumbing inside a house isn't made of lead. Water that rests further into the plumbing, in contact with the lead service line, will have higher lead content than water in the house. \n", "\n", "If the 90th-percentile value of the first-draw samples are below 15 ppb (technically 15.4 due to rounding), the utility is in compliance. If they test above the action level, the utility is required to re-optomize corrosion control and re-sample. If they are still above the lead action level, the utility then needs to begin replacing lead service lines until they test under the action level for two consecutive six-month periods. " ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", " Sampling Protocol: Original Lead and Copper Rule\n", " - 90th-percentile value for the entire dataset: 16.0, This fails the AL\n", "\n", " - 90th-percentile value for each year, for the entire year:\n", " -- 2016: 15.5 ppb, This fails the AL (n= 128)\n", " -- 2017: 16.8 ppb, This fails the AL (n= 115)\n", " -- 2018: 18.6 ppb, This fails the AL (n= 143)\n", " -- 2019: 13.5 ppb, This passes the AL (n= 264)\n", "\n", " - 90th-percentile value for each year, for the annual federal testing months of June-Sept:\n", " -- 2016: 23.8 ppb, This fails the AL (n= 50)\n", " -- 2017: 17.2 ppb, This fails the AL (n= 29)\n", " -- 2018: 15.4 ppb, This passes the AL (n= 63)\n", " -- 2019: 15.2 ppb, This passes the AL (n= 135)\n", "\n", " - 90th-percentile value for each six-month period\n", " -- Jan - June 2016: 22.3 ppb, This fails the AL (n= 25)\n", " -- July - Dec 2016: 15.3 ppb, This passes the AL (n= 103)\n", "\n", " -- Jan - June 2017: 16.3 ppb, This fails the AL (n= 76)\n", " -- July - Dec 2017: 17.2 ppb, This fails the AL (n= 39)\n", "\n", " -- Jan - June 2018: 24.1 ppb, This fails the AL (n= 29)\n", " -- July - Dec 2018: 15.4 ppb, This passes the AL (n= 114)\n", "\n", " -- Jan - June 2019: 12.4 ppb, This passes the AL (n= 179)\n", " -- July - Dec 2016: 16.0 ppb, This fails the AL (n= 85)\n", " \n" ] } ], "source": [ "sampling_results('lcr')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## LCR Proposed Revision \n", "\n", "The proposed revision to the Lead and Copper Rule keeps first-draw sampling, but it adds new requirements for utitilies that exceed certain lead levels. The EPA has proposed establishing a trigger level of 10 ppb, in addition to the action level of 15 ppb. \n", "\n", "If the utility tests between 10 and 15 ppb, they need to re-optomize corrosion control and implement a lead service line replacement program. The revision does not specify a set percentage of lead service lines the utility would be required to replace after a trigger level exceedance, only that a utility's plan must be approved by the state. \n", "\n", "Though the revision does not change the action level, it does add requirements for cities to conduct follow-up testing with individual sites that test higher than 15 ppb. " ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", " Sampling Protocol: Proposed Lead and Copper Rule Revision\n", " - 90th-percentile value for the entire dataset: 16.0, This fails the AL\n", "\n", " - 90th-percentile value for each year, for the entire year:\n", " -- 2016: 15.5 ppb, This fails the AL (n= 128)\n", " -- 2017: 16.8 ppb, This fails the AL (n= 115)\n", " -- 2018: 18.6 ppb, This fails the AL (n= 143)\n", " -- 2019: 13.5 ppb, This fails the trigger level but passes the AL (n= 264)\n", "\n", " - 90th-percentile value for each year, for the annual federal testing months of June-Sept:\n", " -- 2016: 23.8 ppb, This fails the AL (n= 50)\n", " -- 2017: 17.2 ppb, This fails the AL (n= 29)\n", " -- 2018: 15.4 ppb, This fails the trigger level but passes the AL (n= 63)\n", " -- 2019: 15.2 ppb, This fails the trigger level but passes the AL (n= 135)\n", "\n", " - 90th-percentile value for each six-month period\n", " -- Jan - June 2016: 22.3 ppb, This fails the AL (n= 25)\n", " -- July - Dec 2016: 15.3 ppb, This fails the trigger level but passes the AL (n= 103)\n", "\n", " -- Jan - June 2017: 16.3 ppb, This fails the AL (n= 76)\n", " -- July - Dec 2017: 17.2 ppb, This fails the AL (n= 39)\n", "\n", " -- Jan - June 2018: 24.1 ppb, This fails the AL (n= 29)\n", " -- July - Dec 2018: 15.4 ppb, This fails the trigger level but passes the AL (n= 114)\n", "\n", " -- Jan - June 2019: 12.4 ppb, This fails the trigger level but passes the AL (n= 179)\n", " -- July - Dec 2016: 16.0 ppb, This fails the AL (n= 85)\n", " \n" ] } ], "source": [ "sampling_results('revision')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Michigan LCR \n", "\n", "In June 2018, Michigan adopted the country's toughest lead regulations. Utilities with lead service lines in Michigan are required to test the first and the fifth liter from the tap at each site and use the higher of the two results in their 90th-percentile calculations. That means that if there are 50 testing sites, two samples are taken at each and the higher of the two is used when calculating the 90th-percentile value. \n", "\n", "Michigan will also lower the action level to 12 ppb by 2025.\n", "\n", "Adding in just one more liter nearly doubles the 90th-percentile value in Chicago, from 16 to 29.5 ppb. Some monitoring periods have 90th-percentile values as high as 39 ppb, more than double the current LCR action level. " ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", " Sampling Protocol: Michigan Lead and Copper Rule\n", " - 90th-percentile value for the entire dataset: 29.5, This fails the AL\n", "\n", " - 90th-percentile value for each year, for the entire year:\n", " -- 2016: 26.49 ppb, This fails the AL (n= 128)\n", " -- 2017: 36.8 ppb, This fails the AL (n= 115)\n", " -- 2018: 28.1 ppb, This fails the AL (n= 143)\n", " -- 2019: 25.68 ppb, This fails the AL (n= 264)\n", "\n", " - 90th-percentile value for each year, for the annual federal testing months of June-Sept:\n", " -- 2016: 29.96 ppb, This fails the AL (n= 50)\n", " -- 2017: 37.7 ppb, This fails the AL (n= 29)\n", " -- 2018: 23.8 ppb, This fails the AL (n= 63)\n", " -- 2019: 27.22 ppb, This fails the AL (n= 135)\n", "\n", " - 90th-percentile value for each six-month period\n", " -- Jan - June 2016: 25.96 ppb, This fails the AL (n= 25)\n", " -- July - Dec 2016: 26.02 ppb, This fails the AL (n= 103)\n", "\n", " -- Jan - June 2017: 32.55 ppb, This fails the AL (n= 76)\n", " -- July - Dec 2017: 39.38 ppb, This fails the AL (n= 39)\n", "\n", " -- Jan - June 2018: 37.0 ppb, This fails the AL (n= 29)\n", " -- July - Dec 2018: 22.33 ppb, This fails the AL (n= 114)\n", "\n", " -- Jan - June 2019: 19.52 ppb, This fails the AL (n= 179)\n", " -- July - Dec 2016: 27.56 ppb, This fails the AL (n= 85)\n", " \n" ] } ], "source": [ "sampling_results('mi_lcr')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Highest of the 1st-10th Liters\n", "\n", "53.5% of the 650 sites are above the AL if we take the highest sample found in the 1st through 10th liter. The 90th-percentile value in Chicago is as high as 44.9 ppb during some monitoring periods, nearly three times the action level. \n" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", " Sampling Protocol: Highest of the 1st-10th liters\n", " - 90th-percentile value for the entire dataset: 36.9, This fails the AL\n", "\n", " - 90th-percentile value for each year, for the entire year:\n", " -- 2016: 36.7 ppb, This fails the AL (n= 128)\n", " -- 2017: 44.5 ppb, This fails the AL (n= 115)\n", " -- 2018: 35.4 ppb, This fails the AL (n= 143)\n", " -- 2019: 34.0 ppb, This fails the AL (n= 264)\n", "\n", " - 90th-percentile value for each year, for the annual federal testing months of June-Sept:\n", " -- 2016: 36.7 ppb, This fails the AL (n= 50)\n", " -- 2017: 44.5 ppb, This fails the AL (n= 29)\n", " -- 2018: 35.4 ppb, This fails the AL (n= 63)\n", " -- 2019: 37.3 ppb, This fails the AL (n= 135)\n", "\n", " - 90th-percentile value for each six-month period\n", " -- Jan - June 2016: 39.9 ppb, This fails the AL (n= 25)\n", " -- July - Dec 2016: 36.5 ppb, This fails the AL (n= 103)\n", "\n", " -- Jan - June 2017: 37.2 ppb, This fails the AL (n= 76)\n", " -- July - Dec 2017: 44.9 ppb, This fails the AL (n= 39)\n", "\n", " -- Jan - June 2018: 38.5 ppb, This fails the AL (n= 29)\n", " -- July - Dec 2018: 34.7 ppb, This fails the AL (n= 114)\n", "\n", " -- Jan - June 2019: 32.1 ppb, This fails the AL (n= 179)\n", " -- July - Dec 2016: 37.3 ppb, This fails the AL (n= 85)\n", " \n" ] } ], "source": [ "sampling_results('highest_sample')" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "53.54% of sites (348/650) had at least one liter above the action level\n" ] } ], "source": [ "perc_failed = round(100 * len(chicago_sampling[chicago_sampling['highest_sample'] >= 15.5]) / len(chicago_sampling), 2)\n", "\n", "print(f\"{perc_failed}% of sites ({len(chicago_sampling[chicago_sampling['highest_sample'] >= 15.5])}/{len(chicago_sampling)}) had at least one liter above the action level\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## CDC Health Based Action Level\n", "\n", "The 15 ppb action level is an arbitrary standard with no bearing on health. Scientists agree that there is no safe level of lead in drinking water and the Lead and Copper Rule set 0 ppb as goal that utilities should try and achieve. \n", "\n", "\n", "The current Lead and Copper Rule Revision leaves the action level alone. However, if the action level were lowered to be more reflective of a health-based standard, as officials at the CDC wanted, Chicago would be failing the AL according to their compliance sampling. And there's no way of analyzing the data where they'd come close to meeting it using expanded testing." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Data Export" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [], "source": [ "chicago_sampling_out_file = os.path.join(data_out, 'chicago_sampling.csv')\n", "\n", "chicago_sampling.to_csv(chicago_sampling_out_file, index=False)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.8.1" } }, "nbformat": 4, "nbformat_minor": 4 }