Testing other eligibilities

Published

July 25, 2024

Code 
source("R/table_with_options.R")
source("https://gist.githubusercontent.com/defuneste/32d5bffce8c6e88b6322ca4c9861793b/raw/893462fed28362601e63434ecc140bbdcbf6928b/us_states_df.R")

The goal of this page is to record the effect on not including some technologies on the eligibility of BSL and census block.

Excluding DSL service

Code 
select
    geoid_st,
    sum(cnt_total_locations) as cnt_total_locations,
    sum(cnt_underserved) as cnt_underserved,
    sum(cnt_underserved_dsl_excluded) as cnt_loc_underserved_dsl_excluded,
    count(*) as cnt_blocks,
    sum(case when bl_100_20_area = 'underserved_area' then 1 else 0 end) as cnt_underserved_block,
    sum(case when bl_100_20_area_dsl_excluded = 'underserved_area' then 1 else 0 end) as cnt_underserved_block_dsl_excluded
from staging.bead_source_tiger_2020_blocks
group by geoid_st
order by geoid_st

This table will summarize some effects on locations and census blocks categories.

Our previous definition:

  • We are excluding satellites and unlicensed wireless

  • If a location has only services lower than 25/3 it is unserved. If a location only has services with speed between 25/3 and 100/20 it is underserved. It will be served if it equal and above 100/20.

New definition:

  • We are now excluding DSL (technology 10).

  • Unserved are not changing (if they had DSL under 25/3 it was already unserved). Underseved and Served will change because some locations who were served will now move to underserved.

This was for locations. If we move to census block (where we apply this “uncommun” 80/20) it will also change the category of eligibility (because we are changing the classification of locations).

Table dicvtionnary:

  • “United.States.of.America”: names of States

  • “geoid_st: ANSI”number”

  • “number_block”: Number of Census Block per states

  • “cnt_underserved_block” : count of blocks underserved with the previous definition

  • “cnt_underserved_block_dsl_excluded”: count of block if we exclude DSL

  • “diff_block” : cnt_underserved_block_dsl_excluded - cnt_underserved_block

  • “cnt_total_locations”: count of total locations per States

  • “cnt_underserved”: count of locations underserved with previous definition

  • “cnt_loc_underserved_dsl_excluded”: count of locations underserved if we removed DSL

I provided the locations to:

  • do a bit of sanity check

  • Working at the block level imply using the 80/20 rules and kind of assum all block are the same.

Code 
eligibiliy_st_ntia <- read.csv("data/dsl-exluded.csv", colClasses =c("geoid_st" = "character"))
US_slim <- US_states[,c("United.States.of.America", "ANSI_num", "ANSI_let")]
easy_table <- merge(eligibiliy_st_ntia, US_slim, 
                    by.x = "geoid_st", by.y = "ANSI_num",
                    all.x = TRUE, all.y = TRUE)

easy_table$diff_block <- easy_table$cnt_underserved_block_dsl_excluded - easy_table$cnt_underserved_block

easy_table <- easy_table[, c("United.States.of.America",
                            "geoid_st",
                            "number_block", 
                            "cnt_underserved_block",
                            "cnt_underserved_block_dsl_excluded",
                            "diff_block",
                            "cnt_total_locations",
                            "cnt_underserved",
                            "cnt_loc_underserved_dsl_excluded"
                            )]

table_with_options(easy_table)

Some tl:dr:

Not keeping DSL move 26907 blocks from being served to be underserved.

It has heterogneous effects: for some States it has nearly or very low impacts but for other it is importants.