Pensacola Bay FL - Simple workflow

Standardize, clean and wrangle Water Quality Portal data in Pensacola and Perdido Bays into more analytic-ready formats using the harmonize_wq package

US EPA’s Water Quality Portal (WQP) aggregates water quality, biological, and physical data provided by many organizations and has become an essential resource with tools to query and retrieval data using python or R. Given the variety of data and variety of data originators, using the data in analysis often requires data cleaning to ensure it meets the required quality standards and data wrangling to get it in a more analytic-ready format. Recognizing the definition of analysis-ready varies depending on the analysis, the harmonixe_wq package is intended to be a flexible water quality specific framework to help:

Identify differences in data units (including speciation and basis)
Identify differences in sampling or analytic methods
Resolve data errors using transparent assumptions
Reduce data to the columns that are most commonly needed
Transform data from long to wide format

Domain experts must decide what data meets their quality standards for data comparability and any thresholds for acceptance or rejection.

Simple workflow (Temperature and Secchi Disk Depth only)

This example steps through a typical workflow in Pensacola and Perdido Bays to demonstrate commonly used functionality

Install the required libraries

[1]:

import sys
#!python -m pip uninstall harmonize-wq --yes
# Use pip to install the package from pypi or the latest from github
#!{sys.executable} -m pip install harmonize-wq
# For latest dev version
#!{sys.executable} -m pip install git+https://github.com/USEPA/harmonize-wq.git@new_release_0-3-8

dataretrieval Query for a geojson

[2]:

import dataretrieval.wqp as wqp
from harmonize_wq import wrangle

[3]:

# Read geometry for Area of Interest from geojson file url
# NOTE: alternatively you can direct it to a local shapefile
aoi_url = r'https://raw.githubusercontent.com/USEPA/harmonize-wq/main/harmonize_wq/tests/data/PPBays_NCCA.geojson'

[4]:

# Map aoi (geojson is WGS1984 standard)
wrangle.as_gdf(aoi_url).plot()

[4]:

<Axes: >

../_images/notebooks_Harmonize_Pensacola_Simple_10_1.png

[5]:

# Build query
query = {'characteristicName': ['Temperature, water',
                                'Depth, Secchi disk depth',
                                ]}
query['bBox'] = wrangle.get_bounding_box(aoi_url)
query['dataProfile'] = 'narrowResult'

[6]:

# Run query
res_narrow, md_narrow = wqp.get_results(**query)

# dataframe of downloaded results
res_narrow

[6]:

	OrganizationIdentifier	OrganizationFormalName	ActivityIdentifier	ActivityStartDate	ActivityStartTime/Time	ActivityStartTime/TimeZoneCode	MonitoringLocationIdentifier	ResultIdentifier	DataLoggerLine	ResultDetectionConditionText	...	ResultDetectionQuantitationLimitUrl	LaboratoryAccreditationIndicator	LaboratoryAccreditationAuthorityName	TaxonomistAccreditationIndicator	TaxonomistAccreditationAuthorityName	LabSamplePreparationUrl	ProviderName	ActivityStartDateTime	AnalysisStartDateTime	AnalysisEndDateTime
0	USGS-FL	USGS Florida Water Science Center	nwisfl.01.95800571	1958-01-14	07:30:00	EST	USGS-02376100	NWIS-6891366	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NWIS	1958-01-14 12:30:00+00:00	NaT	NaT
1	USGS-FL	USGS Florida Water Science Center	nwisfl.01.95800572	1958-01-14	09:20:00	CST	USGS-02376108	NWIS-6891392	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NWIS	1958-01-14 15:20:00+00:00	NaT	NaT
2	USGS-FL	USGS Florida Water Science Center	nwisfl.01.95900797	1958-10-02	14:20:00	CST	USGS-02376700	NWIS-6916201	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NWIS	1958-10-02 20:20:00+00:00	NaT	NaT
3	USGS-FL	USGS Florida Water Science Center	nwisfl.01.95900766	1958-10-02	15:30:00	EST	USGS-02376100	NWIS-6915883	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NWIS	1958-10-02 20:30:00+00:00	NaT	NaT
4	USGS-FL	USGS Florida Water Science Center	nwisfl.01.95900769	1958-10-02	15:00:00	CST	USGS-02376108	NWIS-6915958	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NWIS	1958-10-02 21:00:00+00:00	NaT	NaT
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
114517	11NPSWRD_WQX	National Park Service Water Resources Division	11NPSWRD_WQX-GUIS_FTPICKNS_1_19680803_1415868	1968-08-03	NaN	NaN	11NPSWRD_WQX-GUIS_FTPICKNS_1	STORET-742739960	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	STORET	NaT	NaT	NaT
114518	11NPSWRD_WQX	National Park Service Water Resources Division	11NPSWRD_WQX-GUIS_FTPICKNS_2_19680803_1415871	1968-08-03	NaN	NaN	11NPSWRD_WQX-GUIS_FTPICKNS_2	STORET-742739999	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	STORET	NaT	NaT	NaT
114519	11NPSWRD_WQX	National Park Service Water Resources Division	11NPSWRD_WQX-GUIS_FTPICKNS_2_19680803_1415875	1968-08-03	NaN	NaN	11NPSWRD_WQX-GUIS_FTPICKNS_2	STORET-742740011	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	STORET	NaT	NaT	NaT
114520	11NPSWRD_WQX	National Park Service Water Resources Division	11NPSWRD_WQX-GUIS_FTPICKNS_S_19680803_1415863	1968-08-03	NaN	NaN	11NPSWRD_WQX-GUIS_FTPICKNS_S	STORET-742740047	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	STORET	NaT	NaT	NaT
114521	11NPSWRD_WQX	National Park Service Water Resources Division	11NPSWRD_WQX-GUIS_FTPICKNS_2_19680803_1415872	1968-08-03	NaN	NaN	11NPSWRD_WQX-GUIS_FTPICKNS_2	STORET-742740002	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	STORET	NaT	NaT	NaT

114522 rows × 81 columns

Harmonize and clean all results

[7]:

from harmonize_wq import harmonize
from harmonize_wq import clean

[8]:

# Harmonize all results
df_harmonized = harmonize.harmonize_all(res_narrow, errors='raise')
df_harmonized

/opt/hostedtoolcache/Python/3.9.25/x64/lib/python3.9/site-packages/harmonize_wq/wq_data.py:158: FutureWarning: unique with argument that is not not a Series, Index, ExtensionArray, or np.ndarray is deprecated and will raise in a future version.
  for bad_meas in pandas.unique(bad_measures):
/opt/hostedtoolcache/Python/3.9.25/x64/lib/python3.9/site-packages/harmonize_wq/clean.py:360: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value 'ResultMeasureValue: "*Not Reported" result cannot be used' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.
  df_out.loc[mask & (df_out["QA_flag"].isna()), "QA_flag"] = flag
/opt/hostedtoolcache/Python/3.9.25/x64/lib/python3.9/site-packages/harmonize_wq/wq_data.py:663: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '[<Quantity(0.46, 'meter')> <Quantity(0.61, 'meter')>
 <Quantity(0.71, 'meter')> ... <Quantity(0.91, 'meter')>
 <Quantity(0.81, 'meter')> <Quantity(0.61, 'meter')>]' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.
  df_out.loc[m_mask, self.out_col] = convert_unit_series(**params)
/opt/hostedtoolcache/Python/3.9.25/x64/lib/python3.9/site-packages/harmonize_wq/wq_data.py:158: FutureWarning: unique with argument that is not not a Series, Index, ExtensionArray, or np.ndarray is deprecated and will raise in a future version.
  for bad_meas in pandas.unique(bad_measures):
/opt/hostedtoolcache/Python/3.9.25/x64/lib/python3.9/site-packages/harmonize_wq/wq_data.py:663: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '[<Quantity(13.9, 'degree_Celsius')> <Quantity(24.4, 'degree_Celsius')>
 <Quantity(21.1, 'degree_Celsius')> ... <Quantity(20.1, 'degree_Celsius')>
 <Quantity(28.6, 'degree_Celsius')> <Quantity(27.9, 'degree_Celsius')>]' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.
  df_out.loc[m_mask, self.out_col] = convert_unit_series(**params)

[8]:

	OrganizationIdentifier	OrganizationFormalName	ActivityIdentifier	ActivityStartDate	ActivityStartTime/Time	ActivityStartTime/TimeZoneCode	MonitoringLocationIdentifier	ResultIdentifier	DataLoggerLine	ResultDetectionConditionText	...	TaxonomistAccreditationIndicator	TaxonomistAccreditationAuthorityName	LabSamplePreparationUrl	ProviderName	ActivityStartDateTime	AnalysisStartDateTime	AnalysisEndDateTime	QA_flag	Secchi	Temperature
0	USGS-FL	USGS Florida Water Science Center	nwisfl.01.95800571	1958-01-14	07:30:00	EST	USGS-02376100	NWIS-6891366	NaN	NaN	...	NaN	NaN	NaN	NWIS	1958-01-14 12:30:00+00:00	NaT	NaT	NaN	NaN	13.9 degree_Celsius
1	USGS-FL	USGS Florida Water Science Center	nwisfl.01.95800572	1958-01-14	09:20:00	CST	USGS-02376108	NWIS-6891392	NaN	NaN	...	NaN	NaN	NaN	NWIS	1958-01-14 15:20:00+00:00	NaT	NaT	NaN	NaN	24.4 degree_Celsius
2	USGS-FL	USGS Florida Water Science Center	nwisfl.01.95900797	1958-10-02	14:20:00	CST	USGS-02376700	NWIS-6916201	NaN	NaN	...	NaN	NaN	NaN	NWIS	1958-10-02 20:20:00+00:00	NaT	NaT	NaN	NaN	21.1 degree_Celsius
3	USGS-FL	USGS Florida Water Science Center	nwisfl.01.95900766	1958-10-02	15:30:00	EST	USGS-02376100	NWIS-6915883	NaN	NaN	...	NaN	NaN	NaN	NWIS	1958-10-02 20:30:00+00:00	NaT	NaT	NaN	NaN	22.8 degree_Celsius
4	USGS-FL	USGS Florida Water Science Center	nwisfl.01.95900769	1958-10-02	15:00:00	CST	USGS-02376108	NWIS-6915958	NaN	NaN	...	NaN	NaN	NaN	NWIS	1958-10-02 21:00:00+00:00	NaT	NaT	NaN	NaN	32.8 degree_Celsius
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
114517	11NPSWRD_WQX	National Park Service Water Resources Division	11NPSWRD_WQX-GUIS_FTPICKNS_1_19680803_1415868	1968-08-03	NaN	NaN	11NPSWRD_WQX-GUIS_FTPICKNS_1	STORET-742739960	NaN	NaN	...	NaN	NaN	NaN	STORET	NaT	NaT	NaT	NaN	NaN	16.1 degree_Celsius
114518	11NPSWRD_WQX	National Park Service Water Resources Division	11NPSWRD_WQX-GUIS_FTPICKNS_2_19680803_1415871	1968-08-03	NaN	NaN	11NPSWRD_WQX-GUIS_FTPICKNS_2	STORET-742739999	NaN	NaN	...	NaN	NaN	NaN	STORET	NaT	NaT	NaT	NaN	NaN	29.1 degree_Celsius
114519	11NPSWRD_WQX	National Park Service Water Resources Division	11NPSWRD_WQX-GUIS_FTPICKNS_2_19680803_1415875	1968-08-03	NaN	NaN	11NPSWRD_WQX-GUIS_FTPICKNS_2	STORET-742740011	NaN	NaN	...	NaN	NaN	NaN	STORET	NaT	NaT	NaT	NaN	NaN	20.1 degree_Celsius
114520	11NPSWRD_WQX	National Park Service Water Resources Division	11NPSWRD_WQX-GUIS_FTPICKNS_S_19680803_1415863	1968-08-03	NaN	NaN	11NPSWRD_WQX-GUIS_FTPICKNS_S	STORET-742740047	NaN	NaN	...	NaN	NaN	NaN	STORET	NaT	NaT	NaT	NaN	NaN	28.6 degree_Celsius
114521	11NPSWRD_WQX	National Park Service Water Resources Division	11NPSWRD_WQX-GUIS_FTPICKNS_2_19680803_1415872	1968-08-03	NaN	NaN	11NPSWRD_WQX-GUIS_FTPICKNS_2	STORET-742740002	NaN	NaN	...	NaN	NaN	NaN	STORET	NaT	NaT	NaT	NaN	NaN	27.9 degree_Celsius

114522 rows × 84 columns

[9]:

# Clean up other columns of data
df_cleaned = clean.datetime(df_harmonized)  # datetime
df_cleaned = clean.harmonize_depth(df_cleaned)  # Sample depth
df_cleaned

[9]:

	OrganizationIdentifier	OrganizationFormalName	ActivityIdentifier	ActivityStartDate	ActivityStartTime/Time	ActivityStartTime/TimeZoneCode	MonitoringLocationIdentifier	ResultIdentifier	DataLoggerLine	ResultDetectionConditionText	...	LabSamplePreparationUrl	ProviderName	ActivityStartDateTime	AnalysisStartDateTime	AnalysisEndDateTime	QA_flag	Secchi	Temperature	Activity_datetime	Depth
0	USGS-FL	USGS Florida Water Science Center	nwisfl.01.95800571	1958-01-14	07:30:00	EST	USGS-02376100	NWIS-6891366	NaN	NaN	...	NaN	NWIS	1958-01-14 12:30:00+00:00	NaT	NaT	NaN	NaN	13.9 degree_Celsius	1958-01-14 12:30:00+00:00	NaN
1	USGS-FL	USGS Florida Water Science Center	nwisfl.01.95800572	1958-01-14	09:20:00	CST	USGS-02376108	NWIS-6891392	NaN	NaN	...	NaN	NWIS	1958-01-14 15:20:00+00:00	NaT	NaT	NaN	NaN	24.4 degree_Celsius	1958-01-14 15:20:00+00:00	NaN
2	USGS-FL	USGS Florida Water Science Center	nwisfl.01.95900797	1958-10-02	14:20:00	CST	USGS-02376700	NWIS-6916201	NaN	NaN	...	NaN	NWIS	1958-10-02 20:20:00+00:00	NaT	NaT	NaN	NaN	21.1 degree_Celsius	1958-10-02 20:20:00+00:00	NaN
3	USGS-FL	USGS Florida Water Science Center	nwisfl.01.95900766	1958-10-02	15:30:00	EST	USGS-02376100	NWIS-6915883	NaN	NaN	...	NaN	NWIS	1958-10-02 20:30:00+00:00	NaT	NaT	NaN	NaN	22.8 degree_Celsius	1958-10-02 20:30:00+00:00	NaN
4	USGS-FL	USGS Florida Water Science Center	nwisfl.01.95900769	1958-10-02	15:00:00	CST	USGS-02376108	NWIS-6915958	NaN	NaN	...	NaN	NWIS	1958-10-02 21:00:00+00:00	NaT	NaT	NaN	NaN	32.8 degree_Celsius	1958-10-02 21:00:00+00:00	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
114517	11NPSWRD_WQX	National Park Service Water Resources Division	11NPSWRD_WQX-GUIS_FTPICKNS_1_19680803_1415868	1968-08-03	NaN	NaN	11NPSWRD_WQX-GUIS_FTPICKNS_1	STORET-742739960	NaN	NaN	...	NaN	STORET	NaT	NaT	NaT	NaN	NaN	16.1 degree_Celsius	NaT	NaN
114518	11NPSWRD_WQX	National Park Service Water Resources Division	11NPSWRD_WQX-GUIS_FTPICKNS_2_19680803_1415871	1968-08-03	NaN	NaN	11NPSWRD_WQX-GUIS_FTPICKNS_2	STORET-742739999	NaN	NaN	...	NaN	STORET	NaT	NaT	NaT	NaN	NaN	29.1 degree_Celsius	NaT	NaN
114519	11NPSWRD_WQX	National Park Service Water Resources Division	11NPSWRD_WQX-GUIS_FTPICKNS_2_19680803_1415875	1968-08-03	NaN	NaN	11NPSWRD_WQX-GUIS_FTPICKNS_2	STORET-742740011	NaN	NaN	...	NaN	STORET	NaT	NaT	NaT	NaN	NaN	20.1 degree_Celsius	NaT	NaN
114520	11NPSWRD_WQX	National Park Service Water Resources Division	11NPSWRD_WQX-GUIS_FTPICKNS_S_19680803_1415863	1968-08-03	NaN	NaN	11NPSWRD_WQX-GUIS_FTPICKNS_S	STORET-742740047	NaN	NaN	...	NaN	STORET	NaT	NaT	NaT	NaN	NaN	28.6 degree_Celsius	NaT	NaN
114521	11NPSWRD_WQX	National Park Service Water Resources Division	11NPSWRD_WQX-GUIS_FTPICKNS_2_19680803_1415872	1968-08-03	NaN	NaN	11NPSWRD_WQX-GUIS_FTPICKNS_2	STORET-742740002	NaN	NaN	...	NaN	STORET	NaT	NaT	NaT	NaN	NaN	27.9 degree_Celsius	NaT	NaN

114522 rows × 86 columns

Transform results from long to wide format

There are many columns in the dataframe that are characteristic specific, that is they have different values for the same sample depending on the characteristic. To ensure one result for each sample after the transformation of the data these columns must either be split, generating a new column for each characteristic with values, or moved out from the table if not being used.

[10]:

from harmonize_wq import wrangle

# Split QA column into multiple characteristic specific QA columns
df_full = wrangle.split_col(df_cleaned)

# Divide table into columns of interest (main_df) and characteristic specific metadata (chars_df)
main_df, chars_df = wrangle.split_table(df_full)

# Combine rows with the same sample organization, activity, location, and datetime
df_wide = wrangle.collapse_results(main_df)

# Reduced columns
df_wide.columns

[10]:

Index(['OrganizationFormalName', 'ProviderName', 'ActivityStartDateTime',
       'AnalysisStartDateTime', 'AnalysisEndDateTime', 'Secchi', 'Temperature',
       'Depth', 'QA_Secchi', 'QA_Temperature'],
      dtype='object')

Results are collapsed by retaining the first result that isn’t NAN. There can be several reasons for multiple results for the same parameter/characteristic sampled at the same station, time and by the same organization. The collapse_results function assumes the user has already reviewed the quality of all results and narrowed down instances of multiple results to only the desired/best/highest quality result before running this function.

Map results

[11]:

from harmonize_wq import location
from harmonize_wq import visualize

# Get harmonized stations clipped to the Area of Interest
stations_gdf, stations, site_md = location.get_harmonized_stations(query, aoi=aoi_url)

/opt/hostedtoolcache/Python/3.9.25/x64/lib/python3.9/site-packages/harmonize_wq/clean.py:356: FutureWarning: Logical ops (and, or, xor) between Pandas objects and dtype-less sequences (e.g. list, tuple) are deprecated and will raise in a future version. Wrap the object in a Series, Index, or np.array before operating instead.
  cond_notna = mask & (df_out["QA_flag"].notna())  # Mask cond and not NA
/opt/hostedtoolcache/Python/3.9.25/x64/lib/python3.9/site-packages/harmonize_wq/clean.py:360: FutureWarning: Logical ops (and, or, xor) between Pandas objects and dtype-less sequences (e.g. list, tuple) are deprecated and will raise in a future version. Wrap the object in a Series, Index, or np.array before operating instead.
  df_out.loc[mask & (df_out["QA_flag"].isna()), "QA_flag"] = flag
/opt/hostedtoolcache/Python/3.9.25/x64/lib/python3.9/site-packages/harmonize_wq/clean.py:360: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value 'LatitudeMeasure: Imprecise: lessthan3decimaldigits' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.
  df_out.loc[mask & (df_out["QA_flag"].isna()), "QA_flag"] = flag
/opt/hostedtoolcache/Python/3.9.25/x64/lib/python3.9/site-packages/harmonize_wq/clean.py:356: FutureWarning: Logical ops (and, or, xor) between Pandas objects and dtype-less sequences (e.g. list, tuple) are deprecated and will raise in a future version. Wrap the object in a Series, Index, or np.array before operating instead.
  cond_notna = mask & (df_out["QA_flag"].notna())  # Mask cond and not NA
/opt/hostedtoolcache/Python/3.9.25/x64/lib/python3.9/site-packages/harmonize_wq/clean.py:360: FutureWarning: Logical ops (and, or, xor) between Pandas objects and dtype-less sequences (e.g. list, tuple) are deprecated and will raise in a future version. Wrap the object in a Series, Index, or np.array before operating instead.
  df_out.loc[mask & (df_out["QA_flag"].isna()), "QA_flag"] = flag

[12]:

# Map average temperature results at each station
gdf_temperature = visualize.map_measure(df_wide, stations_gdf, 'Temperature')
gdf_temperature.plot(column='mean', cmap='OrRd', legend=True)

[12]:

<Axes: >

../_images/notebooks_Harmonize_Pensacola_Simple_23_1.png