analysis of pivot vs non-pivot counties and QCEW wage growth

clean_qcew.R

library(tidyverse)
library(haven)
library(data.table)


clean_qcew_annual <- function(x) {
  # QCEW data from https://www.bls.gov/cew/downloadable-data-files.htm
  fread(cmd = paste0("unzip -p ", x, "_annual_singlefile.zip")) %>% 
    # keep only county-level private sector 
    filter(agglvl_code == 71 & own_code == 5) %>% 
    # drop PR and VI
    filter(as.numeric(str_sub(area_fips, 1, 2)) < 72) %>% 
    transmute(
      fips = as.numeric(area_fips),
      year,
      qcew_pay_avg = avg_annual_pay,
      qcew_emp_avg = annual_avg_emplvl
    )
}

map_dfr(2013:2019, clean_qcew_annual) %>% 
  write_dta("qcew_2013_2019.dta")

cpi.csv

master.do

set more off
clear all

* cpi data 
import delimited cpi.csv, clear 
keep year cpiurs
keep if cpiurs != .
sum cpiurs if year == 2019 
local cpiurs2019 = r(mean)
tempfile cpiurs 
save `cpiurs'

* clean QCEW data
global Rterm_path "/usr/bin/R"
* use R script to convert raw QCEW to Stata format, 
* because Stata is maddeningly slow
* rsource using clean_qcew.R, roptions(--no-save)

* deal with some QCEW county issues
use qcew_2013_2019.dta, clear
tostring fips, gen(fips_string) format(%05.0f)

* problem: won't be able to match "Unknown Or Undefined" QCEW counties to electoral returns
* solution: drop them from QCEW data
drop if substr(fips_string, 3, 5) == "999"

* problem:  Bedford city (51515) added to Bedford County (FIPS 51019) in 2013
* solution: drop entries for Bedford City in 2013
drop if fips == 51515 & year == 2013

* problem: mid-year Shannon County (46113) renamed to Oglala Lakota County (46102) in mid-2015
* solution: drop the QCEW entries for 2015 Shannon, keep 2015 Oglala
*           then rename Oglala to Shannon to match electoral returns
drop if fips == 46113 & year == 2015
replace fips = 46113 if fips == 46102 

* problem: can't match many Alaska returns and QCEW data
* solution: drop Alaska
drop if substr(fips_string, 1, 2) == "02"

drop fips_string
tempfile qcewdata 
save `qcewdata'

* clean electoral data
* from https://dataverse.harvard.edu/dataset.xhtml?persistentId=doi:10.7910/DVN/VOQCHQ
import delimited countypres_2000-2016.csv, clear
replace candidatevotes = "" if candidatevotes == "NA"
destring candidatevotes, replace
replace fips = "" if fips == "NA"
destring fips, replace
encode candidate, gen(candidate_code)
rename state_po state_abb
rename county county_name
rename totalvotes total_votes
rename candidatevotes candidate_votes
keep year state_abb fips candidate_code candidate_votes total_votes county_name

* deal with electoral county issues
* remove entries with no fips
drop if fips == .

* problem:  Kansas city entry in electoral (FIPS 36000) not a county in QCEW
*           weird that it has FIPS of 36000 when 36 is the FIPS code for NY
* solution: going to drop it because there is no clean way to allocate
*           Kansas City votes to other counties
drop if fips == 36000

* deal with mismatches
tostring fips, gen(fips_string) format(%05.0f)
* problem: can't match many Alaska returns and QCEW data
* solution: drop Alaska
drop if substr(fips_string, 1, 2) == "02"
drop fips_string

* problem:  Bedford city (51515) added to Bedford County (51019) in 2013
*           but returns have both
* solution: add all of Bedford city returns to Bedford County
gen new_fips = fips 
replace new_fips = 51019 if fips == 51515
assert county_name == "Bedford" if new_fips == 51019
collapse (sum) candidate_votes total_votes (first) state_abb county_name, by(new_fips year candidate_code)
rename new_fips fips

* create Obama => Obama => Trump indicator
keep if year >= 2008
gen vote_share = candidate_votes/total_votes
egen rank = rank(vote_share), field by(year fips)
* check to make sure there is really only one winner in year X fips
duplicates report year fips if rank == 1
assert r(unique_value) == r(N)
* determine who won county
gen obama = candidate_code == 2 & rank == 1
gen trump = candidate_code == 3 & rank == 1
gen mccain = candidate_code == 7 & rank == 1
gen romney = candidate_code == 8 & rank == 1
gen clinton = candidate_code == 5 & rank == 1
collapse (max) obama trump mccain romney clinton (first) county_name total_votes, by(year state_abb fips) 

* identify swing counties
reshape wide obama trump mccain romney clinton total_votes, i(fips) j(year)
gen pivot_to_trump = obama2008 == 1 & obama2012 == 1 & trump2016 == 1
gen pivot_to_clinton = mccain2008 == 1 & romney2012 == 1 & clinton2016 == 1
tab pivot_to_trump pivot_to_clinton, m
egen mean_total_votes = rowmean(total_votes*)
keep fips county_name state_abb pivot* mean_total_votes trump2016 clinton2016

* merge QCEW data
merge 1:m fips using `qcewdata', assert(3) nogenerate
* confirm this is a balanced panel
tsset fips year
assert r(balanced) == "strongly balanced"

* some counties have no QCEW employment; drop counties with any zeros
tab fips, nofreq 
local before_count = r(r)
tab fips if pivot_to_trump == 1, nofreq
local before_count_pivot = r(r)
gen _zeroemp = qcew_emp_avg == 0
egen zeroemp = max(_zeroemp), by(fips)
drop if zeroemp == 1
drop *zeroemp
tab fips, nofreq 
local after_count = r(r)
tab fips if pivot_to_trump == 1, nofreq
local after_count_pivot = r(r)
di "Removing zero emp counties dropped"
di `before_count_pivot' - `after_count_pivot' " pivot-to-Trump counties"
di `before_count' - `after_count' " counties overall"

merge m:1 year using `cpiurs', keep(3) nogenerate  
gen real_avg_pay = qcew_pay_avg * `cpiurs2019' / cpiurs
rename qcew_pay_avg nom_avg_pay
rename qcew_emp_avg emp
drop cpiurs

keep if year == 2013 | year == 2016 | year == 2019
egen period = group(year) 
lab def period 2 "2013 - 2016" 3 "2016 - 2019"
lab val period period 
tsset fips period
foreach var of varlist *avg_pay emp {
    gen Dpct_`var' = `var' / L.`var' - 1
}

* analysis of pivot vs others
keep if period > 1
keep fips period Dpct* mean_total_votes pivot_to_trump
reshape wide Dpct_real_avg_pay Dpct_nom_avg_pay Dpct_emp, i(fips) j(period)

foreach x in real_avg_pay nom_avg_pay emp {
    gen faster_`x' = Dpct_`x'3 > Dpct_`x'2
}

table pivot_to_trump, c(mean faster_nom_avg_pay mean faster_real_avg_pay)

collapse (mean) Dpct_real_avg_pay2 Dpct_real_avg_pay3 Dpct_nom_avg_pay2 Dpct_nom_avg_pay3 [aw=mean_total_votes], by(pivot_to_trump)
foreach x of varlist Dpct* {
    gen `x'_string = string(round(`x' * 100, 0.1), "%3.1f") + "%"
    drop `x'
    rename `x'_string `x'
}
lab var Dpct_real_avg_pay2 "2013-2016"
lab var Dpct_real_avg_pay3 "2016-2019"
lab var Dpct_nom_avg_pay2 "2013-2016"
lab var Dpct_nom_avg_pay3 "2016-2019"
lab def pivot_to_trump 0 "Other counties" 1 "Pivot counties"
lab val pivot_to_trump pivot_to_trump

list 

keep pivot_to_trump Dpct_real_avg_pay*
export excel using average_growth.xlsx, firstrow(varlabels) replace