baltimore-salary-data.R

library(tidyverse)

salary_url <- "https://services1.arcgis.com/UWYHeuuJISiGmgXx/arcgis/rest/services/EmployeeSalaries_1/FeatureServer/0"

salary_service <- arcgislayers::arc_open(salary_url)

salary_table_src <- arcgislayers::arc_select(salary_service)

# pak::pkg_install("elipousson/baltimoredata")
salary_xwalk <- baltimoredata::entity_xwalk |>
  filter(
    source %in% c(
      "Baltimore_City_Employee_Residency_by_Agency",
      "EmployeeSalaries_1"
    )
  ) |>
  summarise(
    entity_names = list(unique(entity)),
    entity_label = as.character(knitr::combine_words(entity)),
    .by = c(
      name, id
    )
  )

fiscal_year <- function(x) {
  year(x) + (month(x) >= 7)
}

salary_table_prepped <- salary_table_src |>
  arrange(FiscalYear, AgencyID, ID, Name) |>
  mutate(
    AgencyID = if_else(AgencyID == "NA", NA_character_, AgencyID),
    FiscalYear = parse_number(FiscalYear),
    Name = str_trim(Name),
    Name = if_else(
      str_detect(Name, " NA$"),
      str_remove(Name, " NA$"),
      Name
    ),
    JobTitle = if_else(
      JobTitle == "NA",
      NA_character_,
      str_squish(str_to_title(JobTitle))
    ),
    HireFiscalYear = fiscal_year(HireDate),
    YearSinceHire = FiscalYear - HireFiscalYear,
    PositionID = if_else(
      FiscalYear > 2021,
      str_extract(JobTitle, "^[:digit:]+"),
      NA_character_
    ),
    PositionTitle = if_else(
      !is.na(PositionID),
     str_remove(
       str_remove(JobTitle, fixed(PositionID)),
       "^ - "
     ),
      JobTitle
    ),
    Status = if_else(
      AnnualSalary == 0 & GrossPay > 0 & YearSinceHire > 0,
      "Departure or One-Time Payment",
      NA_character_
    )
  )

salary_table_id <- salary_table_prepped |>
  filter(!is.na(AgencyID)) |>
  left_join(
    salary_xwalk,
    by = join_by(
      AgencyID == id
    ),
    na_matches = "never"
  )

salary_table_name <- salary_table_prepped |>
  filter(is.na(AgencyID)) |>
  left_join(
    salary_xwalk |>
      select(!id) |>
      distinct(),
    by = join_by(
      AgencyName == name
    ),
    na_matches = "never"
  )

salary_table <- list(salary_table_id, salary_table_name) |>
  list_rbind() |>
  arrange(FiscalYear, entity_label, ID, Name) |>
  left_join(
    baltimoredata::entity_reference |>
      select(
        starts_with("name"),
        starts_with("entity"),
        starts_with("unit"),
        entity_id = airtable_record_id
      ),
    by = join_by(
      entity_label == name
    )
  )

salary_table_update <- salary_table |>
  left_join(
    salary_table |>
      filter(
        FiscalYear < max(salary_table$FiscalYear)
      ) |>
      mutate(
        entity_id,
        HireDate,
        Name,
        FiscalYear = FiscalYear + 1,
        PriorYearAnnualSalary = AnnualSalary,
        PriorYearGrossPay = GrossPay,
        .keep = "none"
      ),
    relationship = "many-to-many",
    na_matches = "never"
  )