57

I have a data frame which is arranged by descending order of date.

ps1 = data.frame(userID = c(21,21,21,22,22,22,23,23,23), 
             color = c(NA,'blue','red','blue',NA,NA,'red',NA,'gold'), 
             age = c('3yrs','2yrs',NA,NA,'3yrs',NA,NA,'4yrs',NA), 
             gender = c('F',NA,'M',NA,NA,'F','F',NA,'F') 
)

I wish to impute(replace) NA values with previous values and grouped by userID In case the first row of a userID has NA then replace with the next set of values for that userid group.

I am trying to use dplyr and zoo packages something like this...but its not working

cleanedFUG <- filteredUserGroup %>%
 group_by(UserID) %>%
 mutate(Age1 = na.locf(Age), 
     Color1 = na.locf(Color), 
     Gender1 = na.locf(Gender) )

I need result df like this:

                      userID color  age gender
                1     21  blue 3yrs      F
                2     21  blue 2yrs      F
                3     21   red 2yrs      M
                4     22  blue 3yrs      F
                5     22  blue 3yrs      F
                6     22  blue 3yrs      F
                7     23   red 4yrs      F
                8     23   red 4yrs      F
                9     23  gold 4yrs      F
zx8754
  • 52,746
  • 12
  • 114
  • 209
Tarak
  • 1,035
  • 2
  • 8
  • 14
  • See if this is helpful. [Replacing NAs with latest non-NA value](http://stackoverflow.com/questions/7735647/replacing-nas-with-latest-non-na-value) or this [replace NA value with the group value](http://stackoverflow.com/questions/23583739/replace-na-value-with-the-group-value) – Ronak Shah Oct 14 '16 at 10:30

5 Answers5

104
library(tidyr) #fill is part of tidyr

ps1 %>% 
  group_by(userID) %>% 
  #fill(color, age, gender) %>% #default direction down
  fill(color, age, gender, .direction = "downup")

Which gives you:

Source: local data frame [9 x 4]
Groups: userID [3]

  userID  color    age gender
   <dbl> <fctr> <fctr> <fctr>
1     21   blue   3yrs      F
2     21   blue   2yrs      F
3     21    red   2yrs      M
4     22   blue   3yrs      F
5     22   blue   3yrs      F
6     22   blue   3yrs      F
7     23    red   4yrs      F
8     23    red   4yrs      F
9     23   gold   4yrs      F
zx8754
  • 52,746
  • 12
  • 114
  • 209
Rentrop
  • 20,979
  • 10
  • 72
  • 100
  • 5
    The `fill()` function has since been updated to allow filling in both directions at once instead of needing to use it twice. The `.direction` options now include `downup` and `updown`. – Ritchie Sacramento Mar 02 '20 at 22:28
  • 2
    what if your data set is huge and you cant write each columns name – Amit Apr 06 '21 at 06:12
  • 1
    @Amit You can use fill(everything()) to fill all columns – Pake Dec 20 '21 at 19:38
  • Does this only fill with values of the same group or does fill simply take values that are next to the specific row with the NA? – avocado1 Jul 26 '22 at 07:32
5

Using zoo::na.locf directly on the whole data.frame would fill the NA regardless of the userID groups. Package dplyr's grouping has unfortunately no effect on na.locf function, that's why I went with a split:

library(dplyr); library(zoo)
ps1 %>% split(ps1$userID) %>% 
  lapply(function(x) {na.locf(na.locf(x), fromLast=T)}) %>% 
  do.call(rbind, .)
####      userID color  age gender
#### 21.1     21  blue 3yrs      F
#### 21.2     21  blue 2yrs      F
#### 21.3     21   red 2yrs      M
#### 22.4     22  blue 3yrs      F
#### 22.5     22  blue 3yrs      F
#### 22.6     22  blue 3yrs      F
#### 23.7     23   red 4yrs      F
#### 23.8     23   red 4yrs      F
#### 23.9     23  gold 4yrs      F

What it does is that it first splits the data into 3 data.frames, then I apply a first pass of imputation (downwards), then upwards with the anonymous function in lapply, and eventually use rbind to bring the data.frames back together. You have the expected output.

agenis
  • 8,069
  • 5
  • 53
  • 102
  • 1
    You could replace `do.call()` with the more idiomatic `bind_rows()` and `split(ps1$userID)` with `split(.$userID)` – Steven Beaupré Oct 14 '16 at 14:56
  • 1
    Another alternative using `purrr` could also be: `library(purrr); ps1 %>% slice_rows("userID") %>% by_slice(function(x) { na.locf(na.locf(x), fromLast=T) }, .collate = "rows") ` – Steven Beaupré Oct 14 '16 at 15:15
  • 1
    @StevenBeaupré nice! that deserves a new answer on its own ;-) – agenis Oct 14 '16 at 15:21
5

I wrote this function and it is definitely faster than fill and probably faster than na.locf:

fill_NA <- function(x) {
  which.na <- c(which(!is.na(x)), length(x) + 1)
  values <- na.omit(x)

  if (which.na[1] != 1) {
    which.na <- c(1, which.na)
    values <- c(values[1], values)
  }

  diffs <- diff(which.na)
  return(rep(values, times = diffs))
}
Naja Bohanec
  • 71
  • 1
  • 2
  • Thank you for sharing your code which is independent of a grouping variable (which I do not have in my data) and does not require installing the zoo package. – Johannes Ranke Aug 26 '20 at 07:29
  • Except that I am using tidyr anyways, and I discovered that tidyr::fill does the job more elegantly using the pipe, without the need to use mutate(). – Johannes Ranke Aug 26 '20 at 07:41
3

Using @agenis method with na.locf() combined with purrr, you could do:

library(purrr)
library(zoo)

ps1 %>% 
  slice_rows("userID") %>% 
  by_slice(function(x) { 
    na.locf(na.locf(x), fromLast=T) }, 
    .collate = "rows")
Steven Beaupré
  • 21,343
  • 7
  • 57
  • 77
1

A few years down the line, I found that things have changed. Using @Steven Beaupré's approach,

1) Adding na.rm=F ensures no rows are deleted/excluded. 2) The slide_rows() function can be found in the purrrlyr package.

library(purrrlyr)
library(zoo)

ps1 %>% 
  slice_rows("userID") %>% 
  by_slice(function(x) { 
    na.locf(na.locf(x, na.rm=F), fromLast=T, na.rm=F) }, 
    .collate = "rows")
AMS
  • 41
  • 5