CODEWITHSUNDEEP
rdplyr
bvowe
bvowe

Reputation: 3384

R how to fill in NA with rules

data=data.frame(person=c(1,1,1,2,2,2,2,3,3,3,3),
t=c(3,NA,9,4,7,NA,13,3,NA,NA,12),
WANT=c(3,6,9,4,7,10,13,3,6,9,12))

So basically I am wanting to create a new variable 'WANT' which takes the PREVIOUS value in t and ADDS 3 to it, and if there are many NA in a row then it keeps doing this. My attempt is:

library(dplyr)
data %>% 
  group_by(person) %>% 
  mutate(WANT_TRY = fill(t) + 3)

Upvotes: 1

Views: 451

Answers (4)

davsjob
davsjob

Reputation: 1950

You can use functional programming from purrr and "NA-safe" addition from hablar:

library(hablar)
library(dplyr)
library(purrr)

data %>% 
  group_by(person) %>% 
  mutate(WANT2 = accumulate(t, ~.x %plus_% 3))

Result

# A tibble: 11 x 4
# Groups:   person [3]
   person     t  WANT WANT2
    <dbl> <dbl> <dbl> <dbl>
 1      1     3     3     3
 2      1    NA     6     6
 3      1     9     9     9
 4      2     4     4     4
 5      2     7     7     7
 6      2    NA    10    10
 7      2    13    13    13
 8      3     3     3     3
 9      3    NA     6     6
10      3    NA     9     9
11      3    12    12    12

Upvotes: 1

will.pearse
will.pearse

Reputation: 234

This is harder than it seems because of the double NA at the end. If it weren't for that, then the following:

ifelse(is.na(data$t), c(0, data$t[-nrow(data)])+3, data$t)

...would give you want you want. The simplest way, that uses the same logic but doesn't look very clever (sorry!) would be:

.impute <- function(x) ifelse(is.na(x), c(0, x[-length(x)])+3, x)
.impute(.impute(data$t))

...which just cheats by doing it twice. Does that help?

Upvotes: 0

www
www

Reputation: 39154

Here is another way. We can do linear interpolation with the imputeTS package.

library(dplyr)
library(imputeTS)

data2 <- data %>%
  group_by(person) %>%
  mutate(WANT2 = na.interpolation(WANT)) %>%
  ungroup()

data2
# # A tibble: 11 x 4
#    person     t  WANT WANT2
#     <dbl> <dbl> <dbl> <dbl>
#  1      1     3     3     3
#  2      1    NA     6     6
#  3      1     9     9     9
#  4      2     4     4     4
#  5      2     7     7     7
#  6      2    NA    10    10
#  7      2    13    13    13
#  8      3     3     3     3
#  9      3    NA     6     6
# 10      3    NA     9     9
# 11      3    12    12    12

Upvotes: 1

Shree
Shree

Reputation: 11140

Here's one way -

data %>% 
  group_by(person) %>%
  mutate(
    # cs = cumsum(!is.na(t)), # creates index for reference value; uncomment if interested
    w = case_when(
      # rle() gives the running length of NA
      is.na(t) ~ t[cumsum(!is.na(t))] + 3*sequence(rle(is.na(t))$lengths),
      TRUE ~ t
      )
  ) %>% 
  ungroup()

# A tibble: 11 x 4
   person     t  WANT     w
    <dbl> <dbl> <dbl> <dbl>
 1      1     3     3     3
 2      1    NA     6     6
 3      1     9     9     9
 4      2     4     4     4
 5      2     7     7     7
 6      2    NA    10    10
 7      2    13    13    13
 8      3     3     3     3
 9      3    NA     6     6
10      3    NA     9     9
11      3    12    12    12

Upvotes: 1

Related Questions