How to replace NA with the last non-NA in the group?

Question

I have faces DF with some incomplete and repetitive characteristics:

    name <- c("A", "A", "B", "B", "B", "C", "D", "D")
    age <- c(28,NA,NA,NA,NA,NA,53,NA)
    birthplace <- c("city1",NA, "city2",NA,NA,NA,NA,NA)
    value <- 100:107
    df <- data.frame(name,age,birthplace,value)

    name age birthplace value
1    A  28      city1   100
2    A  NA       <NA>   101
3    B  NA      city2   102
4    B  NA       <NA>   103
5    B  NA       <NA>   104
6    C  NA       <NA>   105
7    D  53       <NA>   106
8    D  NA       <NA>   107

Since the value is unique to the string. I want each line with detailed details to look like this:

       name age birthplace value
    1    A  28      city1   100
    2    A  28      city1   101
    3    B  NA      city2   102
    4    B  NA      city2   103
    5    B  NA      city2   104
    6    C  NA       <NA>   105
    7    D  53       <NA>   106
    8    D  53       <NA>   107

I tried to use

library(zoo)
library(dplyr)
df <- df %>% group_by(name) %>% na.locf(na.rm=F)

But this is not very good. Any idea for implementing a function on a group?

Answer 1

As another basic R-solution, here is the poor man na.locf

fill_down <- function(v) {
    if (length(v) > 1) {
        keep <- c(TRUE, !is.na(v[-1]))
        v[keep][cumsum(keep)]
    } else v
}

To populate a group, the approach is to use tapply()to separate and apply to each group and split<-to combine groups into the original geometry, like

fill_down_by_group <- function(v, grp) {
    ## original 'by hand':
    ##     split(v, grp) <- tapply(v, grp, fill_down)
    ##     v
    ## done by built-in function `ave()`
    ave(v, grp, FUN=fill_down)
}

To process multiple columns, you can

elts <- c("age", "birthplace")
df[elts] <- lapply(df[elts], fill_down_by_group, df$name)

Notes

• , dplyr ? , ,

  library(dplyr); library(tidyr)
  df %>% group_by(name) %>% fill_(elts)
  

• , "" (, identical(grp, sort(grp))),

  fill_down_by_grouped <- function(v, grp) {
      if (length(v) > 1) {
          keep <- !(duplicated(v) & is.na(v))
          v[keep][cumsum(keep)]
      } else v
  }
  

• fill_down() 10 ~ 225 ; fill_down_by_grouped() ~ 300 ; fill_down_by_group() ; 10000 ~ 2s, 10M 36s

Answer 2

:

library(dplyr)
library(tidyr)
df %>% group_by(name) %>% fill(age, birthplace)

# Source: local data frame [8 x 4]
# Groups: name [4]

#     name   age birthplace value
#   <fctr> <dbl>     <fctr> <int>
# 1      A    28      city1   100
# 2      A    28      city1   101
# 3      B    NA      city2   102
# 4      B    NA      city2   103
# 5      B    NA      city2   104
# 6      C    NA         NA   105
# 7      D    53         NA   106
# 8      D    53         NA   107

Answer 3

na.locf do

df %>% group_by(name) %>% do(na.locf(., na.rm = FALSE))

Answer 4

, , .

(nested <- df %>% 
  group_by(name) %>% 
  summarize(
    age = na.omit(age)[1], 
    birthplace = na.omit(birthplace)[1], 
    value = list(value)
  )
)
## # A tibble: 4 x 4
##     name   age birthplace     value
##   <fctr> <dbl>     <fctr>    <list>
## 1      A    28      city1 <int [2]>
## 2      B    NA      city2 <int [3]>
## 3      C    NA         NA <int [1]>
## 4      D    53         NA <int [2]>

value s, .

nested %>% tidyr::unnest()
## # A tibble: 8 x 4
##     name   age birthplace value
##   <fctr> <dbl>     <fctr> <int>
## 1      A    28      city1   100
## 2      A    28      city1   101
## 3      B    NA      city2   102
## 4      B    NA      city2   103
## 5      B    NA      city2   104
## 6      C    NA         NA   105
## 7      D    53         NA   106
## 8      D    53         NA   107

Answer 5

R:

do.call(rbind,lapply(split(df, df$name), function(x) {
    tempdf <- x
    if (nrow(tempdf) > length(which(is.na(x$birthplace)))) {
        tempdf[which(is.na(x$birthplace)),c("age","birthplace")] <- tempdf[which(is.na(x$birthplace))[1]-1,c("age","birthplace")]
    }
    return(tempdf)
}))

:

 name age birthplace value
 A    28  city1      100  
 A    28  city1      101  
 B    NA  city2      102  
 B    NA  city2      103  
 B    NA  <NA>       104  
 C    NA  <NA>       105  
 D    53  <NA>       106  
 D    NA  <NA>       107 

Answer 6

R. fill ave, na.omit(x)[1], .

fill <- function(...) ave(..., FUN = function(x) na.omit(x)[1])
transform(df, birthplace = fill(birthplace, name), age = fill(age, name))

: na.locf. fill :

library(zoo)
fill <- function(...) ave(..., FUN = function(x) na.locf(x, na.rm = FALSE))

Answer 7

. . .

library(sqldf)
sqldf('select t1.name, t2.age, t2.birthplace,t1.value from df t1 inner join df t2 on t1.name=t2.name group by t1.value')

Answer 8

, head() :

df <- setNames(data.frame(lapply(names(df), function(d)
               sapply(1:nrow(df), function(i)
                      head(df[df[1:i, c("name")] == df$name[i], c(d)], 1))
        )), names(df))