Parse signatories from excel spreadsheet

parse_signatures.r

library(tidyverse)
library(readxl)

a <- read_xlsx("~/Downloads/Letter to the Vice Chancellor 2021-11-10 (Responses)(1).xlsx")

to_remove <- tolower(c("ana.baezaruiz@bristol.ac.uk", "Valerie.Aspin@bristol.ac.uk"))

dat <- a %>%
	filter(! tolower(`Email Address`) %in% to_remove) %>%
	arrange(`School or department`, `Surname`) %>%
	mutate(
		num = 1:n(),
		name = paste0(num, ". ", Title, " ", `First name(s)`, " ", `Surname`)) %>%
	select(name, `School or department`) %>%
	mutate(
		`School or department` = 
		case_when(
			`School or department` == "School of Experimental Psychology" ~ "School of Psychological Science",
			`School or department` == "CALD" ~ "Centre for Academic Language and Development",
			`School or department` == "Department iof Engineering Mathematics" ~ "Department of Engineering Mathematics",
			TRUE ~ `School or department`)
	)

unlink("sigs.txt")
deps <- unique(dat$`School or department`)
lapply(deps, function(i)
{
	cat("\n", file="sigs.txt", append=TRUE)
	cat(i, file="sigs.txt", append=TRUE)
	cat("\n", file="sigs.txt", append=TRUE)
	x <- subset(dat, `School or department` == i)
	for(j in 1:nrow(x))
	{
		cat(x$name[j], file="sigs.txt", append=TRUE)
		cat("\n", file="sigs.txt", append=TRUE)
	}
})


dat %>%
	group_by(`School or department`) %>%
	summarise(n=n()) %>% as.data.frame()