template2a.Rmd

---
title: "DC Lead Pipe Replacement"
output: 
  flexdashboard::flex_dashboard:
    theme: bootstrap 
    logo: "etc/logo.svg"
    favicon: "etc/icon.png"
    navbar:
      - { title: "Map", href: "#map", align: left }
      - { title: "About", href: "#about", align: left} 
      - { title: "Data Download", icon: "fa-download", href:  "https://github.com/WaterDataLab/lead/raw/main/data/dc_lead.csv", align: left }
      - { title: "Source Code", icon: "fa-github", href: "https://github.com/WaterDataLab/lead", align: left, target: "_blank"}
    orientation: rows
    vertical_layout: fill
    css: "etc/style.css"
---

<script>
  $('.navbar-logo').wrap('<a href="https://www.watergrid.io" target="_blank">');
</script>

```{r setup, include=FALSE}
library(flexdashboard)
library(leaflet)
library(tidyverse)
library(sf)
library(leafgl)

# US Census poverty data
pal_pov <- c("#b3cde3", "#8c96c6", "#4d004b")
pov <- read_rds(here::here("data/dc_pov.rds")) %>% 
  st_transform(4326) %>% 
  rmapshaper::ms_simplify(keep_shapes = TRUE) %>% 
  mutate(
    col = case_when(
      percent_poverty_cat == "Most advantaged" ~pal_pov[1],
      percent_poverty_cat == "Moderately advantaged" ~pal_pov[2],
      percent_poverty_cat == "Least advantaged" ~pal_pov[3]
    ),
    lab = glue::glue("<b>{percent_poverty_cat}</b> ({percent_below_fed_poverty}% below federal poverty limit)<br><i>{NAME}</i>")
  )

# DC lead data
url <- "https://www.dcwater.com/sites/default/files/cleanrivers/service-line.csv"

# webgl
df <- read_csv(url) %>% 
  janitor::clean_names()

# clean up
df <- df %>% 
  select(premise_address, public_service_material, 
         private_service_material, longitude, latitude) %>%
  st_as_sf(coords = c("longitude", "latitude"), crs = 4326) 

# add equity data to points
df <- df %>% 
  st_join(select(pov, NAME:percent_poverty_cat))

# write for download, sorted by percent below poverty line
# df %>%
#   mutate(x = st_coordinates(geometry)[,1],
#          y = st_coordinates(geometry)[,2]) %>%
#   st_drop_geometry() %>%
#   arrange(desc(percent_below_fed_poverty)) %>% 
#   xlsx::write.xlsx(here::here("data/dc_lead.csv"))

# color points based on lead
lead <- c("Lead_brass", "Iron with lead", "Lead and copper", 
          "Lead", "LEAD AND COPPER", "LEAD\n", 
          "Galvanized iron with lead")

# colorblind-safe palette for points
pal_pts <- rcartocolor::carto_pal(name = "Safe")
col_noinfo = "#202020" # dark grey
col_nolead = pal_pts[4] # green
col_privatelead = pal_pts[2] # pink
col_publiclead = pal_pts[3] # yellow
col_alllead = pal_pts[12] # grey

# add colors depending on if lead is present
# green for no lead
# orange & yellow for partial lead depending on whose side it’s on
# red for full lead
# white for no information 
df <- df %>% 
  mutate(
    no_info = ifelse(
      public_service_material == "No information" | 
        private_service_material == "No information", 
      col_noinfo, NA
    ),
    no_lead = ifelse(
      ! public_service_material %in% lead & 
        ! private_service_material %in% lead &
        public_service_material != "No information" &
        private_service_material != "No information", 
      col_nolead, NA
    ),
    private_lead = ifelse(
      private_service_material %in% lead &
        ! public_service_material %in% lead, 
      col_privatelead, NA
    ),
    public_lead = ifelse(
      public_service_material %in% lead &
        ! private_service_material %in% lead, 
      col_publiclead, NA
    ),
    all_lead = ifelse(
      public_service_material %in% lead &
        private_service_material %in% lead, 
      col_alllead, NA
    ),
    NAME = str_remove_all(NAME, ", District of Columbia, District of Columbia"),
    NAME = str_remove_all(NAME, "Census Tract ")
  )

# add popup information
df <- df %>% 
  mutate(popup = ifelse(
    public_service_material == "No information" &
      private_service_material == "No information",
    paste0(
      "<b>", premise_address, "</b><br>",
      # "<b>Socioeconomic group: </b>", percent_poverty_cat, "<br>",
      "<b>", percent_below_fed_poverty, "%</b> below federal poverty line"),
    paste0(
      "<b>", premise_address, "</b><br>",
      "<b>Public: </b>", public_service_material, "<br>",
      "<b>Private: </b>", private_service_material, "<br>",
      # "<b>Socioeconomic group: </b>", percent_poverty_cat, "<br>",
      "<b>", percent_below_fed_poverty, "%</b> below federal poverty line")
    )
  )

# function to format percents
f_format_percent <- function(x){
  x = x * 100
  x = round(x, 1)
  x = formatC(x, digits = 1, format = "f")
  x = paste(x, "%")
  return(x)
}
```

Map {.hidden}
===================================== 

Row
-------------------------------------


### No lead {.value-box}

```{r}
valueBox(f_format_percent(nrow(filter(df, !is.na(no_lead)))/nrow(df)), 
         color = col_nolead, icon = "far fa-circle",
         caption = "Addresses with No lead")
```

### Private lead {.value-box}

```{r}
valueBox(f_format_percent(nrow(filter(df, !is.na(private_lead)))/nrow(df)), 
         color = col_privatelead, icon = "far fa-circle",
         caption = "Addresses with Private lead")
```

### Public lead {.value-box}

```{r}
valueBox(f_format_percent(nrow(filter(df, !is.na(public_lead)))/nrow(df)), 
         color = col_publiclead, icon = "far fa-circle",
         caption = "Addresses with Public lead")
```

### All lead {.value-box}

```{r}
valueBox(f_format_percent(nrow(filter(df, !is.na(all_lead)))/nrow(df)), 
         color = col_alllead, icon = "far fa-circle",
         caption = "Addresses with All lead")
```

### No information {.value-box}

```{r}
valueBox(f_format_percent(nrow(filter(df, !is.na(no_info)))/nrow(df)), 
         color = col_noinfo, icon = "far fa-circle",
         caption = "Addresses with No information")
```


Row
-------------------------------------


### Most Advantaged {.value-box}

```{r}
valueBox(f_format_percent(nrow(filter(df, percent_poverty_cat == "Most advantaged"))/nrow(df)), 
         color = pal_pov[1], icon = "fa-home",
         caption = "Most Advantaged addresses affected")
```

### Moderately Advantaged {.value-box}

```{r}
valueBox(f_format_percent(nrow(filter(df, percent_poverty_cat == "Moderately advantaged"))/nrow(df)), 
         color = pal_pov[2], icon = "fa-home",
         caption = "Moderately Advantaged addresses affected")
```

### Least Advantaged {.value-box}

```{r}
valueBox(f_format_percent(nrow(filter(df, percent_poverty_cat == "Least advantaged"))/nrow(df)), 
         color = pal_pov[3], icon = "fa-home",
         caption = "Least Advantaged addresses affected")
```



Row
-------------------------------------

### **Lead pipes remaining**, last updated `r paste(Sys.time())`

```{r}
# make leaflet
leaflet() %>% 
  # 4 basemaps
  addProviderTiles(provider = providers$CartoDB.Positron) %>% 
  addProviderTiles(provider = providers$CartoDB.DarkMatter, group = "Dark") %>%
  addProviderTiles(provider = providers$OpenStreetMap, group = "Street") %>%
  addProviderTiles(provider = providers$Esri.WorldImagery, group = "World") %>%
  addGlPolygons(
    data = pov,
    fillColor = pov$col,
    fillOpacity = 0.8#,
    # popup = pov$lab
  ) %>% 
  # no info on private and public side
  addGlPoints(
    data = filter(df, no_info == col_noinfo),
    fillColor = "no_info",
    fillOpacity = 1,
    radius = 10,
    popup = "popup",
    group = "No Information"
  ) %>%
  # no lead on public and private side
  addGlPoints(
    data = filter(df, no_lead == col_nolead),
    fillColor = "no_lead",
    fillOpacity = 1,
    radius = 10,
    popup = "popup",
    group = "No Lead"
  ) %>%
  # private lead
  addGlPoints(
    data = filter(df, private_lead == col_privatelead),
    fillColor = "private_lead",
    fillOpacity = 1,
    radius = 10,
    popup = "popup",
    group = "Private Lead"
  ) %>%
  # public lead
  addGlPoints(
    data = filter(df, public_lead == col_publiclead),
    fillColor = "public_lead",
    fillOpacity = 1,
    radius = 10,
    popup = "popup",
    group = "Public Lead"
  ) %>%
  # all lead
  addGlPoints(
    data = filter(df, all_lead == col_alllead),
    fillColor = "all_lead",
    fillOpacity = 1,
    radius = 10,
    popup = "popup",
    group = "Private & Public Lead"
  ) %>%
  flyTo(lng = mean(st_coordinates(df)[, 1]),
        lat = mean(st_coordinates(df)[, 2]), zoom = 12) %>% 
  leaflet.extras::suspendScroll() %>% 
  addLayersControl(
    baseGroups = c("Light", "Dark", "Street", "World"),
    overlayGroups = c("No Information", "No Lead", "Private Lead", 
                      "Public Lead", "Private & Public Lead"),
    options = layersControlOptions(collapsed = FALSE),
    position = "topright"
  ) %>% 
  leaflegend::addLegendImage(
     images = "legend2.png",
     labels = "",
     title = htmltools::tags$div(
       "Legend",
       style = "font-size: 16px; text-align: center; margin-bottom: 5px;"),
     position = "topright", 
     orientation = "vertical", 
     height = 233, width = 180)
```



About {.hidden}
===================================== 

### **Why replace lead pipes?**


<iframe width="100%" height="285" src="https://www.youtube.com/embed/6DJDGtM_d5Y" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen="" data-external="1"></iframe>


#### **History**

DC Water has historic data for the public portion of the water service line from plumbing records, service installation, and some maintenance activity that was reported to DC Water or another utility agency. Where DC Water has verified the pipe material by test pit or visual observation during a public space and/or private property service line replacement, the data source will be shown as "excavation" and is accurate as of the given inspection date. All other information is based upon historic records, but has not been confirmed. The map reflects the information DC Water has available for each active customer in the District.

### **Disclaimer**

The maps provided by the District of Columbia Water and Sewer Authority (“D.C. Water”) are based on historical data, information directly provided by customers, and in some cases, information acquired during physical inspections. DC Water does not guarantee the accuracy of these records and maps, which shall be used for the sole purpose of providing property owners and residents with DC Water’s best available data regarding their private water services, and not for any commercial, legal or other use. These records will be updated constantly as D.C. Water gathers additional information. D.C. Water requests that customers provide to it records of any service line replacements performed by property owners. D.C. Water reserves the right to alter, amend or terminate at any time the display of these maps and records.  

#### **More information**

- <a href="https://www.dcwater.com/faq-page/74" target="_blank">Service Lines - Fact Sheet</a>  
- <a href="https://www.dcwater.com/Lead" target="_blank">DC Water Lead information</a>


### **Contact**

* _General inquiries_: 202-787-2000  
* _Email_: customer.service@dcwater.com  
* _Twitter_: <a href="https://twitter.com/dcwater" target="_blank">@ dcwater</a>