BioFab-Talent-Demand-Analysis.R
Paul Liu
2026-10-07
rm(list = ls())
setwd("C:/Users/Paul Liu/Desktop/Projects/BioFAB Certifications")
library(pacman)
p_load(tidyverse)
p_load(tidycensus)
p_load(forcats)
p_load(stringr)
p_load(openxlsx)
p_load(ipumsr)
p_load(naniar)
p_load(clipr)
p_load(gdata)
p_load(tidyr)
p_load(leaflet)
p_load(tigris)
p_load(tidygeocoder)
p_load(sf)
p_load(readxl)
p_load(htmltools)
p_load(htmlwidgets)
postings <- read.xlsx("BioFab Job Postings.xlsx", sheet = "Job Postings by Location")
by_employers <- read.xlsx("BioFab Job Postings.xlsx", sheet = "Nested Table_Employers by Cty")
by_job_title <- read.xlsx("BioFab Job Postings.xlsx", sheet = "Nested Table_Job Titles by Cty")
########## Settings ##########################################################
top_n <- 5 # employers / job titles listed per county in the tooltip (5 or 10)
# Titles starting with "Travel" (Travel CT Techs, Travel Radiology Technicians, ...)
# are travel-staffing contracts. The job-title sheet has no employer column, so
# this prefix is the only way to keep staffing-agency postings out of the title
# list the way they are kept out of the employer list. Set FALSE to show them.
drop_travel_titles <- TRUE
# Employers the source flags "Staffing Agency? = No" that are staffing, recruiting,
# or search firms. Built by reviewing every employer that can reach a county's
# top 10, plus a keyword pass. Judgment by name -- edit as needed.
# Left OUT because unclear: Turing, Healthforce, WellTech Partners, Kms Solutions,
# Sterling Therapy Solutions, Adn Healthcare.
staffing_override <- c("Premiere Healthcare Staffing", "Bridge Technical Talent",
"Smart Root Staffing", "Zero Staffing LTD.",
"Risus Talent Partners", "The Talent Road", "Ae Talents Group",
"Hudson Manpower", "Talented Medical Solutions",
"Gqr", "Alexander Technology Group", "Brightpath Associates",
"Capstoneone Search", "Careers Integrated Resources",
"Guided Search Partners", "HumanEdge Allied Health",
"Infojini Healthcare", "NavitsPartners", "NurseStar Medical Partners",
"Tempo Employment Services", "Wellspring Nurse Source")
# 68 mappable counties split 9 / 10 / 11 / 10 / 14 / 8 / 6 across these bins
bins_postings <- c(1, 100, 250, 500, 1000, 2500, 5000, Inf)
# Legend labels for integer bins: [lo, hi) shown as "lo - (hi - 1)", top bin "lo+".
# Same function as the supply map.
int_bins <- function(type, cuts, p) {
lo <- cuts[-length(cuts)]
hi <- cuts[-1]
fmt <- function(x) format(x, big.mark = ",", trim = TRUE)
out <- character(length(lo))
for (i in seq_along(lo)) {
out[i] <- if (is.infinite(hi[i])) paste0(fmt(lo[i]), "+")
else if (hi[i] - lo[i] == 1) fmt(lo[i])
else paste0(fmt(lo[i]), " – ", fmt(hi[i] - 1))
}
out
}
fmt_n <- function(x) format(x, big.mark = ",", trim = TRUE)
########## 1. County totals ##################################################
# FIPS xx999 rows are "[State, county not reported]" -- real postings with no
# polygon to put them on. Left off the map and reported here instead.
unreported <- postings %>% filter(FIPS %% 1000 == 999)
cat("Postings with no county (not mapped):", fmt_n(sum(unreported$Postings)),
"of", fmt_n(sum(postings$Postings)), "\n")## Postings with no county (not mapped): 2,659 of 141,165county_postings <- postings %>%
filter(FIPS %% 1000 != 999) %>%
mutate(GEOID = str_pad(FIPS, 5, pad = "0"), # CT FIPS read in as 9110 -> "09110"
# nested sheets say "Middlesex, MA" where this sheet says "Middlesex County, MA"
key = str_replace(County_Name, " (County|Planning Region),", ","))
########## 2. Tooltip lists ##################################################
# Each county block in the nested sheets opens with a subtotal row: blank
# Employer / Job_Title, Postings = the county total. Dropped below.
# Only the 50 counties with the most postings have nested detail; the rest
# fall through the left joins as NA and show "N/A" in the tooltip.
employers_clean <- by_employers %>%
rename(staffing = starts_with("Staffing")) %>%
filter(!is.na(Employer)) %>%
mutate(is_staffing = staffing == "Yes" | Employer %in% staffing_override)
# Ties at the cutoff are common (10 counties tie at rank 5, 24 at rank 10), so
# break them alphabetically to get the same list every run.
top_employers <- employers_clean %>%
filter(!is_staffing) %>%
group_by(key = County_Name) %>%
arrange(desc(Postings), Employer, .by_group = TRUE) %>%
slice_head(n = top_n) %>%
mutate(line = str_c(row_number(), ". ", htmlEscape(Employer),
" <span style='color:#777'>(", fmt_n(Postings), ")</span>")) %>%
summarise(employers_html = str_c(line, collapse = "<br/>"))
top_titles <- by_job_title %>%
filter(!is.na(Job_Title),
!(drop_travel_titles & str_detect(Job_Title, regex("^travel\\b", ignore_case = TRUE)))) %>%
group_by(key = County_Name) %>%
arrange(desc(Postings), Job_Title, .by_group = TRUE) %>%
slice_head(n = top_n) %>%
mutate(line = str_c(row_number(), ". ", htmlEscape(Job_Title),
" <span style='color:#777'>(", fmt_n(Postings), ")</span>")) %>%
summarise(titles_html = str_c(line, collapse = "<br/>"))
########## 3. Geography ######################################################
ne_abb <- c("CT", "ME", "MA", "NH", "RI", "VT")
# year = 2024 pinned: CT switched from 8 counties to 9 planning regions in 2022,
# and the posting FIPS use the planning regions (091xx).
counties_sf <- counties(state = ne_abb, cb = TRUE, year = 2024, class = "sf") %>%
st_transform(4326)## | | | 0% | | | 1% | |= | 1% | |= | 2% | |== | 2% | |== | 3% | |=== | 4% | |=== | 5% | |==== | 5% | |==== | 6% | |===== | 7% | |===== | 8% | |====== | 8% | |====== | 9% | |======= | 9% | |======= | 10% | |======= | 11% | |======== | 11% | |======== | 12% | |========= | 12% | |========= | 13% | |========= | 14% | |========== | 14% | |========== | 15% | |=========== | 15% | |=========== | 16% | |============ | 16% | |============ | 17% | |============ | 18% | |============= | 18% | |============= | 19% | |============== | 19% | |============== | 20% | |============== | 21% | |=============== | 21% | |=============== | 22% | |================ | 22% | |================ | 23% | |================ | 24% | |================= | 24% | |================= | 25% | |================== | 25% | |================== | 26% | |=================== | 27% | |=================== | 28% | |==================== | 28% | |==================== | 29% | |===================== | 29% | |===================== | 30% | |===================== | 31% | |====================== | 31% | |====================== | 32% | |======================= | 32% | |======================= | 33% | |======================== | 34% | |======================== | 35% | |========================= | 35% | |========================= | 36% | |========================== | 37% | |========================== | 38% | |=========================== | 38% | |=========================== | 39% | |============================ | 39% | |============================ | 40% | |============================ | 41% | |============================= | 41% | |============================= | 42% | |============================== | 42% | |============================== | 43% | |============================== | 44% | |=============================== | 44% | |=============================== | 45% | |================================ | 45% | |================================ | 46% | |================================= | 46% | |================================= | 47% | |================================= | 48% | |================================== | 48% | |================================== | 49% | |=================================== | 49% | |=================================== | 50% | |=================================== | 51% | |==================================== | 51% | |==================================== | 52% | |===================================== | 52% | |===================================== | 53% | |====================================== | 54% | |====================================== | 55% | |======================================= | 55% | |======================================= | 56% | |======================================== | 56% | |======================================== | 57% | |======================================== | 58% | |========================================= | 58% | |========================================= | 59% | |========================================== | 59% | |========================================== | 60% | |========================================== | 61% | |=========================================== | 61% | |=========================================== | 62% | |============================================ | 62% | |============================================ | 63% | |============================================= | 64% | |============================================= | 65% | |============================================== | 65% | |============================================== | 66% | |=============================================== | 66% | |=============================================== | 67% | |=============================================== | 68% | |================================================ | 68% | |================================================ | 69% | |================================================= | 69% | |================================================= | 70% | |================================================= | 71% | |================================================== | 71% | |================================================== | 72% | |=================================================== | 72% | |=================================================== | 73% | |=================================================== | 74% | |==================================================== | 74% | |==================================================== | 75% | |===================================================== | 75% | |===================================================== | 76% | |====================================================== | 76% | |====================================================== | 77% | |====================================================== | 78% | |======================================================= | 78% | |======================================================= | 79% | |======================================================== | 79% | |======================================================== | 80% | |======================================================== | 81% | |========================================================= | 81% | |========================================================= | 82% | |========================================================== | 82% | |========================================================== | 83% | |========================================================== | 84% | |=========================================================== | 84% | |=========================================================== | 85% | |============================================================ | 85% | |============================================================ | 86% | |============================================================= | 86% | |============================================================= | 87% | |============================================================= | 88% | |============================================================== | 88% | |============================================================== | 89% | |=============================================================== | 89% | |=============================================================== | 90% | |=============================================================== | 91% | |================================================================ | 91% | |================================================================ | 92% | |================================================================= | 92% | |================================================================= | 93% | |================================================================= | 94% | |================================================================== | 94% | |================================================================== | 95% | |=================================================================== | 95% | |=================================================================== | 96% | |==================================================================== | 96% | |==================================================================== | 97% | |==================================================================== | 98% | |===================================================================== | 98% | |===================================================================== | 99% | |======================================================================| 99% | |======================================================================| 100%ne_states <- states(cb = TRUE, year = 2024, class = "sf") %>%
filter(STUSPS %in% ne_abb) %>%
st_transform(4326)## | | | 0% | |= | 1% | |= | 2% | |== | 3% | |== | 4% | |=== | 5% | |==== | 5% | |==== | 6% | |===== | 7% | |===== | 8% | |====== | 8% | |====== | 9% | |======= | 10% | |======== | 11% | |======== | 12% | |========= | 12% | |========= | 13% | |========== | 14% | |========== | 15% | |=========== | 15% | |=========== | 16% | |============ | 17% | |============ | 18% | |============= | 18% | |============= | 19% | |============== | 19% | |============== | 20% | |=============== | 21% | |=============== | 22% | |================ | 22% | |================ | 23% | |================= | 24% | |================= | 25% | |================== | 25% | |================== | 26% | |=================== | 27% | |==================== | 28% | |==================== | 29% | |===================== | 29% | |===================== | 30% | |====================== | 31% | |====================== | 32% | |======================= | 32% | |======================= | 33% | |======================== | 34% | |======================== | 35% | |========================= | 35% | |========================= | 36% | |========================== | 37% | |========================== | 38% | |=========================== | 38% | |=========================== | 39% | |============================ | 40% | |============================ | 41% | |============================= | 41% | |============================= | 42% | |============================== | 43% | |=============================== | 44% | |=============================== | 45% | |================================ | 45% | |================================ | 46% | |================================= | 47% | |================================= | 48% | |================================== | 48% | |================================== | 49% | |=================================== | 50% | |=================================== | 51% | |==================================== | 51% | |==================================== | 52% | |===================================== | 52% | |===================================== | 53% | |====================================== | 54% | |====================================== | 55% | |======================================= | 55% | |======================================= | 56% | |======================================== | 57% | |======================================== | 58% | |========================================= | 58% | |========================================= | 59% | |========================================== | 59% | |========================================== | 60% | |=========================================== | 61% | |=========================================== | 62% | |============================================ | 62% | |============================================ | 63% | |============================================= | 64% | |============================================= | 65% | |============================================== | 65% | |============================================== | 66% | |=============================================== | 67% | |================================================ | 68% | |================================================ | 69% | |================================================= | 69% | |================================================= | 70% | |================================================== | 71% | |================================================== | 72% | |=================================================== | 72% | |=================================================== | 73% | |==================================================== | 74% | |==================================================== | 75% | |===================================================== | 75% | |===================================================== | 76% | |====================================================== | 76% | |====================================================== | 77% | |====================================================== | 78% | |======================================================= | 78% | |======================================================= | 79% | |======================================================== | 80% | |======================================================== | 81% | |========================================================= | 81% | |========================================================= | 82% | |========================================================== | 82% | |========================================================== | 83% | |=========================================================== | 84% | |=========================================================== | 85% | |============================================================ | 85% | |============================================================ | 86% | |============================================================= | 86% | |============================================================= | 87% | |============================================================== | 88% | |============================================================== | 89% | |=============================================================== | 89% | |=============================================================== | 90% | |================================================================ | 91% | |================================================================ | 92% | |================================================================= | 92% | |================================================================= | 93% | |================================================================= | 94% | |================================================================== | 94% | |================================================================== | 95% | |=================================================================== | 95% | |=================================================================== | 96% | |==================================================================== | 97% | |==================================================================== | 98% | |===================================================================== | 98% | |===================================================================== | 99% | |======================================================================| 100%stopifnot(all(county_postings$GEOID %in% counties_sf$GEOID)) # all 68 should match
########## 4. Tooltip HTML ###################################################
# Leaflet tooltips shrink-wrap their content, so a width on <td> gets ignored and
# names wrap after a word or two. A fixed-width <div> inside each cell holds it.
td_style <- "vertical-align:top; padding:2px 16px 0 0;"
th_style <- "text-align:left; vertical-align:bottom; padding:0 16px 3px 0; border-bottom:1px solid #ddd;"
col_div <- "<div style='width:240px; white-space:normal; line-height:1.45;'>"
county_map_sf <- counties_sf %>%
select(GEOID, geometry) %>%
inner_join(county_postings, by = "GEOID") %>%
left_join(top_employers, by = "key") %>%
left_join(top_titles, by = "key") %>%
mutate(
detail_html = if_else(
is.na(employers_html),
"<br/><span style='color:#777'>Top employers and job titles: N/A</span>",
str_c("<table style='border-collapse:collapse; margin-top:8px;'>",
"<tr><th style='", th_style, "'>Top ", top_n, " employers<br/>",
"<span style='font-weight:normal; font-size:11px; color:#777'>excl. staffing agencies</span></th>",
"<th style='", th_style, "'>Top ", top_n, " job titles",
if (drop_travel_titles) "<br/><span style='font-weight:normal; font-size:11px; color:#777'>excl. travel contracts</span>" else "",
"</th></tr>",
"<tr><td style='", td_style, "'>", col_div, employers_html, "</div></td>",
"<td style='", td_style, "'>", col_div, coalesce(titles_html, "N/A"), "</div></td></tr></table>")),
labels = str_c("<strong style='font-size:15px'>", htmlEscape(County_Name), "</strong><br/>",
"Job postings: <strong>", fmt_n(Postings), "</strong>",
detail_html) %>%
lapply(HTML)
)
########## 5. Map ############################################################
pal <- colorBin(
palette = "YlGnBu",
domain = county_map_sf$Postings,
bins = bins_postings,
na.color = "#bab8ae"
)
bb <- st_bbox(county_map_sf)
demand_map <- leaflet(county_map_sf) %>%
addTiles(
urlTemplate = "https://{s}.basemaps.cartocdn.com/rastertiles/voyager/{z}/{x}/{y}.png?key=cb1_2syh_1_14db23a26507556c64eda286",
attribution = '© <a href="https://www.openstreetmap.org/copyright">OpenStreetMap</a>, © <a href="https://carto.com/attributions">CARTO</a>',
options = tileOptions(
subdomains = "abcd",
maxZoom = 20)) %>%
fitBounds(bb[["xmin"]], bb[["ymin"]], bb[["xmax"]], bb[["ymax"]]) %>%
addPolygons(
fillColor = ~pal(Postings),
weight = 1, opacity = 1, color = "white",
dashArray = "3", fillOpacity = 0.7,
highlightOptions = highlightOptions(
weight = 3, color = "#666", dashArray = "",
fillOpacity = 0.7, bringToFront = TRUE),
label = ~labels,
labelOptions = labelOptions(
style = list("font-weight" = "normal", padding = "6px 10px"),
textsize = "13px", direction = "auto")
) %>%
# state lines for orientation -- several county names repeat across states
addPolylines(
data = ne_states, color = "#555", weight = 1.5, opacity = 0.8,
options = pathOptions(interactive = FALSE)
) %>%
addLegend(
pal = pal, values = ~Postings, opacity = 0.7,
title = "No. of Job Postings<br/><small>New England counties</small>",
position = "bottomright",
labFormat = int_bins
) %>%
# The legend always draws above tooltips (Leaflet puts controls outside the
# map's stacking context), so it covered the job-title column for most of
# southern New England. Fade it out while a tooltip is open -- the tooltip
# shows the exact count, so nothing is lost.
onRender("
function(el, x) {
var legend = el.querySelector('.legend');
if (!legend) return;
legend.style.transition = 'opacity 0.15s';
this.on('tooltipopen', function() { legend.style.opacity = 0; });
this.on('tooltipclose', function() { legend.style.opacity = 1; });
}")
demand_mapsaveWidget(demand_map, file = "BioFAB Job Postings Map.html", selfcontained = TRUE,
title = "BioFAB Job Postings Map")
########## 6. Static map for slides ##########################################
# Sized and styled to sit beside NewEngland_Programs_Map.png (the supply-side
# slide): 7.5 x 9 in portrait, leader-line labels, legend top-left.
p_load(ggrepel)
# Edit before exporting -- add the posting source and date range
source_note <- "Source: BioFab job postings extract."
# Same seven classes and colors as the interactive map. Colors come from the
# leaflet palette itself (evaluated at each bin's lower edge) so the two maps
# can't drift apart if bins_postings changes.
bin_labels <- int_bins(NULL, bins_postings, NULL) %>% str_replace_all("–", "–")
bin_cols <- setNames(pal(head(bins_postings, -1)), bin_labels)
static_sf <- county_map_sf %>%
select(GEOID, County_Name, Postings, geometry) %>%
st_transform(5070) %>%
mutate(post_cat = cut(Postings, bins_postings, labels = bin_labels, right = FALSE))
ne_states_5070 <- st_transform(ne_states, 5070)
# Label the 10 largest counties, plus a marker for the Tech Hub. All labels go
# through one repel layer so they avoid each other and the marker.
top_counties <- static_sf %>%
slice_max(Postings, n = 10) %>%
mutate(short = str_replace(County_Name, " County,", ","),
short = str_replace(short, "^Western Connecticut Planning Region, CT", "Western CT"),
short = str_replace(short, " Planning Region,", " Region,"),
lab = str_c(short, " (", fmt_n(Postings), ")"),
hub = FALSE) %>%
st_point_on_surface()## Warning: st_point_on_surface assumes attributes are constant over geometriesmanchester <- st_sf(lab = "Manchester, NH\nReGen Valley Tech Hub", hub = TRUE,
geometry = st_sfc(st_point(c(-71.4548, 42.9956)), crs = 4326)) %>%
st_transform(5070)
label_pts <- bind_rows(top_counties %>% select(lab, hub), manchester)
bb5070 <- st_bbox(ne_states_5070)
ne_demand_static <- ggplot() +
geom_sf(data = static_sf, aes(fill = post_cat), color = "white", linewidth = 0.3) +
geom_sf(data = ne_states_5070, fill = NA, color = "grey45", linewidth = 0.45) +
geom_sf(data = manchester, shape = 21, size = 3.6, stroke = 1,
fill = "#d7301f", color = "white") +
geom_label_repel(
data = label_pts,
aes(label = lab, geometry = geometry,
fontface = if_else(hub, "bold", "plain"),
color = if_else(hub, "#b2182b", "black")),
stat = "sf_coordinates",
size = 2.7, label.size = 0.15, label.padding = unit(0.12, "lines"),
fill = alpha("white", 0.88), segment.color = "grey40",
min.segment.length = 0, box.padding = 0.5, max.overlaps = Inf, seed = 1
) +
scale_color_identity() +
scale_fill_manual(values = bin_cols, drop = FALSE, name = "Job postings") +
coord_sf(xlim = c(bb5070["xmin"], bb5070["xmax"]),
ylim = c(bb5070["ymin"], bb5070["ymax"]), expand = TRUE) +
labs(
title = "Job postings by county, New England",
subtitle = "Total postings; Manchester, NH (ReGen Valley Tech Hub) marked",
caption = str_c(
"Not shown: ", fmt_n(sum(unreported$Postings)), " postings with no county reported. ",
"Merrimack (NH), Kennebec (ME) and Washington (VT) counties\n",
"include statewide remote postings assigned to the state capital. ", source_note)
) +
theme_void(base_size = 11) +
theme(
plot.title = element_text(face = "bold", size = 13, hjust = 0),
plot.subtitle = element_text(color = "grey30", margin = margin(b = 8)),
plot.caption = element_text(color = "grey45", size = 8, hjust = 0, lineheight = 1.1),
legend.position = "inside",
legend.position.inside = c(0.16, 0.72),
plot.margin = margin(12, 12, 12, 12)
)
ne_demand_static
ggsave("NewEngland_JobPostings_Map.png", ne_demand_static,
width = 7.5, height = 9, dpi = 300, bg = "white")
########## 7. Client workbook ################################################
# Same three sheets, columns and formatting as BioFab Job Postings.xlsx, with
# the two nested tables filtered the way the map's tooltips are:
# - Employers: names in staffing_override re-flagged "Yes", then every
# staffing agency removed (set client_drop_staffing to FALSE to keep them
# listed with the corrected flag instead)
# - Job titles: "Travel ..." contracts removed when drop_travel_titles is TRUE
# Each county keeps its subtotal row, which is the county total shown on the
# map. Rows are re-sorted with the map's alphabetical tie-break so the top 5 /
# top 10 in the file match the tooltip exactly. Sheet 1's data is unchanged.
#
# Built fresh rather than with loadWorkbook(): re-saving the source through
# openxlsx rewrites its body font color as rgb="#000000", which isn't a valid
# Excel color and can trigger a "repair" prompt when the client opens the file.
client_file <- "BioFab Job Postings_Client.xlsx"
client_drop_staffing <- TRUE
# Source county order, subtotal row first, then postings high-to-low, name A-Z
sort_blocks <- function(df, name_col) {
df %>%
mutate(.county = match(County_Name, unique(County_Name)),
.subtotal = is.na(.data[[name_col]])) %>%
arrange(.county, desc(.subtotal), desc(Postings), .data[[name_col]]) %>%
select(-.county, -.subtotal)
}
staff_col <- grep("^Staffing", names(by_employers), value = TRUE)
employers_client <- by_employers %>%
mutate(across(all_of(staff_col),
~ if_else(!is.na(Employer) & Employer %in% staffing_override, "Yes", .x))) %>%
filter(!(client_drop_staffing & !is.na(Employer) & .data[[staff_col]] == "Yes")) %>%
sort_blocks("Employer") %>%
rename(`Staffing Agency?` = all_of(staff_col))
titles_client <- by_job_title %>%
filter(is.na(Job_Title) |
!(drop_travel_titles & str_detect(Job_Title, regex("^travel\\b", ignore_case = TRUE)))) %>%
sort_blocks("Job_Title")
# Styles copied from the source file's styles.xml
hdr_left <- createStyle(fontName = "Helvetica", fontSize = 10, fontColour = "#FFFFFF",
fgFill = "#3E6DCC", halign = "left", valign = "center", wrapText = TRUE)
hdr_right <- createStyle(fontName = "Helvetica", fontSize = 10, fontColour = "#FFFFFF",
fgFill = "#3E6DCC", halign = "right", valign = "center", wrapText = TRUE)
body_text <- createStyle(fontName = "Helvetica", fontSize = 10, halign = "left", valign = "top")
body_num <- createStyle(fontName = "Helvetica", fontSize = 10, halign = "right", valign = "top",
numFmt = "#,##0;[Red]\\ \\(#,##0\\)")
# All three sheets share one layout: text in A:B, postings in C, and any 4th
# column (Staffing Agency?) left in the workbook's default font as in the source
write_client_sheet <- function(wb, sheet, df, widths, header_height = NULL) {
addWorksheet(wb, sheet)
writeData(wb, sheet, df, keepNA = FALSE, headerStyle = NULL)
n <- nrow(df) + 1
addStyle(wb, sheet, hdr_left, rows = 1, cols = 1:2, gridExpand = TRUE)
addStyle(wb, sheet, hdr_right, rows = 1, cols = 3:ncol(df), gridExpand = TRUE)
addStyle(wb, sheet, body_text, rows = 2:n, cols = 1:2, gridExpand = TRUE)
addStyle(wb, sheet, body_num, rows = 2:n, cols = 3, gridExpand = TRUE)
# openxlsx pads every width by 0.71 on save; subtract it to land on the source widths
setColWidths(wb, sheet, cols = seq_along(widths), widths = widths - 0.71)
if (!is.null(header_height)) setRowHeights(wb, sheet, rows = 1, heights = header_height)
}
client_wb <- createWorkbook(creator = "Paul Liu")
write_client_sheet(client_wb, "Job Postings by Location", postings,
widths = rep(21.33203125, 3))
pageSetup(client_wb, "Job Postings by Location", orientation = "portrait",
left = 0.5, right = 0.5, top = 1, bottom = 1, header = 0.5, footer = 0.5)
write_client_sheet(client_wb, "Nested Table_Employers by Cty", employers_client,
widths = rep(21.33203125, 3), header_height = 26.4)
write_client_sheet(client_wb, "Nested Table_Job Titles by Cty", titles_client,
widths = c(30.21875, 71.44140625, 7.88671875))
activeSheet(client_wb) <- 1
saveWorkbook(client_wb, client_file, overwrite = TRUE)
cat("\nClient workbook:", client_file, "\n",
" employer rows:", nrow(by_employers), "->", nrow(employers_client), "\n",
" job-title rows:", nrow(by_job_title), "->", nrow(titles_client), "\n")##
## Client workbook: BioFab Job Postings_Client.xlsx
## employer rows: 2545 -> 1253
## job-title rows: 2544 -> 2205