library(knitr)
library(readxl)
library(ggplot2)
library(reshape2)
library(kableExtra)
library(splitstackshape)
library(tidyverse, quietly = T)
FALSE ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
FALSE ✔ dplyr     1.1.4     ✔ readr     2.1.5
FALSE ✔ forcats   1.0.1     ✔ stringr   1.5.2
FALSE ✔ lubridate 1.9.3     ✔ tibble    3.3.0
FALSE ✔ purrr     1.1.0     ✔ tidyr     1.3.1
FALSE ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
FALSE ✖ dplyr::filter()     masks stats::filter()
FALSE ✖ dplyr::group_rows() masks kableExtra::group_rows()
FALSE ✖ dplyr::lag()        masks stats::lag()
FALSE ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
set.seed(100)

For use with SCCWRP Satisfaction Survey 2026

obtain data

dat <- read_excel("/Users/kylielanglois/Documents/SCCWRP Staff Survey 2026 data.xlsx", 
                  sheet = 1)
#read in data

var.col<-colnames(dat[grepl("agree", colnames(dat))])
dat.m <- melt(dat, measure.vars = var.col)
# reshape for plotting

summarize data

dat.m.summ<-data.frame(Section=dat.m$Section, 
                       Question=dat.m$Question,
                       Question_char=dat.m$Question_char, 
                       Question_num=dat.m$Question_num, 
                       Job_Class=dat.m$Job_Class, 
                       Response_per_value=dat.m$value, 
                       Value=substr(dat.m$variable, 1, 2))
# only take some columns
dat.m.summ$Value.num<-as.numeric(dat.m.summ$Value)
# for below

dt<-data.table::data.table(dat.m.summ) # make into data.table
dt$Response_per_value[is.na(dt$Response_per_value)]<-0 # change NA --> 0
# remove "zero" responses

df.expanded<-expandRows(dt, "Response_per_value") # reshape data
FALSE The following rows have been dropped from the input: 
FALSE 
FALSE 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 19, 20, 21, 23, 24, 25, 26, 27, 28, 31, 32, 33, 35, 37, 39, 40, 41, 42, 44, 45, 47, 52, 57, 64, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 95, 96, 99, 101, 102, 103, 104, 105, 107, 108, 109, 111, 112, 117, 118, 119, 120, 121, 123, 124, 161, 163, 164, 165, 166, 167, 168, 169, 171, 172, 173, 175, 176, 179, 180, 188, 191, 192, 195, 199, 204, 241, 244, 245, 247, 248, 249, 251, 252, 253, 254, 255, 256, 259, 260, 264, 266, 267, 268, 271, 279, 281, 284, 292, 299, 321, 323, 324, 325, 327, 328, 329, 331, 332, 335, 336, 338, 341, 344, 361, 362, 363, 364, 367, 368, 372, 381, 382, 384, 385, 387, 388, 391, 401, 402, 403, 404, 406, 407, 408, 409, 416, 421, 423, 424, 425, 426, 427, 428, 431, 436, 441, 443, 447, 448, 450, 452, 456, 461, 467, 468, 469, 471, 472, 476
# get stats for all data
dat.agg <- df.expanded %>%
  group_by(Section, Question, Question_char, Question_num, Job_Class) %>%
  summarise(mean=round(mean(Value.num, na.rm = T), 2), 
            min=round(min(Value.num, na.rm = T), 2),
            median=round(median(Value.num, na.rm = T), 2), 
            st_dev=round(sd(Value.num, na.rm=T), 2))
FALSE `summarise()` has grouped output by 'Section', 'Question', 'Question_char',
FALSE 'Question_num'. You can override using the `.groups` argument.

visualize data

Working at SCCWRP

Section Question Question_num
Working at SCCWRP SCCWRP fosters a safe, inclusive work environment. 1
Working at SCCWRP SCCWRP values its employees.  2
Working at SCCWRP SCCWRP treats its employees fairly.  3
Working at SCCWRP SCCWRP’s staff is diverse.  4
Question_char Job_Class mean min median st_dev
1
  1. Senior Management
4.71 3 5.0 0.76
2
  1. Senior Management
4.57 3 5.0 0.79
3
  1. Senior Management
4.57 3 5.0 0.79
4
  1. Senior Management
4.14 2 5.0 1.21
1
  1. Scientist/Engineer
4.56 2 5.0 0.81
2
  1. Scientist/Engineer
4.31 3 4.0 0.70
3
  1. Scientist/Engineer
4.12 2 4.0 1.02
4
  1. Scientist/Engineer
3.81 2 4.0 0.91
1
  1. Admin, IT, comms
4.88 4 5.0 0.35
2
  1. Admin, IT, comms
4.38 3 4.5 0.74
3
  1. Admin, IT, comms
4.62 3 5.0 0.74
4
  1. Admin, IT, comms
4.38 3 4.5 0.74
1
  1. Technician
4.06 2 4.5 1.18
2
  1. Technician
3.56 1 4.0 1.15
3
  1. Technician
3.50 1 4.0 1.37
4
  1. Technician
4.19 2 4.5 1.05

Understanding of SCCWRP

Section Question Question_num
Understanding of SCCWRP I understand SCCWRP’s mission. 1
Understanding of SCCWRP I understand how the work I do advances SCCWRP’s mission. 2
Understanding of SCCWRP I understand how SCCWRP evaluates my job performance. 3
Understanding of SCCWRP I understand how SCCWRP evaluates the organization’s performance. 4
Understanding of SCCWRP I understand SCCWRP’s business model.  5
Question_char Job_Class mean min median st_dev
1
  1. Senior Management
4.86 4 5.0 0.38
2
  1. Senior Management
4.86 4 5.0 0.38
3
  1. Senior Management
4.86 4 5.0 0.38
4
  1. Senior Management
4.86 4 5.0 0.38
5
  1. Senior Management
4.86 4 5.0 0.38
1
  1. Scientist/Engineer
5.00 5 5.0 0.00
2
  1. Scientist/Engineer
4.75 4 5.0 0.45
3
  1. Scientist/Engineer
4.56 2 5.0 0.89
4
  1. Scientist/Engineer
4.56 2 5.0 0.81
5
  1. Scientist/Engineer
4.38 2 4.5 0.81
1
  1. Admin, IT, comms
4.50 3 5.0 0.76
2
  1. Admin, IT, comms
4.62 4 5.0 0.52
3
  1. Admin, IT, comms
4.25 2 5.0 1.16
4
  1. Admin, IT, comms
4.25 3 5.0 1.04
5
  1. Admin, IT, comms
4.38 3 5.0 0.92
1
  1. Technician
4.69 4 5.0 0.48
2
  1. Technician
4.38 2 5.0 0.96
3
  1. Technician
3.75 1 4.0 1.24
4
  1. Technician
3.94 1 4.0 1.12
5
  1. Technician
3.69 1 4.0 1.20

Job Satisfaction

Section Question Question_num
Job satisfaction I enjoy working at SCCWRP. 1
Job satisfaction I have a healthy work-life balance at SCCWRP. 2
Job satisfaction I would recommend working at SCCWRP to a friend. 3
Job satisfaction I am satisfied with my career development opportunities at SCCWRP. 4
Job satisfaction I feel appreciated by SCCWRP. 5
Job satisfaction My team/department works effectively together. 6
Job satisfaction My team/department works effectively with other teams/departments. 7
Job satisfaction I have access to the right equipment and training to do my job. 8
Job satisfaction I receive support from SCCWRP when I ask for it. 9
Job satisfaction I feel comfortable expressing ideas and concerns to my supervisor. 10
Job satisfaction I feel safe filling out this survey truthfully. 11
Question_char Job_Class mean min median st_dev
1
  1. Senior Management
4.71 3 5.0 0.76
2
  1. Senior Management
4.00 2 4.0 1.15
3
  1. Senior Management
4.57 3 5.0 0.79
4
  1. Senior Management
4.71 3 5.0 0.76
5
  1. Senior Management
4.43 3 5.0 0.79
6
  1. Senior Management
4.57 4 5.0 0.53
7
  1. Senior Management
4.43 3 5.0 0.79
8
  1. Senior Management
4.71 3 5.0 0.76
9
  1. Senior Management
4.71 3 5.0 0.76
10
  1. Senior Management
4.43 1 5.0 1.51
11
  1. Senior Management
4.43 1 5.0 1.51
1
  1. Scientist/Engineer
4.50 2 5.0 0.82
2
  1. Scientist/Engineer
4.12 1 4.5 1.20
3
  1. Scientist/Engineer
4.25 2 4.5 0.93
4
  1. Scientist/Engineer
4.38 2 5.0 0.89
5
  1. Scientist/Engineer
4.31 3 4.0 0.70
6
  1. Scientist/Engineer
4.44 2 5.0 0.89
7
  1. Scientist/Engineer
4.44 3 4.5 0.63
8
  1. Scientist/Engineer
4.56 2 5.0 0.81
9
  1. Scientist/Engineer
4.31 3 4.0 0.70
10
  1. Scientist/Engineer
4.44 2 5.0 1.09
11
  1. Scientist/Engineer
4.62 2 5.0 0.81
1
  1. Admin, IT, comms
4.62 4 5.0 0.52
2
  1. Admin, IT, comms
4.25 3 4.5 0.89
3
  1. Admin, IT, comms
4.62 4 5.0 0.52
4
  1. Admin, IT, comms
3.71 1 4.0 1.38
5
  1. Admin, IT, comms
4.38 3 4.5 0.74
6
  1. Admin, IT, comms
4.12 3 4.0 0.83
7
  1. Admin, IT, comms
4.12 1 4.5 1.36
8
  1. Admin, IT, comms
4.38 3 4.5 0.74
9
  1. Admin, IT, comms
4.62 4 5.0 0.52
10
  1. Admin, IT, comms
4.50 3 5.0 0.76
11
  1. Admin, IT, comms
4.38 1 5.0 1.41
1
  1. Technician
4.19 2 4.5 0.98
2
  1. Technician
3.50 1 4.0 1.51
3
  1. Technician
3.31 1 3.5 1.40
4
  1. Technician
3.25 1 3.5 1.61
5
  1. Technician
3.50 1 4.0 1.41
6
  1. Technician
4.19 2 4.5 1.05
7
  1. Technician
4.00 2 4.0 0.93
8
  1. Technician
4.38 3 4.5 0.72
9
  1. Technician
4.27 1 5.0 1.10
10
  1. Technician
4.00 1 4.0 1.26
11
  1. Technician
3.56 1 3.5 1.36

Executive Management

Section Question Question_num
SCCWRP executive management Executive management keeps SCCWRP running smoothly. 1
SCCWRP executive management Executive management has a clear vision for SCCWRP’s directions as an organization. 2
SCCWRP executive management Executive management communicates well with SCCWRP staff. 3
SCCWRP executive management Executive management leads by example.  4
Question_char Job_Class mean min median st_dev
1
  1. Senior Management
4.67 4 5.0 0.52
2
  1. Senior Management
4.83 4 5.0 0.41
3
  1. Senior Management
3.67 2 4.0 1.03
4
  1. Senior Management
4.33 3 4.5 0.82
1
  1. Scientist/Engineer
4.69 4 5.0 0.48
2
  1. Scientist/Engineer
4.94 4 5.0 0.25
3
  1. Scientist/Engineer
3.56 2 4.0 1.03
4
  1. Scientist/Engineer
4.31 2 4.5 0.87
1
  1. Admin, IT, comms
4.71 4 5.0 0.49
2
  1. Admin, IT, comms
4.71 4 5.0 0.49
3
  1. Admin, IT, comms
4.25 2 4.5 1.04
4
  1. Admin, IT, comms
4.25 2 4.5 1.04
1
  1. Technician
4.19 2 4.5 1.05
2
  1. Technician
4.73 4 5.0 0.46
3
  1. Technician
3.40 2 3.0 1.06
4
  1. Technician
3.33 1 3.0 1.18