Hi colleagues.
I’m trying to create a new column (“Sites_total”) which is the total number of sites or regions covered by each project (A - J). The encoding was not standard, like some have commas in between, others have none.
For example,
Project Sites
A - Region I, II, III, BARMM
B - Region I Region II Region III BARMM
C - NA
D - Selected hospitals in Region I, Region II, Region III, BARMM
E - Region I and Region II
F - NCR, Region I & Region II
G - Region I - Ilocos Region
H - NCR, Region I, II, III, BARMM
I - CAR, NCR, Region I
J - Region 4A
I have tried the following function but it only worked on A, H, I, and J. In other projects it produced wrong totals (ex. B - 1, C - 1, D - 3, E - 1).
add_commas_and_count <- function(x) {
if (grepl("Regions", x)) {
# Replace spaces between numbers with commas
x <- gsub("(\\d)\\s+(\\d)", "\\1,\\2", x)
# Split by comma and count
regions <- unlist(strsplit(gsub("Regions ", "", x), ","))
return(length(regions))
count_no_commas <- sum(!grepl(",", elements))
return(count_no_commas)
} else if (grepl("^Region", x)) {
# Count individual Region
return(length(unlist(strsplit(x, "Region"))) - 1)
} else {
# For CAR, NCR, BARMM
return(1)
}
}
ODA$Sites_Total <- sapply(ODA`Sites`, add_commas_and_count)
I hope you could guide/help me on this.
Thank you so much.
Regards,
Echo