# Importing the first 2 sheets from the Excel file
library(readxl)
summary1.old <- read_excel("Downloads/Jacksonville Exercise Data1.xlsx",
sheet = "Summary I")
## New names:
## • `Change in Homeownership Rate` -> `Change in Homeownership Rate...17`
## • `Change in Homeownership Rate` -> `Change in Homeownership Rate...19`
summary2.old <- read_excel("Downloads/Jacksonville Exercise Data1.xlsx",
sheet = "Summary II")
# Indexing Rows 1-5 to only get information for Jacksonville, Atlanta, Miami, New Orleans, and Birmingham
summary1 <- summary1.old[c(1:5), ]
summary2 <- summary2.old[c(1:5), ]
# Summary I:
# The goal here is to calculate the percentage difference between Jacksonville and the other cities' values. From there I will be able to compare which cities have the values that are the most similar.
# So I created a function that would calculate the percentage difference between the values in a column from the first row's value (representing Jacksonville).
calculate_percentage_diff <- function(column) {
first_value <- column[1]
percentage_difference <- abs((column - first_value) / first_value) * 100
return(percentage_difference)
}
# I am now applying the function I've created above to the entirety of the Summary I sheet
summ1 <- sapply(summary1, is.numeric)
summary1[summ1] <- lapply(summary1[summ1], calculate_percentage_diff)
summary1[, 3:18] <- round(summary1[summ1][, 3:18])
summary1$`Change in Homeownership Rate...19` <- round(summary1$`Change in Homeownership Rate...19`)
print(summary1)
## # A tibble: 5 × 19
## FIPS Metro `GRP Growth '16 - 21` `Employment Growth` Population Growth '1…¹
## <dbl> <chr> <dbl> <dbl> <dbl>
## 1 0 Jackso… 0 0 0
## 2 55.8 Atlant… 14 48 9
## 3 21.4 Miami,… 46 97 9
## 4 29.8 New Or… 128 118 21
## 5 49.3 Birmin… 73 75 13
## # ℹ abbreviated name: ¹​`Population Growth '17 - '22`
## # ℹ 14 more variables: `Median HH Income '22` <dbl>,
## # `Change in Median HH Income` <dbl>, `Poverty Rate '22` <dbl>,
## # `Change in Poverty Rate` <dbl>,
## # `Gross Regional Product (Per Capita)` <dbl>,
## # `Change in Gross Regional Product (Per Capita)` <dbl>,
## # `Unemployment Rate` <dbl>, `Change in Unemployment Rate` <dbl>, …
# Now that I have a dataset showing the percentage difference of certain criteria between certain cities and Jacksonville, I'm going to add up all the values for percentage difference together to see which has the smallest difference from Jacksonville, which would have 0 as the total.
summary1 <- summary1[,c(2:16)]
summary1$`Total % Diff`<- rowSums(summary1[, 2:15])
print(summary1[,c(1,16)])
## # A tibble: 5 × 2
## Metro `Total % Diff`
## <chr> <dbl>
## 1 Jacksonville, FL 0
## 2 Atlanta, GA 261
## 3 Miami, FL 542
## 4 New Orleans, LA 702
## 5 Birmingham, AL 706
# As we see from the "Total % Diff" column, Atlanta, Georgia seems to be the closest MSA to Jacksonville because it has the smallest percentage differences, according to Summary I.
# When utilizing this method to recommend that Atlanta is the MSA based on the information from Summary I, it is important to assess any outliers in the individual percentage difference calculations.
# Some potential outliers, columns for which Atlanta wasn't the closest to Jacksonville's value, are "Change in Poverty Rate" and "Labor Participation Rate".
# Change in Poverty Rate: Atlanta's Change in Poverty Rate is significantly greater than Jacksonville's (25 vs 0); however, I don't think this will invalidate our hypothesis that Atlanta is the closest MSA because Atlanta still has the closest 2022 unemployment rate to Jacksonville's.
# Labor Participation Rate: Atlanta's Labor Participation Rate is almost double that of Jacksonville, which is a significant difference. This is an area to further analyze as Atlanta and Jacksonville's citizens may not be comparable on an individual level, but based on Summary I they are comparable cities.
# Summary II:
# This data sheet shows information about education levels and wages. I will repeat the same percentage differences method that I used for Summary I.
summ2 <- sapply(summary2, is.numeric)
summary2[summ2] <- lapply(summary2[summ2], calculate_percentage_diff)
summary2[, 3:7] <- round(summary2[summ2][, 3:7])
summary2$`Wages - Top 10% versus Bottom` <- round(summary2$`Wages - Top 10% versus Bottom`)
print(summary2)
## # A tibble: 5 × 8
## FIPS Metro % of Residents Age 25+ with As…¹ Change in Associate'…²
## <dbl> <chr> <dbl> <dbl>
## 1 0 Jacksonville, FL 0 0
## 2 55.8 Atlanta, GA 118 15
## 3 21.4 Miami, FL 115 2
## 4 29.8 New Orleans, LA 359 5
## 5 49.3 Birmingham, AL 476 1
## # ℹ abbreviated names: ¹​`% of Residents Age 25+ with Associate's Degree`,
## # ²​`Change in Associate's Degree`
## # ℹ 4 more variables: `% of Residents Age 25+ with Bachelor's Degree` <dbl>,
## # `Change in Bachelor's Degree` <dbl>, `Edu Migration` <dbl>,
## # `Wages - Top 10% versus Bottom` <dbl>
summary2 <- summary2[,c(3:8)]
summary2$`Total % Diff`<- rowSums(summary2[, 1:6])
print(summary2[,c(7)])
## # A tibble: 5 × 1
## `Total % Diff`
## <dbl>
## 1 0
## 2 279
## 3 233
## 4 524
## 5 600
# Here we can see that Miami, Florida is the most similar on these factors to Jacksonville; however, Atlanta isn't very far off.
# I'm going to now compare the different percentage of degree holders between Jacksonville and Atlanta, to see exactly how different Atlanta is than Jacksonville.
summary2.old$`% of Residents Age 25+ without a Degree`<- 1-summary2.old$`Change in Associate's Degree`- summary2.old$`Change in Bachelor's Degree`
# I now am getting a data set that shows % of people with associates, % of people with bachelors, and % of people without a degree.
summary2.old <- summary2.old[c(1:2), ]
category_sum <- colSums(summary2.old[, c("% of Residents Age 25+ with Associate's Degree", "% of Residents Age 25+ with Bachelor's Degree", "% of Residents Age 25+ without a Degree")])
educ <- summary2.old[, c(3,5,9)]
educ_matrix <- as.matrix(educ)
# Now I am creating a segmented bar chart (Blue is Jacksonville's values and Red is Atlanta's values)
barplot(educ_matrix, beside = TRUE, col = c("blue", "red"),
main = "Comparison of Degree Holders in Jacksonville vs Atlanta",
xlab = " ", ylab = "Percentage (%)",
legend.text = rownames(educ_matrix))
# This shows that Atlanta may have a higher proportion of educated citizens and this is further shown in the differences in "Edu Migration", Atlanta has a higher value likely because it is a bigger city where more educated people move to.
# Final Conclusion: Atlanta, Georgia is the best peer MSA for Jacksonville, Florida. They seem to have very similar economic situations; however, vary on issues of wealth inequality and education.
# Atlanta has a higher proportion of more educated individuals than Florida, which is why the difference in wage between top 10% and bottom 10% is greater in ATL and could potentially explain the higher poverty rate.
# As Atlanta continues to develop in the future with more educated individuals migrating there, the city might not be as good of a peer MSA for Jacksonville and these factors would need to be reassesed.