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

From edegan.com
Jump to navigation Jump to search
 
(2 intermediate revisions by the same user not shown)
Line 10: Line 10:
 
#This is a working title only. The Measuring paper references it under this name for now.
 
#This is a working title only. The Measuring paper references it under this name for now.
  
===Instrument for Determinants===
+
==Instrument==
  
 
I am going to try shocking the number of ESOs using the political party of the incumbent mayor. Data is available from:
 
I am going to try shocking the number of ESOs using the political party of the incumbent mayor. Data is available from:
Line 48: Line 48:
  
 
The main steps are:
 
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
 
# Load the data
 
# prepend zeros to FIPS
 
# prepend zeros to FIPS
# Code mayor_party_final
+
# Code mayor_party_final: Variable is DorR, which takes 1 if D, 0 if R and NULL otherwise.
 
# 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
 +
 +
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!

Latest revision as of 19:49, 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

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: Variable is DorR, which takes 1 if D, 0 if R and NULL otherwise.
  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

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!