View source for Determinants of Future Investment in U.S. Startup Cities

{{AcademicPaper
|Has title=Determinants of Future Investment in U.S. Startup Cities
|Has author=Ed Egan,
|Has paper status=In development
}}

==Notice(s)==

#This paper was originally the empirical component of [[Measuring High-Growth High-Technology Entrepreneurship Ecosystems]].
#This is a working title only. The Measuring paper references it under this name for now.

==Instrument==

I am going to try shocking the number of ESOs using the political party of the incumbent mayor. Data is available from:
*https://libguides.princeton.edu/elections#s-lg-box-10082744
**https://dataverse.harvard.edu/dataset.xhtml?persistentId=doi:10.7910/DVN/SJBWC3
*Ballotopia has current mayors for top 100 cities: https://ballotpedia.org/List_of_current_mayors_of_the_top_100_cities_in_the_United_States
*https://doi.org/10.7910/DVN/KFIKH8 -- downloaded mayoralelections_final.tab but it seems a subset of the above
*Could scrape and process https://www.usmayors.org/elections/election-results/ but it doesn't have parties and would need to be geomatched

By far the best data is:

 @data{DVN/SJBWC3_2017,
  author = {de Benedictis-Kessner, Justin},
  publisher = {Harvard Dataverse},
  title = {{Replication Data for: ``Off-Cycle and Out of Office: Election Timing and the Incumbency Advantage''}},
  UNF = {UNF:6:4fmCzYs43mFR+VunIFHyOg==},
  year = {2017},
  version = {V1},
  doi = {10.7910/DVN/SJBWC3},
  url = {https://doi.org/10.7910/DVN/SJBWC3}
 }

I got it from: https://dataverse.harvard.edu/dataset.xhtml?persistentId=doi:10.7910/DVN/SJBWC3 . It covers mayoral elections for 1945 to 2014. The description is as follows:

''Data on 9,131 mayoral elections in which approx. 10,000 unique candidates ran in 1,016 cities of all sizes, 1950-2014. Replication data for Justin de Benedictis-Kessner, "Off-Cycle and Out of Office: Election Timing and the Incumbency Advantage," The Journal of Politics 80, no. 1 (January 2018): 119-132''

For me the useful variables are:
*FIPS: 6 or 7dg (107000 BIRMINGHAM CITY, AL to 5613900 CHEYENNE CITY, WY)
*year (non-continuous -- election years)
*mayor_party_final (D,R,NA,NP,0 [missing?])
*month
*FIPS_Place_ID: FIPS_Place_ID is 4 to 5dg (7000 to 13900)

If we prepend leading zeros for 6dg, FIPS matches GEOID (used in vcdb4) 0107000 Birmingham, AL and 5613900 Cheyenne, WY. Alternatively, again prepending leading zeros, FIPS_Place_ID matches placefp (5dg) in the TigerPlaces table 07000 Burmingham, AL and 13900 Cheyenne, WY.

The code to handle the load and processing into vcdb4 is in E:\projects\MeasuringHGHTEcosystems\MayoralElections.sql

The main steps are:
# Pre-process the data. 
## Keep only fips, yeardata, govid_14, name, STATE, mayor_party_final, runnerup_party_final, mayor_votes_final, runnerup_votes_final, mayor_name_final, elecdate, month, ID, FIPS_PLACE_ID, term_length, term_limit, demshare, JURIS
## Remove double-quotes, remove NA, force to UTF8
## Remove duplicate entries (see below)
# Load the data
# prepend zeros to FIPS
# Code mayor_party_final: Variable is DorR, which takes 1 if D, 0 if R and NULL otherwise.
# Blowout years
# Join by GEOID, year

There were a small number of erroneous records, which caused duplication at the FIPS-Yeardata level. I manually removed these. For future reference they are (note that geoid is fips with pre-pended zeros where applicable):
 SELECT geoid, yeardata, count(*) FROM mayoralelectionsclean GROUP BY geoid, yeardata HAVING count(*) >1;
 3956882	2005	2
 3916000	2013	2
 1882862	2003	2
 5323515	2010	4
 1842246	2003	2
 4824000	2013	2
 0973770	2003	2
 2567000	1973	2
 1710487	2003	2
 1255775	2004	2
 1840788	2003	2
 0952980	2003	2
 3539380	2003	2
 0943370	2003	2
 2622000	2009	2

Using this approach I have data for some years for 109 cities in my data, and 83% of the city-year records have democrats or republicans as the winners. It's good enough to try!