I. Load relevant packages

library(tidyverse)
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.4     ✔ readr     2.1.5
## ✔ forcats   1.0.0     ✔ stringr   1.5.1
## ✔ ggplot2   3.5.0     ✔ tibble    3.2.1
## ✔ lubridate 1.9.3     ✔ tidyr     1.3.1
## ✔ purrr     1.0.2     
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(conflicted)
library(dplyr)
conflicts_prefer(dplyr::filter)
## [conflicted] Will prefer dplyr::filter over any other package.
library(readxl)
library(ggplot2)

II. Read, clean, and subset data for Jacksonville, FL and Atlanta, GA

Here I read in the Excel data provided for this exercise, clean it for any rows that contain NA (or no value), and subset the data to compare the Jacksonville, FL statistics to the comparable metropolitan statistical area (MSA) of Atlanta, GA.

Summary_I <- read_excel("Jacksonville Exercise Data.xlsx", sheet=1)
## New names:
## • `Change in Homeownership Rate` -> `Change in Homeownership Rate...17`
## • `Change in Homeownership Rate` -> `Change in Homeownership Rate...19`
Summary_II <- read_excel("Jacksonville Exercise Data.xlsx", sheet=2)

Summary_I <- Summary_I[complete.cases(Summary_I), ] %>%
  filter(FIPS == "27260" | FIPS == "12060")
Summary_II <- Summary_II[complete.cases(Summary_II), ] %>%
  filter(FIPS == "27260" | FIPS == "12060")

III. Results from the Summary I statistics page

The first step is to reshape the dataframe to properly visualize the data in a bar plot. We do this by pivoting the columns for the data metrics into one column that specifies the metric used in each city.

# Load the tidyr package
library(tidyr)

# Reshape the columns into new variables labeled "Measure" and "Value"
Summary_I_long <- Summary_I %>%
  pivot_longer(cols = c(`GRP Growth '16 - 21`, 
                        `Employment Growth`, 
                        `Population Growth '17 - '22`, 
                        `Median HH Income '22`, 
                        `Change in Median HH Income`, 
                        `Poverty Rate '22`, 
                        `Change in Poverty Rate`, 
                        `Gross Regional Product (Per Capita)`, 
                        `Change in Gross Regional Product (Per Capita)`, 
                        `Unemployment Rate`, 
                        `Change in Unemployment Rate`, 
                        `Labor Participation Rate`, 
                        `Change in Labor Force`, 
                        `Homeownership Rate`, 
                        `Change in Homeownership Rate...17`, 
                        `Cost Burdened`, 
                        `Change in Homeownership Rate...19`), 
               names_to = "Measure", 
               values_to = "Value")

Summary_I_long$Value <- round(Summary_I_long$Value, digits = 3)

The next step is to visualize our data metrics. The first plot shows changes in Gross Regional Product (GRP), Employment, and Population in Jacksonville and Atlanta. These are categorized as Dynamism metrics in the dataset.

# Filter the data frame to include only desired measures
Plot_1 <- Summary_I_long %>%
  filter(Measure %in% c("GRP Growth '16 - 21", "Employment Growth", "Population Growth '17 - '22"))

# Create a bar plot with y-axis values as percentages and fill with Metro
p1 <- ggplot(Plot_1, aes(x = Measure, y = Value * 100, fill = Metro)) +
  geom_bar(stat = "identity", position = "dodge") +
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  labs(x = "Metric", y = "Value (%)", fill = "Metro") +
  ggtitle("Plot 1: GRP, Employment, and Population changes 
          in Jacksonville, FL and Atlanta, GA")

# Add text labels for values on top of each bar
p1 + geom_text(aes(label = paste0(round(Value * 100, 2), "%"),
                 y = Value * 100),
              position = position_dodge(width = 0.9), vjust = -0.5)

The second plot shows two Prosperity metrics for Median Household (HH) Income in 2022 and GRP per capita in Jacksonville and Atlanta.

# Filter the data frame to include only desired measures
Plot_2 <- Summary_I_long %>%
  filter(Measure %in% c("Median HH Income '22", "Gross Regional Product (Per Capita)"))

# Create a bar plot with y-axis values as dollars and fill with Metro
p2 <- ggplot(Plot_2, aes(x = Measure, y = Value, fill = Metro)) +
  geom_bar(stat = "identity", position = "dodge") +
  scale_y_continuous(labels = scales::dollar_format()) +
  labs(x = "Metric", y = "Value ($)", fill = "Metro") +
  ggtitle("Plot 2: Median HH Income and Gross Regional Product (per cap.) 
          in Jacksonville, FL and Atlanta, GA")

# Add text labels for values on top of each bar
p2 + geom_text(aes(label = paste0("$", format(Value, big.mark = ",", scientific = FALSE)),
                 y = Value),
              position = position_dodge(width = 0.9), vjust = -0.5)

The third plot shows even more Prosperity metrics including Changes in Median HH Income, the Poverty Rate in 2022, the Change in Poverty Rate, and the Change in Gross Regional Product per capita in Jacksonville and Atlanta.

# Filter the data frame to include only desired measures
Plot_3 <- Summary_I_long %>%
  filter(Measure %in% c("Change in Median HH Income", 
                        "Poverty Rate '22", 
                        "Change in Poverty Rate", 
                        "Change in Gross Regional Product (Per Capita)"))

# Create a bar plot with y-axis values as percentages and fill with Metro
p3 <- ggplot(Plot_3, aes(x = str_wrap(Measure, width = 15), y = Value * 100, fill = Metro)) +
  geom_bar(stat = "identity", position = "dodge") +
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  labs(x = "Metric", y = "Value (%)", fill = "Metro") +
  ggtitle("Plot 3: Median HH Income, Poverty Rate, and GRP 
          per cap. in Jacksonville, FL and Atlanta, GA")

# Add text labels for values on top of each bar
p3 + geom_text(aes(label = paste0(round(Value * 100, 2), "%"),
                 y = Value * 100),
              position = position_dodge(width = 0.9), vjust = -0.5)

The fourth plot shows two Economic Opportunity metrics in Jacksonville and Atlanta: Unemployment Rate and changes in the Unemployment Rate.

# Filter the data frame to include only desired measures
Plot_4 <- Summary_I_long %>%
  filter(Measure %in% c("Unemployment Rate", "Change in Unemployment Rate"))

# Create a bar plot with y-axis values as percentages and fill with Metro
p4 <- ggplot(Plot_4, aes(x = Measure, y = Value * 100, fill = Metro)) +
  geom_bar(stat = "identity", position = "dodge") +
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  labs(x = "Metric", y = "Value (%)", fill = "Metro") +
  ggtitle("Plot 4: Unemployment in Jacksonville, FL and Atlanta, GA")

# Add text labels for values on top of each bar
p4 + geom_text(aes(label = paste0(round(Value * 100, 2), "%"),
                 y = Value * 100),
              position = position_dodge(width = 0.9), vjust = -0.5)

The fifth plot shows two more Economic Opportunity metrics in Jacksonville and Atlanta: Labor Force Participation Rate and Changes in the Labor Force.

# Filter the data frame to include only desired measures
Plot_5 <- Summary_I_long %>%
  filter(Measure %in% c("Labor Participation Rate", "Change in Labor Force"))

# Create a bar plot with y-axis values as percentages and fill with Metro
p5 <- ggplot(Plot_5, aes(x = Measure, y = Value * 100, fill = Metro)) +
  geom_bar(stat = "identity", position = "dodge") +
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  labs(x = "Metric", y = "Value (%)", fill = "Metro") +
  ggtitle("Plot 5: Labor Force Participation in 
          Jacksonville, FL and Atlanta, GA")

# Add text labels for values on top of each bar
p5 + geom_text(aes(label = paste0(round(Value * 100, 2), "%"),
                 y = Value * 100),
              position = position_dodge(width = 0.9), vjust = -0.5)

The sixth plot shows two Housing metrics for the Homeownership Rate and Changes in the Homeownership Rate in Jacksonville and Atlanta. The second variable, “Change in Homeownership Rate…17” had a duplicate name in the data (along with the same variable name in Plot 7), so I reformatted it to a discrete label in the x-axis.

# Filter the data frame to include only desired measures
Plot_6 <- Summary_I_long %>%
  filter(Measure %in% c("Homeownership Rate", "Change in Homeownership Rate...17"))

# Create a bar plot with y-axis values as percentages and fill with Metro
p6 <- ggplot(Plot_6, aes(x = Measure, y = Value * 100, fill = Metro)) +
  geom_bar(stat = "identity", position = "dodge") +
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  labs(x = "Metric", y = "Value (%)", fill = "Metro") +
  ggtitle("Plot 6: Homeownership in Jacksonville, FL and Atlanta, GA") +
  scale_x_discrete(labels = c("Change in Homeownership Rate", "Homeownership Rate"))

# Add text labels for values on top of each bar
p6 + geom_text(aes(label = paste0(round(Value * 100, 2), "%"),
                 y = Value * 100),
              position = position_dodge(width = 0.9), vjust = -0.5)

The seventh plot shows two more Housing metrics including Percent of Cost Burdened Households and Changes in the Cost Burdened Household Rate in Jacksonville and Atlanta. The second variable, “Change in Homeownership Rate…19” had a duplicate name in the data (along with the same variable name in Plot 6), so I reformatted it to a discrete label in the x-axis.

# Filter the data frame to include only desired measures
Plot_7 <- Summary_I_long %>%
  filter(Measure %in% c("Cost Burdened", "Change in Homeownership Rate...19"))

# Create a bar plot with y-axis values as percentages and fill with Metro
p7 <- ggplot(Plot_7, aes(x = Measure, y = Value * 100, fill = Metro)) +
  geom_bar(stat = "identity", position = "dodge") +
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  labs(x = "Metric", y = "Value (%)", fill = "Metro") +
  ggtitle("Plot 7: Cost burdened households in 
          Jacksonville, FL and Atlanta, GA") +
  scale_x_discrete(labels = c("Change in Cost Burdened", "Cost Burdened"))

# Add text labels for values on top of each bar
p7 + geom_text(aes(label = paste0(round(Value * 100, 2), "%"),
                 y = Value * 100),
              position = position_dodge(width = 0.9), vjust = -0.5)

IV. Results from the Summary II statistics page

Like in Part III, the first step is to reshape the dataframe to properly visualize the data in a bar plot. We do this by pivoting the columns for the data metrics into one column that specifies the metric used in each city.

# Reshape the columns into new variables labeled "Measure" and "Value"
Summary_II_long <- Summary_II %>%
  pivot_longer(cols = c(`% of Residents Age 25+ with Associate's Degree`, 
                        `Change in Associate's Degree`, 
                        `% of Residents Age 25+ with Bachelor's Degree`, 
                        `Change in Bachelor's Degree`, 
                        `Edu Migration`, 
                        `Wages - Top 10% versus Bottom`),
               names_to = "Measure", 
               values_to = "Value")

Summary_II_long$Value <- round(Summary_II_long$Value, digits = 3)

The next step is to visualize our data metrics. This plot shows Talent metrics such as Education Levels by Attainment of Associate’s and Bachelor’s degrees along with changes in such degree attainments in Jacksonville and Atlanta.

# Filter the data frame to include only desired measures
Plot_8 <- Summary_II_long %>%
  filter(Measure %in% c("% of Residents Age 25+ with Associate's Degree", 
                        "Change in Associate's Degree",
                        "% of Residents Age 25+ with Bachelor's Degree",
                        "Change in Bachelor's Degree"))

# Create a bar plot with y-axis values as percentages and fill with Metro
p8 <- ggplot(Plot_8, aes(x = str_wrap(Measure, width = 15), y = Value * 100, fill = Metro)) +
  geom_bar(stat = "identity", position = "dodge") +
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  labs(x = "Metric", y = "Value (%)", fill = "Metro") +
  ggtitle("Plot 8: Education levels in Jacksonville, FL and Atlanta, GA")

# Add text labels for values on top of each bar
p8 + geom_text(aes(label = paste0(round(Value * 100, 2), "%"),
                 y = Value * 100),
              position = position_dodge(width = 0.9), vjust = -0.5)

The next plot shows a key Connectivity metric of Net Migration Levels of College Educated Individuals in Jacksonville and Atlanta.

# Filter the data frame to include only desired measures
Plot_9 <- Summary_II_long %>%
  filter(Measure %in% c("Edu Migration"))

# Create a bar plot with y-axis values as percentages and fill with Metro
p9 <- ggplot(Plot_9, aes(x = Measure, y = Value, fill = Metro)) +
  geom_bar(stat = "identity", position = "dodge") +
  scale_y_continuous(labels = scales::number_format(scale = 1)) +
  labs(x = "Metric", y = "Value", fill = "Metro") +
  ggtitle("Plot 9: Net migration of college educated individuals 
          in Jacksonville, FL and Atlanta, GA")

# Add text labels for values on top of each bar
p9 + geom_text(aes(label = paste0(round(Value, 2)),
                 y = Value),
              position = position_dodge(width = 0.9), vjust = -0.5)

The final plot, a measure of Inclusivity, compares the Wages of the top 10% versus the Bottom 10% in Jacksonville and Atlanta.

# Filter the data frame to include only desired measures
Plot_10 <- Summary_II_long %>%
  filter(Measure %in% c("Wages - Top 10% versus Bottom"))

# Create a bar plot with y-axis values as percentages and fill with Metro
p10 <- ggplot(Plot_10, aes(x = Measure, y = Value * 100, fill = Metro)) +
  geom_bar(stat = "identity", position = "dodge") +
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  labs(x = "Metric", y = "Value (%)", fill = "Metro") +
  ggtitle("Plot 10: Wages of Top 10% vs Bottom 10% 
          in Jacksonville, FL and Atlanta, GA")

# Add text labels for values on top of each bar
p10 + geom_text(aes(label = paste0(round(Value * 100, 2), "%"),
                 y = Value * 100),
              position = position_dodge(width = 0.9), vjust = -0.5)