INTRODUCTION

This document presents the analysis of the review comments for the CMCs (Calibration and Measurement Capabilities) at the intraregional level for the SIM. The data cycle analyzed is XXVI (2025)and sourced from KCDB of March 23, 2026.

1. LOADING LIBRARIES AND DATA

# Libraries
library(readxl) 
library(dplyr) 
library(purrr) 
library(ggplot2) 
library(stringr) 
library(tidyr) 
library(forcats) 
library(writexl)

# File path (Make sure to replace this with your actual file path)
file_path <- "C:/Users/Bryan Calderón/Desktop/KCDB data.xlsx" 

# Get sheet names
sheets <- excel_sheets(file_path)

# Function to extract necessary columns
extract_data <- function(sheet) { 
  df <- read_excel(file_path, sheet = sheet)
  names(df) <- toupper(trimws(names(df)))
  
  if(all(c("RMO", "COMMENT") %in% names(df))) { 
    df %>% 
      select(RMO, COMMENT) %>% 
      filter(!is.na(COMMENT)) %>% 
      mutate(SHEET_NAME = sheet) 
  } 
}

# Apply function to all sheets
final_df <- map_dfr(sheets, extract_data)

# Show the first few rows
head(final_df)
## # A tibble: 6 × 3
##   RMO      COMMENT                                                    SHEET_NAME
##   <chr>    <chr>                                                      <chr>     
## 1 AFRIMETS Please respond to comments from reviewers.                 SIM-QM-CR…
## 2 AFRIMETS The Record of participation and Core Capability matrix do… SIM-QM-CR…
## 3 AFRIMETS The Record of participation and Core Capability matrix do… SIM-QM-CR…
## 4 AFRIMETS Comments for publication column will be published on the … SIM-QM-CR…
## 5 AFRIMETS A record of participation to support a core competency cl… SIM-QM-CR…
## 6 COOMET   Please check whether uncertainty convention 2 has been ch… SIM-QM-CR…

2. COMMENT CLASSIFICATION

Below, an automatic classification of the comments is performed using specific keywords through regular expressions.

# Automatic comment classification
final_df <- final_df %>% 
  mutate(CATEGORY = case_when( 
    str_detect(COMMENT, regex("uncertainty|uncertainties|budget", ignore_case = TRUE)) ~ "Uncertainty", 
    str_detect(COMMENT, regex("traceability|crm|reference material", ignore_case = TRUE)) ~ "Traceability", 
    str_detect(COMMENT, regex("validation|method|performance", ignore_case = TRUE)) ~ "Method Validation", 
    str_detect(COMMENT, regex("scope|range|measurand", ignore_case = TRUE)) ~ "Scope Definition", 
    str_detect(COMMENT, regex("comparison|key comparison|CIPM", ignore_case = TRUE)) ~ "Comparison Evidence", 
    TRUE ~ "Other" 
  ))

# Show overall percentages by category
category_percentage <- final_df %>% 
  count(CATEGORY) %>% 
  mutate(Percentage = round((n / sum(n)) * 100, 2)) %>% 
  arrange(desc(Percentage))

category_percentage
## # A tibble: 6 × 3
##   CATEGORY                n Percentage
##   <chr>               <int>      <dbl>
## 1 Other                1225      66.1 
## 2 Uncertainty           434      23.4 
## 3 Traceability           94       5.07
## 4 Comparison Evidence    52       2.81
## 5 Scope Definition       26       1.4 
## 6 Method Validation      22       1.19
# Show percentages by category excluding "Other"
category_percentage_no_other <- final_df %>% 
  filter(CATEGORY != "Other") %>% 
  count(CATEGORY) %>% 
  mutate(Percentage = round((n / sum(n)) * 100, 2)) %>% 
  arrange(desc(Percentage))

category_percentage_no_other
## # A tibble: 5 × 3
##   CATEGORY                n Percentage
##   <chr>               <int>      <dbl>
## 1 Uncertainty           434      69.1 
## 2 Traceability           94      15.0 
## 3 Comparison Evidence    52       8.28
## 4 Scope Definition       26       4.14
## 5 Method Validation      22       3.5

3. VISUALIZATIONS

Graph 1: Total Frequency of Comments per RMO

plot_rmo <- final_df %>% 
  count(RMO) %>% 
  ggplot(aes(x = fct_reorder(RMO, n), y = n)) + 
  geom_col(fill = "steelblue") + 
  coord_flip() + 
  labs(
    title = "Total Comments per RMO", 
    x = "RMO", 
    y = "Number of Comments"
  ) + 
  theme_minimal()

plot_rmo

Graph 2: Global Distribution of Categories

plot_category <- final_df %>% 
  count(CATEGORY) %>% 
  ggplot(aes(x = fct_reorder(CATEGORY, n), y = n)) + 
  geom_col(fill = "darkorange") + 
  coord_flip() + 
  labs(
    title = "Distribution of Comment Categories", 
    x = "Category", 
    y = "Number of Comments"
  ) + 
  theme_minimal()

plot_category

Graph 3: Categories by RMO

plot_rmo_category <- final_df %>% 
  count(RMO, CATEGORY) %>% 
  ggplot(aes(x = RMO, y = n, fill = CATEGORY)) + 
  geom_col() + 
  labs(
    title = "Comment Categories by RMO", 
    x = "RMO", 
    y = "Number of Comments"
  ) + 
  theme_minimal()

plot_rmo_category