QEX
folder_path <- "d:/r/data extraction process/"
data <-
tibble(filename = list.files(path = folder_path, full.names = T, pattern = "\\.xlsx$")) %>%
mutate(data = map(filename, ~read_excel(.x, sheet = "DATA EXTRACTION SHEET", skip = 2))) %>%
unnest(data) %>%
filter(!is.na(`Coder name`)) %>%
filter(!`Coder name` == "Record your name") %>%
janitor::clean_names() %>%
mutate(batch = str_extract(filename, "(?<=Group )\\d+")) %>%
select(-filename) %>%
group_by(batch) %>%
arrange(coder_name) %>%
mutate(coders = paste0("coder_", dense_rank(coder_name))) %>%
ungroup() %>%
select(-coder_name)
data_long <- data %>%
pivot_longer(
cols = -c(batch, coders, estimate_id),
names_to = "variable",
values_to = "value"
) %>%
mutate(value = na_if(value, "NA"),
value = na_if(value, "N/A"))
data_pivoted <- data_long %>%
pivot_wider(
names_from = coders,
values_from = value
) %>%
mutate(matched = case_when(
is.na(coder_1) & is.na(coder_2) ~ TRUE,
is.na(coder_1) & !is.na(coder_2) ~ FALSE,
!is.na(coder_1) & is.na(coder_2) ~ FALSE,
coder_1 == coder_2 ~ TRUE,
TRUE ~ FALSE))
each question level (heat map)
data_pivoted %>%
count(batch, estimate_id, variable, matched) %>%
mutate(matched = ifelse(matched, "count_true", "count_false")) %>%
pivot_wider(
names_from = matched,
values_from = n,
values_fill = 0 # Fill in 0 if there are missing values
) %>%
count(variable, count_true) %>%
filter(count_true == 1) %>%
mutate(pct = n / max(n)) %>%
filter(pct != 1) %>%
mutate(variable = fct_reorder(variable, pct)) %>%
ggplot(aes(pct, variable)) +
geom_col() +
scale_x_continuous(label = scales::percent_format()) +
labs(y = "")
each estimate_id level
each_estimate_id <-
data_pivoted %>%
group_by(estimate_id, batch) %>%
summarize(n = n(),
agreement_rate = sum(matched == 1)/n,
.groups = "drop")
each_estimate_id
## # A tibble: 267 × 4
## estimate_id batch n agreement_rate
## <chr> <chr> <int> <dbl>
## 1 73628889_1 48 114 0.833
## 2 73628889_2 48 114 0.833
## 3 73795904_1 47 114 0.807
## 4 73795904_10 47 114 0.798
## 5 73795904_11 47 114 0.772
## 6 73795904_12 47 114 0.772
## 7 73795904_13 47 114 0.772
## 8 73795904_14 47 114 0.772
## 9 73795904_15 47 114 0.763
## 10 73795904_16 47 114 0.763
## # … with 257 more rows
study level
study_level <-
each_estimate_id %>%
mutate(id = str_extract(estimate_id, "\\d+(?=_)")) %>%
group_by(id, batch) %>%
summarize(mean = mean(agreement_rate),
.groups = "drop")
study_level
## # A tibble: 44 × 3
## id batch mean
## <chr> <chr> <dbl>
## 1 73628889 48 0.833
## 2 73795904 47 0.781
## 3 73796232 47 0.842
## 4 73797297 47 0.833
## 5 73797942 51 0.842
## 6 73798224 48 0.219
## 7 73798778 51 0.842
## 8 73801283 48 0.807
## 9 73801284 48 0.605
## 10 73801962 52 0.868
## # … with 34 more rows
- These study level data can be merged with the study feature such as
evaluation design, the number of outcomes and others
batch level
batch_level <-
study_level %>%
group_by(batch) %>%
summarize(mean = mean(mean),
.groups = "drop")
batch_level
## # A tibble: 4 × 2
## batch mean
## <chr> <dbl>
## 1 47 0.713
## 2 48 0.694
## 3 51 0.825
## 4 52 0.762
- These batch level data can be merged with the time spent on each
batch and the number of studies and outcomes to quantify the difficulty
of each studies
rob
folder_path_rob <- "d:/r/data extraction_ROB/"
data_rob <-
tibble(filename = list.files(path = folder_path_rob, full.names = TRUE, pattern = "\\.xlsx$")) %>%
mutate(data = map(filename, ~read_excel(.x, sheet = "QED "))) %>%
mutate(shee = "QED") %>%
unnest(data)
## New names:
## New names:
## • `General` -> `General...3`
## • `General` -> `General...4`
## • `General` -> `General...5`
## • `General` -> `General...6`
## • `General` -> `General...7`
## • `General` -> `General...8`
## • `General` -> `General...9`
## • `1: Selection bias - Justification` -> `1: Selection bias -
## Justification...11`
## • `1: Selection bias - Justification` -> `1: Selection bias -
## Justification...12`
## • `1: Selection bias - Justification` -> `1: Selection bias -
## Justification...13`
## • `2: Confounding - Justification` -> `2: Confounding - Justification...15`
## • `2: Confounding - Justification` -> `2: Confounding - Justification...16`
## • `2: Confounding - Justification` -> `2: Confounding - Justification...17`
## • `2: Confounding - Justification` -> `2: Confounding - Justification...18`
## • `2: Confounding - Justification` -> `2: Confounding - Justification...19`