Housing affordability index for professors

For this assignment, I continue my investigation of professor salaries. In particular, I investigate the relative ‘impoverishment’ of faculty as median home prices rise from 2018-2022, disproportionately to salary increases.

Data on Q3 median home values for 2018-2022 was extracted from:

https://www.zillow.com/research/data/

Data on 2022 salaries was extracted from:

https://www.ziprecruiter.com/Salaries/What-Is-the-Average-Professor-Salary-by-State

For 2018-2021 salaries, average professor pay raise was extracted from:

https://www.aaup.org/sites/default/files/AAUP-2021-SurveyTables.pdf

From the table, raises were:

2022: 2%

2021: 1%

2020: 2.8%

2019: 2%

2018: 2.8%

These were used to reduce 2022 pay to calculate 2018-2021 pay.

Data manipulation

>For each year a basic affordability index defined as home price to annual income ratio was calculated. This was inspired by

https://data.oecd.org/price/housing-prices.htm

Basically, a home price of 240K relative to a salary of 80K would give an index of 3.

>A simple difference (change between 2022 and 2018) was also calculated. for example, a difference of 1 would indicate a home in 2022 would cost an extra yearly salary, as compared to 2018.

>A percentage increase in monthly expenditure on housing was also calculated. This is the percent change between 2018 and 2022 for mortgage/ monthly salary. Mortgage was a calculated as 2*price/360, monthly salary as salary/12.

Table

The table show the following:

>State, Median Salary, Home Prices for 2018 and 2022, the Affordability Index for 2022, and the Change in Affordability between 2018-2022.

>The long table is put in a scroll box

>The table is themed

>The headers are nested

>The rows are highlight-able

>The Change in Affordability is color-coded for a difference greater than 1 salary

>A tooltip over Change in Affordability shows the percentage increase in monthly expenditure 2018-2022

>A sparkline ‘Trend’ shows yearly Affordability Indices for 2018-2022 when hovered over

Clearly, meager yearly raises - often the case in academia as raises are tied to promotions and less so performance - mean that the real earnings of university professors have decreased dramatically as housing costs increase!

library(tidyverse)
library(knitr)
library(kableExtra)
library(sparkline)
library(tidyr)
library(dplyr)
library(htmlwidgets)
table <- read.csv("statedatafortable.csv")
colnames(table)[1] <- gsub('^...','',colnames(table)[1])
tidytable <- read.csv("statedataforsparkle.csv") %>%
    gather(key ="Quarter", value ="Index", Q3_2018:Q3_2022)
colnames(tidytable)[1] <- gsub('^...','',colnames(tidytable)[1])
table.kable <- as.data.frame(table)
table.kable %>% add_column(Trend = NA)
for(s in table.kable$State){
  # vector of data for figure for cases
  trend <- tidytable %>%
    filter(State==s) %>%
    arrange(Quarter) %>%
    pull(Index)
  # add figure to data.frame
  table.kable[which(table.kable$State==s),"Trend"] <- spk_chr(trend)
}
table.kable$Difference.22.18 <- ifelse(
  table.kable$Difference.22.18 > 1,
  cell_spec(table.kable$Difference.22.18, color = "red", bold = T, 
            tooltip=paste("Professors in", table$State, "pay",table$Percent, "% of their income more on housing in 2022")),
  cell_spec(table.kable$Difference.22.18, 
            tooltip=paste("Professors in", table$State, "pay",table$Percent, "% of their income more on housing in 2022"))
)
sparkline(0)
table.kable.full <- table.kable%>% 
  select(State, Salary.2022, Home.Price.2018, Home.Price.2022, Index.2022, Difference.22.18, Trend) %>%
  kbl(escape = FALSE,
      col.names = c("State",
                    "Median Salary",
                    "2018",
                    "2022",
                    "2022",
                    "Change, 2018-22", 
                    "Trend"),
  align = c("l","r","r","r","r","r","r"),
      digits = 1, format.args = list(big.mark = ",")) %>%
  add_header_above(c(" ", " ", "Median Home Price" = 2, "Affordability Index" = 2, " "))  %>%
  kable_styling(font_size=15, fixed_thead = TRUE) %>%
  scroll_box(width = "100%", height = "400px") %>%
  kable_paper(lightable_options = "hover", full_width = FALSE)
table.kable.full
Median Home Price
Affordability Index
State Median Salary 2018 2022 2022 Change, 2018-22 Trend
Alabama 57,211 143,746 215,294 3.8 1
Alaska 73,437 273,780 336,888 4.6 0.6
Arizona 64,641 246,828 432,850 6.7 2.6
Arkansas 64,652 129,578 187,071 2.9 0.7
California 67,777 539,705 769,405 11.4 2.8
Colorado 66,384 388,633 577,500 8.7 2.4
Connecticut 75,937 256,151 360,650 4.7 1.1
Delaware 66,396 257,868 361,656 5.4 1.3
Florida 55,451 237,880 406,988 7.3 2.7
Georgia 51,700 196,302 323,991 6.3 2.2
Hawaii 76,903 652,747 910,349 11.8 2.7
Idaho 67,102 259,622 471,341 7.0 2.8
Illinois 61,049 202,719 267,383 4.4 0.8
Indiana 62,673 150,876 227,165 3.6 1
Iowa 70,529 153,468 199,388 2.8 0.5
Kansas 66,310 154,099 214,970 3.2 0.7
Kentucky 62,770 144,283 205,598 3.3 0.8
Louisiana 61,309 171,458 218,008 3.6 0.5
Maine 63,536 234,414 369,767 5.8 1.8
Maryland 69,743 308,203 405,957 5.8 1
Massachusetts 77,143 418,406 583,964 7.6 1.7
Michigan 62,709 163,081 237,236 3.8 1
Minnesota 76,580 246,909 334,482 4.4 0.9
Mississippi 64,662 125,150 171,319 2.6 0.6
Missouri 60,436 161,668 234,924 3.9 1
Montana 63,305 273,186 463,871 7.3 2.7
Nebraska 66,854 178,360 248,627 3.7 0.8
Nevada 75,893 291,643 454,158 6.0 1.8
New Hampshire 69,059 280,119 438,366 6.3 2
New Jersey 66,800 332,315 471,719 7.1 1.7
New Mexico 70,344 192,138 299,814 4.3 1.3
New York 73,275 301,340 411,861 5.6 1.2
North Carolina 55,181 198,243 328,682 6.0 2.1
North Dakota 72,382 235,196 284,130 3.9 0.4
Ohio 72,553 146,427 216,746 3.0 0.8
Oklahoma 63,137 129,008 187,915 3.0 0.8
Oregon 73,253 357,692 515,439 7.0 1.8
Pennsylvania 60,463 190,938 268,984 4.4 1
Rhode Island 74,359 294,876 437,424 5.9 1.6
South Carolina 65,466 187,584 299,173 4.6 1.5
South Dakota 68,820 210,779 305,170 4.4 1.1
Tennessee 78,582 185,453 309,460 3.9 1.4
Texas 59,450 206,066 315,815 5.3 1.6
Utah 70,843 333,120 562,693 7.9 2.9
Vermont 67,148 254,373 370,790 5.5 1.4
Virginia 68,476 279,725 382,958 5.6 1.2
Washington 73,974 389,355 613,674 8.3 2.6
West Virginia 61,594 111,071 144,640 2.3 0.4
Wisconsin 73,671 187,746 268,737 3.6 0.9
Wyoming 64,156 250,754 339,353 5.3 1.1