adding and dividing multiple columns

[yoyong]

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.

[technocrat]

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

[yoyong]

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.

[technocrat]

@technocrat

a <- which(dat[1] == "5-10" & dat[2] > 1)
b <- which(dat[1] == "11-16" & dat[2] > 1)

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).

@technocrat

result[1, 2] <- find_ratio(a)
result[2, 2] <- find_ratio(b)

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.