Latent Innovation by County

library(dplyr)

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

library(coriverse)
library(DBI)

con <- connect_to_db('sch_source')
latent_inno <- cori.db::read_db(con, "goetz_latent_innovation")
dbDisconnect(con)

readr::write_csv(latent_inno, '../export/data/goetz_latent_innovation.csv')

Rural Latent Innovation

source('../scripts/utils-data.R')
cbsa <- getCBSA()


rural_latent_inno <- dplyr::left_join(
    latent_inno,
    cbsa,
    by = c("fips" = "geoid")
  ) %>%
  dplyr::filter(
    rural_cbsa_flag == 1
  )

readr::write_csv(rural_latent_inno, '../export/data/goetz_latent_innovation_rural.csv')

sum_stats <- latent_inno %>%
  dplyr::left_join(
    cbsa,
    by = c("fips" = "geoid")
  ) %>%
  dplyr::group_by(metropolitan_designation) %>%
  dplyr::summarise(
    mean_innovation = mean(innovation, na.rm = TRUE),
    sd_innovation = sd(innovation, na.rm = TRUE),
    sum_innovation = sum(innovation, na.rm = TRUE)
  )

knitr::kable(sum_stats)

metropolitan_designation	mean_innovation	sd_innovation	sum_innovation
Metro	0.4124808	0.9376038	486.72734
Micro	-0.0993061	0.8819528	-65.54204
Non-CBSA	-0.3237543	0.9825001	-418.93804
NA	-0.3158961	0.7329809	-1.57948