### Create a data frame with the sectors and sector names
sectors <- data.frame(cbind(
c("101", "1011", "1012", "1013", "102", "1021", "1022",
"1023", "1024", "1025", "1026", "1027", "1028", "1029"),
c("Producing", "NatRes", "Construction", "Manufacturing",
"Services", "Trade", "Information", "Financial", "Professional",
"EducationHealth", "Hospitality", "Other",
"Public", "Unclassified")))
names(sectors) <- c("sector", "description")
### Use the BLS R function to access the industry-specific api - their notes below
# ******************************************************************************************
# qcewGetIndustryData : This function takes a year, quarter, and industry code
# and returns an array containing the associated industry data. Use 'a' for
# annual averages. Some industry codes contain hyphens. The CSV files use
# underscores instead of hyphens. So 31-33 becomes 31_33.
# For all industry codes and titles see:
# http://www.bls.gov/cew/doc/titles/industry/industry_titles.htm
qcewGetIndustryData <- function (year, qtr, industry) {
url <- "http://www.bls.gov/cew/data/api/YEAR/QTR/industry/INDUSTRY.csv"
url <- sub("YEAR", year, url, ignore.case=FALSE)
url <- sub("QTR", qtr, url, ignore.case=FALSE)
url <- sub("INDUSTRY", industry, url, ignore.case=FALSE)
read.csv(url, header = TRUE, sep = ",", quote="\"", dec=".", na.strings=" ", skip=0)
}
### Use this loop to go through the sectors loaded in the data frame
for (i in 1:length(sectors[,1])) {
if (i == 1) {
dt1 <- qcewGetIndustryData("2015", "3", sectors[1,1])
dt1 <- aggregate(dt1$month2_emplvl, by=list(Category=dt1$area_fips), FUN=sum)
names(dt1) <- c("area_fips", as.character(sectors[1,2]))
} else if (i == 2) {
print(paste("getting data for ", sectors[i,2]))
dt2 <- qcewGetIndustryData("2015", "3", sectors[i,1])
dt2 <- aggregate(dt2$month2_emplvl, by=list(Category=dt2$area_fips), FUN=sum)
names(dt2) <- c("area_fips", as.character(sectors[i,2]))
data <- join(dt1, dt2, type = "inner")
} else {
print(paste("getting data for ", sectors[i,2]))
dt3 <- qcewGetIndustryData("2015", "3", sectors[i,1])
dt3 <- aggregate(dt3$month2_emplvl, by=list(Category=dt3$area_fips), FUN=sum)
names(dt3) <- c("area_fips", as.character(sectors[i,2]))
data <- join(data, dt3, type = "inner")
}
}
## [1] "getting data for NatRes"
## Joining by: area_fips
## [1] "getting data for Construction"
## Joining by: area_fips
## [1] "getting data for Manufacturing"
## Joining by: area_fips
## [1] "getting data for Services"
## Joining by: area_fips
## [1] "getting data for Trade"
## Joining by: area_fips
## [1] "getting data for Information"
## Joining by: area_fips
## [1] "getting data for Financial"
## Joining by: area_fips
## [1] "getting data for Professional"
## Joining by: area_fips
## [1] "getting data for EducationHealth"
## Joining by: area_fips
## [1] "getting data for Hospitality"
## Joining by: area_fips
## [1] "getting data for Other"
## Joining by: area_fips
## [1] "getting data for Public"
## Joining by: area_fips
## [1] "getting data for Unclassified"
## Joining by: area_fips
# Assign row names and column names for reference through the transformations
rownames(data) <- data$area_fips
colnames(data) <- c("FIPS", "Producing", "NatRes", "Construction", "Manufacturing",
"Services", "Trade", "Information", "Financial", "Professional",
"EducationHealth", "Hospitality", "Other",
"Public", "Unclassified")
# Check out the data
str(data)
## 'data.frame': 1349 obs. of 15 variables:
## $ FIPS : Factor w/ 3714 levels "01000","01001",..: 1 13 22 26 38 50 53 69 70 71 ...
## $ Producing : int 359031 1388 1506 7582 40293 30300 14716 7120 61363 0 ...
## $ NatRes : int 18433 258 117 344 1739 1007 139 306 18954 0 ...
## $ Construction : int 82105 0 101 665 14895 9618 2788 5832 21035 0 ...
## $ Manufacturing : int 258493 0 1288 6573 23659 19675 11789 982 21374 0 ...
## $ Services : int 1327260 1896 2302 13129 272504 136552 31898 40524 264618 256 ...
## $ Trade : int 398822 706 671 4275 74250 37869 8931 13566 73298 151 ...
## $ Information : int 22146 18 0 175 6368 1838 266 998 6325 15 ...
## $ Financial : int 95179 92 117 564 26570 7762 1720 2803 12715 0 ...
## $ Professional : int 233037 78 72 951 43793 21494 5097 18866 31908 28 ...
## $ EducationHealth: int 411746 615 818 3834 86170 36789 8065 2320 65835 0 ...
## $ Hospitality : int 200315 120 173 1720 34466 16984 4608 3 43582 38 ...
## $ Other : int 45774 85 156 237 9605 5631 892 1173 10014 0 ...
## $ Public : int 124845 161 244 1074 17051 7431 2163 322 42611 189 ...
## $ Unclassified : int 23 1 0 2 8 1 1 0 433 2 ...
#####################################
# Now we go to the LQ functions ####
#####################################
# By Pierre-Alexandre Balland
# Department of Economic Geography, Utrecht University
# May 5, 2013
# This R script returns a location quotient (indicates the concentration of a particular technological classes, sectors, employment categories
# in a given spatial unit. This is a ratio
# As an input you just need a unit-by-region matrix in which the cells indicate the number of patents (or firms, or employees)
# a given spatial unit (a city, a country) has in a given economic unit.
### Convert the data frame to a matrix and take a look
data <- data.matrix( data[ ,2:length(data[1,]) ] )
head(data)
## Producing NatRes Construction Manufacturing Services Trade
## 01000 359031 18433 82105 258493 1327260 398822
## 01023 1388 258 0 0 1896 706
## 01041 1506 117 101 1288 2302 671
## 01049 7582 344 665 6573 13129 4275
## 01073 40293 1739 14895 23659 272504 74250
## 01097 30300 1007 9618 19675 136552 37869
## Information Financial Professional EducationHealth Hospitality Other
## 01000 22146 95179 233037 411746 200315 45774
## 01023 18 92 78 615 120 85
## 01041 0 117 72 818 173 156
## 01049 175 564 951 3834 1720 237
## 01073 6368 26570 43793 86170 34466 9605
## 01097 1838 7762 21494 36789 16984 5631
## Public Unclassified
## 01000 124845 23
## 01023 161 1
## 01041 244 0
## 01049 1074 2
## 01073 17051 8
## 01097 7431 1
# Calculate the
share_tech_city <- data / rowSums (data)
# The share of agriculture in London is 0.048, so agriculture accounts for about 5% of the economic activity
head(share_tech_city )
## Producing NatRes Construction Manufacturing Services
## 01000 0.10036624 0.005152900 0.02295225 0.07226108 0.3710323
## 01023 0.25618309 0.047619048 0.00000000 0.00000000 0.3499446
## 01041 0.19907469 0.015465962 0.01335096 0.17025777 0.3042961
## 01049 0.18436474 0.008364742 0.01617021 0.15982979 0.3192462
## 01073 0.06185876 0.002669753 0.02286715 0.03632185 0.4183545
## 01097 0.09100438 0.003024469 0.02888713 0.05909278 0.4101264
## Trade Information Financial Professional EducationHealth
## 01000 0.11148971 0.006190860 0.02660706 0.065144922 0.11510258
## 01023 0.13030639 0.003322259 0.01698044 0.014396456 0.11351052
## 01041 0.08869795 0.000000000 0.01546596 0.009517515 0.10812954
## 01049 0.10395137 0.004255319 0.01371429 0.023124620 0.09322796
## 01073 0.11399034 0.009776303 0.04079089 0.067232038 0.13229020
## 01097 0.11373746 0.005520332 0.02331274 0.064556046 0.11049374
## Hospitality Other Public Unclassified
## 01000 0.05599757 0.012796009 0.03490011 6.429594e-06
## 01023 0.02214839 0.015688446 0.02971576 1.845700e-04
## 01041 0.02286847 0.020621282 0.03225380 0.000000e+00
## 01049 0.04182371 0.005762918 0.02611550 4.863222e-05
## 01073 0.05291301 0.014745821 0.02617709 1.228179e-05
## 01097 0.05101051 0.016912399 0.02231860 3.003445e-06
share_tech_total <- colSums (data) / sum (data)
# At the international level, agriculture accounts for 10 % of the economic activities (0.104)
head(share_tech_total)
## Producing NatRes Construction Manufacturing Services
## 0.075749961 0.007423578 0.024575691 0.043589253 0.420922368
## Trade
## 0.101410482
LQ <- t(share_tech_city)/ share_tech_total
LQ[is.na(LQ)] <- 0
LQ[which(LQ==Inf)] <- 0
LQ <- t(LQ)
# here we go, this is the location quotient for each spatial unit - economic unit pair
# The LQ for London is 0.459 (0.048/0.104), which means that agriculture is less concentrated in London than worldwide (50% less)
head(LQ)
## Producing NatRes Construction Manufacturing Services Trade
## 01000 1.3249675 0.6941262 0.9339413 1.6577729 0.8814744 1.0993904
## 01023 3.3819568 6.4145684 0.0000000 0.0000000 0.8313757 1.2849400
## 01041 2.6280500 2.0833569 0.5432587 3.9059574 0.7229269 0.8746428
## 01049 2.4338592 1.1267804 0.6579759 3.6667246 0.7584444 1.0250555
## 01073 0.8166177 0.3596316 0.9304785 0.8332754 0.9938995 1.1240489
## 01097 1.2013786 0.4074139 1.1754353 1.3556731 0.9743517 1.1215552
## Information Financial Professional EducationHealth Hospitality
## 01000 0.5677909 0.9076670 0.8832177 1.0490198 0.9472863
## 01023 0.3046989 0.5792667 0.1951834 1.0345100 0.3746747
## 01041 0.0000000 0.5276023 0.1290360 0.9854690 0.3868559
## 01049 0.3902739 0.4678460 0.3135175 0.8496593 0.7075133
## 01073 0.8966275 1.3915309 0.9115143 1.2056639 0.8951062
## 01097 0.5062938 0.7952854 0.8752339 1.0070157 0.8629224
## Other Public Unclassified
## 01000 0.7956769 1.3367894 0.004848820
## 01023 0.9755334 1.1382116 0.139191768
## 01041 1.2822653 1.2354269 0.000000000
## 01049 0.3583477 1.0003098 0.036675550
## 01073 0.9169194 1.0026690 0.009262199
## 01097 1.0516408 0.8548759 0.002265021