Last active
July 14, 2022 05:31
-
-
Save MokeEire/7ab4bfd70a73e9e0c8af5fcd6dcaabb6 to your computer and use it in GitHub Desktop.
Use `cols_label` to relabel multiple columns with the `gt` package
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
library(tidyverse) | |
library(gt) | |
# Read in data | |
# Using LA Budget data: https://controllerdata.lacity.org/Budget/City-Budget-and-Expenditures/uyzw-yi8n | |
la_budget_raw = read_csv("https://controllerdata.lacity.org/api/views/uyzw-yi8n/rows.csv?accessType=DOWNLOAD") | |
## OPTIONAL ## | |
# cleaner column names | |
library(janitor) | |
la_budget = clean_names(la_budget_raw) | |
## OPTIONAL ## | |
# Pivot data to a table-friendly(-ish) format | |
la_budget_table = la_budget %>% | |
# use all the id columns you care about | |
# select the column which is being pivoted to the column names (fiscal year in this case) | |
# select the values you care about | |
pivot_wider(id_cols = c(department_name, department, fund_name, fund, account_name, account, account_group_name), | |
names_from = budget_fiscal_year, | |
values_from = c(adopted_budget_amount, total_expenditures, budget_change_amount, budget_transfer_in_amount, | |
budget_transfer_out_amount, total_budget, encumbrance_amount, budget_uncommitted_amount)) %>% | |
# Arrange the data and column order | |
arrange(department, fund, account) %>% | |
select(department_name, department, fund_name, fund, account_name, account, account_group_name, | |
contains("2012"), contains("2013"), contains("2014"), contains("2015"), contains("2016"), contains("2017"), | |
contains("2018"), contains("2019"), contains("2020")) | |
# Create a named vector of column names and their associated labels | |
col_labs = head(la_budget_table, 1) %>% # Take the first row of a column | |
mutate_all(as.character) %>% # prevents errors in pivot_longer | |
# convert to column-value pair dataframe | |
pivot_longer(everything(), names_to = "column", values_to = "value") %>% | |
# remove the value | |
select(-value) %>% | |
# Craft the desired label | |
# Below I remove the year, replace underscores with spaces, and convert to title case | |
mutate(label = str_to_title(str_replace_all(str_remove(column, "_20[0-9]{2}$"), "_", " "))) %>% | |
# Deframe to named vector | |
deframe() | |
# Use the .list argument in cols_label | |
la_budget_table %>% | |
gt() %>% | |
cols_label(.list = col_labs) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment