newbie newbie - 1 month ago 7
R Question

How to assign index while ignoring NAs by group

given the following

data.table
:

library(data.table)
dt <- data.table(id=c(1,2,3,4), Year1=c(NA, 30, 40, NA), Year2=c(NA, 30, 20 ,70), Year3=c(60, 40, 0, 10), Year4=c(10,20,30,30))
dtm <- melt(dt, id.var = 'id')[order(id, variable)]


I would like to assign index to
dtm
, so that the expected return will be as follows:

id variable value index
1: 1 Year1 NA NA
2: 1 Year2 NA NA
3: 1 Year3 60 1
4: 1 Year4 10 2
5: 2 Year1 30 1
6: 2 Year2 30 2
7: 2 Year3 40 3
8: 2 Year4 20 4
9: 3 Year1 40 1
10: 3 Year2 20 2
11: 3 Year3 0 3
12: 3 Year4 30 4
13: 4 Year1 NA NA
14: 4 Year2 70 1
15: 4 Year3 10 2
16: 4 Year4 30 3


I have tried
dtm[ ,index:=1:.N, by = id]
, but it does not ignore NAs.

Could you please give me suggestion?

Answer

We need to specify the 'i' to ignore the NAs

dtm[!is.na(value), index := 1:.N , by = id]
dtm
#    id variable value index
# 1:  1    Year1    NA    NA
# 2:  1    Year2    NA    NA
# 3:  1    Year3    60     1
# 4:  1    Year4    10     2
# 5:  2    Year1    30     1
# 6:  2    Year2    30     2
# 7:  2    Year3    40     3
# 8:  2    Year4    20     4
# 9:  3    Year1    40     1
#10:  3    Year2    20     2
#11:  3    Year3     0     3
#12:  3    Year4    30     4
#13:  4    Year1    NA    NA
#14:  4    Year2    70     1
#15:  4    Year3    10     2
#16:  4    Year4    30     3
Comments