3

I want to count how many TRUE markers I have for each group in a tidy data.table:

DT <- data.table(      id = c(1   ,1    ,1    ,2   ,2    ,2   ,2   ,2    )
                 , marker = c(TRUE,FALSE,FALSE,TRUE,FALSE,TRUE,TRUE,FALSE))

So I tried DT[marker==TRUE, num_markers := .N, by = id], that outputs:

   id marker num_markers
1:  1   TRUE           1
2:  1  FALSE          NA
3:  1  FALSE          NA
4:  2   TRUE           3
5:  2  FALSE          NA
6:  2   TRUE           3
7:  2   TRUE           3
8:  2  FALSE          NA

Instead, the desired output is:

   id marker num_markers
1:  1   TRUE           1
2:  1  FALSE           1
3:  1  FALSE           1
4:  2   TRUE           3
5:  2  FALSE           3
6:  2   TRUE           3
7:  2   TRUE           3
8:  2  FALSE           3

How do I adjust the code to get the desired output (remove NA for each id and complete with group number of markers?)

Fabio Correa
  • 1,257
  • 1
  • 11
  • 17

3 Answers3

3

Maybe use sum on marker column:

DT[, num_markers := sum(marker), by = id ][]

#    id marker num_markers
# 1:  1   TRUE           1
# 2:  1  FALSE           1
# 3:  1  FALSE           1
# 4:  2   TRUE           3
# 5:  2  FALSE           3
# 6:  2   TRUE           3
# 7:  2   TRUE           3
# 8:  2  FALSE           3
zx8754
  • 52,746
  • 12
  • 114
  • 209
1
DT[, num_markers := (.SD[marker, .N]), by = id]

which gives:

> DT
   id marker num_markers
1:  1   TRUE           1
2:  1  FALSE           1
3:  1  FALSE           1
4:  2   TRUE           3
5:  2  FALSE           3
6:  2   TRUE           3
7:  2   TRUE           3
8:  2  FALSE           3
Will
  • 910
  • 7
  • 17
-1

Another option using ave

DT[, num_markers := ave(marker,id,FUN = sum)]

which gives

> DT
   id marker num_markers
1:  1   TRUE           1
2:  1  FALSE           1
3:  1  FALSE           1
4:  2   TRUE           3
5:  2  FALSE           3
6:  2   TRUE           3
7:  2   TRUE           3
8:  2  FALSE           3
ThomasIsCoding
  • 96,636
  • 9
  • 24
  • 81