---
title: "CWP Project Tracking: Evaluation of the Alabama Wastewater Demonstration"
output:
flexdashboard::flex_dashboard:
storyboard: true
social: menu
source: embed
theme:
primary: "#7BAFD4"
navbar-bg: "#13294B"
---
```{r setup}
#Load libraries
library(tidyverse)
library(readxl)
library(openxlsx)
library(purrr)
library(ggpubfigs)
library(flexdashboard)
library(epoxy)
```
```{r color palettes}
#Load color palettes
primary_palette = c("#7BAFD4", "#13294B", "#151515", "#FFFFFF", "#F8F8F8")
secondary_palette = c("#4F758B", "#00594C", "#EF426F", "#00A5AD", "#FFD100", "#C4D600","#F4E8DD")
```
```{r load data}
#Load data
#Read in the excel file names
raw_files = fs::dir_ls("./data/", glob="*.xlsx")
#Read in the data from the excel files and sheets
for(i in 1:length(raw_files)){
my_sheet_names <- excel_sheets(raw_files[i])
my_sheets <- lapply(my_sheet_names, function(x) read_excel(raw_files[i], sheet = x))
names(my_sheets) <- my_sheet_names
list2env(my_sheets, envir=.GlobalEnv)
}
```
```{r}
todays_date = Sys.Date()
```
### Household Engagement
```{r participant engagement, fig.width = 10, fig.height = 6}
participant_engagement = enrollment_recruitment_visits %>%
pivot_longer(cols = c("Enrollment", "V01", "V02", "V03", "V04", "V05", "V06"),
names_to = "benchmark", values_to = "date") %>%
mutate(date = as.Date(date)) %>%
mutate(tally = case_when(is.na(date) == TRUE ~ 0,
TRUE ~ 1))
households_enrolled =
participant_engagement %>%
filter(benchmark == "Enrollment") %>%
tally()
n_enrolled = as.numeric(households_enrolled$n)
households_arm =
participant_engagement %>%
filter(benchmark == "Enrollment") %>%
group_by(study_arm) %>%
tally()
n_sewerage = as.numeric(households_arm[households_arm$study_arm == "sewerage",2])
n_intervention = as.numeric(households_arm[households_arm$study_arm == "intervention",2])
n_intervention_phaseIV = 0
participant_engagement %>%
ggplot() +
geom_col(aes(x = benchmark, y = tally, fill = study_arm)) +
ylim(0,50) +
ylab("Number of Households") +
xlab("Stage")+
labs(fill = "Study Arm") +
scale_fill_manual(values = secondary_palette) +
theme_big_grid() +
theme(legend.position = "right")
```
***
We are aiming to enroll a total of 50 households into this study, including 10 households with existing municipal sewer connections, 30 households from Phase III, and 10 households from Phase IV.
As of `r todays_date`, `r n_enrolled` households have been enrolled into this study, including:
- `r n_sewerage` households with existing municipal sewer connections
- `r n_intervention` households connecting to the demonstration system in Phase III
- `r n_intervention_phaseIV` households connecting to the demonstration system in Phase IV.
### Sample Collection and Processing
```{r sample collection and processing, fig.width = 10, fig.height = 8}
#How many samples have been collected and cultured?
Cultured =
Colilert %>%
group_by(HouseholdNo, Type, Status) %>%
tally() %>%
group_by(Type, Status) %>%
tally() %>%
ungroup() %>%
mutate(Type = as.factor(Type),
Type = fct_recode(Type,
"Septic Tank Influent" = "SLS",
"Sewage Lagoon Influent" = "SGLN",
"Soil - Impacted" = "IS",
"Soil - Unimpacted" = "US",
"Drinking Water" = "DW")) %>%
filter(Status != "C") %>%
select(Type, n) %>%
mutate(benchmark = "Cultured")
#How many samples have been archived for extraction?
archive = rbind(`BOX-DWA-HH01-`, `BOX-SOILA-HH01-`, `BOX-WWA-HH01-`) #Only the "A" boxes. "B" is for backup.
Archived = archive %>%
drop_na() %>%
separate(sample_id, into = c("household_id", "visit_no", "Type", "replicate"), sep = "-") %>%
mutate(Type = as.factor(Type),
Type = fct_recode(Type,
"Septic Tank Influent" = "SLS",
"Sewage Lagoon Influent" = "SLL",
"Soil - Impacted" = "IS",
"Soil - Unimpacted" = "US",
"Drinking Water" = "DW")) %>%
filter(Type != "CW") %>%
group_by(Type) %>%
summarise(n = n()) %>%
mutate(benchmark = "Archived")
#How many samples have been extracted?
Extracted =
data.frame(Type = c("Septic Tank Influent",
"Sewage Lagoon Influent",
"Soil - Impacted",
"Soil - Unimpacted",
"Drinking Water"),
n = 0,
benchmark = "Extracted")
#How many samples have been analyzed by TAC?
TAC =
data.frame(Type = c("Septic Tank Influent",
"Sewage Lagoon Influent",
"Soil - Impacted",
"Soil - Unimpacted",
"Drinking Water"),
n = 0,
benchmark = "TAC")
sample_processing = rbind(Cultured, Archived, Extracted, TAC)
#Generate a figure
sample_processing %>%
ggplot(aes(x = reorder(benchmark, -n), y = n, fill = Type)) +
geom_col() +
ylab("No. Samples") +
xlab("Processing Stage")+
theme_big_grid() +
theme(legend.position = "right") +
scale_fill_manual(values = secondary_palette) +
ylim(0,50)
#Add details to the caption
n_collected =
sample_processing %>%
group_by(benchmark) %>%
summarize(n = sum(n))
n_cultured = as.numeric(n_collected[n_collected$benchmark == "Cultured",2])
n_archived = as.numeric(n_collected[n_collected$benchmark == "Archived",2])
n_extracted = as.numeric(n_collected[n_collected$benchmark == "Extracted",2])
n_TAC = as.numeric(n_collected[n_collected$benchmark == "Extracted",2])
```
***
From each household, at each of the six visits, we will collect:
- Drinking water
- Soil, undisturbed
- Soil, impacted by raw sewage (if relevant)
- Septic sludge or influent wastewater from municipal sewer
All samples will be cultured for E. coli and enterococci. A portion of these samples will be extracted for total nucleic acids and analyzed for pathogenic targets by TaqMan Array Cards.
As of `r Sys.Date()`,
- `r n_cultured` total samples have been collected.
- `r n_cultured` total samples have been cultured for E. coli and enterococci.
- `r n_archived` total samples have been archived in duplicate.
- `r n_extracted` total samples have been extracted for total nucleic acids.
- `r n_TAC` total samples have been analyzed by TaqMan Array Card.
### Preliminary Data
