CODEWITHSUNDEEP
rdata.table
Ben L
Ben L

Reputation: 97

Count by group with condition (in R data.table)

Consider a dataset that consists of ID and Val.

# dataset
ID Val Counter
 1   2       1
 1   4       2
 1  NA       2
 1  13       3
 1  12       4
 2  NA       0
 2  33       1
 2   5       2
 2   5       3

A counter per subgroup can be added by dt[, normal_counter := 1:.N, by=ID]. I am looking for a counter that is not incremented when there is an NAvalue (see counter in example above).

Upvotes: 0

Views: 541

Answers (1)

thelatemail
thelatemail

Reputation: 93938

This is a cumulative sum of non-NA values by group, so:

dat[, cntr := cumsum(!is.na(Val)), by=ID]
dat
#   ID Val Counter cntr
#1:  1   2       1    1
#2:  1   4       2    2
#3:  1  NA       2    2
#4:  1  13       3    3
#5:  1  12       4    4
#6:  2  NA       0    0
#7:  2  33       1    1
#8:  2   5       2    2
#9:  2   5       3    3

Upvotes: 4

Related Questions