TADA Module 1: Water Quality Portal Data Discovery and Cleaning
TADA Team
2026-08-11
Source:vignettes/TADAModule1.Rmd
TADAModule1.RmdOverview
This vignette walks through how to use the TADA R Package to discover and clean (i.e., wrangle, Quality Assure and Quality Control (QAQC), and harmonize) Water Quality Portal (WQP) data from multiple organizations.
Install and Load the EPATADA R Package
First, install and load the remotes package specifying the repo. This is needed before installing EPATADA because it is only available on GitHub (not CRAN).
install.packages("remotes")
# Load the remotes library
library(remotes)Next, install and load TADA using the remotes package. TADA R Package dependencies will also be downloaded automatically from CRAN with the TADA install. You may be prompted in the console to update dependency packages that have more recent versions available. If you see this prompt, it is recommended to update all of them (enter 1 into the console).
remotes::install_github("USEPA/EPATADA",
ref = "develop",
dependencies = TRUE
)Finally, use the library() function to load the TADA R Package into your R session.
TADA_DataRetrieval
WQP data is retrieved and processed for compatibility with TADA. This function, TADA_DataRetrieval, builds on USGS’s dataRetrieval R package functions. It joins three WQP profiles: Site, Sample Results (physical/chemical metadata), and Project. In addition, it changes all data in the Characteristic, Speciation, Fraction, and Unit fields to uppercase and addresses result values that include special characters.
This function accepts the same inputs as the dataRetrieval
readWQPdata function. readWQPdata does not
restrict the characteristics pulled from Water Quality Portal
(WQP).
Data retrieval filters include:
startDate
endDate
characteristicName
sampleMedia
siteType
statecode (see list of possible state and territory abbreviations here)
countycode
siteid
organization
project
huc
characteristicType
providers
In addition to these filters, TADA_DataRetrieval accepts additional
geospatial-related filters that are not included in the dataRetrieval
readWQPdata function:
aoi_sf
tribal_area_type
tribe_name_parcel (Note: The TADA_TribalOptions function can be used to narrow down options for use with this tribe_name_parcel filter option. See ?TADA_TribalOptions for more info).
After data is downloaded using the filters above, the default TADA_DataRetrieval function also automatically runs the TADA_AutoClean function. If desired, users can set applyautoclean = FALSE in their TADA_DataRetrieval calls. In this example, we will set applyautoclean = FALSE and run it as a separate step in the workflow.
Tips:
-
All the query filters for the WQP work as an AND but within the fields there are ORs. For example:
Characteristics: If you choose pH & DO - it’s an OR. This means you will retrieve both pH OR DO data if available.
States: Similarly, if you choose VA and IL, it’s an OR. This means you will retrieve both VA OR IL data if available.
Combinations of fields are ANDs, such as State/VA AND Characteristic/DO”. This means you will receive all DO data available in VA.
“Characteristic” and “Characteristic Type” also work as an AND. This means that the Characteristic must fall within the CharacteristicGroup if both filters are being used, if not you will get an error.
The “siteid” is a general term WQP uses to describe both Site IDs from USGS databases and Monitoring Location Identifiers (from WQX). Each monitoring location in the Water Quality Portal (WQP) has a unique Monitoring Location Identifier, regardless of the database from which it derives. The Monitoring Location Identifier from the WQP is the concatenated Organization Identifier plus the Site ID number. Site IDs that only include a number are only unique identifiers for monitoring locations within USGS NWIS or EPA’s WQX databases separately.
The aoi_sf and tribal arguments are meant to be used on their own. For example, if both an aoi_sf argument and tribal information are provided an error is returned because it’s unclear what the priority location should be for the query. Similarly, aoi_sf and tribal_area_type are not meant to be used with location-related filters (e.g., statecode, siteid). In these instances a warning is returned but the query proceeds by using only the aoi_sf or tribal_area_type information.
Additional resources:
Review function documentation by entering the following code into the console: ?TADA_DataRetrieval
Use the code below to download data from the WQP using TADA_DataRetrieval. Edit the code chunk below to define your own WQP query inputs.
Downloads using TADA_DataRetrieval will have the same columns each time, but be aware that data are uploaded to the Water Quality Portal by individual organizations, which may or may not follow the same conventions. Data and metadata quality are not guaranteed! Carefully explore data to make sure it meets your quality assurance requirements.
Note: TADA_DataRetrieval (by leveraging dataRetrieval), automatically converts the date times to UTC. It also automatically converts field formats to dates, datetimes, and numerics based on a standard algorithm.
Enter ?TADA_DataRetrieval into the console to review example queries and additional information.
This example includes monitoring data collected from Jan 2018 to Jan 2019 by six organizations: 1) Red Lake Band of Chippewa Indians, 2) Sac & Fox Nation, 3) Pueblo of Pojoaque, 4) Minnesota Chippewa Tribe (Fond du Lac Band), 5) Pueblo of Tesuque, and 6) The Chickasaw Nation.
TADAProfile <- TADA_DataRetrieval(organization = c("REDLAKE_WQX", "SFNOES_WQX", "PUEBLO_POJOAQUE", "FONDULAC_WQX", "PUEBLOOFTESUQUE", "CNENVSER"), startDate = "2018-01-01", endDate = "2019-01-01", applyautoclean = FALSE, ask = FALSE)We will move forward with this example in the remainder of the vignette.
We will first use a subset of this example to demonstrate using new TADA_DataRetrieval options that allow for spatial or tribe-specific queries:
Focusing just on the “PUEBLO_POJOAQUE” organization, rerun the example above:
TADAProfile_single <- TADA_DataRetrieval(
organization = "PUEBLO_POJOAQUE",
startDate = "2018-01-01",
endDate = "2019-01-01",
applyautoclean = FALSE,
ask = FALSE
)The same results can now be obtained using a combination of the tribal_area_type and tribe_name_parcel arguments. Both must be used together. The tribal_area_type argument indicates which one of four layer datasets (“Alaska Native Allotments”, “American Indian Reservations”, “Off-reservation Trust Lands”, or “Oklahoma Tribal Statistical Areas”) of tribal land data to query within. Note that “Alaska Native Villages” and “Virginia Federally Recognized Tribes” layers will not return a successful query. These four tribal_area_type layer options include multiple tribes. Therefore, tribe_name_parcel is where users can enter the specific name of the tribal land of interest as listed in the layer. In this example for Pueblo of Pojoaque, running TADA_TribalOptions(“American Indian Reservations”) could be used here to determine the correct spelling for this argument, “Pueblo of Pojoaque, New Mexico”, as listed in the TRIBE_NAME column.
# Review TRIBE_NAME column to get name format for the TADA_DataRetrieval tribe_name_parcel function input
TRIBE_NAME <- TADA_TribalOptions("American Indian Reservations")
TADAProfile_tribal <- TADA_DataRetrieval(
tribal_area_type = "American Indian Reservations",
tribe_name_parcel = "Pueblo of Pojoaque, New Mexico",
startDate = "2018-01-01",
endDate = "2019-01-01",
applyautoclean = FALSE,
ask = FALSE
)
# They are equivalent:
all.equal(data.frame(TADAProfile_single), data.frame(TADAProfile_tribal))Additionally, the aoi_sf argument can be used to provide an sf spatial object as a query filter. We can match the output of the two short Pueblo of Pojoaque examples above, using tigris::native_areas to acquire Census Bureau spatial data:
TADAProfile_spatial <- TADA_DataRetrieval(
aoi_sf = tigris::native_areas() |> dplyr::filter(NAMELSAD == "Pueblo of Pojoaque"),
startDate = "2018-01-01",
endDate = "2019-01-01",
applyautoclean = FALSE,
ask = FALSE
)
all.equal(data.frame(TADAProfile_single), data.frame(TADAProfile_spatial))Note: In this example the output data is identical from these three input methods. However, in some instances this may not be the case. This is because the tribal_area_type method is based on spatial data and so spatial boundaries must be taken into account when comparing query results. The same applies when using aoi_sf results.
Let’s repeat this process for Red Lake Band of Chippewa Indians. In this case, we will get additional observations from other organizations who are sampling with the tribal boundary. This is one great benefit of this query option! There is additional data available that may be useful but is missed if only the organization query filter is used.
TADAProfile_single_2 <- TADA_DataRetrieval(
organization = "REDLAKE_WQX",
startDate = "2018-01-01",
endDate = "2019-01-01",
applyautoclean = FALSE,
ask = FALSE
)
TADAProfile_tribal_2 <- TADA_DataRetrieval(
tribal_area_type = "American Indian Reservations",
tribe_name_parcel = "Red Lake Band of Chippewa Indians, Minnesota",
startDate = "2018-01-01",
endDate = "2019-01-01",
applyautoclean = FALSE,
ask = FALSE
)
# Review unique organizations
unique(TADAProfile_single_2$OrganizationFormalName)
unique(TADAProfile_tribal_2$OrganizationFormalName)USGS dataRetrieval
Uncomment below (optional) if you would like to review differences between the profiles you would get using USGS’s readWQPdata vs. EPA’s TADA_DataRetrieval (compare dataRetrieval_example to TADAProfile). The profiles are different because TADA_DataRetrieval automatically joins in data from multiple WQP profiles, and does some additional data cleaning as part of the data retrieval process.
# dataRetrieval_example <- dataRetrieval::readWQPdata(organization = c("REDLAKE_WQX", "SFNOES_WQX", "PUEBLO_POJOAQUE", "FONDULAC_WQX", "PUEBLOOFTESUQUE", "CNENVSER"), startDate = "2018-01-01", endDate = "2019-01-01", ignore_attributes = TRUE)Big Data Queries
If you need to download a large amount of data from across a large area, the TADA_DataRetrieval function now handles this automatically. Whereas in the past there was a second function (TADA_BigDataRetrieval) to do this, the standard TADA_DataRetrieval function now checks the number of results in each query and uses similar methods as TADA_BigDataRetrieval when necessary.
The function does multiple synchronous data calls to the WQP (waterqualitydata.us). It uses the WQP summary service to limit the sites downloaded to only those with relevant data. It pulls back data from set number of stations at a time and then joins the data back together to produce a single TADA compatible dataframe as the output.
Users can leverage the new maxrecs function input for TADA_DataRetrieval to specify the maximum number of records to query at once (i.e., without breaking into smaller queries). The default is 250000 records.
TADA_DataRetrieval now also prompts the user (when ask = TRUE) to confirm that they want to download the dataset. As part of this prompt the expected number of rows of data are provided to help in making the decision. As the downloads occur, a progress bar is shown as well.
See ?TADA_DataRetrieval for more details. WARNING, some of the examples below can take multiple HOURS to run. The total run time depends on your query inputs.
# AK_AL_WaterTemp <- TADA_DataRetrieval(startDate = "2000-01-01", endDate = "2022-12-31", characteristicName = "Temperature, water", statecode = c("AK","AL"))
#
# AllWaterTemp <- TADA_DataRetrieval(characteristicName = "Temperature, water")
#
# AllPhosphorus <- TADA_DataRetrieval(characteristicName = "Phosphorus")
#
# AllCT <- TADA_DataRetrieval(statecode = "CT")Filter data based on media type
Some TADA users are interested in using WQP data for surface water only or for analysis of some non-water data. The TADA_MediaFilter function can assist in identifying results of interest. Multiple columns are used to identify groundwater results as different organizations may populate different combinations of fields in order to identify a result as groundwater.
This function identifies surface water, groundwater, and sediment results. Users can specify whether all results should be returned with a new column, TADA.Media.Flag, identifying if the result should be included in further analysis or if only results that should be in included are returned.
The defaults are to include surface water, exclude groundwater and sediment, and to return only the results that should be used for analysis (clean = TRUE). This is shown in the active example below. If you would like to see all results with the TADA.Media.Flag column, you can uncomment the example where clean = FALSE.
If you are not interested in using TADA_MediaFilter, but would like to filter by activity media, uncomment the example to filter for water data only by using dplyr::filter() with TADA.ActivityMediaName.
# Filter to retain only results for use in analysis
TADAProfile <- TADA_MediaFilter(TADAProfile,
clean = TRUE,
surface_water = FALSE,
ground_water = TRUE,
sediment = TRUE,
other = TRUE
)
# Add TADA.Media.Flag column to identify which results should be used for analysis
# TADAProfile <- TADA_MediaFilter(TADAProfile, clean = FALSE)
# Remove data for non-water media types, alternate workflow without using TADA_MediaFilter()
# TADAProfile <- dplyr::filter(TADAProfile, TADA.ActivityMediaName == "WATER")TADA_AutoClean
Now TADA_AutoClean can be run on a smaller dataset after unnecessary results have been removed. It performs the following functions on the data retrieved from the WQP:
TADA_ConvertSpecialChars - converts result value columns to numeric and flags non-numeric values that could not be converted.
TADA_ConvertResultUnits - unifies result units for easier quality control and review
TADA_ConvertDepthUnits - converts depth units to a consistent unit (meters).
TADA_IDCensoredData - categorizes detection limit data and identifies mismatches in result detection condition and result detection limit type.
Other helpful actions - converts important text columns to all upper-case letters, removes exact duplicates, and uses WQX format rules to harmonize specific NWIS metadata conventions (e.g. move characteristic speciation from the TADA.ResultMeasure.MeasureUnitCode column to the TADA.MethodSpeciationName column)
As a general rule, TADA functions do not change any contents in the WQP-served columns. Instead, they add new columns with the prefix “TADA.” The following columns are numeric versions of their WQP origins:
- TADA.ResultMeasureValue
- TADA.DetectionQuantitationLimitMeasure.MeasureValue
- TADA.LatitudeMeasure
- TADA.LongitudeMeasureThese functions also add the columns TADA.ResultMeasureValueDataTypes.Flag and TADA.DetectionQuantitationLimitMeasure.MeasureValueDataTypes.Flag, which provide information about the result values that is needed to address censored data later on (i.e., nondetections). Specifically, these new columns flag if special characters are included in result values, and specifies what the special characters are.
# run TADA_AutoClean on filtered dataset to convert special characters, result units, and depth units and identify censored data.
TADAProfile <- TADA_AutoClean(TADAProfile)Review all column names in the TADA Profile to familiarize yourself with the dataset after TADA_AutoClean has added additional TADA prefixed columns. TADA_SummarizeColumn summarizes the data set based on the user specified column and returns a dataframe displaying the number of sites and number of records for each unique value in the specified column. The example below uses TADA.CharacteristicName.
# View column names for TADAProfile
colnames(TADAProfile)## [1] "ResultIdentifier"
## [2] "ActivityTypeCode"
## [3] "ActivityMediaName"
## [4] "TADA.ActivityMediaName"
## [5] "ActivityMediaSubdivisionName"
## [6] "CountryCode"
## [7] "StateCode"
## [8] "CountyCode"
## [9] "MonitoringLocationName"
## [10] "TADA.MonitoringLocationName"
## [11] "MonitoringLocationTypeName"
## [12] "TADA.MonitoringLocationTypeName"
## [13] "MonitoringLocationDescriptionText"
## [14] "LatitudeMeasure"
## [15] "TADA.LatitudeMeasure"
## [16] "LongitudeMeasure"
## [17] "TADA.LongitudeMeasure"
## [18] "HorizontalCoordinateReferenceSystemDatumName"
## [19] "HUCEightDigitCode"
## [20] "MonitoringLocationIdentifier"
## [21] "TADA.MonitoringLocationIdentifier"
## [22] "ResultSampleFractionText"
## [23] "TADA.ResultSampleFractionText"
## [24] "CharacteristicName"
## [25] "TADA.CharacteristicName"
## [26] "SubjectTaxonomicName"
## [27] "MethodSpeciationName"
## [28] "TADA.MethodSpeciationName"
## [29] "TADA.ComparableDataIdentifier"
## [30] "ActivityStartDate"
## [31] "ActivityStartTime.Time"
## [32] "ActivityStartTime.TimeZoneCode"
## [33] "ActivityStartDateTime"
## [34] "ResultMeasureValue"
## [35] "ResultMeasure.MeasureUnitCode"
## [36] "TADA.ResultMeasureValue"
## [37] "TADA.ResultMeasure.MeasureUnitCode"
## [38] "TADA.WQXResultUnitConversion"
## [39] "ResultValueTypeName"
## [40] "TADA.ResultMeasureValueDataTypes.Flag"
## [41] "ResultDetectionConditionText"
## [42] "DetectionQuantitationLimitTypeName"
## [43] "DetectionQuantitationLimitMeasure.MeasureValue"
## [44] "DetectionQuantitationLimitMeasure.MeasureUnitCode"
## [45] "TADA.DetectionQuantitationLimitMeasure.MeasureValue"
## [46] "TADA.DetectionQuantitationLimitMeasure.MeasureUnitCode"
## [47] "TADA.DetectionQuantitationLimitMeasure.MeasureValueDataTypes.Flag"
## [48] "ResultDepthHeightMeasure.MeasureValue"
## [49] "TADA.ResultDepthHeightMeasure.MeasureValue"
## [50] "TADA.ResultDepthHeightMeasure.MeasureValueDataTypes.Flag"
## [51] "ResultDepthHeightMeasure.MeasureUnitCode"
## [52] "TADA.ResultDepthHeightMeasure.MeasureUnitCode"
## [53] "ResultDepthAltitudeReferencePointText"
## [54] "ActivityRelativeDepthName"
## [55] "ActivityDepthHeightMeasure.MeasureValue"
## [56] "TADA.ActivityDepthHeightMeasure.MeasureValue"
## [57] "TADA.ActivityDepthHeightMeasure.MeasureValueDataTypes.Flag"
## [58] "ActivityDepthHeightMeasure.MeasureUnitCode"
## [59] "TADA.ActivityDepthHeightMeasure.MeasureUnitCode"
## [60] "ActivityTopDepthHeightMeasure.MeasureValue"
## [61] "TADA.ActivityTopDepthHeightMeasure.MeasureValue"
## [62] "TADA.ActivityTopDepthHeightMeasure.MeasureValueDataTypes.Flag"
## [63] "ActivityTopDepthHeightMeasure.MeasureUnitCode"
## [64] "TADA.ActivityTopDepthHeightMeasure.MeasureUnitCode"
## [65] "ActivityBottomDepthHeightMeasure.MeasureValue"
## [66] "TADA.ActivityBottomDepthHeightMeasure.MeasureValue"
## [67] "TADA.ActivityBottomDepthHeightMeasure.MeasureValueDataTypes.Flag"
## [68] "ActivityBottomDepthHeightMeasure.MeasureUnitCode"
## [69] "TADA.ActivityBottomDepthHeightMeasure.MeasureUnitCode"
## [70] "ResultTimeBasisText"
## [71] "StatisticalBaseCode"
## [72] "ResultFileUrl"
## [73] "ResultAnalyticalMethod.MethodName"
## [74] "ResultAnalyticalMethod.MethodDescriptionText"
## [75] "ResultAnalyticalMethod.MethodIdentifier"
## [76] "ResultAnalyticalMethod.MethodIdentifierContext"
## [77] "ResultAnalyticalMethod.MethodUrl"
## [78] "SampleCollectionMethod.MethodIdentifier"
## [79] "SampleCollectionMethod.MethodIdentifierContext"
## [80] "SampleCollectionMethod.MethodName"
## [81] "SampleCollectionMethod.MethodDescriptionText"
## [82] "SampleCollectionEquipmentName"
## [83] "MeasureQualifierCode"
## [84] "ResultStatusIdentifier"
## [85] "ResultCommentText"
## [86] "ActivityCommentText"
## [87] "HydrologicCondition"
## [88] "HydrologicEvent"
## [89] "DataQuality.PrecisionValue"
## [90] "DataQuality.BiasValue"
## [91] "DataQuality.ConfidenceIntervalValue"
## [92] "DataQuality.UpperConfidenceLimitValue"
## [93] "DataQuality.LowerConfidenceLimitValue"
## [94] "SamplingDesignTypeCode"
## [95] "LaboratoryName"
## [96] "ResultLaboratoryCommentText"
## [97] "ActivityIdentifier"
## [98] "OrganizationIdentifier"
## [99] "OrganizationFormalName"
## [100] "ProjectName"
## [101] "ProjectDescriptionText"
## [102] "ProjectIdentifier"
## [103] "ProjectFileUrl"
## [104] "QAPPApprovedIndicator"
## [105] "QAPPApprovalAgencyName"
## [106] "AquiferName"
## [107] "AquiferTypeName"
## [108] "LocalAqfrName"
## [109] "ConstructionDateText"
## [110] "WellDepthMeasure.MeasureValue"
## [111] "WellDepthMeasure.MeasureUnitCode"
## [112] "WellHoleDepthMeasure.MeasureValue"
## [113] "WellHoleDepthMeasure.MeasureUnitCode"
## [114] "ProviderName"
## [115] "LastUpdated"
## [116] "ActivityDepthAltitudeReferencePointText"
## [117] "ActivityEndDate"
## [118] "ActivityEndTime.Time"
## [119] "ActivityEndTime.TimeZoneCode"
## [120] "ActivityEndDateTime"
## [121] "ActivityConductingOrganizationText"
## [122] "SampleAquifer"
## [123] "ActivityLocation.LatitudeMeasure"
## [124] "ActivityLocation.LongitudeMeasure"
## [125] "ResultWeightBasisText"
## [126] "ResultTemperatureBasisText"
## [127] "ResultParticleSizeBasisText"
## [128] "USGSPCode"
## [129] "BinaryObjectFileName"
## [130] "BinaryObjectFileTypeCode"
## [131] "AnalysisStartDate"
## [132] "ResultDetectionQuantitationLimitUrl"
## [133] "LabSamplePreparationUrl"
## [134] "ActivityStartTime.TimeZoneCode_offset"
## [135] "ActivityEndTime.TimeZoneCode_offset"
## [136] "SourceMapScaleNumeric"
## [137] "HorizontalAccuracyMeasure.MeasureValue"
## [138] "HorizontalAccuracyMeasure.MeasureUnitCode"
## [139] "HorizontalCollectionMethodName"
## [140] "VerticalMeasure.MeasureValue"
## [141] "VerticalMeasure.MeasureUnitCode"
## [142] "VerticalAccuracyMeasure.MeasureValue"
## [143] "VerticalAccuracyMeasure.MeasureUnitCode"
## [144] "VerticalCollectionMethodName"
## [145] "VerticalCoordinateReferenceSystemDatumName"
## [146] "FormationTypeText"
## [147] "ProjectMonitoringLocationWeightingUrl"
## [148] "DrainageAreaMeasure.MeasureValue"
## [149] "DrainageAreaMeasure.MeasureUnitCode"
## [150] "ContributingDrainageAreaMeasure.MeasureValue"
## [151] "ContributingDrainageAreaMeasure.MeasureUnitCode"
## [152] "SampleTissueAnatomyName"
# Review the number of sites and number of records for each CharacteristicName in TADAProfile
TADAProfile_CharSummary <- TADA_SummarizeColumn(TADAProfile, "TADA.CharacteristicName")
# View TADAProfile_CharSummary
TADAProfile_CharSummary## # A tibble: 104 × 3
## TADA.CharacteristicName n_sites n_records
## <chr> <int> <int>
## 1 .ALPHA.-ENDOSULFAN 6 7
## 2 .ALPHA.-HEXACHLOROCYCLOHEXANE 6 7
## 3 .BETA.-ENDOSULFAN 6 7
## 4 .BETA.-HEXACHLOROCYCLOHEXANE 6 7
## 5 .DELTA.-HEXACHLOROCYCLOHEXANE 6 7
## 6 ALDRIN 6 7
## 7 ALKALINITY, TOTAL 128 692
## 8 ALPHA PARTICLE 6 14
## 9 ALUMINUM 6 7
## 10 AMMONIA-NITROGEN 83 328
## # ℹ 94 more rowsInvalid coordinates
Review station locations and summary information using the TADA_OverviewMap function. TADA_OverviewMap counts the number of unique results, characteristics, and organizations at each monitoring location in the dataset and creates a tidy map for reviewing summary stats spatially. Larger point sizes indicate more results collected at a given site, while darker blue colors indicate more unique characteristics collected at the site. Users may click on a site to view a pop-up with this summary information, including the number of organizations that reported results at that site. This map may inform a user’s decision to remove/correct sites that are outside the US.
TADA_OverviewMap(TADAProfile)