B Generating the Data (R Script)
You do not need this appendix to follow the book. The five CSV files are ready to download in Appendix A. Use this appendix if you want to see how the data was made, or to generate a larger dataset.
The dataset was generated with the R script at the end of this appendix.
Because the script uses a fixed random seed (set.seed(123)), running
it again produces exactly the same files (checked with R 4.5).
B.1 Step 1: Install R
- Download and install R from https://cran.r-project.org/.
- Recommended: install RStudio Desktop from https://posit.co/download/rstudio-desktop/. RStudio is a free editor that makes R scripts easier to run.
You do not need to install any R packages yourself. The script checks for the four packages it uses (dplyr, lubridate, stringr and tidyr) and installs any that are missing. This needs an internet connection the first time you run it.
B.2 Step 2: Save the Script
- Create a folder for the project, for example
Documents/PowerBI-Book. - In RStudio, select File → New File → R Script.
- Copy the full script from the end of this appendix and paste it into the new file.
- Save the file in your folder as
Generate Sample data.R.
If you are reading the PDF, copy the script from the online version of the book at https://www.growinganalyst.com/power-bi-book/r-script.html instead. Long lines are wrapped in the PDF, and the online code block has a copy button.
B.3 Step 3: Run the Script
- In RStudio, select Session → Set Working Directory → To Source File Location. This tells R to save the output in your project folder.
- Select Source, at the top right of the script editor. This runs the whole script.
If you prefer the command line, open a terminal in your folder and run:
B.4 Step 4: Check the Output
When the script finishes, it prints where the files were saved, the number of rows written and the number of injected data-quality issues:
Files saved in: C:/Users/you/Documents/PowerBI-Book/assets/data
Rows written
dim_patient: 3500
dim_facility: 60
dim_date: 366
fact_patient_visits: 10030
fact_targets: 960
Injected data-quality issues
anc_visit ' yes ': 74
anc_visit 'YES': 58
ANC 'Yes' for male patients: 10
tb_test_date with '/': 342
art_start_date 1900-01-01: 6
art_regimen 'Tld': 17
art_regimen 'TLD ': 15
art_regimen missing on ART: 4
duplicate visit rows: 30
The five CSV files are in the assets/data folder inside your project
folder. To save them somewhere else, change output_dir near the top of
the script.
The printed issue counts are measured before the 30 duplicate rows are added, so they can differ slightly from the table in Appendix A.
To generate a larger dataset, for example to test performance, increase
n_patients and n_visits near the top of the script.
B.5 The Script
# --------------------------------------------------
# Synthetic Integrated Health Dataset
# Purpose: Generate the sample data used in the book
# Domains: ANC, HIV, ART, Malaria, TB, NCDs
#
# All records are simulated. Rates are illustrative only and are
# NOT estimates for any real population.
#
# The clinical logic is internally consistent (for example, ANC
# visits only for pregnant patients and ART only after an
# HIV-positive result). A small, counted set of data-quality
# problems is then injected on purpose so readers can find and fix
# them in Power Query. The counts are printed at the end of the run.
#
# How to run: open this file in RStudio and select Source, or run
# Rscript "Generate Sample data.R"
# Output: five CSV files in the folder named in output_dir below
# --------------------------------------------------
# -------------------------
# Step 1: Install any missing packages (needs internet the first time)
# -------------------------
required_packages <- c("dplyr", "lubridate", "stringr", "tidyr")
missing_packages <- setdiff(required_packages, rownames(installed.packages()))
if (length(missing_packages) > 0) {
install.packages(missing_packages, repos = "https://cloud.r-project.org")
}
# -------------------------
# Step 2: Load the packages
# -------------------------
suppressPackageStartupMessages({
library(dplyr)
library(lubridate)
library(stringr)
library(tidyr)
})
# -------------------------
# Step 3: Settings
# -------------------------
# Folder for the CSV files. It is created inside your working directory
# if it does not exist. Run getwd() to see your working directory.
output_dir <- "assets/data"
# Fixed seed: every run produces exactly the same data
set.seed(123)
n_patients <- 3500
n_facilities <- 60
n_visits <- 10000
n_duplicates <- 30
# -------------------------
# Dimensions
# -------------------------
patients <- tibble(
patient_id = 1:n_patients,
sex = sample(c("Male", "Female"), n_patients, replace = TRUE),
age = sample(0:80, n_patients, replace = TRUE)
) %>%
mutate(
age_group = case_when(
age < 5 ~ "<5",
age < 15 ~ "5–14",
age < 50 ~ "15–49",
TRUE ~ "50+"
),
pregnant = if_else(sex == "Female" & age >= 15 & age <= 49,
sample(c("Yes", "No"), n(), replace = TRUE, prob = c(0.25, 0.75)),
"No"),
# True HIV infection status. Not exported: only test results are observable.
hiv_infected = runif(n()) < 0.05
)
dim_facility <- tibble(
facility_id = 1:n_facilities,
facility_name = paste("Facility", facility_id),
facility_type = sample(c("Primary", "Secondary"), n_facilities, replace = TRUE),
ownership = sample(c("Public", "Private"), n_facilities, replace = TRUE),
district = sample(paste("District", 1:12), n_facilities, replace = TRUE),
state = sample(c("State A", "State B", "State C"), n_facilities, replace = TRUE)
)
# month.abb is used instead of month(label = TRUE) so month names do
# not depend on the computer's locale.
dim_date <- tibble(
date = seq.Date(as.Date("2024-01-01"), as.Date("2024-12-31"), by = "day")
) %>%
mutate(
date_id = as.integer(format(date, "%Y%m%d")),
year = year(date),
quarter = paste0("Q", quarter(date)),
month_num = month(date),
month = month.abb[month_num],
iso_year = isoyear(date),
iso_week = isoweek(date)
)
# -------------------------
# Visits (clean logic)
# -------------------------
visits <- tibble(
patient_id = sample(patients$patient_id, n_visits, replace = TRUE),
facility_id = sample(dim_facility$facility_id, n_visits, replace = TRUE),
date = sample(dim_date$date, n_visits, replace = TRUE)
) %>%
arrange(date) %>%
mutate(visit_id = row_number()) %>%
left_join(patients %>% select(patient_id, sex, age, pregnant, hiv_infected),
by = "patient_id") %>%
mutate(
anc_visit = if_else(pregnant == "Yes",
sample(c("Yes", "No"), n(), replace = TRUE, prob = c(0.6, 0.4)),
"No"),
malaria_tested = sample(c("Yes", "No"), n(), replace = TRUE, prob = c(0.7, 0.3)),
tb_screened = sample(c("Yes", "No"), n(), replace = TRUE, prob = c(0.4, 0.6)),
hiv_tested = sample(c("Yes", "No"), n(), replace = TRUE, prob = c(0.5, 0.5)),
ncd_screened = if_else(age >= 18,
sample(c("Yes", "No"), n(), replace = TRUE, prob = c(0.35, 0.65)),
"No"),
malaria_positive = if_else(malaria_tested == "Yes",
sample(c("Yes", "No"), n(), replace = TRUE, prob = c(0.3, 0.7)),
"No"),
tb_test_done = if_else(tb_screened == "Yes",
sample(c("Yes", "No"), n(), replace = TRUE, prob = c(0.8, 0.2)),
"No"),
# Sample collected on the visit day or up to 3 days later
tb_test_date = if_else(tb_test_done == "Yes",
as.character(date + sample(0:3, n(), replace = TRUE)),
NA_character_),
tb_positive = if_else(tb_test_done == "Yes",
sample(c("Yes", "No"), n(), replace = TRUE, prob = c(0.1, 0.9)),
"No"),
hypertension = if_else(ncd_screened == "Yes",
sample(c("Yes", "No"), n(), replace = TRUE, prob = c(0.25, 0.75)),
"No"),
diabetes = if_else(ncd_screened == "Yes",
sample(c("Yes", "No"), n(), replace = TRUE, prob = c(0.12, 0.88)),
"No")
)
# HIV: a test is positive only for infected patients, and a patient is
# not retested after their first positive result.
visits <- visits %>%
arrange(patient_id, date, visit_id) %>%
group_by(patient_id) %>%
mutate(
positive_if_tested = hiv_tested == "Yes" & hiv_infected,
already_diagnosed = lag(cumany(positive_if_tested), default = FALSE),
hiv_tested = if_else(already_diagnosed, "No", hiv_tested),
hiv_positive = if_else(hiv_tested == "Yes" & hiv_infected, "Yes", "No"),
diagnosis_date = if (any(hiv_positive == "Yes")) min(date[hiv_positive == "Yes"]) else as.Date(NA)
) %>%
ungroup()
# ART: 85% of diagnosed patients start ART 0-14 days after diagnosis.
# Regimen and treatment line are fixed per patient.
art_patients <- visits %>%
filter(!is.na(diagnosis_date)) %>%
distinct(patient_id, diagnosis_date) %>%
arrange(patient_id) %>%
mutate(
initiated = runif(n()) < 0.85,
art_start = diagnosis_date + sample(0:14, n(), replace = TRUE),
regimen = sample(c("TLD", "TLE", "ABC-3TC-DTG", "AZT-3TC-NVP"), n(),
replace = TRUE, prob = c(0.65, 0.15, 0.15, 0.05)),
line = sample(c("First", "Second", "Third"), n(),
replace = TRUE, prob = c(0.85, 0.10, 0.05))
) %>%
filter(initiated)
visits <- visits %>%
left_join(art_patients %>% select(patient_id, art_start, regimen, line),
by = "patient_id") %>%
mutate(
art_started = if_else(!is.na(art_start) & date >= art_start, "Yes", "No"),
art_start_date = if_else(art_started == "Yes", as.character(art_start), NA_character_),
art_regimen = if_else(art_started == "Yes", regimen, NA_character_),
art_line = if_else(art_started == "Yes", line, NA_character_)
)
# Patient HIV status as at the end of the period, derived from test results
hiv_status <- visits %>%
group_by(patient_id) %>%
summarise(
hiv_status = case_when(
any(hiv_positive == "Yes") ~ "Positive",
any(hiv_tested == "Yes") ~ "Negative",
TRUE ~ "Unknown"
),
.groups = "drop"
)
dim_patient <- patients %>%
left_join(hiv_status, by = "patient_id") %>%
mutate(hiv_status = coalesce(hiv_status, "Unknown")) %>%
select(patient_id, sex, age, hiv_status, age_group, pregnant)
# -------------------------
# Targets (facility x quarter)
# Simulated as 90-130% of the clean quarterly count. Real targets come
# from population estimates and program plans.
# -------------------------
fact_targets <- visits %>%
left_join(dim_date %>% select(date, year, quarter), by = "date") %>%
group_by(facility_id, year, quarter) %>%
summarise(
`Malaria tests` = sum(malaria_tested == "Yes"),
`HIV tests` = sum(hiv_tested == "Yes"),
`TB screenings` = sum(tb_screened == "Yes"),
`NCD screenings` = sum(ncd_screened == "Yes"),
.groups = "drop"
) %>%
pivot_longer(c(`Malaria tests`, `HIV tests`, `TB screenings`, `NCD screenings`),
names_to = "indicator", values_to = "actual") %>%
mutate(
target = pmax(1L, as.integer(round(actual * runif(n(), 0.9, 1.3)))),
date_id = as.integer(sprintf("%d%02d01", year, (as.integer(substr(quarter, 2, 2)) - 1) * 3 + 1))
) %>%
arrange(facility_id, year, quarter, indicator) %>%
select(facility_id, date_id, year, quarter, indicator, target)
# -------------------------
# Inject data-quality issues (deliberate)
# -------------------------
anc_yes <- which(visits$anc_visit == "Yes")
anc_variant <- sample(c("Yes", " yes ", "YES"), length(anc_yes), replace = TRUE,
prob = c(0.6, 0.2, 0.2))
visits$anc_visit[anc_yes] <- anc_variant
# ANC recorded against a male patient (data-entry error)
male_rows <- which(visits$sex == "Male")
male_anc <- sample(male_rows, 10)
visits$anc_visit[male_anc] <- "Yes"
# TB test dates entered as "2024/03-15" instead of "2024-03-15"
tb_rows <- which(!is.na(visits$tb_test_date))
tb_slash <- tb_rows[runif(length(tb_rows)) < 0.10]
visits$tb_test_date[tb_slash] <- str_replace(visits$tb_test_date[tb_slash], "-", "/")
# Placeholder ART start date
art_rows <- which(!is.na(visits$art_start_date))
art_1900 <- art_rows[runif(length(art_rows)) < 0.05]
visits$art_start_date[art_1900] <- "1900-01-01"
# Inconsistent regimen labels and missing regimen while on ART
tld_rows <- which(visits$art_regimen == "TLD")
tld_var <- sample(c("TLD", "Tld", "TLD "), length(tld_rows), replace = TRUE,
prob = c(0.80, 0.12, 0.08))
visits$art_regimen[tld_rows] <- tld_var
reg_rows <- which(!is.na(visits$art_regimen))
reg_blank <- reg_rows[runif(length(reg_rows)) < 0.05]
visits$art_regimen[reg_blank] <- NA_character_
# -------------------------
# Fact table
# Date attributes (year, quarter, month, iso_week) are copied into the
# fact table on purpose; the book removes them in Power Query.
# -------------------------
fact_patient_visits <- visits %>%
left_join(dim_date %>% select(date, date_id, year, quarter, month, iso_week), by = "date") %>%
arrange(visit_id) %>%
select(visit_id, patient_id, facility_id,
anc_visit, malaria_tested, tb_screened, hiv_tested, ncd_screened,
malaria_positive, tb_test_done, tb_test_date, tb_positive,
hiv_positive, art_started, art_start_date,
hypertension, diabetes,
date_id, year, quarter, month, iso_week,
art_regimen, art_line)
# Duplicate records (the same visit entered twice)
dup_rows <- sort(sample(seq_len(nrow(fact_patient_visits)), n_duplicates))
fact_patient_visits <- bind_rows(fact_patient_visits, fact_patient_visits[dup_rows, ]) %>%
arrange(visit_id)
# -------------------------
# Write CSVs
# -------------------------
dir.create(output_dir, showWarnings = FALSE, recursive = TRUE)
write.csv(dim_patient, file.path(output_dir, "dim_patient.csv"), row.names = FALSE)
write.csv(dim_facility, file.path(output_dir, "dim_facility.csv"), row.names = FALSE)
write.csv(dim_date, file.path(output_dir, "dim_date.csv"), row.names = FALSE)
write.csv(fact_patient_visits, file.path(output_dir, "fact_patient_visits.csv"), row.names = FALSE)
write.csv(fact_targets, file.path(output_dir, "fact_targets.csv"), row.names = FALSE)
# -------------------------
# Summary of injected issues
# -------------------------
cat("Files saved in:", normalizePath(output_dir, winslash = "/"), "\n")
cat("Rows written\n")
cat(" dim_patient: ", nrow(dim_patient), "\n")
cat(" dim_facility: ", nrow(dim_facility), "\n")
cat(" dim_date: ", nrow(dim_date), "\n")
cat(" fact_patient_visits:", nrow(fact_patient_visits), "\n")
cat(" fact_targets: ", nrow(fact_targets), "\n")
cat("Injected data-quality issues\n")
cat(" anc_visit ' yes ': ", sum(anc_variant == " yes "), "\n")
cat(" anc_visit 'YES': ", sum(anc_variant == "YES"), "\n")
cat(" ANC 'Yes' for male patients: ", length(male_anc), "\n")
cat(" tb_test_date with '/': ", length(tb_slash), "\n")
cat(" art_start_date 1900-01-01: ", length(art_1900), "\n")
cat(" art_regimen 'Tld': ", sum(tld_var == "Tld"), "\n")
cat(" art_regimen 'TLD ': ", sum(tld_var == "TLD "), "\n")
cat(" art_regimen missing on ART: ", length(reg_blank), "\n")
cat(" duplicate visit rows: ", n_duplicates, "\n")Power BI for M&E and Public Health Data Analysts · The Growing Analyst