Reduce Waiting Time in Antenatal Care Services

Midwife Scheduling Intervention on USG Day at Puskesmas Bojong

---

1 Data Sources

# 1. Load Libraries
library(readxl)
library(tidyverse)
library(stringr)

# ------------------------------------------------------------
# 2. Import Data
# ------------------------------------------------------------
file_path <- "C:/Users/user/OneDrive - Oxford University Clinical Research Unit/PA Expert Shadowing/Intervensi Bojong/Expert Shadowing Patients Purbalingga.xlsx"

data_raw <- read_excel(file_path, sheet = "Bojong-Intervensi")

# ------------------------------------------------------------
# 3. Basic Cleaning
# ------------------------------------------------------------
data_clean <- data_raw %>%
  mutate(
    phc_id            = str_trim(phc_id),
    patient_id        = str_trim(patient_id),
    service_unit      = str_to_lower(str_trim(service_unit)),
    activity_category = str_to_lower(str_trim(activity_category)),
    observation_date  = as.Date(observation_date),
    duration_final    = if_else(!is.na(duration_seconds) & duration_seconds > 0, duration_seconds, NA_real_)
  ) %>%
  mutate(
    visit_id = paste(phc_id, patient_id, observation_date, sep = "_")
  )

# ------------------------------------------------------------
# 4. Looping Through Specific Dates
# ------------------------------------------------------------
target_dates <- as.Date(c("2026-02-25", "2026-02-28", "2026-03-04"))

for (current_date in as.character(target_dates)) {
  
  # Filter data for the loop's current date
  daily_data <- data_clean %>% filter(observation_date == as.Date(current_date))
  
  if (nrow(daily_data) == 0) {
    message(paste("No data found for date:", current_date))
    next
  }
  
  # Set dynamic X-axis limits based on the date
  limit_x <- if (current_date == "2026-02-28") 25 else 20
  
  # Calculate Total N (Unique Visits)
  total_n <- n_distinct(daily_data$visit_id)
  
  # Prepare Waiting Time Summary
  plot_summary <- daily_data %>%
    filter(activity_category == "menunggu") %>%
    mutate(
      waiting_location = case_when(
        str_detect(service_unit, "lab") ~ "Lab",
        str_detect(service_unit, "kia|poli kia") ~ "KIA",
        str_detect(service_unit, "usg") ~ "USG",
        str_detect(service_unit, "registrasi|pendaftaran") ~ "Registration",
        str_detect(service_unit, "farmasi|apotek") ~ "Pharmacy",
        TRUE ~ NA_character_
      )
    ) %>%
    filter(!is.na(waiting_location)) %>%
    group_by(visit_id, waiting_location) %>%
    summarise(visit_wait_minutes = sum(duration_final, na.rm = TRUE) / 60, .groups = "drop") %>%
    group_by(waiting_location) %>%
    summarise(mean_minutes = mean(visit_wait_minutes, na.rm = TRUE), .groups = "drop")
}

2 Descriptive Table

#Table#
library(tibble)
library(kableExtra)

# Define the summary categories
# Removed the duplicate 'Midwife Assistance' column
summary_table_data <- tibble(
  `Date` = c("25 February and 4 March", "28 February"),
  `Midwife Assistance` = c("Yes", "No"),
  `Number of Patients (N)` = c("9", "9")
)

# Render the table
summary_table_data %>%
  kbl(caption = "Table 1. Overview of Observed Antenatal Care Visits at Puskesmas Bojong") %>%
  kable_styling(
    bootstrap_options = c("striped", "hover", "condensed"), 
    full_width = TRUE,
    font_size = 14
  ) %>%
  column_spec(1, bold = TRUE) %>%
  row_spec(0, bold = TRUE, background = "#F2F2F2") # Adds a light grey header

Table 1. Overview of Observed Antenatal Care Visits at Puskesmas Bojong

Date	Midwife Assistance	Number of Patients (N)
25 February and 4 March	Yes	9
28 February	No	9

3 Result

3.1 Comparison of Mean Waiting Time per ANC Visit

# ------------------------------------------------------------
# 1. Hitung Summary Before (Data Tanggal 28 Feb)
# ------------------------------------------------------------
summary_before_clean <- data_clean %>%
  filter(observation_date == as.Date("2026-02-28")) %>%
  filter(activity_category == "menunggu") %>%
  mutate(
    waiting_location = case_when(
      str_detect(service_unit, "lab") ~ "Lab",
      str_detect(service_unit, "kia|poli kia") ~ "KIA",
      str_detect(service_unit, "usg") ~ "USG",
      str_detect(service_unit, "registrasi|pendaftaran") ~ "Registration",
      str_detect(service_unit, "farmasi|apotek") ~ "Pharmacy",
      TRUE ~ NA_character_
    )
  ) %>%
  filter(!is.na(waiting_location)) %>%
  group_by(visit_id, waiting_location) %>%
  summarise(visit_wait_minutes = sum(duration_final, na.rm = TRUE) / 60, .groups = "drop") %>%
  group_by(waiting_location) %>%
  summarise(mean_minutes = mean(visit_wait_minutes, na.rm = TRUE), .groups = "drop") %>%
  mutate(group = "Before Intervention")

# ------------------------------------------------------------
# 2. Hitung Summary After (Gabungan 25 Feb & 4 Maret)
# ------------------------------------------------------------
target_combined <- as.Date(c("2026-02-25", "2026-03-04"))

summary_after_clean <- data_clean %>%
  filter(observation_date %in% target_combined) %>%
  filter(activity_category == "menunggu") %>%
  mutate(
    waiting_location = case_when(
      str_detect(service_unit, "lab") ~ "Lab",
      str_detect(service_unit, "kia|poli kia") ~ "KIA",
      str_detect(service_unit, "usg") ~ "USG",
      str_detect(service_unit, "registrasi|pendaftaran") ~ "Registration",
      str_detect(service_unit, "farmasi|apotek") ~ "Pharmacy",
      TRUE ~ NA_character_
    )
  ) %>%
  filter(!is.na(waiting_location)) %>%
  group_by(visit_id, waiting_location) %>%
  summarise(visit_wait_minutes = sum(duration_final, na.rm = TRUE) / 60, .groups = "drop") %>%
  group_by(waiting_location) %>%
  summarise(mean_minutes = mean(visit_wait_minutes, na.rm = TRUE), .groups = "drop") %>%
  mutate(group = "After Intervention")

# ------------------------------------------------------------
# 3. Gabungkan Data & Visualisasi
# ------------------------------------------------------------
comparison_data <- bind_rows(summary_before_clean, summary_after_clean) %>%
  mutate(group = factor(group, levels = c("Before Intervention", "After Intervention")))

ggplot(comparison_data, 
       aes(x = mean_minutes, 
           y = reorder(waiting_location, mean_minutes), 
           fill = group)) +
  geom_col(position = position_dodge2(reverse = TRUE), width = 0.7) +
  scale_fill_manual(
    values = c("Before Intervention" = "#4BE3A1", "After Intervention" = "#E84ACF"),
    labels = c("Before Intervention (N = 9)", "After Intervention (N = 9)")
  ) +
  scale_x_continuous(limits = c(0, 25), breaks = seq(0, 25, 5)) +
  labs(title = "Comparison: Mean Waiting Time per ANC Visit",
       subtitle = "Before vs After Midwife Scheduling Intervention",
       x = "Mean Waiting Time (minutes)", y = "Service Unit", fill = NULL) +
  theme_minimal() +
  theme(legend.position = "bottom")

3.2 Mean Waiting Time per ANC Visit Before Intervention

# 1. Filter Data Khusus Tanggal 28 Februari
target_date_28 <- as.Date("2026-02-28")

data_28 <- data_clean %>%
  filter(observation_date == target_date_28) %>%
  filter(activity_category == "menunggu") %>%
  mutate(
    waiting_location = case_when(
      str_detect(service_unit, "lab") ~ "Lab",
      str_detect(service_unit, "kia|poli kia") ~ "KIA",
      str_detect(service_unit, "usg") ~ "USG",
      str_detect(service_unit, "registrasi|pendaftaran") ~ "Registration",
      str_detect(service_unit, "farmasi|apotek") ~ "Pharmacy",
      TRUE ~ NA_character_
    )
  ) %>%
  filter(!is.na(waiting_location))

# 2. Hitung Rata-rata Menit (Gunakan data_28, bukan data_before)
summary_before <- data_28 %>%
  group_by(visit_id, waiting_location) %>%
  summarise(visit_wait_minutes = sum(duration_final, na.rm = TRUE) / 60, .groups = "drop") %>%
  group_by(waiting_location) %>%
  summarise(mean_minutes = mean(visit_wait_minutes, na.rm = TRUE), .groups = "drop")

# 3. Hitung Total N (Unique Visits)
total_n_28 <- n_distinct(data_clean %>% filter(observation_date == target_date_28) %>% pull(visit_id))

# 4. Visualisasi
ggplot(summary_before, 
       aes(x = mean_minutes, 
           y = reorder(waiting_location, mean_minutes))) +
  geom_col(fill = "#f3b61f", width = 0.7) +
  scale_x_continuous(
    limits = c(0, 25), 
    breaks = seq(0, 25, by = 5),
    minor_breaks = seq(0, 25, by = 2.5),
    expand = expansion(mult = c(0, 0.05))
  ) +
  labs(
    title = "Mean Waiting Time ANC Visit Before Intervention",
    subtitle = paste0("Puskesmas Bojong (N = ", total_n_28, ")"),
    x = "Mean Waiting Time (minutes)",
    y = "Service Unit"
  ) +
  theme_minimal(base_size = 12) +
  theme(
    plot.title = element_text(face = "bold"),
    panel.grid.major.y = element_blank()
  )

3.3 Mean Waiting Time per ANC Visit After Intervention

# 1. Tentukan tanggal yang ingin digabung
target_combined <- as.Date(c("2026-02-25", "2026-03-04"))

# 2. Proses Data (Filter -> Kategorisasi -> Hitung Rerata)
combined_summary <- data_clean %>%
  filter(observation_date %in% target_combined) %>%
  filter(activity_category == "menunggu") %>%
  mutate(
    waiting_location = case_when(
      str_detect(service_unit, "lab") ~ "Lab",
      str_detect(service_unit, "kia|poli kia") ~ "KIA",
      str_detect(service_unit, "usg") ~ "USG",
      str_detect(service_unit, "registrasi|pendaftaran") ~ "Registration",
      str_detect(service_unit, "farmasi|apotek") ~ "Pharmacy",
      TRUE ~ NA_character_
    )
  ) %>%
  filter(!is.na(waiting_location)) %>%
  # Hitung total menit menunggu per pasien per lokasi
  group_by(visit_id, waiting_location) %>%
  summarise(visit_wait_minutes = sum(duration_final, na.rm = TRUE) / 60, .groups = "drop") %>%
  # Hitung rata-rata dari seluruh pasien (N)
  group_by(waiting_location) %>%
  summarise(
    mean_minutes = mean(visit_wait_minutes, na.rm = TRUE),
    n_obs = n(), # Opsional: melihat berapa banyak kunjungan di tiap unit
    .groups = "drop"
  )

# 3. Hitung Total N Unik untuk Subtitle
total_n_combined <- data_clean %>% 
  filter(observation_date %in% target_combined) %>% 
  summarise(n = n_distinct(visit_id)) %>% 
  pull(n)

# 4. Visualisasi
# Visualisasi dengan garis bantu di antara angka (setiap 2.5 menit)
ggplot(combined_summary, 
       aes(x = mean_minutes, 
           y = reorder(waiting_location, mean_minutes))) +
  geom_col(fill = "#f3b61f", width = 0.7) +
  
  # Pengaturan Sumbu X
  scale_x_continuous(
    limits = c(0, 20),                # Batas maksimal 20
    breaks = seq(0, 20, by = 5),       # Garis utama & Angka (0, 5, 10, 15, 20)
    minor_breaks = seq(0, 20, by = 2.5), # Garis bantu di tengah (2.5, 7.5, dst)
    expand = expansion(mult = c(0, 0.05))
  ) +
  
  labs(
    title = "Mean Waiting Time ANC Visit After Intervention",
    subtitle = paste0("Puskesmas Bojong (N = ", total_n_combined, ")"),
    x = "Mean Waiting Time (minutes)",
    y = "Service Unit"
  ) +
  
  theme_minimal(base_size = 12) +
  theme(
    plot.title = element_text(face = "bold"),
    # Menampilkan garis utama (lebih tegas)
    panel.grid.major.x = element_line(color = "grey90"), 
    panel.grid.minor.x = element_line(color = "grey90"), 
    panel.grid.major.y = element_blank()
  )

4 Key Insights

4.1 Distribution of Patient Waiting Times by Service Unit

1. Some patients did not have waiting time at registration because they did not arrive at the same time, so no queue was formed.
2. Patient distribution depends on gestational age and the type of examination needed. Even on USG days, not all patients have USG or lab tests.

4.2 Limitations of the observation

On February 25, observations at the registration were not conducted due to limited coverage of the Public Health Center (Puskesmas) area, making it not feasible for a single observer to monitor 5 service units simultaneously.