Hotspot Planning 2/25/2015

Some summaries of past hotspot activity to aid in planning 2015 surveys.

First, load water quality data.

## Loading wq database: \\psf\Home\Dropbox\MysticDB\MysticDB_20150224.accdb
## Fetching tables...done
## Merging tables...done
## Excluding field blanks, duplicates...done
## Excluding flagged results...done

## Loading precip file: \\psf\Home\Dropbox\MysticDB\Processed\Precip\LoganPrecip.xlsx

## Computing antecedent precip...done
## Computing DateHour column in wq dataframe...done
## Merging wq and precip...done

Narrow to Hotspot bacteria data.

# filter for hotspot data only 
hot <- tbl_df(wq2)  %>%
  filter(ProjectID == "HOTSPOT") %>%
  filter(CharacteristicID == "ECOLI" | CharacteristicID == "ENT") 
 # filter(LocationTypeID == 27)

• How many samples by town, in last three years?
• What were the median, 75th and 90th quantile values in each town?

three <- hot %>%
  filter(Datetime > "2012-01-01")

byTown3yr <- three %>%
  group_by(MunicipalityID) %>%
  summarize(q90 = quantile(ResultValue, 0.9, na.rm = TRUE), 
            q75 = quantile(ResultValue, 0.75, na.rm = TRUE),
            median = median(ResultValue, na.rm = TRUE), n = n()) %>%
  arrange(desc(n), desc(q90))

Total number of samples in last three years = 322

MunicipalityID	q90	q75	median	n
Medford	34300.0	5654.00	767.0	53
Malden	51720.0	9678.00	1549.0	41
Somerville	6039.4	2975.00	1450.0	28
Melrose	12694.8	3537.00	744.0	27
Belmont	4815.9	2595.00	460.5	24
Chelsea	310.0	196.25	41.5	24
Arlington	14390.0	4813.00	1379.0	21
Woburn	169.3	111.50	34.0	20
Winchester	50910.0	2152.25	443.5	16
Cambridge	79846.0	22417.50	9472.5	14
Everett	225465.0	45112.50	1280.0	12
Revere	572.3	100.25	15.0	12
Stoneham	19909.2	7691.75	2671.0	10
Lexington	34264.4	1773.50	29.5	8
NA	3640.0	1828.00	270.0	7
Boston	106.5	101.25	92.5	2
Winthrop	28.9	25.75	20.5	2
East Boston	784.0	784.00	784.0	1

• How many samples by town, in last five years?
• What were the median, 75th and 90th quantile values in each town?

five <- hot %>%
  filter(Datetime > "2010-01-01")

byTown5yr <- five %>%
  group_by(MunicipalityID) %>%
  summarize(q90 = quantile(ResultValue, 0.9, na.rm = TRUE), 
            q75 = quantile(ResultValue, 0.75, na.rm = TRUE),
            median = median(ResultValue, na.rm = TRUE), n = n()) %>%
  arrange(desc(n), desc(q90))

Total number of samples in last five years = 625

MunicipalityID	q90	q75	median	n
Chelsea	6867	2416	362	74
Malden	16366	3808	970	68
Medford	63490	7202	1160	64
Somerville	18876	4826	2069	59
Arlington	14138	2524	395	55
Belmont	7945	2456	526	51
Melrose	9129	3237	395	43
Revere	24060	833	120	37
Winchester	20486	2400	432	35
Woburn	372	196	64	35
Cambridge	35796	19945	3922	23
Stoneham	9775	5886	580.5	18
Everett	192475	33550	1280	14
Lexington	6932	312.5	34	11
Boston	41755	19863	5794	9
East Boston	30120	5040	1726	9
Winthrop	3992	1342	537	7
NA	3640	1828	270	7
Burlington	2120	1700	770	5
Wakefield	324	324	324	1

• How many samples by town, in all years?
• What were the median, 75th and 90th quantile values in each town?

all <- hot 
  #filter(Datetime > "2012-01-01")

byTownAll <- all %>%
  group_by(MunicipalityID) %>%
  summarize(q90 = quantile(ResultValue, 0.9, na.rm = TRUE), 
            q75 = quantile(ResultValue, 0.75, na.rm = TRUE),
            median = median(ResultValue, na.rm = TRUE), n = n()) %>%
  arrange(desc(n), desc(q90))

Total number of samples in all years = 2089