CODEWITHSUNDEEP
rdplyr
MLE
MLE

Reputation: 1043

replace NAs using existing value by group

I need to replace the missing values in s1 and s2 using the values in vals and vals2 of the same relative position. I am having trouble indexing the position in dplyr by group. If other methods are available, I happy to try.

Data

id = c(1,1,1,1,1,1,2,2,2,2)
date=101:110
vals = c(101:110)
vals2 = c(1:10)


test = data.frame(id,date,vals,vals2)
test<-test%>% group_by(id)%>%
  mutate(s1=rollsumr(vals,k=3,fill=NA),
        s2=rollsumr(vals,k=2,fill=NA))

test

      id  date  vals vals2    s1    s2
   <dbl> <int> <int> <int> <int> <int>
 1    1.   101   101     1    NA    NA
 2    1.   102   102     2    NA   203
 3    1.   103   103     3   306   205
 4    1.   104   104     4   309   207
 5    1.   105   105     5   312   209
 6    1.   106   106     6   315   211
 7    2.   107   107     7    NA    NA
 8    2.   108   108     8    NA   215
 9    2.   109   109     9   324   217
10    2.   110   110    10   327   219

My code (not working)

test<-test %>%
   mutate(s1=replace(s1, ???, NA),
          s2=replace(s2,???,NA))

Desire output

      id  date  vals vals2    s1    s2
   <dbl> <int> <int> <int> <int> <int>
 1    1.   101   101     1    101   1
 2    1.   102   102     2    102   203
 3    1.   103   103     3   306   205
 4    1.   104   104     4   309   207
 5    1.   105   105     5   312   209
 6    1.   106   106     6   315   211
 7    2.   107   107     7    107    7
 8    2.   108   108     8    108   215
 9    2.   109   109     9   324   217
10    2.   110   110    10   327   219

Upvotes: 1

Views: 43

Answers (1)

markus
markus

Reputation: 26343

You can use ifelse

test %>% 
 mutate(s1 = ifelse(is.na(s1), vals, s1),
        s2 = ifelse(is.na(s2), vals2, s2))
# A tibble: 10 x 6
# Groups:   id [2]
#     id  date  vals vals2    s1    s2
#  <dbl> <int> <int> <int> <int> <int>
#1    1.   101   101     1   101     1
#2    1.   102   102     2   102   203
#3    1.   103   103     3   306   205
#4    1.   104   104     4   309   207
#5    1.   105   105     5   312   209
#6    1.   106   106     6   315   211
#7    2.   107   107     7   107     7
#8    2.   108   108     8   108   215
#9    2.   109   109     9   324   217
#10   2.   110   110    10   327   219

Upvotes: 2

Related Questions