dat <- data.frame(
age_groups = c("5-10", "5-10", "5-10", "5-10", "11-16", "11-16", "11-16", "11-16"),
code = c(1, 2, 3, 4, 1, 2, 3, 4),
n = c(10, 20, 30, 40, 20, 40, 10, 20)
)
# get positions of the two groups, excluding code 1
a <- which(dat[1] == "5-10" & dat[2] > 1)
b <- which(dat[1] == "11-16" & dat[2] > 1)
# function to calculate ratios
find_ratio <- function(x) {
(dat[x, ][2, 3] + dat[x, ][3, 3]) / (dat[x, ][1, 3] + dat[x, ][3, 3])
}
# create empty data frame
result <- data.frame(NULL, NULL)
# populate data frame
result[1, 1] <- head(dat, 1)[1, 1]
result[2, 1] <- tail(dat, 1)[1, 1]
result[1, 2] <- find_ratio(a)
result[2, 2] <- find_ratio(b)
# assign column names
colnames(result) <- c("age_group", "ratio")
result
#> age_group ratio
#> 1 5-10 1.166667
#> 2 11-16 0.500000