Hubs

McNair Project

Hubs
Project Information
Project Title	Hubs
Owner	Hira Farooqi
Start Date
Deadline
Keywords	Data
Primary Billing
Notes
Has project status	Active
Copyright © 2016 edegan.com. All Rights Reserved.

The Hubs Research Project is a full-length academic paper analyzing the effectiveness of "hubs", a component of the entrepreneurship ecosystem, in the advancement and growth of entrepreneurial success in a metropolitan area. It focuses on cities in the United States as the primary unit of analysis.

This page contains information about data used for this research project, including data sources, location of data on RDP and details on data processing.

Information on initial data work done prior to Summer 2017 can be found at Hubs Summer 2016.

COMPUSTAT Data

The data set includes information on publicly traded firms in the US. It was obtained from the Wharton Research Data Services (https://wrds-web.wharton.upenn.edu/wrds/index.cfm?).

Raw Data is in:

E:\McNair\Projects\Hubs\Summer 2017
Z:\Hubs\2017

The source file is RandDExpenditures.txt. It contains:

• Date from 1980-2017 (July).
• 427799 records
• Fields include:
  • R&D Expenditure
  • Address (inc. city, zip, state)
  • Revenue of firms

Database is cities

SQL script is: COMPUSTAT.sql

Output file is COMPUSTATSummary.txt. It contains:

• Variables: City, year, No.public firms, sum R&D, sum Sales, sum total assets
• 1979-2016
• 4440 cities

It is located in

Z:\Hubs\2017\Output_Files

NSF Data

Data is in:

E:\McNair\Projects\Hubs\Summer 2017
Z:\Hubs\2017

Database is cities

SQL script is: nsf_2017.sql

The source files are: nsf2017.txt, copied from table nsf, and nsf_institution copied from table nsf_grants_institution from the biotech db.

They contain:

• Award ID
• Award Institution
• Award Effective date
• Institution city
• Award Value
• Organization state code

From 1900 - 2017

Output file is nsfSummary.txt. It contains:

• Variables: City, State code year, nsf_nogrants, nsf_valuegrant
• 1900-2017

Joined NSF table

The joined nsf table with the VC table is found in db cities. The table is named merged_nsf. All the values of nogrants and valuegrant with missing values for years 1990-2017 are set equal to 0. The sql script is in

Z:\HUbs\2017\sql scripts

NIH Data

Data is in:

Z:\Hubs
E:\McNair\Projects\Hubs\Summer 2017

Database is cities SQL script is: nih2017.sql The source files are:

• nih_1986_2001.csv
• nih_2002_2012.txt
• nih_2013_2015

located in E:\McNair\Projects\Federal Grant Data\NIH

The script that cleans NIH data and generates the summary table is titled nihSummary. It is located here:

Z:\Hubs\2017\sql scripts

This table includes

• year
• city
• state
• country
• nogrants (number of grants)
• valuegrant
• city_state

• Date from 1986-2015

Joined NIH table

The joined NIH table with the VC table is found in db cities. The table is named merged_nih. All the values of nih_valuegrant and nih_nogrants with missing values for years 1986-2015 are set equal to 0. The sql script is in

Z:\HUbs\2017\sql scripts

Clinical Trials Data

Data is in:

Z:\Hubs
E:\McNair\Projects\Hubs\Summer 2017

Database is cities SQL script is: ctrials.sql The source file is:

• medclinical.txt

located in Z:\Hubs\2017

• Date from 1999-2017

Joined clinical trials table

The file which contains the number of trials in each city and year is located in:

Z:\Hubs\2017

The file is in:

Z:\Hubs\2017\clean data

The name of the file is:

 ctrialsSummary.txt

It contains:

• city
• year
• city_state_year
• noctrials - number of trials

The ctrials is joined with VC table. The joined SQL script is: new_ctrials.sql and it is located in

Z:\Hubs\2017\sql scripts

The name of the joined table is new_merged_ctrials.

It contains:

• city
• state
• city_state_id
• city_state_year
• year
• noctrials
• seedamtm
• earlyamtm
• lateramtm
• selamtm
• numseeds
• numearly
• numlater
• numsel

All the values of noctrials with missing values for years 1999-2017 are set equal to 0.

Population Data

Data is in:

Z:\Hubs
E:\McNair\Projects\Hubs\Summer 2017

Database is cities

SQL script is: population.sql The source files are:

• pop2000_2009.xlsx
• pop2010_2016.xlsx

They contain:

• State
• City name
• Year
• Population Estimates

Date from 2000-2016

Joined population table

Data is in:

Z:\Hubs\2017\clean data

The file names are

1_population.txt - contains data on population estimates from 2000-2009
2_population.txt - contains data on population estimates from 2010-2016

Database is cities SQL script is: new_population.sql, located in

Z:\Hubs\2017\sql scripts

The population table is joined on VC table. The table is called new_merged_population.

They contain:

• City
• State
• city_state_id to uniquely identify each city
• city_state_year to uniquely identify each city in each year
• Population estimates
• Year
• Code from the state code and Fips code
• State full name

Income Data

Raw data was obtained from Census data, American Communities Survey.

Raw Data is in:

E:\McNair\Projects\Hubs\Summer 2017\MSA Income_raw.zip 

Date from 2005-2015

The master list with MSAs and principal cities is titled list2.xls. It is located at:

Z:\Hubs\2017

This master list includes:

• MSA code
• MSA name
• Principal City
• State
• Place code (city code)
• State Code

This master list was edited to associate each principal city with a unique state. E.g. if New York is the principal city located in New York-New Jersey MSA, it was associated with state NY-NJ. So list was edited to put New York with NY.

Cleaned Income data files are in

Z:\Hubs\2017\merging_on_ID 

They contain:

• MSA code
• MSA
• Year
• Total Household Income

The MSA-City-State look up file is titled msa_city_state_wcode.txt. It is located in

Z:\Hubs\2017\merging_on_ID 

The SQL file that merges income data from ACS (by MSA - Year) with the MSA-City file is titled income.sql. It is located here:

Z:\Hubs\2017\sql scripts
  

The final income table is in db cities titled merged_income.

It includes:

• MSA
• City
• State
• Year
• Total Household Income

The table includes 8780 observations

Joined income table

Data is in:

Z:\Hubs\clean data

The file names are:

INC_05.txt - INC_15.txt

Database is cities SQL script is: merged_income.sql

They contain:

• City
• State
• city_state_id to uniquely identify each city
• Income
• Year
• Code from the state code and Fips code

Employment Data

Data on employment was obtained from American Communities Survey, US Census Bureau.

Raw Data is in:

E:\McNair\Projects\Hubs\Summer 2017\Employment Data by MSA

Cleaned files are in

Z:\Hubs\2017\clean data  

They contain:

• MSA code
• MSA
• Year
• Employment rate of individuals 16 years or older
• Unemployment rate of individuals 16 years or older

Date from 2005-2015

The SQL file that merges employment data from ACS (by MSA - Year) with the MSA-City file is titled Employment.sql. The file is located in:

Z:\Hubs\2017

The final table is in db cities titled merged_employment.

It includes:

• MSA
• City
• Year
• Employment rate
• Unemployment rate

Joined employment table

Data is in:

Z:\Hubs\clean data

The file names are:

EMP_05.txt - EMP_15.txt 

Database is cities SQL script is: new_employment.sql and it is located in Z:\Hubs\2017\sql scripts

The final table which is joined on VC is in db cities titled new_merged_employment.

They contain:

• City
• State
• Code from the state code and Fips code
• city_state_id to uniquely identify each city
• city_state_year to uniquely identify each city in each year
• Employment rates of individuals of 16 years or older
• Unemployment rates of individuals of 16 years or older
• Year

Schooling Data

Data on schooling was obtained from American Communities Survey, US Census Bureau.

Raw Data is in:

E:\McNair\Projects\Hubs\Summer 2017\School Enrollment Data by MSA

Cleaned files are in

Z:\Hubs\2017\clean data

They contain:

• MSA code
• MSA
• Year
• Total number of population 3 years and over enrolled in school
• Percent of population 3 years and over enrolled in public school
• Percent of population 3 years and over enrolled in private school

Date from 2005-2015

The SQL file that merges schooling data from ACS (by MSA - Year) with the MSA-City file is titled schooling.sql. The file is located in:

Z:\Hubs\2017

The final table is in db cities titled merged_schooling.

It includes:

• MSA
• City
• Year
• Total
• Percent_public_schooling
• Percent_private_schooling

Joined schooling table

Data is in:

Z:\Hubs\clean data

The file names are:

SCH_05.txt - SCH_15.txt

Database is cities SQL script which joins this table with VC table is: new_merged_schooling.sql The final table is in db cities titled new_merged_schooling.

It contains:

• City
• State
• city_state_id to uniquely identify each city
• city_state_year to uniquely identify each city in each year
• Total number of school enrollment
• Percentage enrolled in public schools
• Percentage enrolled in private schools
• Year
• Code from the state code and Fips code

VC Data

Raw Data is in:

 Z:\VentureCapitalData\SDCVCData
 The file name is roundcitystateyear.txt

It contains:

• city
• state
• year
• seedamtm - seed, amount in millions
• earlyamtm - early, amount in millions
• lateramtm - late, amount in millions
• selamtm - seed early late, amount in millions
• numseeds - number of seeds
• numearly
• numlater
• numsel

Date from 1953-2017

The table is in db cities titled vc.

It includes:

• city
• state
• city_state_id
• city_state_year
• seedamtm
• earlyamtm
• lateramtm
• selamtm
• numseeds
• numearly
• numlater
• numsel
• year