 # adding and dividing multiple columns

Hi everyone.
I have a sample dataset below. There are 10 age groups.

age_groups code n
5-10 1 10
5-10 2 20
5-10 3 30
5-10 4 40
11-16 1 20
11-16 2 40
11-16 3 10
11-16 4 20

I want to produce the table below where the ratio column (in 2dp) follows the formula [(sum of codes 3 and 4)/(sum of codes 2 and 4)] for each age group.

age_groups ratio
5-10 1.17
11-16 0.50

Appreciate any help.

Thanks.

``````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 == "5-10" & dat > 1)
b <- which(dat == "11-16" & dat > 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
``````

Thank you very much.

Will the codes apply to more than 2 age groups? I have 10 age groups. I should have clarified that in my post. is the model for the next 8 age groups `d, e, f, g, h, i, j, k` (skip `c` because it's the name of the built-in function).

similarly is extended as

``````result[3,2]  <- find_ratio(d)
``````

etc.

This all looks more complicated than it really is—using the `[` subset operator, though, is both powerful and easy to apply once understood. Together with `which` it vastly decreases the amount of syntax required for this class of problems, compared to alternatives.

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.