Last Modified: 2018-08-10 22:38
Water quality data for San Diego county beaches from CEDEN, with added features for log transformation, quantiles and group codes.
This datasets rebuilds ceden.waterboards.ca.gov-beachwatch-sandiego with
constant and null columns removed and many features added. It also breaks out
station information into a seperate datasets, and enumerates the many
difference combinations of methodname/analyte/unit, adding a code for each
group to the dataset in measure_code. The measure code identifies sets of
records that have compatible measurements.
The dataset adds counts, mean, median and quantiles for groups of
station_code/measure_code. The dataset rows are grouped, for each station and
measure code, and mean, median and quantiles computed for each group. The procedure is performed both for result and for lresult, the log of results.
After computing the group summary statistics, the processing creates
dichotomous features for the relationship of result and lresult to the
summary value, including:
These variables are particularly useful for doing logistic regressions across the measure code groups or stations.
This datasets excludes the const and empty columns from the source dataset. These columns and their values are:
program BeachWatch
parentproject BeachWatch_San Diego County
project BeachWatch_San Diego County
locationcode SurfZone
collectiondepth -88
unitcollectiondepth NR
sampletypecode Grab
collectionreplicate 1
resultsreplicate 1
labsampleid Not Recorded
matrixname samplewater
mdl -88
rl -88
batchverification NR
compliancecode NR
eventcode WQ
protocolcode Not Recorded
collectionmethodname Water_Grab
collectiondevicedescription Not Recorded
calibrationdate 0000-00-00
positionwatercolumn Not Recorded
preppreservationname Not Recorded
preppreservationdate 0000-00-00 00:00:00
digestextractmethod Not Recorded
digestextractdate 0000-00-00
analysisdate 0000-00-00
dilutionfactor -88
expectedvalue 0
submissioncode NR
county San Diego
county_fips 73
regional_board San Diego
rb_number 9
sampleid Not Recorded
The dataset also excludes these Null columns:
The most prevalent measure code in this dataset is 24 for Enterococcus (analyte) meaured with Enterolert (methodname) in units of MPN/100 mL. This is probably because in 2004, the EPA changed recomendations to use Enterococcus as a primary indicator bacteria in coastal waters:
EPA subsequently recommended the use of E. coli or enterococci for fresh
recreational waters and enterococci for marine recreational waters because
levels of these organisms more accurately predict acute gastrointestinal
illness than levels of fecal coliforms.
Measurement stations
| Header | Datatype | Description |
|---|---|---|
| stationname | string | Full name of the station |
| stationcode | string | Short name for the station |
| stationgroup | string | First two characters of station code |
| targetlatitude | number | Station lattitude |
| targetlongitude | number | Station longitude |
Measurement group codes
| Header | Datatype | Description |
|---|---|---|
| analyte | string | The type of bacterial being measured, for coliform, total coliform, Enterococcus or E. coli. |
| methodname | string | Method used to count bacteria, such as “MTF”, “SM 9222 B” or “Enterolert” |
| unit | string | Measurement units, type of count per 100 ml. |
| measure_code | integer | The number of the unique group of analyte, mehodname and unit. |
Beachwatch data, with features added
| Header | Datatype | Description |
|---|---|---|
| stationcode | string | Short name for the station |
| stationgroup | string | First two characters of station code |
| sampledate | datetime | Date sample was taken |
| collectiontime | time | Time sample was taken |
| measure_code | integer | Reference to the measure codes, the unique groups of analyte, mehodname and unit. |
| analyte | string | The type of bacterial being measured, for coliform, total coliform, Enterococcus or E. coli. |
| methodname | string | Method used to count bacteria, such as “MTF”, “SM 9222 B” or “Enterolert” |
| unit | string | Measurement units, type of count per 100 ml. |
| result | integer | Measured value |
| result_group_count | integer | Number of records in this measurement group |
| result_group_std | number | Standard deviation for this group |
| result_group_mean | number | Mean for this group |
| result_group_25pctl | number | 25th percentile for this group |
| result_group_median | number | Median for this group |
| result_group_75pctl | number | 75th percentile for this group |
| result_gt_median | number | 1 if the result is greater than the median, 0 otherwise |
| result_gt_mean | number | 1 if the result is greater than the mean, 0 otherwise |
| result_lte_25pctl | number | 1 if the result is less than or equal to the 25th percentile, 0 otherwise |
| result_gte_75pctl | number | 1 if the result is greater than or equal to the 75th percentile, 0 otherwise |
| lresult | number | Log of measured value |
| lresult_group_std | number | Standard deviation of log(result) for this group |
| lresult_group_mean | number | Mean of log(result) for this group |
| lresult_group_25pctl | number | 25th percentile of log(result) for this group |
| lresult_group_median | number | Median of log(result) for this group |
| lresult_group_75pctl | number | 75th percentile of log(result) for this group |
| lresult_gt_lmedian | number | 1 if the result is greater than the median of log(result) , 0 otherwise |
| lresult_gt_lmean | number | 1 if the result is greater than the mean of log(result) , 0 otherwise |
| lresult_lte_25pctl | number | 1 if the result is less than or equal to the 25th percentile of log(result) , 0 otherwise |
| lresult_gte_75pctl | number | 1 if the result is greater than or equal to the 75th percentile of log(result) , 0 otherwise |
| labbatch | string | Identifier of the processing group in the lab |
| resultqualcode | string | Result Qualifier code. See result_qual_cv documentation |
| qacode | string | QA code. See qa_cv documentation |
| sampleagency | string | Code for agency that took the sample |
| labagency | string | Code for agency that analyzed sample |
| submittingagency | string | Code for agency that submitted sample to lab |