Library Hours for Campus 99
Report date: 2026-02-25
Start date: 99 June 9999
1) Request info
Requestor Name: Ninety Niner
Email Address: someone@99.edu
Phone: 999-999-9999
Details: I am considering adjusting the 99 library hours to be open when we have the largest number of students on campus. Could you send me the data on student activity for 99 for the past three academic years?
Date Needed: 9999-09-99
## SQL query
query=tbl(db_connection,dbplyr::sql("
SELECT *
FROM SSRMEET
JOIN SSBSECT
ON SSBSECT_CRN = SSRMEET_CRN
AND SSBSECT_TERM_CODE = SSRMEET_TERM_CODE
JOIN STVTERM
ON SSBSECT_TERM_CODE = STVTERM_CODE
JOIN STVCAMP
ON SSBSECT_CAMP_CODE = STVCAMP_CODE
JOIN STVSCHD
ON SSRMEET_SCHD_CODE = STVSCHD_CODE
"))EMILY = query |>
filter(SSBSECT_TERM_CODE >= 202210 & SSBSECT_TERM_CODE <= 202430) |>
select(STVTERM_DESC,SSRMEET_TERM_CODE, STVCAMP_DESC,STVSCHD_DESC,SSBSECT_SUBJ_CODE,SSBSECT_CRN, SSBSECT_ENRL,SSRMEET_SUN_DAY,SSRMEET_MON_DAY,SSRMEET_TUE_DAY,SSRMEET_WED_DAY,SSRMEET_THU_DAY,SSRMEET_FRI_DAY,SSRMEET_SAT_DAY,SSRMEET_MTYP_CODE,SSRMEET_BEGIN_TIME,SSRMEET_END_TIME,SSRMEET_START_DATE,SSRMEET_END_DATE) |>
collect()EMILY1 = EMILY |>
mutate(STVTERM_DESC = factor(STVTERM_DESC,levels=STVTERM_DESC[order(SSRMEET_TERM_CODE)] |>
unique())) |>
filter(!is.na(SSRMEET_BEGIN_TIME),
STVSCHD_DESC != 'Internet Online D2L')
EMILY2 = EMILY1 |>
pivot_longer(cols = c('SSRMEET_SUN_DAY','SSRMEET_MON_DAY','SSRMEET_TUE_DAY','SSRMEET_WED_DAY','SSRMEET_THU_DAY','SSRMEET_FRI_DAY','SSRMEET_SAT_DAY'), names_to = "SSRMEET_DAY",values_to = "Day") |>
filter(!is.na(Day)) |>
mutate(begin_time = format(strptime(SSRMEET_BEGIN_TIME, format = "%H%M"), format = "%I:%M %p"),
end_time = format(strptime(SSRMEET_END_TIME, format = "%H%M"), format = "%I:%M %p"),
class_times = paste(begin_time,"-",end_time)) |>
mutate(Day = case_when(Day == 'M' ~ "Monday",
Day == 'R' ~ "Thursday",
Day == 'T' ~ "Tuesday",
Day == "W" ~ "Wednesday",
SSRMEET_DAY == 'SSRMEET_FRI_DAY' ~ "Friday",
SSRMEET_DAY == 'SSRMEET_SAT_DAY' ~ "Saturday",
SSRMEET_DAY == 'SSRMEET_SUN_DAY' ~ "Sunday",
TRUE ~ Day)) |>
mutate(Day = factor(Day,levels = c("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"))) |>
mutate(begin_time = factor(begin_time,levels = begin_time[order(SSRMEET_BEGIN_TIME)] |>
unique())) |>
mutate(end_time = factor(end_time,levels = end_time[order(SSRMEET_END_TIME)] |>
unique()))
EMILY3 = EMILY2 |>
filter(STVCAMP_DESC == "Kershaw County Campus")# Combine begin_time and end_time into long format
EMILY_long = EMILY3 |>
pivot_longer(cols = c(begin_time, end_time),
names_to = "time_type", values_to = "time") |>
group_by(time_type, time,STVTERM_DESC,SSRMEET_TERM_CODE) |>
summarise(total_enrollment = sum(SSBSECT_ENRL, na.rm = TRUE), .groups = "drop") |>
mutate(academic_year = paste("AY", as.numeric(substr(as.character(SSRMEET_TERM_CODE), 1, 4))))
EMILY_long_binned = EMILY_long |>
mutate(time_char = as.character(time),
parsed_time = parse_time(time_char, format = "%I:%M %p"),
hour = hour(parsed_time),
time_bin = cut(hour,
breaks = seq(6, 22, by = 2),
right = FALSE,
labels = c("6–8 AM", "8–10 AM", "10–12 AM", "12–2 PM",
"2–4 PM", "4–6 PM", "6–8 PM", "8–10 PM"))) |>
group_by(academic_year,SSRMEET_TERM_CODE,STVTERM_DESC, time_type, time_bin) |>
summarise(total_enrollment = sum(total_enrollment, na.rm = TRUE), .groups = "drop")2) Class Times based on Student Enrollment
2.1) 99 Campus from Fall 2022 to Summer 2025
EMILY_long_binned_all = EMILY_long |>
mutate(
time_char = as.character(time), # Convert factor to character
parsed_time = parse_time(time_char, format = "%I:%M %p"),
hour = hour(parsed_time),
time_bin = cut(hour,
breaks = seq(6, 22, by = 2), # 6–8, 8–10, ..., 20–22
right = FALSE,
labels = c("6–8 AM", "8–10 AM", "10–12 AM", "12–2 PM",
"2–4 PM", "4–6 PM", "6–8 PM", "8–10 PM"))) |>
group_by(time_type, time_bin) |>
summarise(total_enrollment = sum(total_enrollment, na.rm = TRUE), .groups = "drop")
# Plot
ggplot(EMILY_long_binned_all, aes(x = time_bin, y = total_enrollment, fill = time_type)) +
geom_col(position = "stack") +
labs(title = "Attendance by Start and End Times",
x = "", y = "Students Attending Class", fill = "") +
theme_minimal(base_size = 12) +
theme(axis.text.x = element_text(angle = 45, hjust = 1, size = 9))
ggplot(EMILY_long_binned_all, aes(x = time_bin, y = total_enrollment, color = time_type, group = time_type)) +
geom_line(size = 1) +
geom_point(size = 1.3) +
labs(
title = "Attendance by Start and End Times",
x = " ", y = "Students Attending Class", color = ""
) +
theme_minimal(base_size = 13) +
theme(axis.text.x = element_text(angle = 45, hjust = 1, size = 10))
ggplot(EMILY_long_binned, aes(x = time_bin, y = total_enrollment, fill = time_type, group = time_type)) +
geom_col(size = 1,position = 'stack') +
#geom_point(size = 1) +
facet_grid(~academic_year) +
labs(title = "Attendance at 99 by Academic Year",
x = " ", y = "Students Attending Class", color = "",fill='') +
theme_minimal(base_size = 12) +
theme(axis.text.x = element_text(angle = 45, hjust = 1, size = 8))
2.2) Split by Semester/Academic Year from Fall 2022 to Summer 2025
ggplot(EMILY_long_binned, aes(x = time_bin, y = total_enrollment, color = time_type, group = time_type)) +
geom_line(size = 1) +
geom_point(size = 1.3) +
facet_wrap(~STVTERM_DESC) +
labs(title = "Attendance at 99 by Semester",
x = " ", y = "Students Attending Class", color = "") +
theme_minimal(base_size = 12) +
theme(axis.text.x = element_text(angle = 45, hjust = 1, size = 8))
ggplot(EMILY_long_binned, aes(x = time_bin, y = total_enrollment, fill = time_type, group = time_type)) +
geom_col(size = 1,position = 'dodge') +
#geom_point(size = 1) +
facet_grid(~academic_year) +
labs(title = "Attendance at 99 by Academic Year",
x = " ", y = "Students Attending Class", color = "",fill='') +
theme_minimal(base_size = 12) +
theme(axis.text.x = element_text(angle = 45, hjust = 1, size = 8))