index.qmd

---
title: "Data Innovation"
subtitle: "Streamlining Broadband Insights with `cori.data.fcc`"
author: "Olivier Leroy"
date: "October 20 2024"
institute: "Center On Rural Innovation"
fontsize: 20pt
favicon: assets/images/Logo-Mark_Black.png
format:
  revealjs: 
    theme: src/custom_cori.scss
    logo: assets/images/CORI_Logo.png
    css: assets/fonts.css
    footnotes-hover:: true
from: markdown+emoji
editor: visual
resources: assets
---

# How are you getting your data? Are you downloading it? Is it hard?

## Overview

I.    <span style="font-size: 125%;">**Introduction**</span> ![](assets/images/overview.png){fig-align="right"}
II.   <span style="font-size: 125%;">**Research using the latest broadband service data**</span>
III.  <span style="font-size: 125%;">**Challenges to using the latest broadband service data**</span>
  <br />_i.e., More data more problems_
IV.   <span style="font-size: 125%;">**Our broadband data package: `cori.data.fcc`**</span>
  <br />_i.e., How we accelerate innovation by making this complex,_
  <br />_ever-changing broadband data more accessible and usable for research_


## :wave: Hi, I'm Olivier, a Senior Data Engineer at CORI

<br />

> Meet the people the people who can coax treasure out of messy, unstructured, data[^hbr]  

<p>&nbsp;</p><br />

- Working with source data—often referred to as "big", "messy", "unstructured" data—is a growing challenge

[^hbr]: [Harvard Business Review](https://hbr.org/2012/10/data-scientist-the-sexiest-job-of-the-21st-century)

- Socials: [LinkedIn](https://www.linkedin.com/in/olivier-leroy-r/) | [Mastodon](https://fosstodon.org/@defuneste) | [Personal Website](https://branchtwigleaf.com/about.html)

::: {.notes}
I find ways to add structure and meaning to data so that it can effectively inform the work of the organization’s analysts, researchers and community managers.
:::


## CORI's Mission

![](assets/images/cori-mission-slide-8.png)


## What Broadband means to Rural America

::::{.columns}

:::{.column width="54%"}

<p>&nbsp;</p><br />

We believe that **small towns** are home to **big ideas** — and combining new models of economic development with strategic investments in **new infrastructure** can **empower rural communities** across the U.S. to participate in and benefit from the nation's growing **tech economy**.

:::

:::{.column width="44%"}

![](assets/images/Shaniqua%20CorleyMoore.jpg)

:::

::::

### Broadband knowledge is an important part of our work


# Out of necessity we had to become experts in broadband data

## We’ve packaged our expertise into tools…

:::: {.columns}

::: {.column width="54%"}

[Rural Broadband Mapping Tool](https://rural-broadband-map.ruralinnovation.us/){.external target="_blank"}

![](assets/images/small_bead_tool.gif)

![](assets/images/bead_qr.svg){fig-align="center"}

:::

::: {.column width="44%"}

[Broadband Climate Risk Mitigation Tool](https://broadband-risk.ruralinnovation.us/){.external target="_blank"}

![](assets/images/bb_climate_2.png)

![](assets/images/bb_risk.svg){fig-align="center"}

:::

::::

::: {.notes}
- we are working with these data at the scale of the US (including territories) at the granularity of census blocks
- we are enhancing this data with other sources ACS, FEMA and more
- public facing aps (“people are going to check their house”)
:::


## … and research
<!--
::::{.columns }

::: {.column width="50%" .fragment}

![](assets/images/ideation.png)

:::

::: {.column width="50%" .fragment}

![](assets/images/report_slide.png){width="50%"}

![](assets/images/calixlgo.png)
:::

::::

::: {.notes}
from ideation to report
:::


##  Broadband research is expanding, going beyond traditional economic analysis
-->
:::: {.columns}

::: {.column width="50%"}

- **[The Fiber Broadband Housing Premium Across Three US States](https://www.tandfonline.com/doi/full/10.1080/21681376.2024.2305951#abstract)**
  <br />Whitacre, B. (2024). The fiber broadband housing premium across three US States. Regional Studies, Regional Science, 11(1), 38–62. [https://doi.org/10.1080/21681376.2024.2305951](https://www.tandfonline.com/doi/full/10.1080/21681376.2024.2305951#abstract)


- **[Beyond connectivity: The role of broadband in rural economic growth and resilience](https://ruralinnovation.us/resources/reports/report-the-role-of-broadband-in-rural-economic-growth-and-resilience/)**
  <br />Weinstein, A., Erouart, M., & Dewbury, A. (2024) Beyond Connectivity: The role of broadband in rural economic growth and resilience. Center on Rural Innovation. [https://ruralinnovation.us/resources/reports/report-the-role-of-broadband-in-rural-economic-growth-and-resilience/](https://ruralinnovation.us/resources/reports/report-the-role-of-broadband-in-rural-economic-growth-and-resilience/)


:::

::: {.column width="50%"}
![](assets/images/presenting_beyond_connectivity.png){width="75%"}

:::

::::


## Broadband research, not only about housing market

::::{.columns}

::: {.column width="70%"}

![](assets/images/rural_broadband_adoptions_vs_estab_pct_change.png)

:::

::: {.column width="30%"}

<br />

The growth rate of **new businesses** is **213%** higher for rural communities with **high broadband utilization**

:::

::::

<p style="padding-right: 10%; text-align: right">Weinstein, A., Erouart, M., & Dewbury, A. (2024)</p>

:::{.notes}
- Broadband access can have a transformative impact on economic growth and resilience,
  <br />but our research found that _access alone is insufficient_
- Broadband utilization is a key metric, encompassing both adoption rates and effective leveraging of infrastructure
:::


# Broadband research is evolving<br >... and so is the data!

## Broadband data is more detailed than ever before but at a cost

:::: {.columns}

::: {.column width="60%" .fragment}

|                      | [Form 477](https://www.fcc.gov/economics-analytics/industry-analysis-division/form-477-resources) | [National Broadband Map](https://broadbandmap.fcc.gov/home){white-space="nowrap"}                                                                                                                                                    |
|----------------------|---------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| US Census Boundaries | 2010                                                                                              | 2020                                                                                                                                                                                                                                 |
| Granularity          | Census blocks                                                                                     | [Locations](https://broadbandmap.fcc.gov/location-summary/fixed?version=dec2022&location_id=1350199199&addr1=390+MORGAN+ST&addr2=OBERLIN%2C+OH+44074&zoom=15.00&vlon=-82.231744&vlat=41.287448&br=r&speed=25_3&tech=1_2_3_4_5_6_7_8) |
| Timeframe            | 2014-2021                                                                                         | 2022 - *Ongoing*                                                                                                                                                                                                                     |
| Releases             | **twice a year**                                                                                  | **twice a year**                                                                                                                                                                                                           |
| Records              | **416,447,807**                                                                                   | **3,488,191,994**                                                                                                                                                                                                                    |
| Size                 | **400MB/year**                                                                                    | **22GB/year**                                                                                                                                                                                                                        |

:::

::: {.column width="40%" .fragment}

<p>&nbsp;</p><br />

- Data has grown **50 fold** from one data product to the next

- Require **domain knowledge**

- **Evolving** data landscape

:::

::::

::: {.notes}

- Agency in charge of regulating "everything" communications

These datasets are voluminous, involving hundreds of files, especially for the Form 477 data which covers ISP data since 2014

increase 50th fold

FCC releases lack "guard rails" in terms of data quality and consistency.  In addition to publishing very meager change logs (based on CostQuest’s pre-release/proprietary updates), issues include multiple encodings, erroneous values, and limited quality control

Analysis of the National Broadband Map (NBM) data reveals inconsistencies and potential errors in key identifiers like the FCC Registration Number (FRN), Provider ID, and Brand Name:
* Different FRNs and Provider IDs are used for the same ISP ("EATEL Corp.")
The same Provider ID is associated with different FRNs (Acentek).
Multiple FRNs exist for well-known ISPs like Windstream and AT&T.

:::


## How you would use the FCC's data/platform…

:::: {.columns}

::: {.column width="50%"}

<p>&nbsp;</p><br />

![](assets/manual_fcc.mov)

:::

::: {.column width="50%"}

<p>&nbsp;</p><br />

- :repeat: **Repeat** for every State  (56)
- :repeat: **Repeat** for every version  (??)
- :red_circle: **Error** prone (500 hundred clicks)

:::

::::

## FCC data does not meet the growing needs of researchers

<p>&nbsp;</p><br />

- <span style="font-size: 125%;">I wish I didn’t have to manually download FCC data</span>![](assets/images/data_center.png){fig-align="right" width="40%"}

- <span style="font-size: 125%;">I wish I could share my analysis (and code) with colleagues</span>

- <span style="font-size: 125%;">I wish I could easily perform quality checks on the raw FCC data</span>

-  <span style="font-size: 125%;"> **There’s a steep learning curve in working with this data … I wish I had more time to focus on  interesting research and analysis** </span>

# `cori.data.fcc`

:::{.notes}
CORI has developed a product to address this problem by packaging data as code…
:::

## … data packaged as code

:::{.incremental}
1. **Address Data Challenges**<br />
  - Data is packaged as code to simplify data access, reduce errors, and promote collaboration.
  - Low-level data transformations are codified: easy for others to reproduce.

2. **Accelerate Innovation**<br />
  - Broadband data is packaged so that researchers can focus on analysis and insights, not data wrangling.

3. **Unlock Deeper Insights Faster**<br />
  - `cori.data.fcc` provides fast access to granular details, essential for understanding broadband challenges across multiple geographic scales.
:::

:::{.fragment}

> [packages can act like team members such as the **IT Guy**, **Analyst**, Tech Lead, or Project Manager](https://www.emilyriederer.com/post/team-of-packages/#collaboration).


::: 

::: {.notes}

promote collaboration: open source, more value from our codes, start discussion with upstream, USAC

:::


## 440 files in 12 lines of codes

:::: {.columns}

::: {.column width="50%"}

::: {.panel-tabset}

### Visual
![](assets/dl_nbm_smaller.mov)

### Code

```{r}
#| label: example_dl_nbm
#| eval: false
#| echo: true
library(cori.data.fcc)

dir <- "data_swamp/nbm/"

get_nbm_release()

nbm_data <- get_nbm_available()

system(sprintf("mkdir -p %s", dir))

dl_nbm(
  path_to_dl = "data_swamp/nbm",
  release_date = "June 30, 2023",
  data_type = "Fixed Broadband",
  data_category = "Nationwide",
)
# part to check if dl was successful
num_files <- get_nbm_available() |>
  dplyr::filter(release == "June 30, 2023" &
                data_type == "Fixed Broadband" &
                data_category == "Nationwide") |>
  nrow()

files_dl <- length(list.files(dir,
                              pattern = "*.zip"))

identical(num_files, files_dl)
# TRUE

```

:::

:::

::: {.column width="50%"}

<p>&nbsp;</p><br />

- Created **quality checks** to reduce errors

- Complexity is handled in our **upstream** process and **abstracted** so that users can focus on what brings **value**!

- Added DuckDB

:::

::::

::: {.notes}
DuckDB unlocked in-process analytics, so access to a database management system/server is no longer required in order to explore and use the data

:::

## How to use the package > Choose your own adventure!

:::{.incremental}

- _Broadband data at the census block (or tract, county, etc.) level is perfect for my research_: <span>&nbsp;</span> <span>&nbsp;</span> <span>&nbsp;</span> <span>&nbsp;</span> <span>&nbsp;</span> <span>&nbsp;</span> <span>&nbsp;</span> <span>&nbsp;</span>
  <br />**Download the transformed data for NBM from CORI ([ISP](https://ruralinnovation.github.io/cori.data.fcc/reference/get_frn_nbm_bl.html) / [County](https://ruralinnovation.github.io/cori.data.fcc/reference/get_county_nbm_raw.html))**

- _I need source data but working with hundreds of CSV is not for me_:
  <br />**Download raw data as tables from CORI ([NBM](https://ruralinnovation.github.io/cori.data.fcc/reference/get_county_nbm_raw.html) / [Form 477](https://ruralinnovation.github.io/cori.data.fcc/reference/get_f477.html))**

- _I need to inspect the source (raw) data_:
  <br />**Download raw data [files directly from the FCC](https://ruralinnovation.github.io/cori.data.fcc/articles/Check_and_download_NBM_data.html)**

- The guides linked above can help you with each step!

:::



# Examples use cases


## Unlock analysis with 3 repeatable lines of codes

:::: {.columns}

::: {.column width="60%"}

::: {.panel-tabset}

### Visual

![](assets/images/nek_bb_service.png)


### Code

```{r service-nek}
#| echo: true
#| eval: false
#| code-line-numbers: "6,7,8"
library(cori.data.fcc); library(dplyr); library(sf); library(tigris)
library(ggplot2); library(cori.charts); library(basemapR)

cori.charts::load_fonts()

caledonia_co_nbm <- cori.data.fcc::get_nbm_bl(geoid_co = "50005")
essex_co_nbm <- cori.data.fcc::get_nbm_bl(geoid_co = "50009")
orleans_co_nbm <- cori.data.fcc::get_nbm_bl(geoid_co = "50019")

nek_nbm <- dplyr::bind_rows(caledonia_co_nbm, essex_co_nbm, orleans_co_nbm)

# tigris to get places and block
vt_blocks <- tigris::blocks("VT", progress_bar = FALSE)
vt_places <- tigris::places(state = "VT", progress_bar = FALSE)

# wrangling
nek_bb_blocks <- inner_join(
    vt_blocks,
    nek_nbm,
    by = c("GEOID20" = "geoid_bl")
  ) |>
  mutate(
    pct_100_20 = cnt_100_20 / cnt_total_locations,
    pct_fiber = cnt_fiber_locations / cnt_total_locations
  )

# Get major NEK Place centroids for map labeling
vt_places_centroids <- vt_places[lengths(sf::st_intersects(vt_places, nek_bb_blocks)) > 0, ] |>
  st_centroid()

# Map
bbox <- sf::st_bbox(nek_bb_blocks) |>
        cori.charts::fit_bbox_to_aspect_ratio(target_aspect_ratio = 2)

fig <- ggplot(data = nek_bb_blocks) +
  base_map(
    bbox,
    increase_zoom = 3,
    basemap = 'voyager'
  ) +
  geom_sf(aes(fill = pct_100_20), color = "dimgray", linewidth = 0.1, alpha = 0.9) +
  scale_fill_cori(
    discrete = FALSE,
    palette = "ctg2pu",
    labels = scales::label_percent(),
    reverse = T
  ) +
  geom_sf_label(data = vt_places_centroids, aes(label = NAME), size = 2, color = "black", family = "Lato", fontface = "bold") +
  coord_sf(
    expand = TRUE,
    xlim = c(bbox['xmin'], bbox['xmax']),
    ylim = c(bbox['ymin'], bbox['ymax'])
  ) +
  theme_cori_map() +
  theme(
    legend.key.width = unit(50, "pt")
  ) +
  labs(
    title = "Broadband service in the Northeast Kingdom",
    subtitle = "Percent of locations with access to 100/20 Mbps service by census block",
    caption = "Data source: 2023 FCC National Broadband Map\nMap source: © OpenStreetMap contributors © CARTO",
    x = NULL,
    y = NULL
  )

```

:::

:::

::: {.column width="40%"}

<p>&nbsp;</p><br />

- **3 lines** of codes to get the data…

-  … avoiding thousands of lines of code or clicks 

- Use census blocks: **easy match with other data sources** (ACS, BEA, etc ..)


:::


::::

##  Dive deeper, faster!

:::: {.columns}

::: {.column width="60%"}

::: {.panel-tabset}

### Visual

![](assets/images/nek_fiber_service.png)


### Code

```{r fiber-nek}
#| echo: true
#| eval: false

fig <- ggplot(data = nek_bb_blocks) +
  base_map(
    bbox,
    increase_zoom = 3,
    basemap = 'voyager'
  ) +
  geom_sf(aes(fill = pct_fiber), color = "dimgray", linewidth = 0.1, alpha = 0.6) +
  scale_fill_cori(
    discrete = FALSE,
    palette = "ctg2pu",
    labels = scales::label_percent(),
    reverse = T
  ) +
  geom_sf_label(data = vt_places_centroids, aes(label = NAME), size = 2, color = "black", family = "Lato", fontface = "bold") +
  coord_sf(
    expand = TRUE,
    xlim = c(bbox['xmin'], bbox['xmax']),
    ylim = c(bbox['ymin'], bbox['ymax'])
  ) +
  theme_cori_map() +
  theme(
    legend.key.width = unit(50, "pt"),
  ) +
  labs(
    title = "Fiber access in the Northeast Kingdom",
    subtitle = "Percent of locations with access to fiber by census block",
    caption = "Data source: 2023 FCC National Broadband Map\nMap source: © OpenStreetMap contributors © CARTO",
    x = NULL,
    y = NULL
  )

```
:::

:::

::: {.column width="40%"}

<p>&nbsp;</p><br />

- From a **simple analysis**: good on main street, bad further away

- To a **deeper** analysis: 	

  * Only 11.5% of locations have fiber access in the Northeast Kingdom

  * 6/25 ISP are providing Fiber

  * We see that NEK broadband provides the best (most inclusive) coverage in the area. This insight relates to our recent broadband research


:::

::::



## cori.data.fcc supports spatial and graph data at multiple scales

:::: {.columns}

::: {.column width="60%"}


::: {.panel-tabset}

### Visual

```{r display_graph}
#| echo: false
#| eval: true
library(igraph)
library(crosstalk);library(DT);library(threejs)


oh_graph <- readRDS("data//oh_graph.rds")

draw_me_a_graph <- function(x, ...) {
  threejs::graphjs(x,
          vertex.label = V(x)$provider_name,
          vertex.color = rep(2, vcount(x)),
          vertex.size = .1,
          edge.color = "grey",
          edge.width = 3, ...)
}


g <- draw_me_a_graph(oh_graph, brush=TRUE)
points3d(g, vertices(g), color="black", pch=V(oh_graph)$provider_name, size=1.5)
```


### Code

```{r}
#| echo: TRUE
#| eval: FALSE
#| code-line-numbers: "8,46"
library(tigris);library(cori.data.fcc);library(igraph);library(dplyr)
library(crosstalk);library(DT);library(threejs)

oh <- tigris::counties(state = "39") # tigris is a great example of data as code

talk_to_me <- function(x) {
    message(sprintf("Love Ohio: %s", x))
    cori.data.fcc::get_nbm_bl(x)
}
oh_nbm <- lapply(oh$GEOID, talk_to_me) |> dplyr::bind_rows()

oh2_nbm <- oh_nbm[!is.na(oh_nbm$combo_frn), ]

od_me <- function(x) {

  temp <- oh2_nbm[x, "array_frn"][[1]]
  geoid_bl <- oh2_nbm[x, "geoid_bl"]
  if (length(temp) == 1L)
  {
    return(data.frame(V1 = temp, V2 = NA, geoid_bl = geoid_bl))
  }
  bob <- as.data.frame(t(combn(temp, 2)))
  bob$geoid_bl <- geoid_bl
  return(bob)
}

od <- lapply(1:nrow(oh2_nbm), od_me) |> dplyr::bind_rows()
od <- od[!is.na(od$V2),]

bob <- rbind(data.frame(frn = od$V1, geoid_bl = od$geoid_bl),
             data.frame(frn = od$V2, geoid_bl = od$geoid_bl))

cnt_bl <- summarise(bob, cnt_bl = n_distinct(geoid_bl), cnt_rel = n(), .by = frn)

od <- od[!is.na(od$V2),]

od$combo <- paste(od$V1, od$V2, sep = " - ")
od$count <- 1

rel <- od |> dplyr::summarize(n = sum(count), .by = combo)
give_me_from <- function(x) unlist(strsplit(x, " - "))[1]
give_me_to <- function(x) unlist(strsplit(x, " - "))[2]
rel$from <- sapply(rel$combo , give_me_from)
rel$to <- sapply(rel$combo, give_me_to)

fcc_slim <- cori.data.fcc::fcc_provider[, c("frn", "provider_name")]
frn <- data.frame( frn = unique(c(rel$from, rel$to)))
frn <- merge(frn, fcc_slim, by.x = "frn", by.y = "frn")
frn <- merge(frn, cnt_bl, by.x = "frn", by.y = "frn")

oh_graph <- graph_from_data_frame(rel[,c("from", "to")], directed = FALSE, vertices = frn)

oh_graph <- graph_from_data_frame(rel[,c("from", "to")], directed = FALSE, vertices = frn)

draw_me_a_graph <- function(x, ...) {
  threejs::graphjs(x,
          vertex.label = V(x)$provider_name,
          vertex.color = rep(2, vcount(x)),
          vertex.size = .1,
          edge.color = "grey",
          edge.width = 3, ...)
}

g <- draw_me_a_graph(oh_graph, brush=TRUE)
points3d(g, vertices(g), color="black", pch=V(oh_graph)$provider_name, size=1.5)
```

:::

:::

::: {.column width="40%"}

<p>&nbsp;</p><br />

#### Mapping the ISP market in Ohio?

- 1321 ISP operating in Ohio

- How to interpret: 

  * Providers competing in competitive markets are at the center

  * Providers with few competitors are on the edges

- This type of analysis may be helpful in telling a story about ISP speeds and services, and ultimately general service quality. 


:::

::::


## How to get the package

:::: {.columns}

::: {.column width="50%"}

0. The source code is hosted on [GitHub](https://github.com/ruralinnovation/cori.data.fcc) (Version control)

1. You need the R package [{remotes}](https://remotes.r-lib.org/).

```{r}
#| eval: false
#| label: install-package
#| echo: true

install.packages("remotes")
remotes::install_github("ruralinnovation/cori.data.fcc")
```

2. Load it!

```{r}
#| echo: true
library(cori.data.fcc)
```

3. :construction: Check the version! :construction:
```{r}
#| label: version-package
#| echo: true
packageVersion("cori.data.fcc")
```

###  Open source supported by CORI, welcome any collaborations!

:::

::: {.column width="50%"}

<br />

```{=html}
<iframe width="600" height="600" src="https://ruralinnovation.github.io/cori.data.fcc/" title="cori.data.fcc"></iframe>
```

:::

::::


# Summary

## Our response to the changing broadband research landscape


::::{.columns}

::: {.column width="50%"}

<p>&nbsp;</p><br />


- We hope to **reduce the entry costs** associated with broadband research by making this a publically available product.

- We’ve **packaged the data (and expertise)** to add an **extra “team member”** to your research team

- And in return we hope to:

  * **Support** the expansion of exciting broadband research, positively impacting rural communities.

  * Increase the visibility and accountability of FCC products. 

  * Increase awareness of the **important role broadband plays in rural areas**.


:::

::: {.column width="50%"}

<br />

![](assets/images/rural_nonrural_broadband_gap.png)

:::

::::

## Contacts Us: 

::::{.columns}

:::{.column width="50%"}
Website: [https://ruralinnovation.us](https://ruralinnovation.us)

[LinkedIn](https://www.linkedin.com/company/11099658) | [Twitter](https://twitter.com/Ruralinno) | [Facebook](https://www.facebook.com/RuralInno/) | [Instagram](https://www.instagram.com/ruralinno/) | [YouTube](https://www.youtube.com/channel/UCyqbtSrv4Nad9vVv4dQptcA)

![](assets/images/10%20(1).png)


:::

:::{.column width="50%"}

- Built with R and [cori.data.fcc](https://ruralinnovation.github.io/cori.data.fcc/){.external target="_blank"}

- Created by [Quarto](https://quarto.org/) (format: revealjs)

- Hosted and deployed on [GitHub](https://github.com/ruralinnovation/conf_URISA_2023)<br />
  [https://ruralinnovation.github.io/prez_auber_2024/](https://ruralinnovation.github.io/prez_auber_2024/#/title-slide){.external target="_blank"}

<br />

![](assets/images/qr.svg){height="300" fig-align="left"}


:::

::::

# Thank you!