R: Fill an empty cell with the value of the last non-empty cell

Question

In Excel, it's easy to grab a cell inside a column and drag down to replace many cells below so that each cell becomes the same value as the original.

This function can be performed in R using a for loop. I spent some time trying to figure it out today and thought that I would share in favor of the following person in my shoes:

for (row in 2:length(data$column)){ # 2 so you don't affect column names
    if(data$column[row] == "") {    # if its empty...
        data$column[row] = data$column[row-1] # ...replace with previous row value
    }
}

This worked for me, although it took a long time to work with a huge data file (5-10 minutes). There may be a more efficient way to achieve this feature, and I encourage everyone to say how this can be done.

Thanks and good luck.

Answer 1

df <- data.frame(a = c(1:5, "", 3, "", "", "", 4), stringsAsFactors = FALSE)

> df
   a
1  1
2  2
3  3
4  4
5  5
6   
7  3
8   
9   
10  
11 4

while(length(ind <- which(df$a == "")) > 0){
  df$a[ind] <- df$a[ind -1]
}

> df
   a
1  1
2  2
3  3
4  4
5  5
6  5
7  3
8  3
9  3
10 3
11 4

EDIT:

set.seed(1)
N = 1e6
df <- data.frame(a = sample(c("",1,2),size=N,replace=TRUE),
                 stringsAsFactors = FALSE)

if(df$a[1] == "") {df$a[1] <- NA}

system.time(
  while(length(ind <- which(df$a == "")) > 0){
    df$a[ind] <- df$a[ind - 1]
  }, gcFirst = TRUE)

user  system elapsed 
0.89    0.00    0.88 

Answer 2

, na.locf zoo, data.table. y, ( x ). na.locf , , NA. 16.

library(data.table)
library(zoo)
N=1e6  ##  number of rows 
DT <- data.table(x=sample(c("",1,2),size=N,replace=TRUE))
system.time(DT[!nzchar(x),x:=NA][,y:=na.locf(x)])
## user  system elapsed 
## 0.59    0.30    1.78 
# x y
# 1:  2 2
# 2: NA 2
# 3: NA 2
# 4:  1 1
# 5:  1 1
# ---     
#   999996:  1 1
# 999997:  2 2
# 999998:  2 2
# 999999: NA 2
# 1000000: NA 2

Answer 3

:

library(dplyr)
library(zoo)

N = 1e6
df <- data.frame(x = sample(c(NA,"A","B"), size=N, replace=TRUE))

system.time(test <- df %>% dplyr::do(zoo::na.locf(.)))

   user  system elapsed 
  0.082   0.000   0.130