Create conditional sum score in new column R

fhr 0
Viewed 31

EDIT: I added another column with day in the sample data, as beep is nested within day.

I have the following data. I am trying to get a sum of a in the row of beep = 3 by, beep, day, and id. See column b for what I would like to achieve. I have tried it using dyplr and group_by, but so far no succes. Any ideas on how to approach this are much appreciated!

Note: actual data contains missings in beep (skipped)

structure(list(id = c(1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 
2L, 2L), date = c("1-1-2022", "1-1-2022", "1-1-2022", "2-1-2022", 
"2-1-2022", "2-1-2022", "1-1-2022", "1-1-2022", "1-1-2022", "2-1-2022", 
"2-1-2022", "2-1-2022"), beep = c(1L, 2L, 3L, 1L, 2L, 3L, 1L, 
2L, 3L, 1L, 2L, 3L), a = c(1L, 0L, 1L, 1L, 0L, 0L, 1L, 0L, 0L, 
1L, 1L, 0L), b = c(NA, NA, 2L, NA, NA, 1L, NA, NA, 1L, NA, NA, 
2L)), class = "data.frame", row.names = c(NA, -12L))
id day beep a b
1 1-1-22 1 1 NA
1 1-1-22 2 0 NA
1 1-1-22 3 1 2
1 2-1-22 1 1 NA
1 2-1-22 2 0 NA
1 2-1-22 3 0 1
2 1-1-22 1 1 NA
2 1-1-22 2 0 NA
2 1-1-22 3 0 1
2 2-1-22 1 1 NA
2 2-1-22 2 1 NA
2 2-1-22 3 0 2
dplyr r
1 Answers

You could use ave which will populate the summary statistic (in this case sum) across all rows for each unique group, defined here by both df$date and df$id:

df$ave_b <- ave(df$a, df$date, df$id, FUN = sum)

If you then want to only keep the sum in those rows where beep = 3, you can set the rest to NA

is.na(df$ave_b) <- !df$beep == 3

The resulting column will be filled with NA for all other values of beep

> df
   id     date beep a  b ave_b
1   1 1-1-2022    1 1 NA    NA
2   1 1-1-2022    2 0 NA    NA
3   1 1-1-2022    3 1  2     2
4   1 2-1-2022    1 1 NA    NA
5   1 2-1-2022    2 0 NA    NA
6   1 2-1-2022    3 0  1     1
7   2 1-1-2022    1 1 NA    NA
8   2 1-1-2022    2 0 NA    NA
9   2 1-1-2022    3 0  1     1
10  2 2-1-2022    1 1 NA    NA
11  2 2-1-2022    2 1 NA    NA
12  2 2-1-2022    3 0  2     2

There's a one-liner using tapply but it will fail if there are groups with no beep == 3 - this is the safest technique I can think of.

Disclaimer: if you have cases where beep > 3, as per Francesco Grossetti's comment, those would be summed too - is this possible, and if so what is your desired behaviour?

Ottie0
Related