library(DBI)
library(RSQLite)
library(ggplot2)
driver <- dbDriver("SQLite")
connect <- dbConnect(driver, dbname = "state_M2012_dl.db")
occupations = dbGetQuery(connect, "select * from occupations")
occupations$STATE = factor(occupations$STATE)
occupations$OCC_TITLE = factor(occupations$OCC_TITLE)
occupations$OCC_GROUP = factor(occupations$OCC_GROUP)
occupations$REGION = factor(occupations$REGION)
summary(occupations)
## STATE
## Alabama : 22
## Alaska : 22
## Arizona : 22
## Arkansas : 22
## California: 22
## Colorado : 22
## (Other) :1045
## OCC_TITLE
## Architecture and Engineering Occupations : 54
## Arts, Design, Entertainment, Sports, and Media Occupations: 54
## Building and Grounds Cleaning and Maintenance Occupations : 54
## Business and Financial Operations Occupations : 54
## Community and Social Service Occupations : 54
## Computer and Mathematical Occupations : 54
## (Other) :853
## OCC_GROUP TOT_EMP JOBS_1000 LOC_Q
## major:1177 Min. : 210 Min. : 0.41 Min. :0.130
## 1st Qu.: 17080 1st Qu.: 16.77 1st Qu.:0.840
## Median : 49490 Median : 34.84 Median :0.970
## Mean : 111483 Mean : 45.60 Mean :0.999
## 3rd Qu.: 123910 3rd Qu.: 61.71 3rd Qu.:1.090
## Max. :2403360 Max. :200.94 Max. :7.780
##
## A_MEAN A_MEDIAN REGION
## Min. : 18070 Min. : 17790 Midwest :264
## 1st Qu.: 31260 1st Qu.: 27240 Northeast:196
## Median : 40860 Median : 37350 Other : 59
## Mean : 48065 Mean : 42644 South :372
## 3rd Qu.: 62760 3rd Qu.: 55090 West :286
## Max. :144010 Max. :136780
##
# ------See for each major occupation category, the annual salary in each
# state (repeatitive barcharts, color divided by states)-----------
library(plyr)
options(scipen = 10)
for (i in levels(occupations$OCC_TITLE)) {
occupations_subclass = subset(occupations, OCC_TITLE == i)
chart2_data = ddply(occupations_subclass, c("STATE", "REGION"), summarise,
mediumsalary = mean(A_MEDIAN))
chart2 <- ggplot(chart2_data, aes(x = reorder(STATE, -mediumsalary), y = mediumsalary,
fill = factor(REGION)))
forprint2 <- chart2 + geom_bar(stat = "identity", alpha = I(0.8)) + guides(col = guide_legend(ncol = 2)) +
labs(x = "State", y = "Medium Salary", title = i) + theme(axis.text.x = element_text(angle = 90,
hjust = 1))
print(forprint2)
}
# ------Facet Graph about for each state, annual salary of each major
# occupation (facet barcharts, color divided by major
# occupations)-----------
occu_in_states <- aggregate.data.frame(occupations$A_MEDIAN, by = list(state = occupations$STATE,
jobs = occupations$OCC_TITLE), FUN = mean, simplify = TRUE)
options(scipen = 10)
facets <- ggplot(occu_in_states, aes(x = jobs, y = x, group = jobs, fill = factor(jobs)))
facets + geom_bar(stat = "identity", alpha = I(0.8)) + guides(col = guide_legend(ncol = 2)) +
labs(x = "Major Occupations", y = "Medium Salary") + facet_wrap(~state)
# ------For each major occupation category, see each detail category's
# salary (repetitive boxplots, color divided by detail
# occupations)-----------
connect2 <- dbConnect(driver, dbname = "state_M2012_dl_2.db")
occupations2 = dbGetQuery(connect2, "select * from occupations2")
occupations2 <- na.omit(occupations2)
occupations2$STATE = factor(occupations2$STATE)
occupations2$OCC_TITLE = factor(occupations2$OCC_TITLE)
occupations2$OCC_GROUP = factor(occupations2$OCC_GROUP)
occupations2$OCC_CODE = factor(occupations2$OCC_CODE)
occupations2$MAJOR_OCC = factor(occupations2$MAJOR_OCC)
summary(occupations2)
## STATE OCC_CODE
## California : 814 11-0000: 54
## Texas : 788 11-1021: 54
## New York : 778 11-2022: 54
## Pennsylvania: 777 11-3011: 54
## Florida : 775 11-3021: 54
## Illinois : 769 11-3031: 54
## (Other) :30993 (Other):35370
## OCC_TITLE
## Accountants and Auditors : 54
## Administrative Services Managers : 54
## Architecture and Engineering Occupations : 54
## Arts, Design, Entertainment, Sports, and Media Occupations: 54
## Automotive Service Technicians and Mechanics : 54
## Bakers : 54
## (Other) :35370
## OCC_GROUP TOT_EMP JOBS_1000 LOC_Q
## detailed:34517 Min. : 30 Min. : 0.00 Min. : 0.01
## major : 1177 1st Qu.: 240 1st Qu.: 0.16 1st Qu.: 0.66
## Median : 790 Median : 0.49 Median : 0.94
## Mean : 7315 Mean : 2.98 Mean : 1.16
## 3rd Qu.: 2940 3rd Qu.: 1.50 3rd Qu.: 1.27
## Max. :2403360 Max. :200.94 Max. :120.82
##
## A_MEAN A_MEDIAN MAJOR_OCC
## Min. : 16640 Min. : 16450 51 : 4364
## 1st Qu.: 31440 1st Qu.: 29440 25 : 2855
## Median : 43190 Median : 40870 43 : 2841
## Mean : 49701 Mean : 46902 29 : 2571
## 3rd Qu.: 61470 3rd Qu.: 58160 47 : 2341
## Max. :204950 Max. :187040 49 : 2214
## (Other):18508
for (i in levels(occupations2$MAJOR_OCC)) {
major_subset = subset(occupations2, MAJOR_OCC == i, select = c(OCC_TITLE,
A_MEDIAN))
boxplot <- ggplot(major_subset, aes(factor(OCC_TITLE), A_MEDIAN))
if (i == "11") {
major_name = "Management Occupations"
} else if (i == "13") {
major_name = "Business and Financial Operations Occupations"
} else if (i == "15") {
major_name = "Computer and Mathematical Occupations"
} else if (i == "17") {
major_name = "Architecture and Engineering Occupations"
} else if (i == "19") {
major_name = "Life, Physical, and Social Science Occupations"
} else if (i == "21") {
major_name = "Community and Social Service Occupations"
} else if (i == "23") {
major_name = "Legal Occupations"
} else if (i == "25") {
major_name = "Education, Training, and Library Occupations"
} else if (i == "27") {
major_name = "Arts, Design, Entertainment, Sports, and Media Occupations"
} else if (i == "29") {
major_name = "Healthcare Practitioners and Technical Occupations"
} else if (i == "31") {
major_name = "Healthcare Support Occupations"
} else if (i == "33") {
major_name = "Protective Service Occupations"
} else if (i == "35") {
major_name = "Food Preparation and Serving Related Occupations"
} else if (i == "37") {
major_name = "Building and Grounds Cleaning and Maintenance Occupations"
} else if (i == "39") {
major_name = "Personal Care and Service Occupations"
} else if (i == "41") {
major_name = "Sales and Related Occupations"
} else if (i == "43") {
major_name = "Office and Administrative Support Occupations"
} else if (i == "45") {
major_name = "Farming, Fishing, and Forestry Occupations"
} else if (i == "47") {
major_name = "Construction and Extraction Occupations"
} else if (i == "49") {
major_name = "Installation, Maintenance, and Repair Occupations"
} else if (i == "51") {
major_name = "Production Occupations"
} else if (i == "53") {
major_name = "Transportation and Material Moving Occupations"
} else {
major_name = "Others"
}
forprint <- boxplot + geom_boxplot(fill = "#6CCCDD", colour = "#1C7F9E",
alpha = I(0.5)) + guides(guide_legend = "none") + labs(x = "Detail Occupations",
y = "Annual Salary", title = major_name) + theme(axis.text.x = element_text(angle = 90,
hjust = 1))
print(forprint)
}
# ------For each major occupation category, see each detail category's
# salary (Cluster Analysis)-----------