Difference between revisions of "Determinants of Future Investment in U.S. Startup Cities"

From edegan.com
Jump to navigation Jump to search
Line 51: Line 51:
 
## 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
 
## 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 double-quotes, remove NA, force to UTF8
 +
## Remove duplicate entries (see below)
 
# Load the data
 
# Load the data
 
# prepend zeros to FIPS
 
# prepend zeros to FIPS
Line 56: Line 57:
 
# Blowout years
 
# Blowout years
 
# Join by GEOID, year
 
# 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

Revision as of 19:27, 29 May 2020

Academic Paper
Title Determinants of Future Investment in U.S. Startup Cities
Author Ed Egan
Status In development
© edegan.com, 2016


Notice(s)

  1. This paper was originally the empirical component of Measuring High-Growth High-Technology Entrepreneurship Ecosystems.
  2. 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:

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:

  1. Pre-process the data.
    1. 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
    2. Remove double-quotes, remove NA, force to UTF8
    3. Remove duplicate entries (see below)
  2. Load the data
  3. prepend zeros to FIPS
  4. Code mayor_party_final
  5. Blowout years
  6. 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
Retrieved from "http://www.edegan.com/mediawiki/index.php?title=Determinants_of_Future_Investment_in_U.S._Startup_Cities&oldid=26358"