35 lines
1000 B
R
35 lines
1000 B
R
# Assumes you have https://github.com/tirandagan/fccULSloader installed and
|
|
# updated somewhere
|
|
|
|
library(dbplyr)
|
|
library(RSQLite)
|
|
library(dplyr)
|
|
library(tidyr)
|
|
library(janitor)
|
|
library(USAboundaries)
|
|
|
|
con <- dbConnect(SQLite(), "fccULSloader/src/data/fcc_data.db")
|
|
|
|
am <- tbl(con, "AM")
|
|
en <- tbl(con, "EN")
|
|
|
|
a <- dbGetQuery(con, "SELECT COUNT(*) FROM EN LEFT JOIN HD ON
|
|
EN.unique_system_identifier = HD.unique_system_identifier
|
|
WHERE license_status = 'A';") #%>%
|
|
#janitor::clean_names() %>%
|
|
#head(2)
|
|
#filter(status_code == "A") %>%
|
|
#summarize(count = n()) #%>%
|
|
#collect()
|
|
|
|
print(a)
|
|
|
|
b <- dbGetQuery(con, "SELECT UPPER(state), COUNT(*) FROM EN LEFT JOIN HD ON
|
|
EN.unique_system_identifier = HD.unique_system_identifier
|
|
WHERE license_status = 'A'
|
|
GROUP BY UPPER(state);") %>%
|
|
left_join(state_codes %>% filter(state_abbr != ""),
|
|
by = c("UPPER(state)" = "state_abbr"))
|
|
|
|
print(b)
|