Event-Data-Analysis.Rmd

---
title: "Event Data Analysis"
author: "Kunwu Lyu"
date: "2024-06-26"
output: pdf_document
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE, 
                      size = "small", 
                      collapse = TRUE, 
                      comment = NA, 
                      warning = FALSE, 
                      message = FALSE,
                      error = TRUE) # change it to TRUE
```


## Loading Packages
```{r}
library(dplyr)
library(tidyverse)
library(stringr)
library(lubridate)
library(tidyr)
library(forcats)
library(ggplot2)
library(viridis)
library(viridisLite)
library(purrr)
library(gganimate)
library(ggthemes)
library(leaflet)
library(patchwork)
library(readr)
library(janitor)
library(plotly)
```


## Data Wrangling

```{r}
# Define the directory containing the files
data_dir <- "data"

# Define the file codes
file_codes <- c("F14", "W15", "S15", "F15", "W16", "S16", "F16", "W17", "S17", 
                "F17", "W18", "S18", "F18", "W19", "S19", "F19", "W20", "S20", 
                "F20", "W21", "S21", "F21", "W22", "S22", "F22", "W23", "S23", 
                "F23", "W24", "S24")

# Create a function to read a file given its code
read_and_clean_event_file <- function(code) {
  file_path <- paste0(data_dir, "2015-2024 Events Data - ", code, " - Event Data.csv")
  data <- read_csv(file_path)
  clean_names(data) %>% 
    mutate(across(everything(), as.character)) %>% 
    mutate(term = code)
}

term_to_year <- function(term) {
  year <- as.numeric(str_sub(term, 2, 3))
  season <- str_sub(term, 1, 1)
  start_year <- if_else(season == "F", 2000 + year, 2000 + year - 1)
  end_year <- start_year + 1
  return(paste0(start_year, "-", end_year))
}

# Use purrr to read all files and store them in a named list
event_data_list <- set_names(map(file_codes, read_and_clean_event_file), file_codes)

combined_data <- reduce(event_data_list, full_join)

combined_data_filtered <- combined_data %>%
  filter(!is.na(what), what != "") %>% 
  filter(what != "Choir & Jazz Rehearsal") %>% 
  filter(what != "Jazz Rehearsal") %>% 
  mutate(date = as.Date(ymd(date))) %>% 
  mutate(support_level = if_else(support_level == "N" | support_level == "Y", "L", support_level)) %>% 
  mutate(
    department = str_replace_all(department, "WCC", "ODOA"),
    department = str_replace_all(department, "MSUC", "MUSC"),
    department = str_replace_all(department, "French Dept|French", "FREN"),
    department = str_replace_all(department, "English", "ENGL"),
    department = str_replace_all(department, "Pres. Office", "PRES"),
    department = str_replace_all(department, "History", "HIST"),
    department = str_replace_all(department, "THD", "THDA"),
    department = str_replace_all(department, "Inclusion & Equity", "IEC"),
    venue = str_replace_all(venue, "Skinner Chapel", "Chapel"),
    department = str_replace_all(department, "/", " & "),
    department = str_replace_all(department, ",", " &"),
    venue = str_replace_all(venue, ",", " &"),
    department = str_replace_all(department, "\\s+", " ")  # Remove extra spaces
  ) %>% 
  select(-wk) %>% 
  mutate(department_type = case_when(
      department == "MUSC" ~ "MUSC",
      department == "ODOA" ~ "ODOA",
      department == "CSA" ~ "CSA",
      str_detect(department, "&") ~ "Collab",
      TRUE ~ "Others"
    )) %>% 
  mutate(what = str_replace_all(what, "Jazz Ensemble Concert|Jazz Area Concert", "Jazz Concert"),
         what = str_replace_all(what, "Symphony Band Concert", "Symphony Concert"),
         what = str_replace_all(what, "Composition Recital", "Composition Showcase Recital"),
         what = str_replace_all(what, "Harpichord", "Harpsichord"),
         what = str_replace_all(what, "Emsemble", "Ensemble"),
         what = str_replace_all(what, "Juest Cellin'", "Just Cellin'"),
         what = str_replace_all(what, "Facutly|FACULTY|Mazariello", "Faculty")) %>% 
  mutate(event_type = case_when(
    str_detect(what, "GUEST|ODOA|Concert Series|SPCO") ~ "Guest",
    str_detect(what, "Faculty") ~ "Faculty Recital",
    str_detect(what, "Student|Senior|Junior|Piano Recital: |Johnson|Verma Jameson") ~ "Student Recital",
    str_detect(what, "Studio Recital|Organ & Harpsichord|Composition Showcase Recital|Chamber Recital|Chamber Music Recital|Chamber Music|Organ Recital|Strings Recital|Violin & Viola|Violin/Viola|Drum Ensemble|Drum Recital|Voice Showcase Recital|Chinese Music Recital|Piano Studios Recital|Jazz Chamber|Piano Recital|Comps Fest|Recorder Recital|Music Ensemble|Studio") ~ "Studio Recital",
    str_detect(what, "Orchestra Concert|Jazz Concert|Symphony Concert|Symphony Band|Choir Concert|Orchestra and Choir|Chinese & Global|Chinese Global Concert|Chinese and Global|Chinese Music Concert|Chinese Music Ensemble|Chinese Ensemble|Music Comps|Jazz Vocal Concert") ~ "Ensemble Concert",
    str_detect(what, "CSA|Just Cellin|Lunar New Year|ACA|A Cappella|Accidentals|Exit 69|Date Knight|Knights|Knightingales|International Festival") ~ "Student Activity",
    str_detect(what, "Masterclass|Lecture|Symposium") ~ "Masterclass",
    str_detect(what, "Trustees|Trustee's|Presidents|Conference|President's|Presentation") ~ "Presentation",
    str_detect(what, "Clinic|Music Fest|Music Department Showcase|Melinda Russell|Launch|Event|Opening") ~ "Special Events",
      TRUE ~ "Guest"
  )) %>% 
  mutate(year = term_to_year(term)) %>% 
  mutate(term = factor(term, levels = c("F14", "W15", "S15",
                                        "F15", "W16", "S16", "F16", "W17", "S17", 
                                        "F17", "W18", "S18", "F18", "W19", "S19", 
                                        "F19", "W20", "S20", "F20", "W21", "S21", 
                                        "F21", "W22", "S22", "F22", "W23", "S23", 
                                        "F23", "W24", "S24"), ordered = TRUE)) %>%
  arrange(year, term) %>%
  mutate(
    term_category = case_when(
      str_detect(term, "^F") ~ "Fall",
      str_detect(term, "^W") ~ "Winter",
      str_detect(term, "^S") ~ "Spring"
    )
  ) %>%
  mutate(term_category = factor(term_category, levels = c("Spring", "Winter", "Fall"), ordered = TRUE))

glimpse(combined_data_filtered)


event_summary <- combined_data_filtered %>% 
  group_by(year, term) %>% 
  summarize(term_total = n(), .groups = 'drop') %>%
  group_by(year) %>% 
  mutate(year_total = sum(term_total)) %>% 
  ungroup()

```



## EDA Plots

# Overall Event Summary
```{r}
# Sort the data frame by year and term in the order of F, W, S
event_summary <- event_summary %>%
  mutate(term = factor(term, levels = c("F14", "W15", "S15", 
                                        "F15", "W16", "S16", "F16", "W17", "S17", 
                                        "F17", "W18", "S18", "F18", "W19", "S19", 
                                        "F19", "W20", "S20", "F20", "W21", "S21", 
                                        "F21", "W22", "S22", "F22", "W23", "S23", 
                                        "F23", "W24", "S24"), ordered = TRUE)) %>%
  arrange(year, term) %>%
  mutate(
    term_category = case_when(
      str_detect(term, "^F") ~ "Fall",
      str_detect(term, "^W") ~ "Winter",
      str_detect(term, "^S") ~ "Spring"
    )
  ) %>%
  mutate(term_category = factor(term_category, levels = c("Fall", "Winter", "Spring"), ordered = TRUE))


# Create the stacked bar chart
ggplot(event_summary, aes(x = year, y = term_total, fill = term_category)) +
  geom_bar(stat = "identity") +
  geom_text(aes(label = term_total), 
            position = position_stack(vjust = 0.5), 
            size = 3, 
            color = "black") +
  scale_fill_manual(values = c("Fall" = "#FF9999", "Winter" = "#99CCFF", "Spring" = "#99FF99")) +
  labs(x = "Year", y = "Total Events", fill = "Term") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1)) -> gg_plot

ggplotly(gg_plot)

event_summary
```


```{r}
library(dplyr)
library(ggplot2)
library(plotly)

# Create a sequential index for the terms across all years
event_summary <- event_summary %>%
  arrange(year, term) %>%
  mutate(term_index = row_number())  # This creates a sequential index

gg_plot <- ggplot(event_summary, aes(x = as.numeric(factor(term)), y = term_total, group = 1)) +
  geom_line(aes(color = term_category), size = 1) +
  geom_point(aes(color = term_category), size = 2) +
  scale_color_manual(values = c("Fall" = "#FF9999", "Winter" = "#99CCFF", "Spring" = "#99FF99")) +
  scale_x_continuous(breaks = 1:nrow(event_summary), labels = event_summary$term) +
  labs(x = "Term", y = "Total Events", color = "Term Category") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

# Convert to plotly for interactivity
plotly_chart <- ggplotly(gg_plot)


# In your Shiny app, render this as an interactive plot
plotly_chart
```


```{r}
gg_plot <- ggplot(event_summary, aes(x = year, y = term_total, fill = term_category, group = term_category)) +
  geom_area(position = "stack") +
  labs(x = "Year", y = "Total Events", fill = "Term") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

plotly_chart <- ggplotly(gg_plot)
plotly_chart
```


# Breakdown of Events by Support Level
```{r}
library(dplyr)
library(tidyr)
library(purrr)
library(ggplot2)

# Function to summarize and pivot data for each year
summarize_and_pivot <- function(current_year) {
  combined_data_filtered %>%
    filter(year == current_year) %>%
    group_by(term_category, support_level) %>%
    summarize(Support = n(), .groups = 'drop') %>%
    pivot_wider(names_from = support_level, values_from = Support, values_fill = list(Support = 0)) %>%
    mutate(year = current_year)  # Add a year column to identify the table
}

# List of years to iterate over
years <- unique(combined_data_filtered$year)

# Use purrr::map to apply summarize_and_pivot function for each year
support_tables <- map_dfr(years, summarize_and_pivot)  # Combine into a single data frame

# Melt the data for ggplot
support_tables_melted <- support_tables %>%
  pivot_longer(cols = -c(term_category, year), names_to = "support_level", values_to = "Support")

# Ensure the term_category and support_level are factors with the correct order
support_tables_melted <- support_tables_melted %>%
  filter(support_level != "NA") %>% 
  mutate(term_category = factor(term_category, levels = c("Fall", "Winter", "Spring"), ordered = TRUE),
         support_level = factor(support_level, levels = c("NA", "H", "M", "L"), ordered = TRUE))

# Calculate percentages for each segment
support_tables_melted <- support_tables_melted %>%
  group_by(year, term_category) %>%
  mutate(total_support = sum(Support),
         percentage = (Support / total_support) * 100)

# Create the facet plot
ggplot(support_tables_melted, aes(x = term_category, y = Support, fill = support_level)) +
  geom_bar(stat = "identity", position = "stack") +
  geom_text(aes(label = paste0(Support, " (", round(percentage, 1), "%)")),
            position = position_stack(vjust = 0.5), size = 2, color = "black") +
  facet_wrap(~ year) +
  labs(x = "Term", y = "Support Count", fill = "Support Level") +
  scale_fill_manual(values = c("H" = "#FF9999", "M" = "#99CCFF", "L" = "#99FF99", "NA" = "black")) +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

support_tables_melted
```

```{r}
library(dplyr)
library(ggplot2)
library(plotly)

# Calculate total support and average audience count
support_summary <- combined_data_filtered %>%
  filter(support_level != "NA") %>%
  group_by(year, support_level) %>%
  summarize(
    total_support = n(),
    average_audience_count = mean(as.numeric(audience_count[!is.na(audience_count)]), na.rm = TRUE),
    .groups = 'drop'
  )

# Convert year to a factor with ordered levels if needed
support_summary <- support_summary %>%
  mutate(year = factor(year, levels = unique(year), ordered = TRUE),
         support_level = factor(support_level, levels = c("NA", "H", "M", "L"), ordered = TRUE))

# Create the bubble chart with ggplot2
ggplot_bubble_chart <- ggplot(support_summary, aes(x = year, y = average_audience_count, size = total_support, color = support_level)) +
  geom_point(alpha = 0.7) +
  scale_size_continuous(range = c(3, 20)) + # Adjust the range for bubble sizes
  scale_color_manual(values = c("H" = "#FF9999", "M" = "#99CCFF", "L" = "#99FF99", "NA" = "black")) +
  labs(x = "Year", y = "Average Audience Count", size = "Total Support", color = "Support Level") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

# Convert to a plotly object
plotly_bubble_chart <- ggplotly(ggplot_bubble_chart)

# Render the plotly object (in Shiny, you would use `renderPlotly`)
plotly_bubble_chart

```

```{r}
library(dplyr)
library(ggplot2)
library(plotly)

# Define the correct order for the terms
term_order <- c("F14", "W15", "S15", "F15", "W16", "S16", "F16", "W17", "S17",
                 "F17", "W18", "S18", "F18", "W19", "S19", "F19", "W20", "S20",
                 "F20", "W21", "S21", "F21", "W22", "S22", "F22", "W23", "S23",
                 "F23", "W24", "S24")

# Calculate total support and average audience count
support_summary <- combined_data_filtered %>%
  filter(support_level != "NA") %>%
  group_by(term, support_level) %>%
  summarize(
    total_support = n(),
    average_audience_count = mean(as.numeric(audience_count[!is.na(audience_count)]), na.rm = TRUE),
    .groups = 'drop'
  )

# Convert term to a factor with the specified order
support_summary <- support_summary %>%
  mutate(term = factor(term, levels = term_order, ordered = TRUE),
         support_level = factor(support_level, levels = c("NA", "H", "M", "L"), ordered = TRUE))

# Create the bubble chart with ggplot2
ggplot_bubble_chart <- ggplot(support_summary, aes(x = term, y = average_audience_count, size = total_support, color = support_level)) +
  geom_point(alpha = 0.7) +
  scale_size_continuous(range = c(3, 20)) + # Adjust the range for bubble sizes
  scale_color_manual(values = c("H" = "#FF9999", "M" = "#99CCFF", "L" = "#99FF99", "NA" = "black")) +
  labs(x = "Term", y = "Average Audience Count", size = "Total Support", color = "Support Level") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

# Convert to a plotly object
plotly_bubble_chart <- ggplotly(ggplot_bubble_chart)

# Render the plotly object (in Shiny, you would use `renderPlotly`)
plotly_bubble_chart

```


# Breakdown of Events by Department/Source

```{r}
# Function to summarize and pivot data for each year
summarize_and_pivot_department <- function(current_year) {
  combined_data_filtered %>%
    filter(year == current_year) %>%
    group_by(term_category, department_type) %>%
    summarize(DepartmentCount = n(), .groups = 'drop') %>%
    pivot_wider(names_from = department_type, values_from = DepartmentCount, values_fill = list(DepartmentCount = 0)) %>%
    mutate(year = current_year)  # Add a year column to identify the table
}

# List of years to iterate over
years <- unique(combined_data_filtered$year)

# Use purrr::map to apply summarize_and_pivot function for each year
department_tables <- map_dfr(years, summarize_and_pivot_department)  # Combine into a single data frame

# Melt the data for ggplot
department_tables_melted <- department_tables %>%
  pivot_longer(cols = -c(term_category, year), names_to = "department_type", values_to = "DepartmentCount")

# Ensure the term_category and department_type are factors with the correct order
department_tables_melted <- department_tables_melted %>%
  mutate(term_category = factor(term_category, levels = c("Fall", "Winter", "Spring"), ordered = TRUE),
         department_type = factor(department_type, levels = c("MUSC", "ODOA", "CSA", "Collab", "Others"), ordered = TRUE))

# Filter out NA department types (if any)
department_tables_melted <- department_tables_melted %>%
  filter(department_type != "NA")

# Calculate percentages for each segment
department_tables_melted <- department_tables_melted %>%
  group_by(year, term_category) %>%
  mutate(total_count = sum(DepartmentCount, na.rm = T),
         percentage = (DepartmentCount / total_count) * 100)

# Create the facet plot
ggplot(department_tables_melted, aes(x = term_category, y = DepartmentCount, fill = department_type)) +
  geom_bar(stat = "identity", position = "stack") +
  geom_text(aes(label = paste0(DepartmentCount, " (", round(percentage, 1), "%)")),
            position = position_stack(vjust = 0.5), size = 2, color = "black",
            check_overlap = TRUE) +
  facet_wrap(~ year) +
  labs(x = "Term", y = "Department Count", fill = "Department Type") +
  scale_fill_manual(values = c("MUSC" = "#FF9999", "ODOA" = "#99CCFF", "CSA" = "#99FF99", "Collab" = "#FFD700", "Others" = "#FFA500")) +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

department_tables_melted
```

```{r}
ggplot(department_tables_melted, aes(x = year, y = DepartmentCount, fill = department_type, group = department_type)) +
  geom_area(position = "stack") +
  labs(x = "Term", y = "Department Count", fill = "Department Type") +
  scale_fill_manual(values = c("MUSC" = "#FF9999", "ODOA" = "#99CCFF", "CSA" = "#99FF99", "Collab" = "#FFD700", "Others" = "#FFA500")) +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))


```

```{r}
ggplot(department_tables_melted, aes(x = year, y = DepartmentCount, color = department_type, group = department_type)) +
  geom_line() +
  geom_point() +
  labs(x = "Term", y = "Department Count", color = "Department Type") +
  scale_color_manual(values = c("MUSC" = "#FF9999", "ODOA" = "#99CCFF", "CSA" = "#99FF99", "Collab" = "#FFD700", "Others" = "#FFA500")) +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

```


```{r}
# Summarize and pivot data for each term and add year information
summarize_and_pivot_department <- function(current_term) {
  combined_data_filtered %>%
    filter(term == current_term) %>%
    group_by(department_type, year) %>%
    summarize(DepartmentCount = n(), .groups = 'drop') %>%
    pivot_wider(names_from = department_type, values_from = DepartmentCount, values_fill = list(DepartmentCount = 0)) %>%
    mutate(term = current_term)  # Add a term column to identify the term
}

# List of terms to iterate over
terms <- unique(combined_data_filtered$term)

# Use purrr::map to apply summarize_and_pivot function for each term
department_tables <- map_dfr(terms, summarize_and_pivot_department)  # Combine into a single data frame

# Melt the data for ggplot
department_tables_melted <- department_tables %>%
  pivot_longer(cols = -c(term, year), names_to = "department_type", values_to = "DepartmentCount")

# Ensure the term and department_type are factors with the correct order
department_tables_melted <- department_tables_melted %>%
  mutate(department_type = factor(department_type, levels = c("MUSC", "ODOA", "CSA", "Collab", "Others"), ordered = TRUE))

# Calculate percentages for each segment
department_tables_melted <- department_tables_melted %>%
  group_by(term) %>%
  mutate(total_count = sum(DepartmentCount, na.rm = TRUE),
         percentage = (DepartmentCount / total_count) * 100)

library(ggplot2)
library(dplyr)

# Define shading intervals
shading_intervals <- data.frame(
  start = c(1, 4, 7, 10, 13, 16, 19, 22, 25, 28), # Starting indices of each shaded area
  end = c(3, 6, 9, 12, 15, 18, 21, 24, 27, 30),   # Ending indices of each shaded area
  fill = rep(c("lightgrey", "white"), times = 5)    # Alternating shading colors
)

# Ensure the term is an ordered factor with the correct order
department_tables_melted <- department_tables_melted %>%
  mutate(term = factor(term, levels = c("F14", "W15", "S15", "F15", "W16", "S16", 
                                        "F16", "W17", "S17", "F17", "W18", "S18", 
                                        "F18", "W19", "S19", "F19", "W20", "S20", 
                                        "F20", "W21", "S21", "F21", "W22", "S22", 
                                        "F22", "W23", "S23", "F23", "W24", "S24"), 
                         ordered = TRUE))

# Create the cumulative line chart with alternating shading
ggplot(department_tables_melted, aes(x = term, y = DepartmentCount, color = department_type, group = department_type)) +
  geom_line() +
  geom_point() +
  labs(x = "Term", y = "Department Count", color = "Department Type") +
  scale_color_manual(values = c("MUSC" = "#FF9999", "ODOA" = "#99CCFF", "CSA" = "#99FF99", "Collab" = "#FFD700", "Others" = "#FFA500")) +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
  geom_rect(data = shading_intervals, aes(xmin = start, xmax = end, ymin = -Inf, ymax = Inf, fill = fill), alpha = 0.2, inherit.aes = FALSE) +
  scale_fill_identity()

```


# Breakdown of Music, Collab & ODOA Events by Type
```{r}
# Function to summarize and pivot data for each year
summarize_and_pivot_event <- function(current_year) {
  combined_data_filtered %>%
    filter(year == current_year) %>%
    group_by(term_category, event_type) %>%
    summarize(EventCount = n(), .groups = 'drop') %>%
    pivot_wider(names_from = event_type, values_from = EventCount, values_fill = list(EventCount = 0)) %>%
    mutate(year = current_year)  # Add a year column to identify the table
}

# List of years to iterate over
years <- unique(combined_data_filtered$year)

# Use purrr::map to apply summarize_and_pivot function for each year
event_tables <- map_dfr(years, summarize_and_pivot_event)  # Combine into a single data frame

# Melt the data for ggplot
event_tables_melted <- event_tables %>%
  pivot_longer(cols = -c(term_category, year), names_to = "event_type", values_to = "EventCount")

library(dplyr)

# Check the data types and unique values
str(event_tables_melted)
unique(event_tables_melted$term_category)
unique(event_tables_melted$event_type)

# Inspect the structure of the dataset
summary(event_tables_melted)

# Ensure term_category and event_type are characters before converting to factors
event_tables_melted <- event_tables_melted %>%
  mutate(
    term_category = as.character(term_category),
    event_type = as.character(event_type)
  )

# Apply the factor conversion with ordering
event_tables_melted <- event_tables_melted %>%
  mutate(
    term_category = factor(term_category, levels = c("Fall", "Winter", "Spring"), ordered = TRUE),
    event_type = factor(event_type, levels = c("Ensemble Concert", "Student Activity", "Studio Recital",
                                               "Guest", "Faculty Recital", "Student Recital",
                                               "Special Events", "Presentation", "Masterclass"),
                        ordered = TRUE)
  )


# Calculate percentages for each segment
event_tables_melted <- event_tables_melted %>%
  group_by(year, term_category) %>%
  mutate(total_count = sum(EventCount, na.rm = T),
         percentage = (EventCount / total_count) * 100)

# Create the facet plot
ggplot(event_tables_melted, aes(x = term_category, y = EventCount, fill = event_type)) +
  geom_bar(stat = "identity", position = "stack") +
  geom_text(aes(label = paste0(EventCount, " (", round(percentage, 1), "%)")),
            position = position_stack(vjust = 0.5), size = 2, color = "black",
            check_overlap = TRUE) +
  facet_wrap(~ year) +
  labs(x = "Term", y = "Event Count", fill = "Event Type") +
  scale_fill_brewer(palette = "Set2") +  # Using a Brewer palette for default colors
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

event_tables_melted
```

```{r}
combined_data_filtered %>% filter(event_type == "Guest",
                                  str_detect(department, "MUSC")) %>% 
  filter(!str_detect(what, "Masterclass")) -> guest_only

write_csv(guest_only, file = "guest_only.csv")


```

```{r}
music_guest_only <- read_csv(file = "guest_only.csv")

print_guest_dat <- music_guest_only %>% 
  select(date, what, genre, sponsor, year, term_category) %>% 
  mutate(what = str_replace_all(what, "GUEST: ", ""))

write_tsv(print_guest_dat, "print_guest.tsv")
glimpse(print_guest_dat)

print_guest_fn <- function(data) {
  # Extract unique years
  years <- unique(data$year)
  
  for (year in years) {
    cat("\n---------------------------\n")  # Divider between years
    cat(year, "\n", sep = "")
    
    # Extract term categories for the current year
    terms <- unique(data$term_category[data$year == year])
    
    for (term in terms) {
      cat("\n", term, "\n", sep = "")
      
      # Extract rows corresponding to the current term and year
      term_data <- data[data$year == year & data$term_category == term, ]
      
      if (nrow(term_data) == 0) {
        cat("No guests\n")
      } else {
        for (i in 1:nrow(term_data)) {
          event <- term_data$what[i]
          genre <- term_data$genre[i]
          sponsor <- term_data$sponsor[i]
          cat(event, " | ", genre, " | ", sponsor, "\n", sep = "")
        }
      }
    }
  }
  
  cat("\n---------------------------\n")  # Final divider
}


print_guest_fn(print_guest_dat)

```

```{r}
library(lubridate)
library(dplyr)

# Example mapping of term to start dates
term_start_dates <- data.frame(
  term = c("F14", "W15", "S15", 
           "F15", "W16", "S16", 
           "F16", "W17", "S17", 
           "F17", "W18", "S18", 
           "F18", "W19", "S19", 
           "F19", "W20", "S20", 
           "F20", "W21", "S21", 
           "F21", "W22", "S22", 
           "F22", "W23", "S23", 
           "F23", "W24", "S24"),  # Add all relevant terms
  start_date = as.Date(c("2014-09-15", "2015-01-05", "2015-03-30",
                         "2015-09-14", "2016-01-04", "2016-03-28",
                         "2016-09-12", "2017-01-04", "2017-03-27",
                         "2017-09-11", "2018-01-03", "2018-03-26",
                         "2018-09-10", "2019-01-07", "2019-04-01",
                         "2019-09-16", "2020-01-06", "2020-03-30",
                         "2020-09-14", "2021-01-04", "2021-03-29",
                         "2021-09-15", "2022-01-05", "2022-03-28",
                         "2022-09-12", "2023-01-04", "2023-03-27",
                         "2023-09-11", "2024-01-03", "2024-03-25"))  # Adjust start dates accordingly
)

# Join the start dates to the main data and calculate week_of_term
combined_data_filtered <- combined_data_filtered %>%
  left_join(term_start_dates, by = c("term" = "term")) %>%
  mutate(
    # Calculate the week of the term based on Monday as the first day of the week
    week_of_term = as.integer((floor_date(date, unit = "week", week_start = 1) - 
                               floor_date(start_date, unit = "week", week_start = 1)) / 7) + 1
  )

# Reorder columns to make week_of_term the third column
combined_data_filtered <- combined_data_filtered %>%
  select(venue, date, week_of_term, everything())

# Clean up by removing the start_date column if needed
combined_data_filtered <- combined_data_filtered %>%
  select(-start_date)

```


```{r}
# Define the file codes
file_codes <- c("F14", "W15", "S15", "F15", "W16", "S16", "F16", "W17", "S17",
                "F17", "W18", "S18", "F18", "W19", "S19", "F19", "W20", "S20",
                "F20", "W21", "S21", "F21", "W22", "S22", "F22", "W23", "S23",
                "F23", "W24", "S24")
# Define the term start dates (hardcoded as per the original logic)
term_start_dates <- data.frame(
  term = file_codes,  # Add all relevant terms
  start_date = as.Date(c("2014-09-15", "2015-01-05", "2015-03-30",
                         "2015-09-14", "2016-01-04", "2016-03-28",
                         "2016-09-12", "2017-01-04", "2017-03-27",
                         "2017-09-11", "2018-01-03", "2018-03-26",
                         "2018-09-10", "2019-01-07", "2019-04-01",
                         "2019-09-16", "2020-01-06", "2020-03-30",
                         "2020-09-14", "2021-01-04", "2021-03-29",
                         "2021-09-15", "2022-01-05", "2022-03-28",
                         "2022-09-12", "2023-01-04", "2023-03-27",
                         "2023-09-11", "2024-01-03", "2024-03-25"))  # Adjust start dates accordingly
)
write_csv(term_start_dates, "data/term_list.csv")
```





```{r}
data_dir <- "data/"

term_list <- read_csv("data/term_list.csv")

dataWranglingfn <- function(term_list) {
  # Create a function to read a file given its code
  read_and_clean_event_file <- function(code) {
    file_path <- paste0(data_dir, "2015-2024 Events Data - ", code, " - Event Data.csv")
    data <- read_csv(file_path)
    clean_names(data) %>% 
      mutate(across(everything(), as.character)) %>% 
      mutate(term = code)
  }
  
  term_to_year <- function(term) {
    year <- as.numeric(str_sub(term, 2, 3))
    season <- str_sub(term, 1, 1)
    start_year <- if_else(season == "F", 2000 + year, 2000 + year - 1)
    end_year <- start_year + 1
    return(paste0(start_year, "-", end_year))
  }
  
  # Use purrr to read all files and store them in a named list
  event_data_list <- set_names(map(term_list$term, read_and_clean_event_file), term_list$term)
  
  combined_data <- reduce(event_data_list, full_join)
  
  # Function to calculate the week of term
  calculate_week_of_term <- function(event_date, term) {
    start_date <- term_start_dates %>%
      filter(term == !!term) %>%
      pull(start_date)
    
    if(length(start_date) == 0) return(NA_integer_)  # Return NA if no start_date is found
    
    # Calculate the week of the term based on Monday as the first day of the week
    week_of_term <- as.integer((floor_date(event_date, unit = "week", week_start = 1) - 
                                  floor_date(start_date, unit = "week", week_start = 1)) / 7) + 1
    return(week_of_term)
  }
  
  # Apply the new function to calculate week_of_term dynamically
  
  combined_data_filtered <- combined_data %>%
    filter(!is.na(what), what != "") %>%
    filter(what != "Choir & Jazz Rehearsal") %>%
    filter(what != "Jazz Rehearsal") %>%
    mutate(date = as.Date(ymd(date))) %>%
    mutate(
      livestream = coalesce(livestream, live_stream)  # If 'livestream' is NA, use 'live_stream'
    ) %>%
    select(-live_stream) %>%  
    mutate(department = ifelse(str_detect(what, "CSA|Just Cellin|Lunar New Year|ACA|A Cappella|Accidentals|Exit 69|Date Knight|Knights|Knightingales|International Festival"), "CSA", department)) %>% 
    mutate(
      support_level = fct_relevel(factor(support_level), "H", "M", "L"),
      audio_needs = fct_relevel(factor(audio_needs), "H", "M", "L"),
      stage_needs = fct_relevel(factor(stage_needs), "H", "M", "L"),
      lighting_needs = fct_relevel(factor(lighting_needs), "H", "M", "L"),
      projection = fct_relevel(factor(projection), "Y", "N"),
      video_recording = fct_relevel(factor(video_recording), "Y", "N"),
      livestream = fct_relevel(factor(livestream), "Y", "N"),
      poster = fct_relevel(factor(poster), "Y", "N"),
      program = fct_relevel(factor(program), "Y", "N"),
      reception = fct_relevel(factor(reception), "Y", "N")
    ) %>%
    mutate(
      venue = factor(venue),
      department = factor(department)
    ) %>%  
    mutate(
      audience_count = as.numeric(ifelse(grepl("^[0-9]+$", audience_count), audience_count, NA)),
      days_committed = as.numeric(ifelse(grepl("^[0-9]+$", days_committed), days_committed, NA)),
      av_staff = as.numeric(ifelse(grepl("^[0-9]+$", av_staff), av_staff, NA)),
      pac_staff = as.numeric(ifelse(grepl("^[0-9]+$", pac_staff), pac_staff, NA))
    ) %>%  
    mutate(support_level = if_else(support_level == "N" | support_level == "Y", "L", support_level)) %>%
    mutate(
      department = str_replace_all(department, "WCC", "ODOA"),
      department = str_replace_all(department, "MSUC", "MUSC"),
      department = str_replace_all(department, "French Dept|French", "FREN"),
      department = str_replace_all(department, "English", "ENGL"),
      department = str_replace_all(department, "Pres. Office", "PRES"),
      department = str_replace_all(department, "History", "HIST"),
      department = str_replace_all(department, "THD", "THDA"),
      department = str_replace_all(department, "Inclusion & Equity", "IEC"),
      venue = str_replace_all(venue, "Skinner Chapel", "Chapel"),
      department = str_replace_all(department, "/", " & "),
      department = str_replace_all(department, ",", " &"),
      venue = str_replace_all(venue, ",", " &"),
      department = str_replace_all(department, "\\s+", " ")  # Remove extra spaces
    ) %>%
    select(-wk) %>%
    mutate(department_type = case_when(
      department == "MUSC" ~ "MUSC",
      department == "ODOA" ~ "ODOA",
      department == "CSA" ~ "CSA",
      str_detect(department, "&") ~ "Collab",
      TRUE ~ "Others"
    )) %>%
    mutate(what = str_replace_all(what, "Jazz Ensemble Concert|Jazz Area Concert", "Jazz Concert"),
           what = str_replace_all(what, "Symphony Band Concert", "Symphony Concert"),
           what = str_replace_all(what, "Composition Recital", "Composition Showcase Recital"),
           what = str_replace_all(what, "Harpichord", "Harpsichord"),
           what = str_replace_all(what, "Emsemble", "Ensemble"),
           what = str_replace_all(what, "Juest Cellin'", "Just Cellin'"),
           what = str_replace_all(what, "Facutly|FACULTY|Mazariello", "Faculty")) %>%
    mutate(event_type = case_when(
      str_detect(what, "GUEST|ODOA|Concert Series|SPCO") ~ "Guest / Masterclass",
      str_detect(what, "Faculty") ~ "Faculty Recital",
      str_detect(what, "Faculty&Guest") ~ "Faculty + Guest",
      str_detect(what, "Student|Senior|Junior|Piano Recital: |Johnson|Verma Jameson") ~ "Student Recital",
      str_detect(what, "Studio Recital|Organ & Harpsichord|Composition Showcase Recital|Chamber Recital|Chamber Music Recital|Chamber Music|Organ Recital|Strings Recital|Violin & Viola|Violin/Viola|Drum Ensemble|Drum Recital|Voice Showcase Recital|Chinese Music Recital|Piano Studios Recital|Jazz Chamber|Piano Recital|Comps Fest|Recorder Recital|Music Ensemble|Studio") ~ "Studio Recital",
      str_detect(what, "Orchestra Concert|Jazz Concert|Symphony Concert|Symphony Band|Choir Concert|Orchestra and Choir|Chinese & Global|Chinese Global Concert|Chinese and Global|Chinese Music Concert|Chinese Music Ensemble|Chinese Ensemble|Music Comps|Jazz Vocal Concert") ~ "Ensemble Concert",
      str_detect(what, "CSA|Just Cellin|Lunar New Year|ACA|A Cappella|Accidentals|Exit 69|Date Knight|Knights|Knightingales|International Festival") ~ "Student Activity",
      str_detect(what, "Masterclass|Lecture|Symposium") ~ "Guest / Masterclass",
      str_detect(what, "Trustees|Trustee's|Presidents|Conference|President's|Presentation") ~ "Presentation",
      str_detect(what, "Clinic|Music Fest|Music Department Showcase|Melinda Russell|Launch|Event|Opening") ~ "Special Events",
      TRUE ~ "Guest"
    )) %>%
    mutate(year = term_to_year(term)) %>%
    mutate(term = factor(term, levels = term_start_dates$term, ordered = TRUE)) %>%
    arrange(year, term) %>%
    mutate(
      term_category = case_when(
        str_detect(term, "^F") ~ "Fall",
        str_detect(term, "^W") ~ "Winter",
        str_detect(term, "^S") ~ "Spring"
      )
    ) %>%
    mutate(term_category = factor(term_category, levels = c("Spring", "Winter", "Fall"), ordered = TRUE)) %>%
    # Apply week_of_term calculation
    rowwise() %>%
    mutate(week_of_term = calculate_week_of_term(date, term)) %>%
    ungroup()
  
  # Example event summary after filtering and transformation
  event_summary <- combined_data_filtered %>%
    group_by(year, term) %>%
    summarize(term_total = n(), .groups = 'drop') %>%
    group_by(year) %>%
    mutate(year_total = sum(term_total)) %>%
    ungroup()
  
  combined_data_filtered %>% filter(event_type == "Guest / Masterclass",
                                    str_detect(department, "MUSC")) %>% 
    filter(!str_detect(what, "Masterclass")) -> guest_only
  
  # Get the unique years and append the "All" option
  year_choices <- c("All", combined_data_filtered %>% pull(year) %>% unique())
  
  return(list(combined_data_filtered = combined_data_filtered,
              event_summary = event_summary,
              year_choices = year_choices))
}

result_list <- dataWranglingfn(term_list)
combined_data_filtered <- result_list$combined_data_filtered
event_summary <- result_list$event_summary
year_choices <- result_list$year_choices
```


```{r}
term_list <- read_csv("data/term_list.csv")

guest_only_base <- read_csv("data/guest_only_base.csv")

dataWranglingfn <- function(term_list) {
  # Set term_list as term_start_dates
  term_start_dates <- term_list
  
  # Create a function to read a file given its code
  read_and_clean_event_file <- function(code) {
    file_path <- paste0(data_dir, "2015-2024 Events Data - ", code, " - Event Data.csv")
    data <- read_csv(file_path)
    clean_names(data) %>%
      mutate(across(everything(), as.character)) %>%
      mutate(term = code)
  }
  
  # Convert term to academic year range
  term_to_year <- function(term) {
    year <- as.numeric(str_sub(term, 2, 3))
    season <- str_sub(term, 1, 1)
    start_year <- if_else(season == "F", 2000 + year, 2000 + year - 1)
    end_year <- start_year + 1
    return(paste0(start_year, "-", end_year))
  }
  
  # Use purrr to read all files and store them in a named list
  event_data_list <- set_names(map(term_list$term, read_and_clean_event_file), term_list$term)
  
  combined_data <- reduce(event_data_list, full_join)
  
  # Function to calculate the week of term
  calculate_week_of_term <- function(event_date, term) {
    start_date <- term_start_dates %>%
      filter(term == !!term) %>%
      pull(start_date)
    
    if (length(start_date) == 0) return(NA_integer_)  # Return NA if no start_date is found
    
    # Calculate the week of the term based on Monday as the first day of the week
    week_of_term <- as.integer((floor_date(event_date, unit = "week", week_start = 1) - 
                                  floor_date(start_date, unit = "week", week_start = 1)) / 7) + 1
    return(week_of_term)
  }
  
  # Filter and clean combined data
  combined_data_filtered <- combined_data %>%
    filter(!is.na(what), what != "") %>%
    filter(what != "Choir & Jazz Rehearsal") %>%
    filter(what != "Jazz Rehearsal") %>%
    mutate(date = as.Date(ymd(date))) %>%
    mutate(
      livestream = coalesce(livestream, live_stream)  # If 'livestream' is NA, use 'live_stream'
    ) %>%
    select(-live_stream) %>%
    # Perform further cleaning and factor conversions
    mutate(department = ifelse(str_detect(what, "CSA|Just Cellin|Lunar New Year|ACA|A Cappella|Accidentals|Exit 69|Date Knight|Knights|Knightingales|International Festival"), "CSA", department)) %>%
    mutate(
      support_level = fct_relevel(factor(support_level), "H", "M", "L"),
      audio_needs = fct_relevel(factor(audio_needs), "H", "M", "L"),
      stage_needs = fct_relevel(factor(stage_needs), "H", "M", "L"),
      lighting_needs = fct_relevel(factor(lighting_needs), "H", "M", "L"),
      projection = fct_relevel(factor(projection), "Y", "N"),
      video_recording = fct_relevel(factor(video_recording), "Y", "N"),
      livestream = fct_relevel(factor(livestream), "Y", "N"),
      poster = fct_relevel(factor(poster), "Y", "N"),
      program = fct_relevel(factor(program), "Y", "N"),
      reception = fct_relevel(factor(reception), "Y", "N")
    ) %>%
    mutate(
      venue = factor(venue),
      department = factor(department)
    ) %>%
    mutate(
      audience_count = as.numeric(ifelse(grepl("^[0-9]+$", audience_count), audience_count, NA)),
      days_committed = as.numeric(ifelse(grepl("^[0-9]+$", days_committed), days_committed, NA)),
      av_staff = as.numeric(ifelse(grepl("^[0-9]+$", av_staff), av_staff, NA)),
      pac_staff = as.numeric(ifelse(grepl("^[0-9]+$", pac_staff), pac_staff, NA))
    ) %>%
    mutate(support_level = if_else(support_level == "N" | support_level == "Y", "L", support_level)) %>%
    # Further cleaning
    mutate(
      department = str_replace_all(department, "WCC", "ODOA"),
      department = str_replace_all(department, "MSUC", "MUSC"),
      department = str_replace_all(department, "French Dept|French", "FREN"),
      department = str_replace_all(department, "English", "ENGL"),
      department = str_replace_all(department, "Pres. Office", "PRES"),
      department = str_replace_all(department, "History", "HIST"),
      department = str_replace_all(department, "THD", "THDA"),
      department = str_replace_all(department, "Inclusion & Equity", "IEC"),
      venue = str_replace_all(venue, "Skinner Chapel", "Chapel"),
      department = str_replace_all(department, "/", " & "),
      department = str_replace_all(department, ",", " &"),
      venue = str_replace_all(venue, ",", " &"),
      department = str_replace_all(department, "\\s+", " ")  # Remove extra spaces
    ) %>%
    # Handle department types
    mutate(department_type = case_when(
      department == "MUSC" ~ "MUSC",
      department == "ODOA" ~ "ODOA",
      department == "CSA" ~ "CSA",
      str_detect(department, "&") ~ "Collab",
      TRUE ~ "Others"
    )) %>%
    # Correcting event names
    mutate(what = str_replace_all(what, "Jazz Ensemble Concert|Jazz Area Concert", "Jazz Concert")) %>%
    mutate(year = term_to_year(term)) %>%
    mutate(term = factor(term, levels = term_start_dates$term, ordered = TRUE)) %>%
    arrange(year, term) %>%
    mutate(
      term_category = case_when(
        str_detect(term, "^F") ~ "Fall",
        str_detect(term, "^W") ~ "Winter",
        str_detect(term, "^S") ~ "Spring"
      )
    ) %>%
    mutate(term_category = factor(term_category, levels = c("Spring", "Winter", "Fall"), ordered = TRUE)) %>%
    # Apply week_of_term calculation
    rowwise() %>%
    mutate(week_of_term = calculate_week_of_term(date, term)) %>%
    ungroup()
  
  # Example event summary after filtering and transformation
  event_summary <- combined_data_filtered %>%
    group_by(year, term) %>%
    summarize(term_total = n(), .groups = 'drop') %>%
    group_by(year) %>%
    mutate(year_total = sum(term_total)) %>%
    ungroup()

  # Filter guest-only events (if required)
  combined_data_filtered <- combined_data_filtered %>%
  left_join(guest_only_base %>% select(what, genre, sponsor), by = "what", relationship = "many-to-many")
  
  combined_data_filtered %>%
    filter(event_type == "Guest / Masterclass", str_detect(department, "MUSC")) %>%
    filter(!str_detect(what, "Masterclass")) -> guest_only

  # Get the unique years and append the "All" option
  year_choices <- c("All", combined_data_filtered %>% pull(year) %>% unique())
  
  return(list(combined_data_filtered = combined_data_filtered,
              event_summary = event_summary,
              year_choices = year_choices,
              guest_only = guest_only))
}

dataWranglingfn(term_list)
```

```{r}
# Load A BUNCH of packages
library(shiny)
library(shinydashboard)
library(shinydashboardPlus)
library(dplyr)
library(tidyverse)
library(reactable)
library(reactablefmtr)
library(shinycssloaders)
library(lubridate)
library(ggplot2)
library(shinyjs)
library(viridis)
library(viridisLite)
library(purrr)
library(stringr)
library(forcats)
library(gganimate)
library(ggthemes)
library(leaflet)
library(patchwork)
library(probably)
library(readr)
library(janitor)
library(shinyalert)
library(shinyWidgets)
library(tidyr)
library(tidymodels)
library(yardstick)
library(plotly)
library(hardhat)
library(vip)
library(rpart.plot)
library(ranger)
library(broom)
library(googlesheets4)
library(DT)
library(sodium)
library(formattable)
library(shinyfullscreen)
remotes::install_github("timelyportfolio/dataui")
gs4_auth(email = "lyuk@carleton.edu", cache = ".secrets")



## Local version
# Define the directory containing the files
data_dir <- "data/"

term_list <- read_csv("data/term_list.csv")

guest_only_base <- read_csv("data/guest_only_base.csv")

dataWranglingfn <- function(term_list) {
  # Create a function to read a file given its code
  read_and_clean_event_file <- function(code) {
    file_path <- paste0(data_dir, "2015-2024 Events Data - ", code, " - Event Data.csv")
    data <- read_csv(file_path)
    clean_names(data) %>%
      mutate(across(everything(), as.character)) %>%
      mutate(term = code)
  }

  term_to_year <- function(term) {
    year <- as.numeric(str_sub(term, 2, 3))
    season <- str_sub(term, 1, 1)
    start_year <- if_else(season == "F", 2000 + year, 2000 + year - 1)
    end_year <- start_year + 1
    return(paste0(start_year, "-", end_year))
  }

  # Use purrr to read all files and store them in a named list
  event_data_list <- set_names(map(term_list$term, read_and_clean_event_file), term_list$term)

  combined_data <- reduce(event_data_list, full_join)

  # Function to calculate the week of term
  calculate_week_of_term <- function(event_date, term) {
    start_date <- term_list %>%
      filter(term == !!term) %>%
      pull(start_date)

    if(length(start_date) == 0) return(NA_integer_)  # Return NA if no start_date is found

    # Calculate the week of the term based on Monday as the first day of the week
    week_of_term <- as.integer((floor_date(event_date, unit = "week", week_start = 1) -
                                  floor_date(start_date, unit = "week", week_start = 1)) / 7) + 1
    return(week_of_term)
  }

  # Apply the new function to calculate week_of_term dynamically

  combined_data_filtered <- combined_data %>%
    filter(!is.na(what), what != "") %>%
    filter(what != "Choir & Jazz Rehearsal") %>%
    filter(what != "Jazz Rehearsal") %>%
    mutate(date = as.Date(ymd(date))) %>%
    mutate(
      livestream = coalesce(livestream, live_stream)  # If 'livestream' is NA, use 'live_stream'
    ) %>%
    select(-live_stream) %>%
    mutate(department = ifelse(str_detect(what, "CSA|Just Cellin|Lunar New Year|ACA|A Cappella|Accidentals|Exit 69|Date Knight|Knights|Knightingales|International Festival"), "CSA", department)) %>%
    mutate(
      support_level = fct_relevel(factor(support_level), "H", "M", "L"),
      audio_needs = fct_relevel(factor(audio_needs), "H", "M", "L"),
      stage_needs = fct_relevel(factor(stage_needs), "H", "M", "L"),
      lighting_needs = fct_relevel(factor(lighting_needs), "H", "M", "L"),
      projection = fct_relevel(factor(projection), "Y", "N"),
      video_recording = fct_relevel(factor(video_recording), "Y", "N"),
      livestream = fct_relevel(factor(livestream), "Y", "N"),
      poster = fct_relevel(factor(poster), "Y", "N"),
      program = fct_relevel(factor(program), "Y", "N"),
      reception = fct_relevel(factor(reception), "Y", "N")
    ) %>%
    mutate(
      venue = factor(venue),
      department = factor(department)
    ) %>%
    mutate(
      audience_count = as.numeric(ifelse(grepl("^[0-9]+$", audience_count), audience_count, NA)),
      days_committed = as.numeric(ifelse(grepl("^[0-9]+$", days_committed), days_committed, NA)),
      av_staff = as.numeric(ifelse(grepl("^[0-9]+$", av_staff), av_staff, NA)),
      pac_staff = as.numeric(ifelse(grepl("^[0-9]+$", pac_staff), pac_staff, NA))
    ) %>%
    mutate(support_level = if_else(support_level == "N" | support_level == "Y", "L", support_level)) %>%
    mutate(
      department = str_replace_all(department, "WCC", "ODOA"),
      department = str_replace_all(department, "MSUC", "MUSC"),
      department = str_replace_all(department, "French Dept|French", "FREN"),
      department = str_replace_all(department, "English", "ENGL"),
      department = str_replace_all(department, "Pres. Office", "PRES"),
      department = str_replace_all(department, "History", "HIST"),
      department = str_replace_all(department, "THD", "THDA"),
      department = str_replace_all(department, "Inclusion & Equity", "IEC"),
      venue = str_replace_all(venue, "Skinner Chapel", "Chapel"),
      department = str_replace_all(department, "/", " & "),
      department = str_replace_all(department, ",", " &"),
      venue = str_replace_all(venue, ",", " &"),
      department = str_replace_all(department, "\\s+", " ")  # Remove extra spaces
    ) %>%
    select(-wk) %>%
    mutate(department_type = case_when(
      department == "MUSC" ~ "MUSC",
      department == "ODOA" ~ "ODOA",
      department == "CSA" ~ "CSA",
      str_detect(department, "&") ~ "Collab",
      TRUE ~ "Others"
    )) %>%
    mutate(what = str_replace_all(what, "Jazz Ensemble Concert|Jazz Area Concert", "Jazz Concert"),
           what = str_replace_all(what, "Symphony Band Concert", "Symphony Concert"),
           what = str_replace_all(what, "Composition Recital", "Composition Showcase Recital"),
           what = str_replace_all(what, "Harpichord", "Harpsichord"),
           what = str_replace_all(what, "Emsemble", "Ensemble"),
           what = str_replace_all(what, "Juest Cellin'", "Just Cellin'"),
           what = str_replace_all(what, "Facutly|FACULTY|Mazariello", "Faculty")) %>%
    mutate(event_type = case_when(
      str_detect(what, "GUEST|ODOA|Concert Series|SPCO") ~ "Guest / Masterclass",
      str_detect(what, "Faculty") ~ "Faculty Recital",
      str_detect(what, "Student|Senior|Junior|Piano Recital: |Johnson|Verma Jameson") ~ "Student Recital",
      str_detect(what, "Studio Recital|Organ & Harpsichord|Composition Showcase Recital|Chamber Recital|Chamber Music Recital|Chamber Music|Organ Recital|Strings Recital|Violin & Viola|Violin/Viola|Drum Ensemble|Drum Recital|Voice Showcase Recital|Chinese Music Recital|Piano Studios Recital|Jazz Chamber|Piano Recital|Comps Fest|Recorder Recital|Music Ensemble|Studio") ~ "Studio Recital",
      str_detect(what, "Orchestra Concert|Jazz Concert|Symphony Concert|Symphony Band|Choir Concert|Orchestra and Choir|Chinese & Global|Chinese Global Concert|Chinese and Global|Chinese Music Concert|Chinese Music Ensemble|Chinese Ensemble|Music Comps|Jazz Vocal Concert") ~ "Ensemble Concert",
      str_detect(what, "CSA|Just Cellin|Lunar New Year|ACA|A Cappella|Accidentals|Exit 69|Date Knight|Knights|Knightingales|International Festival") ~ "Student Activity",
      str_detect(what, "Masterclass|Lecture|Symposium") ~ "Guest / Masterclass",
      str_detect(what, "Trustees|Trustee's|Presidents|Conference|President's|Presentation") ~ "Presentation",
      str_detect(what, "Clinic|Music Fest|Music Department Showcase|Melinda Russell|Launch|Event|Opening") ~ "Special Events",
      TRUE ~ "Guest / Masterclass"
    )) %>%
    mutate(event_type = case_when(str_detect(what, "Faculty&Guest") ~ "Faculty + Guest",
                                  TRUE ~ event_type  # Keep existing values for other cases
                                  )) %>% 
    mutate(year = term_to_year(term)) %>%
    mutate(term = factor(term, levels = term_list$term, ordered = TRUE)) %>%
    arrange(year, term) %>%
    mutate(
      term_category = case_when(
        str_detect(term, "^F") ~ "Fall",
        str_detect(term, "^W") ~ "Winter",
        str_detect(term, "^S") ~ "Spring"
      )
    ) %>%
    mutate(term_category = factor(term_category, levels = c("Spring", "Winter", "Fall"), ordered = TRUE)) %>%
    # Apply week_of_term calculation
    rowwise() %>%
    mutate(week_of_term = calculate_week_of_term(date, term)) %>%
    ungroup()

  # Example event summary after filtering and transformation
  event_summary <- combined_data_filtered %>%
    group_by(year, term) %>%
    summarize(term_total = n(), .groups = 'drop') %>%
    group_by(year) %>%
    mutate(year_total = sum(term_total)) %>%
    ungroup()

  # combined_data_filtered <- combined_data_filtered %>%
  #   left_join(guest_only_base %>% select(venue, date, what, department, genre, sponsor), by = join_by(venue, date, what, department)
  #             # %>% select(what, genre, sponsor), by = "what", relationship = "many-to-many"
  #             )
  # Perform the join
  combined_data_filtered_updated <- combined_data_filtered %>%
    left_join(guest_only_base %>% select(venue, date, what, department, term, genre, sponsor), 
              by = c("venue", "date", "what", "department", "term"))
  

  combined_data_filtered_updated %>% filter(event_type == "Guest / Masterclass",
                                    str_detect(department, "MUSC")) %>%
    filter(!str_detect(what, "Masterclass")) -> guest_only

  # Get the unique years and append the "All" option
  year_choices <- c("All", combined_data_filtered_updated %>% pull(year) %>% unique())

  return(list(combined_data_filtered = combined_data_filtered_updated,
              event_summary = event_summary,
              year_choices = year_choices,
              guest_only = guest_only))
}

dataWranglingfn(term_list) -> test1

test1$combined_data_filtered %>% drop_na(audience_count, av_staff, pac_staff) -> combined_data_filtered_cleaned

concert_lm <- lm(audience_count ~ support_level + av_staff*pac_staff, data = combined_data_filtered_cleaned)
summary(concert_lm)
```