Can not fill 2 concecutive rows using dplyr lag function

Question

I use R and R package dplyr. I want in data frame fill missing values from previous row value (increase it by 1). It works well if I have a gap of one, but if there are 2 or more consecutive missing values then I have a problem. Let say here I have a data frame and for some rows it values are missing.

  id val1 val2
1  0    0    0
2  1    1    1
3  2   NA    NA
4  3    3    3
5  4   NA   NA
6  5   NA   NA
7  6    6    6

Here val2 is for which I perform the increase and val1 is just original value. For NA values I want to take previous row value and increase it by 1. I use dplyr package and lag function.
Here is the code:

library(dplyr)
tmp.data <- data.frame(id = c(0:6),val1 = c(0:6)) %>%
  mutate(
    val1 = ifelse( val1 %in% c(2,4:5),NA,val1),
    val2 = val1,
  ) 

tmp.data <- tmp.data %>%
  mutate(
    val2 = ifelse(
      is.na(val2),
      lag(val2) + 1,
      val2
      )
  )

Do you want to have 4 and 5 filled in for the missing values or 4 and 4? — kath, Sep 23 '18 at 18:53

tmfmnk · Accepted Answer · 2018-10-11T22:00:45.307

1

Solution 1a: Assuming increase from previous missing value(s), with 2 consecutive missing rows

Using dplyr:

tmp.data %>%
  mutate(val2 = ifelse(is.na(val2), lag(val2) + 1, val2),
         val2 = ifelse(is.na(val2), lag(val2) + 1, val2))

Solution 1b: Assuming increase from previous missing value(s), with N consecutive missing rows

Using data.table, zoo and dplyr:

setDT(tmp.data)[, consec := seq_len(.N), by=rleid(val2)]

tmp.data %>%
  mutate(val2 = ifelse(is.na(val2), na.locf(val2) + consec, val2)) %>%
  select(-consec)

Or written together:

tmp.data %>%
  group_by(rleid(val2)) %>%
  mutate(consec = seq_along(val2)) %>%
  ungroup() %>%
  mutate(val2 = ifelse(is.na(val2), na.locf(val2) + consec, val2)) %>%
  select(id, val1, val2)

Solution 2: Not assuming increase from previous missing value(s)

Using dplyr and zoo:

tmp.data %>%
  mutate(val2 = ifelse(is.na(val2), na.locf(val2) + 1, val2))

edited Oct 11 '18 at 22:00

answered Sep 23 '18 at 18:53

tmfmnk

38,881
4
47
67

I want to have 4 and 5, I mean to take from previous value which is already increased. – K.I. Sep 23 '18 at 19:19
Updated my code. It is a very ad hoc solution. Do you need something more general? – tmfmnk Sep 23 '18 at 19:34
Yes I need something more generic. Missing rows can be any integer. – K.I. Sep 23 '18 at 19:57
Solution 1b also needs package zoo. But yes it works. – K.I. Sep 24 '18 at 06:41

Can not fill 2 concecutive rows using dplyr lag function

1 Answers1