path_numeric <- "ECTA_Checklist_numeric.xlsx"
sheet <- 1
raw <- read_excel(path_numeric, sheet = sheet, col_names = FALSE, col_types = "text")
hdr1 <- raw %>% slice(1) %>% unlist(use.names = FALSE) %>% as.character()
hdr2 <- raw %>% slice(2) %>% unlist(use.names = FALSE) %>% as.character()
df <- raw %>%
slice(-(1:2)) %>%
setNames(make_clean_names(hdr1))
# show all columns that look like response num
names(df)[str_detect(names(df), "response")]## [1] "response_num" "response_id"# look at the first 10 rows of any response_num-ish columns
df %>%
select(matches("response")) %>%
slice(1:10)## # A tibble: 10 × 2
## response_num response_id
## <chr> <chr>
## 1 1 R_5n8uf417ewQHPAB
## 2 2 R_5n2asikY37FyhVv
## 3 3 R_7thFz2VzkuC5oE2
## 4 4 R_6kq3YnehcLT57tW
## 5 5 R_1Eu5FfxrdN4vh7b
## 6 6 R_5I6ezMzm8JJEENz
## 7 7 R_5PoNheVM0IP8CKw
## 8 8 R_6rlzfYQDgZWx1uR
## 9 9 R_7Es5c6nNuvoGlPN
## 10 10 R_6vbIUHrePjU9m2Ldf <- df %>%
mutate(response_num = as.numeric(response_num))
df %>% count(response_num, sort = FALSE)## # A tibble: 35 × 2
## response_num n
## <dbl> <int>
## 1 1 1
## 2 2 1
## 3 3 1
## 4 4 1
## 5 5 1
## 6 6 1
## 7 7 1
## 8 8 1
## 9 9 1
## 10 10 1
## # ℹ 25 more rows## [1] "response_num" "response_id" "landing_page_3" "cil_rating_1"
## [5] "cil_rating_2" "cil_rating_3" "cil_rating_4" "cil_rating_5"
## [9] "cil_rating_6" "cil_rating_7" "mgl_rating_1" "mgl_rating_2"
## [13] "mgl_rating_3" "mgl_rating_4" "mgl_rating_5" "mgl_rating_6"
## [17] "mgl_rating_7" "mgl_rating_8" "vdl_rating_1" "vdl_rating_2"
## [21] "vdl_rating_3" "vdl_rating_4" "vdl_rating_5" "vdl_rating_6"
## [25] "vdl_rating_7" "vdl_rating_8"collab_items <- c("cil_rating_1",
"cil_rating_2",
"cil_rating_3",
"cil_rating_4",
"cil_rating_5",
"cil_rating_6",
"cil_rating_7")
mot_items <- c("mgl_rating_1",
"mgl_rating_2",
"mgl_rating_3",
"mgl_rating_4",
"mgl_rating_5",
"mgl_rating_6",
"mgl_rating_7",
"mgl_rating_8")
vision_items <- c("vdl_rating_1",
"vdl_rating_2",
"vdl_rating_3",
"vdl_rating_4",
"vdl_rating_5",
"vdl_rating_6",
"vdl_rating_7",
"vdl_rating_8") # Safety: ensure item cols are numeric
df <- df %>%
mutate(across(all_of(c(collab_items, mot_items, vision_items)), ~ parse_number(as.character(.x))))
df_scores <- df %>%
mutate(
collab_mean = rowMeans(across(all_of(collab_items)), na.rm = TRUE),
mot_mean = rowMeans(across(all_of(mot_items)), na.rm = TRUE),
vision_mean = rowMeans(across(all_of(vision_items)), na.rm = TRUE),
# # Optional: proportion of items rated 4 for each checklist (top-box intensity)
# collab_topbox = rowMeans(across(all_of(collab_items), ~ .x == 4), na.rm = TRUE),
# mot_topbox = rowMeans(across(all_of(mot_items), ~ .x == 4), na.rm = TRUE),
# vision_topbox = rowMeans(across(all_of(vision_items), ~ .x == 4), na.rm = TRUE)
)
df_scores %>% select(collab_mean, mot_mean, vision_mean) %>%
summary() ## collab_mean mot_mean vision_mean
## Min. :1.571 Min. :2.750 Min. :1.000
## 1st Qu.:2.893 1st Qu.:3.625 1st Qu.:3.125
## Median :3.429 Median :3.750 Median :3.500
## Mean :3.332 Mean :3.728 Mean :3.296
## 3rd Qu.:3.821 3rd Qu.:4.000 3rd Qu.:3.750
## Max. :4.000 Max. :4.000 Max. :4.000
## NA's :1 NA's :1comp <- df_scores %>% select(collab_mean, mot_mean, vision_mean)
# Spearman
cor_spearman <- cor(comp, use = "pairwise.complete.obs", method = "spearman")
# Pearson
cor_pearson <- cor(comp, use = "pairwise.complete.obs", method = "pearson")
cor_spearman## collab_mean mot_mean vision_mean
## collab_mean 1.0000000 0.6783971 0.6391721
## mot_mean 0.6783971 1.0000000 0.7213459
## vision_mean 0.6391721 0.7213459 1.0000000## collab_mean mot_mean vision_mean
## collab_mean 1.0000000 0.7275893 0.5725500
## mot_mean 0.7275893 1.0000000 0.6168301
## vision_mean 0.5725500 0.6168301 1.0000000Use Spearman for intepretation due to ordinal scale:
cor_tests <- list(
collab_mot = cor.test(df_scores$collab_mean, df_scores$mot_mean, method = "spearman"),
collab_vision = cor.test(df_scores$collab_mean, df_scores$vision_mean, method = "spearman"),
mot_vision = cor.test(df_scores$mot_mean, df_scores$vision_mean, method = "spearman")
)
cor_tests$collab_mot##
## Spearman's rank correlation rho
##
## data: df_scores$collab_mean and df_scores$mot_mean
## S = 2104.9, p-value = 1.038e-05
## alternative hypothesis: true rho is not equal to 0
## sample estimates:
## rho
## 0.6783971Spearman’s rank correlation indicated a strong positive association between Collaboration and Motivation & Guidance leadership practices (ρ = 0.68, p < .001), suggesting that leaders who reported more frequent collaborative practices also tended to report higher levels of motivation and guidance.
Results from a Spearman correlation test showed a strong and statistically significant positive relationship between collaboration and motivation/guidance leadership practices (ρ = 0.68, p < .001). This finding suggests that collaborative leadership practices and practices related to motivating and guiding staff tend to occur together.
desc_comp <- df_scores %>%
summarise(
n = sum(!is.na(collab_mean) | !is.na(mot_mean) | !is.na(vision_mean)),
collab_mean = mean(collab_mean, na.rm = TRUE),
mot_mean = mean(mot_mean, na.rm = TRUE),
vision_mean = mean(vision_mean, na.rm = TRUE),
)
desc_comp## # A tibble: 1 × 4
## n collab_mean mot_mean vision_mean
## <int> <dbl> <dbl> <dbl>
## 1 35 3.33 3.73 3.30## [1] "response_num" "response_id" "landing_page_3" "cil_rating_1"
## [5] "cil_rating_2" "cil_rating_3" "cil_rating_4" "cil_rating_5"
## [9] "cil_rating_6" "cil_rating_7" "mgl_rating_1" "mgl_rating_2"
## [13] "mgl_rating_3" "mgl_rating_4" "mgl_rating_5" "mgl_rating_6"
## [17] "mgl_rating_7" "mgl_rating_8" "vdl_rating_1" "vdl_rating_2"
## [21] "vdl_rating_3" "vdl_rating_4" "vdl_rating_5" "vdl_rating_6"
## [25] "vdl_rating_7" "vdl_rating_8"agency_col <- "landing_page_3"
desc_by_agency <- df_scores %>%
group_by(.data[[agency_col]]) %>%
summarise(
n = n(),
collab_mean = mean(collab_mean, na.rm = TRUE),
mot_mean = mean(mot_mean, na.rm = TRUE),
vision_mean = mean(vision_mean, na.rm = TRUE),
.groups = "drop"
) %>%
arrange(desc(n))
desc_by_agency_disp <- desc_by_agency %>%
rename(
Agency = landing_page_3
)reactable(
desc_by_agency_disp,
searchable = TRUE,
highlight = TRUE,
striped = TRUE,
compact = TRUE,
defaultPageSize = 10,
showPageSizeOptions = TRUE,
columns = list(
Agency = colDef(minWidth = 180),
n = colDef(
name = "Number of Respondents",
align = "center"
),
collab_mean = colDef(
name = "Collaboration (Mean)",
align = "right",
format = colFormat(digits = 2)
),
mot_mean = colDef(
name = "Motivation & Guidance (Mean)",
align = "right",
format = colFormat(digits = 2)
),
vision_mean = colDef(
name = "Vision & Direction (Mean)",
align = "right",
format = colFormat(digits = 2)
)
)
# defaultSorted = list(
# list(id = "n", desc = TRUE)
)item_cols <- c(collab_items, mot_items, vision_items)
item_dist_wide <- df_scores %>%
select(all_of(item_cols)) %>%
pivot_longer(everything(), names_to = "item", values_to = "response") %>%
mutate(response = as.numeric(response)) %>%
filter(!is.na(response)) %>%
mutate(response = factor(
response, levels = 1:4,
labels = c("1 Seldom/Never", "2 Some of the time", "3 As often as I can", "4 Most of the time")
)) %>%
count(item, response, name = "n") %>%
group_by(item) %>%
mutate(pct = n / sum(n)) %>%
ungroup() %>%
left_join(item_lookup, by = "item") %>%
mutate(pct = pct * 100) %>% # percent as 0-100 for easy formatting
select(item_label, item_qid, item, response, pct) %>%
pivot_wider(names_from = response, values_from = pct) %>%
arrange(item_label)item_domain <- tibble::tibble(
item = c(collab_items, mot_items, vision_items),
checklist = c(
rep("Collaboration", length(collab_items)),
rep("Motivation & Guidance", length(mot_items)),
rep("Vision & Direction", length(vision_items))
)
)
item_dist_wide <- item_dist_wide %>%
left_join(item_domain, by = "item") %>%
relocate(checklist, .before = item_label) %>%
arrange(checklist, item_label)reactable(
item_dist_wide,
searchable = TRUE,
highlight = TRUE,
striped = TRUE,
compact = TRUE,
defaultPageSize = 25,
showPageSizeOptions = TRUE,
columns = list(
checklist = colDef(name = "Checklist"),
item_label = colDef(name = "Item"),
item_qid = colDef(name = "QID", minWidth = 90),
item = colDef(name = "Var", show = FALSE), # hide the raw variable name
`1 Seldom/Never` = colDef(
name = "1 - Seldom or never",
align = "right",
format = colFormat(digits = 0, suffix = "%")
),
`2 Some of the time` = colDef(
name = "2 - Some of the time",
align = "right",
format = colFormat(digits = 0, suffix = "%")
),
`3 As often as I can` = colDef(
name = "3 - As often as I can",
align = "right",
format = colFormat(digits = 0, suffix = "%")
),
`4 Most of the time` = colDef(
name = "4 - Most of the time",
align = "right",
format = colFormat(digits = 0, suffix = "%")
)
),
defaultSorted = "checklist"
)