Determinants of Future Investment in U.S. Startup Cities
| Academic Paper | |
|---|---|
| Title | Determinants of Future Investment in U.S. Startup Cities |
| Author | Ed Egan |
| Status | In development |
| © edegan.com, 2016 | |
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 for Determinants
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
- 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 = Template: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
- 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
