Innovation Sector Job Change

Summary Statistics for Pct. Change in Innovation Employment (2005-2020)

moodys_employ <- getMoodysInnovationEmployStatistics()
cbsa <- getCBSA()

moodys_employ_2005_2020 <- moodys_employ %>%
  dplyr::filter(year == 2020 | year == 2005) %>%
  dplyr::mutate(
    year = ifelse(year == 2005, "employ_2005", "employ_2020")
  ) %>%
  tidyr::pivot_wider(names_from = year, values_from = value) %>%
  dplyr::group_by(geoid_co) %>%
  dplyr::summarise(
    jobs_2005 = sum(employ_2005, na.rm = TRUE),
    jobs_2020 = sum(employ_2020, na.rm = TRUE)
  ) %>%
  dplyr::mutate(
    pct_change = ifelse(jobs_2005 == 0 & jobs_2020 == 0, 0,
                        ifelse(jobs_2005 == 0, NA, (jobs_2020 - jobs_2005)/jobs_2005)
                )
  ) %>%
  dplyr::left_join(
    cbsa,
    by = c("geoid_co"="geoid")
  ) 

sum_stats <- moodys_employ_2005_2020 %>%
  dplyr::mutate(
    change_grouping = ifelse(pct_change > .5, "50% increase or more",
                        ifelse(pct_change > 0, "0 to 50% increase",
                               ifelse(pct_change == 0, "No change",
                                      ifelse(pct_change > -.5, "0 to 50% decrease",
                                             ifelse(is.na(pct_change), "No data available", "50% decrease or more")
                                             )
                                      )
                               )
                        )
  ) %>%
  dplyr::filter(!is.na(metropolitan_designation)) %>%
  dplyr::group_by(metropolitan_designation, change_grouping) %>%
  dplyr::summarise(
    mean_jobs_2005 = mean(jobs_2005, na.rm = TRUE),
    mean_jobs_2020 = mean(jobs_2020, na.rm = TRUE),
    mean_pct_change = mean(pct_change, na.rm = TRUE),
    sum_jobs_2005 = sum(jobs_2005, na.rm = TRUE),
    sum_jobs_2020 = sum(jobs_2020, na.rm = TRUE),
    sd_jobs_2005 = sd(jobs_2005, na.rm = TRUE),
    sd_jobs_2020 = sd(jobs_2020, na.rm = TRUE),
    sd_pct_change = sd(pct_change, na.rm = TRUE),
    share_jobs_2005 = sum_jobs_2005 / sum(moodys_employ_2005_2020$jobs_2005),
    share_jobs_2020 = sum_jobs_2020 / sum(moodys_employ_2005_2020$jobs_2020),
    share_jobs_change = share_jobs_2020 - share_jobs_2005,
    share_jobs_pct_change = share_jobs_change / share_jobs_2005
  )

knitr::kable(sum_stats)

metropolitan_designation	change_grouping	mean_jobs_2005	mean_jobs_2020	mean_pct_change	sum_jobs_2005	sum_jobs_2020	sd_jobs_2005	sd_jobs_2020	sd_pct_change	share_jobs_2005	share_jobs_2020	share_jobs_change	share_jobs_pct_change
Metro	0 to 50% decrease	2.8192393	2.3685798	-0.2211532	1370.1503	1151.1298	7.2123864	6.3608724	0.1423480	0.3965338	0.2960120	-0.1005218	-0.2535012
Metro	0 to 50% increase	3.4457209	4.0948509	0.2139157	1185.3280	1408.6287	11.6144829	13.4774875	0.1452849	0.3430446	0.3622277	0.0191830	0.0559200
Metro	50% decrease or more	0.6920417	0.2675240	-0.6185980	66.4360	25.6823	1.5531472	0.6883022	0.1025942	0.0192272	0.0066042	-0.0126230	-0.6565184
Metro	50% increase or more	2.6098126	4.9045167	3.5016468	561.1097	1054.4711	13.6856376	22.5127690	32.6257111	0.1623902	0.2711563	0.1087661	0.6697826
Metro	No change	0.0689000	0.0689000	0.0000000	0.0689	0.0689	NA	NA	NA	0.0000199	0.0000177	-0.0000022	-0.1114681
Micro	0 to 50% decrease	0.2588517	0.1957738	-0.2363771	82.0560	62.0603	0.3033936	0.2324468	0.1367145	0.0237477	0.0159588	-0.0077890	-0.3279887
Micro	0 to 50% increase	0.2124488	0.2492430	0.2022378	36.5412	42.8698	0.2798220	0.3182446	0.1342305	0.0105754	0.0110239	0.0004486	0.0424174
Micro	50% decrease or more	0.3597049	0.1347590	-0.6383975	21.9420	8.2203	0.5533980	0.2293687	0.0940916	0.0063502	0.0021138	-0.0042364	-0.6671225
Micro	50% increase or more	0.1523620	0.3288990	1.3677181	15.2362	32.8899	0.2287915	0.5360619	2.1419857	0.0044095	0.0084576	0.0040481	0.9180455
Micro	No change	0.0002500	0.0002500	0.0000000	0.0010	0.0010	0.0002887	0.0002887	0.0000000	0.0000003	0.0000003	0.0000000	-0.1114681
Non-CBSA	0 to 50% decrease	0.0630621	0.0477901	-0.2505866	38.0895	28.8652	0.1086967	0.0851855	0.1370426	0.0110234	0.0074227	-0.0036008	-0.3266477
Non-CBSA	0 to 50% increase	0.0648950	0.0782053	0.2070299	22.1941	26.7462	0.1100759	0.1324778	0.1342144	0.0064232	0.0068778	0.0004546	0.0707734
Non-CBSA	50% decrease or more	0.0502119	0.0159394	-0.6653838	8.0339	2.5503	0.0800178	0.0220350	0.1127112	0.0023251	0.0006558	-0.0016693	-0.7179424
Non-CBSA	50% increase or more	0.0413673	0.0865821	1.4323591	6.7015	14.0263	0.0675881	0.1428962	1.6417244	0.0019395	0.0036069	0.0016674	0.8597053
Non-CBSA	No change	0.0102182	0.0102182	0.0000000	0.2248	0.2248	0.0178176	0.0178176	0.0000000	0.0000651	0.0000578	-0.0000073	-0.1114681
Non-CBSA	NA	0.0000000	0.0247500	NaN	0.0000	0.0495	0.0000000	0.0345775	NA	0.0000000	0.0000127	0.0000127	Inf

Note: share_jobs is the the number of jobs for that metropolitan designation and change grouping divided by the total number of us innovation jobs in that year