vvEPA
Instructions for Accessing
Unregulated Contaminant Monitoring Rule (UCMR) Results
Data Summary Documents and the UCMR Webpages
Before conducting your own assessment of the UCMR results, please review the EPA's UCMR Data Summary
document for each monitoring cycle (UCMR 1, UCMR 2, UCMR 3, UCMR 4, UCMR 5), particularly noting the Data
Considerations and Data Definitions sections. More information about each UCMR cycle, including the list of
contaminants and analytical methods, types of PWSs required to monitor, frequency and location of sample
collection, and sampling design, can also be found on the UCMR webpages.
In this Document
• Option 1: Using the UCMR 5 and UCMR Archival Data Finders
o Filtering and Sorting Occurrence Data
o Downloading Data
• Option 2: Using the UCMR Occurrence Data Text Files
o Filtering Occurrence Data
o Additional Data Element and Indicators Text Files
• If you cannot find results for a public water system (PWS)
Option 1: Using the UCMR 5 and UCMR Archival Data Finders
The UCMR 5 Data Finder allows people to easily view, filter, and download analytical results and summary
metrics from the fifth Unregulated Contaminant Monitoring Rule from a web browser. Similarly, the UCMR
Archival Data Finder allows people to easily access analytical results and summary metrics from the first through
fourth monitoring cycles (UCMR 1-4).
1. Begin by reviewing the Data Definitions for the UCMR 5 and UCMR Archival Data Finders in Table 1 and
Table 2. respectively.
2. Access the Data Finders through the links on the EPA's UCMR website in the Occurrence Data section.
a. The UCMR 5 Data Finder includes the following main tabs:
i. The Summary Pivot Table contains an expandable and filterable table of UCMR 5
analytical results summarized by contaminant, EPA Region, and state.
ii. The Result Data Table contains a filterable table of UCMR 5 analytical results.
iii. Contaminant Information contains a filterable table with the UCMR 5 contaminants,
their respective minimum reporting levels (MRLs), and available health-based reference
concentrations (ref cone) and reference doses for some of the UCMR 5 contaminants.
b. The UCMR Archival Data Finder includes the following main tabs:
i. The Summary Pivot Table contains an expandable and filterable table of UCMR 1-4
analytical results summarized by UCMR cycle, contaminant, EPA Region, and state.
ii. The Result Data Table contains a filterable table of UCMR 1-4 analytical results.
iii. Contaminant Information contains a filterable table with the UCMR 1-4 contaminants,
their respective UCMR cycle, UCMR MRL, CASRN, DTXSID, classification, and UCMR
March 2024
Office of Water (MS-140)
EPA 815-B-24-009
Page 1 of 8
-------
monitoring requirement. If a contaminant was monitored under multiple UCMR cycles,
it will be included in this table multiple times.
Filtering and Sorting Occurrence Data
3. There are multiple ways to filter occurrence data in the Data Finders. Two options are:
a. Use one or more quick filter options along the left side of the screen to filter by: UCMR Cycle
(Archival Data Finder only), EPA Region, State, PWS, PWS Size, Facility Water Type,
Contaminant, Results > MRL, or Results > Ref Cone (UCMR 5 Data Finder only).
b. Use the magnifying glass icon to the right of any column in the Result Data Table.
4. After clicking on a quick filter or a magnifying glass icon, all available options will show in a listbox. From
there, either type to search for a specific option or choose from the list. Click to select the option(s);
selected options will be highlighted green. Once selected, click on the check mark icon in the upper right
corner of the listbox or press Enter on the keyboard.
a. Numerical fields (e.g., the Result field) can be searched and filtered by value using numerical
operators (i.e., < [less than], > [greater than], <= [less than or equal to], >= [greater than or equal
to], = [equal to]).
i. For example, to search for any result greater than 0.01, click on the magnifying glass to
the right of the Result field, type >0.01, and press Enter on the keyboard.
5. All filter selections will be applied to all tables and summary numbers in the Data Finder and will be
shown in green in the Selections bar at the top.
a. To clear individual selections, click the X icon to the right of a filter in the current Selections bar.
b. To clear all selections, click the blue Clear Selections button on the bottom left side below the
quick filters.
6. To sort the data within a table, click on the column heading you wish to sort by. The first time you click
on a column heading, the data will sort in ascending order (i.e., low to high for numbers, A to Z for
letters). If you click on the heading a second time, the data will sort in descending order (i.e., high to low
for numbers, Z to A for letters).
Downloading Data
7. All tables in the UCMR Data Finders are downloadable to a Microsoft Excel file (.xlsx) which can be
opened directly in Microsoft Excel or imported into another software program of your choice. All filters
that are applied when you click the download button will apply to the data included in the downloaded
file.
a. To download the Result Data Table, click on the Download Data Table button and follow the
instructions on the screen to save the file.
i. The data must be filtered to 1,000,000 total results or less before this table can be
downloaded.
b. To download the Summary Pivot Table, click on the Download Summary Table button and
follow the instructions on the screen to save the file.
c. To download the Contaminant Information table, click on the Download Contaminant Info
button and follow the instructions on the screen to save the file.
Page 2 of 8
-------
Option 2: Using the UCMR Occurrence Data Text Files
For those interested in large-scale data processing using statistical or data analysis software, the EPA
recommends using the occurrence data text files containing the UCMR analytical results as well as additional
information reported during monitoring, if applicable. The UCMR datasets are available for download in a tab-
delimited text file format and can be imported into various programs. To improve accessibility and transparency
across the UCMR datasets, the UCMR 1-5 occurrence data text files have the same data fields. The following
steps review how to import the text files into Microsoft Excel. A basic understanding of Excel is necessary to
effectively use the instructions. Microsoft Excel 2013 or a newer version is recommended due to the size of the
datasets. These instructions were created using Excel in Microsoft 365.
1. Begin by reviewing the Data Definitions for the occurrence data text files in the EPA's UCMR Data
Summary document for each monitoring cycle.
2. Access the text files through the EPA's UCMR website in the Occurrence Data section.
3. Determine which dataset you are interested in and then Save the zip file to your computer.
a. Select UCMR 1, 2, 3, 4, or 5 Occurrence Data Text Files to view all the analytical results (i.e.,
results for all contaminants reported by all PWSs; those reported to date for UCMR 5). The
UCMR_All.txt files may be too large to be imported into Excel, in which case you can try other
applications (e.g., Microsoft Access) or import a subset of the data described in 3b and 3c.
b. Select UCMR 1, 2, 3, 4, or 5 Occurrence Data Text Files by State to view all the analytical results
(those reported to date for UCMR 5), organized by Tribes and states. Within each zip file, one
text file will have all results for Tribal PWSs and for the states starting alphabetically with A
through L (e.g., UCMR5_AII_Tribes_AK_LA.txt); another file will have all results for the states
starting alphabetically with M through W (e.g., UCMR5_AII_MA_WY.txt). The results are
organized this way to address file size limitations and streamline data management.
c. Select UCMR 1, 2, 3, 4, or 5 Occurrence Data Text Files by Method Classification to view all the
analytical results (those reported to date for UCMR 5), organized by analytical method. Within
each zip file, you will find individual text files with results organized by method (e.g., a Method
200.7 file with results for lithium).
4. Navigate to the location on your computer where you saved the zip file and unzip or extract the zip file
contents.
5. Open a blank workbook in Microsoft Excel.
6. In the workbook, select Data among the tabs at the top of the page.
7. On the far left top of the screen in the Get & Transform Data section, select From Text/CSV. (Alternate
option: Click Get Data and select From File from the dropdown menu and then choose From Text/CSV).
8. You will be prompted to select a text file. Find a text file you unzipped or extracted in Step 4 and click
Import.
9. A preview of the file will appear. The default settings will be displayed. Under Delimiter, make sure Tab
is selected. Click Transform Data.
10. The Query Editor will appear. In the Applied Steps section on the right side of the Query Editor under
Promoted Headers, click on the X next to Changed Type to remove it. This will change the data type of
all columns to "Text" format, which is useful for most columns in the file.
11. In the preview of the file, change data types for the following columns:
a. Scroll to the right until you see the CollectionDate column. Click on the data type options (icon:
ABC) to the left of the column name and select Date from the dropdown menu.
b. Scroll to the MRL column and select Decimal Number from the data type dropdown menu.
Page 3 of 8
-------
c. Scroll to the AnalyticalResultValue column and select Decimal Number from the data type
dropdown menu. Note: the preview will show null for any results that are below the MRL.
12. In the Home tab at the far left click Close & Load. The import may take several minutes.
a. Excel worksheets have a limit of 1,048,576 rows. If the text file you are importing has more rows
than this, you will see the message: "The query returned more data than will fit on a
worksheet." In this case, you may consider downloading and importing a subset of the data; see
Step 3.
b. This import created a "link" between the Excel spreadsheet and the text file. If you want to
"unlink" the Excel workbook and the text file, go to the Table Design tab at the top of the screen
and in the External Table Data section, click Unlink (icon: broken chain). A popup window will
appear saying, "This will permanently remove the query definition from the sheet. Continue?"
Click Yes.
c. All fields (except CollectionDate) are currently stored in a "General" format. To ensure text
fields that may contain numbers (e.g., PWSID, FacilitylD, SamplePointID, ZIPCode) remain in a
"Text" format, highlight the column or range of columns you want to convert and navigate to
the Number section of the Home tab. Click on the dropdown field that reads General and select
Text. This prevents leading zeros from being removed from cells in these columns.
13. Save the Excel file. Note: You will need to repeat the above Steps to review updated UCMR 5 datasets
posted by the EPA quarterly until completion of data reporting in 2026.
Filtering Occurrence Data
14. Each column header will have a small dropdown arrow displayed allowing you to filter the results.
a. To look for results for a specific PWS, click the dropdown arrow for PWSID or PWSName. Within
the search field, type the ID or name and select from the displayed list. Click OK.
i. To search for a different PWS, click the dropdown arrow and Clear Filter From [field
name].
b. To filter the results by Contaminant, click the column dropdown arrow and select from the list.
i. To further filter for results by AnalyticalResultValue, click the column dropdown arrow
and hover the cursor over the Number Filters option. From there, you can select a
method for filtering the data (e.g., greater than, greater than or equal to). After you
select a filtering method, a Custom Autofilter pop up box will appear allowing you to
enter one or more values and optional other methods for filtering the data (e.g., greater
than X, less than Y).
c. Multiple filters can be applied, allowing you to look for data for a specific PWS and for a specific
contaminant of interest.
15. To remove all filters at once, select Data among the tabs at the top of the page and in the Sort & Filter
section, click Clear or de-select Filter and the entire dataset will again be displayed.
Additional Data Element and Indicators Text Files
16. The following text files include information reported beyond the analytical results for UCMR
contaminants. The files are contained in the respective occurrence data zip files for each UCMR cycle, as
described in Step 3. For Data Definitions specific to each of the files listed below, please see the
corresponding UCMR Data Summary.
a. UCMR[3, 4, 5j_ZIPCodes.txt - U.S. Postal Service ZIP Code(s) for all areas served by a PWS,
available for UCMR 3, UCMR 4, and UCMR 5
Page 4 of 8
-------
b. UCMRl_Aeromonas_AddtlDataElem.txt - Supplemental data from Screening Survey
monitoring for Aeromonas (pH, pH Method, Temperature, Temperature Units, Temperature
Method, Turbidity, Turbidity Units, Turbidity Method, Free Disinfectant Residual, Free
Disinfectant Residual Units, Free Disinfectant Residual Method, Total Disinfectant Residual, Total
Disinfectant Residual Units, Total Disinfectant Residual Method)
c. UCMR2_Disinfectant_AddtlDataElem.txt - Disinfectant Type
d. UCMR3_Virus_lndicators.txt - Analytical results from Pre-Screen Testing monitoring for
microbiological indicators including total coliforms, E. coli, Enterococci, aerobic spores, male
specific phage, and somatic phage
e. UCMR3_Disinfectant_AddtlDataElem.txt - Disinfectant Type
f. UCMR4_Cyanotoxins_AddtlDataElem.txt - Disinfectant Type, Treatment Information,
Cyanotoxin Bloom Occurrence, Cyanotoxin Occurrence, Cyanotoxin Possible Bloom Treatment,
Cyanotoxin Possible Bloom Source Water
g. UCMR4_HAA_AddtlDataElem.txt - Disinfectant Type, Disinfectant Residual, Treatment
Information
h. UCMR4_HAA_lndicators.txt - Analytical results for total organic carbon and bromide
i. UCMR5_AddtlDataElem.txt - Disinfectant Type, Treatment Information, Lithium Occurrence,
Lithium Treatment, PFAS Occurrence, PFAS Treatment, Potential PFAS Sources, Potential PFAS
Sources Detail
17. To look at the text files described in the previous Step, you will need to import the associated datasets
into new worksheets.
a. To add a worksheet to the workbook that contains your occurrence data worksheet, click the
plus (+) symbol in the bottom left corner of the screen next to the existing worksheet.
b. Follow Steps 1-13 to import another text file into the new worksheet.
i. See Step 12c to ensure that the ZIPCode field remains in a "Text" format to prevent
leading zeros from being removed from cells.
c. In your occurrence data worksheet, follow Step 14a to filter for a specific PWSID or PWSName
and note the PWSID for this system.
i. In the additional data elements worksheet, use the dropdown arrow for PWSID. Within
the search field, type the PWSID you noted into the search box and click OK. You will
now see the available additional data elements for sampling locations and events for the
selected PWS.
1. You can apply other filters to view the additional data elements for a specific
facility, sample point, or sample event.
2. Note: For UCMR 5, PWSs may have entered additional data elements for sample
events that do not yet have analytical results, and additional data elements may
not yet be available for all sample events with analytical results.
ii. In the ZIP Codes worksheet, use the dropdown arrow for PWSID and within the search
field, type the PWSID you noted into the search box and click OK. You will now see the
reported ZIP Code(s) served by the PWS.
1. Note: For UCMR 5, PWSs that do not yet have analytical results may have
entered ZIP Codes, and ZIP Codes may not yet be available for all PWSs with
analytical results.
Page 5 of 8
-------
cannot find results for a public water system (PWS):
In the Data Finders,
-------
Table 1. Data Definitions for the UCMR 5 Data Finder
Field Name
Definition
PWS ID
Public Water System (PWS) Identification Code. The code used to identify each PWS. The code
begins with the standard 2-character postal state abbreviation or Region code for Tribes; the
remaining 7 numbers are unique to each PWS in the state. Utah PWS IDs begin with 4 letters
(UTAH) followed by 5 numbers
PWS Name
Name of the PWS
Contaminant
The UCMR 5 contaminant analyzed
Result (ng/L)
Numeric value of the analytical result in pg/L for the contaminant. Results less than the UCMR MRL
are indicated by 10,000)
Facility Water Type
Source of water at the facility: SW (surface water), GW (ground water), GU (ground water under
the direct influence of surface water), MX (any combination of SW, GW, and GU)
Sample Point Type
Sampling Point Type Code: EP (entry point to the distribution system)
EPA Region
EPA Region (states): Region 1 (CT, ME, MA, NH, Rl, VT), Region 2 (NJ, NY, PR [Puerto Rico], VI
[Virgin Islands]), Region 3 (DE, DC, MD, PA, VA, WV), Region 4 (AL, FL, GA, KY, MS, NC, SC, TN),
Region 5 (IL, IN, Ml, MN, OH, Wl), Region 6 (AR, LA, NM, OK, TX), Region 7 (IA, KS, MO, NE), Region
8 (CO, MT, ND, SD, UT, WY), Region 9 (AZ, CA, HI, NV, AS [American Samoa], GU [Guam], MP
[Northern Marianas Islands], NN [Navajo Nation]), Region 10 (AK, ID, OR, WA)
State
State abbreviation. Tribal PWSs without primacy are attributed to an EPA Region (01, 02, 03, 04,
05, 06, 07, 08, 09, 10)
Minimum Reporting Level
(MRL, ng/L)
Minimum Reporting Level defined by UCMR 5 in |ag/L for the contaminant. Based on laboratory
capability; not related to contaminant health effects information
Table 2. Data Definitions for the UCMR Archival Data Finder
Field Name
Definition
UCMR Cycle
UCMR cycle and monitoring years. Results may have sample collection dates outside the designated UCMR
sample collection timeframe (e.g., resample collection): UCMR 4 (2018-2020), UCMR 3 (2013-2015), UCMR 2
(2008-2010), UCMR 1 (2001-2005)
PWS ID
Public Water System (PWS) Identification Code. The code used to identify each PWS. The code begins with the
standard 2-character postal state abbreviation or Region code for Tribes; the remaining 7 numbers are unique
to each PWS in the state. Utah PWS IDs for UCMR 2-4 begin with 4 letters (UTAH) followed by 5 numbers
PWS Name
Name of the PWS
Contaminant
The UCMR contaminant analyzed
Result
Numeric value of the analytical result in pg/L for the chemical contaminants, GC/L or MPN/L for the viruses,
and CFU/lOOmL for the microbial contaminants. Results less than the UCMR MRL are indicated by
-------
Field Name
Definition
Sample Point ID
Identification code for each sample point location at the PWS
Sample Point Name
Name of the sample point at the PWS. This field is blank for some UCMR 1 records
Associated Facility
ID
Identification code for the facility of the associated MR (UCMR 2 and 3 only; null for UCMR 1 and 4)
Associated Sample
Point ID
Identification code for the sample point of the associated MR (UCMR 2 and 3 only; null for UCMR 1 and 4)
Sample Event Code
Identification code for each sample event: SE1, SE2, SE3, SE4 for all contaminants, where "SE1" and "SE2"
represent the first and second sample event for all water types and "SE3" and "SE4" represent the third and
fourth sample event for SW and GU sources only; SE5, SE6, SE7, SE8 for UCMR 4 cyanotoxins (SW and GU
sources only). (UCMR 2-4 only; null for UCMR 1)
Sample ID
Identification code for each sample. For UCMR 1 this field was created using a concatenation of PWS ID,
Facility ID, Sample Point ID, Collection Date, Method ID, and UCMR1 Sample Type
Method ID
Identification code of the analytical method
PWS Size
Size category [S (< 10,000), L (> 10,000)] of the PWS based on retail population as indicated by the Safe
Drinking Water Information System (Federal) (SDWIS/FED) as of the applicability date for the cycle:
• UCMR 5: February 1,2021
• UCMR 4: December 31, 2015
• UCMR 3: December 31, 2010
• UCMR 2: June 30, 2005
• UCMR 1: 20001
Facility Water Type
Source of water at the facility: SW (surface water), GW (ground water), GU (ground water under the direct
influence of surface water), MX (any combination of SW, GW, and GU). This field is blank for some UCMR 1
records where facility water type was not definitively known
Sample Point Type
Sampling Point Type Code: EP (entry point to the distribution system), SR (source water - untreated water),
DS (distribution system), LD (location in the distribution system where the disinfectant residual is the lowest
which is approved by the state for THM [DBP] and/or total coliform sampling), MD (midpoint in the
distribution system where the chlorine residual would be expected to be typical for the system such as the
location for sampling coliform indicator bacteria as described in 40 CFR 141.21), MR (point of maximum
retention is the point located the furthest from the entry point to the distribution system that is approved by
the state for trihalomethane [THM] (disinfectant byproducts [DBP]) and/or total coliform sampling), UK (not
definitively known)
EPA Region
EPA Region (states): Region 1 (CT, ME, MA, NH, Rl, VT), Region 2 (NJ, NY, PR [Puerto Rico], VI [Virgin Islands]),
Region 3 (DE, DC, MD, PA, VA, WV), Region 4 (AL, FL, GA, KY, MS, NC, SC, TN), Region 5 (IL, IN, Ml, MN, OH,
Wl), Region 6 (AR, LA, NM, OK, TX), Region 7 (IA, KS, MO, NE), Region 8 (CO, MT, ND, SD, UT, WY), Region 9
(AZ, CA, HI, NV, AS [American Samoa], GU [Guam], MP [Northern Marianas Islands], NN [Navajo Nation]),
Region 10 (AK, ID, OR, WA)
State
State abbreviation. Tribal PWSs without primacy are attributed to an EPA Region (01, 02, 04, 05, 06, 07, 08,
09,10)
Monitoring
Requirement
AM (Assessment Monitoring), SS (Screening Survey), PST (Pre-Screen Testing)
Minimum Reporting
Level (MRL)
Minimum Reporting Level defined by UCMR in |ag/L for the chemical contaminants, GC/L or MPN/L for the
viruses, and CFU/lOOmL for the microbial contaminants. Based on laboratory capability; not related to
contaminant health effects information
UCMR1 Sample
Type
RFS (raw field sample), RDS (raw duplicate field sample), TFS (treated field sample), TDS (treated duplicate
field sample) (UCMR 1 only, blank for some records; null for UCMR 2-4)
CASRN
Chemical Abstracts Service Registry Number (CASRN) is a unique identifier assigned by the Chemical Abstracts
Service (a division of the American Chemical Society) to every chemical substance (organic and inorganic
compounds, polymers elements, nuclear particles, etc.) in the open scientific literature. It contains up to 10
digits, separated by hyphens into three parts
DTXSID
Distributed Structure-Searchable Toxicity Substance Identifier (DTXSID) is a unique substance identifier used
in the EPA's CompTox Chemicals database, where a substance can be any single chemical, mixture, or polymer
1 A fixed PWS applicability date was not codified for UCMR 1, unlike the subsequent UCMR cycles, resulting in some revisions to
population served that occurred during the UCMR 1 monitoring period.
Page 8 of 8
------- |