CODEWITHSUNDEEP
rdata.table
East Liu
East Liu

Reputation: 107

R data.table Retain the first non-NA value of a group till end of the group

I want to retain the first non-NA value of each group, called it baseline, to end of the corresponding group as below.

The data i have:

data <- data.table(id=rep(c(1,2,3),each=4), value=c(12, 10, 17, 19, 21, 22, 34, 18, NA, 12, 32, 18))



   id value
 1:  1    12
 2:  1    10
 3:  1    17
 4:  1    19
 5:  2    21
 6:  2    22
 7:  2    34
 8:  2    18
 9:  3    NA
10:  3    12
11:  3    32
12:  3    18

I want to retain the first non-NA value for each group as below:

   id value BASE
 1:  1    12   12
 2:  1    10   12
 3:  1    17   12
 4:  1    19   12
 5:  2    21   21
 6:  2    22   21
 7:  2    34   21
 8:  2    18   21
 9:  3    NA   NA
10:  3    12   12
11:  3    32   12
12:  3    18   12

Please pay attention to id=3 for which the first value is NA which should not be retained.

Upvotes: 2

Views: 306

Answers (3)

TheN
TheN

Reputation: 513

This should work

data[, BASE := na.omit(value)[1], by = id]

Upvotes: 1

PavoDive
PavoDive

Reputation: 6496

filter the NA values and assing using .SD:

data[!is.na(value), BASE := .SD[1L], by = id, .SDcols = "value"]

Upvotes: 4

Ronak Shah
Ronak Shah

Reputation: 389265

We can get the first non-NA value from each id and then replace the NAs value back to NA

library(data.table)
data[, BASE := first(na.omit(value)), by = id][is.na(value), BASE:=NA]

data
#    id value BASE
# 1:  1    12   12
# 2:  1    10   12
# 3:  1    17   12
# 4:  1    19   12
# 5:  2    21   21
# 6:  2    22   21
# 7:  2    34   21
# 8:  2    18   21
# 9:  3    NA   NA
#10:  3    12   12
#11:  3    32   12
#12:  3    18   12

Or using the same logic with dplyr

library(dplyr)
data %>%
  group_by(id) %>%
  mutate(BASE = first(na.omit(value)), 
         BASE = replace(BASE, is.na(value), NA))

Upvotes: 0

Related Questions