Geek asks and answers

R - Scroll through each line and fill in the value from the previous line

I would like to check if the value from a specific column contains the value NA, and if so, fill this NA space with the value from the previous row. I'm still trying to find a family of functions apply.

eg. I want to do this:

      Date   Balance
2012-01-01      1000
2012-01-02        NA
2012-01-03        NA
2012-01-04      1200
2012-01-05      1215
2012-01-06        NA

at

      Date   Balance
2012-01-01      1000
2012-01-02      1000
2012-01-03      1000
2012-01-04      1200
2012-01-05      1215
2012-01-06      1215
+5
source share
2 answers

This is the task for the function na.locffrom the zoo package. Cm.?na.locf

Consider DFyour data.frame, then:

DF <- read.table(text="      Date   Balance
2012-01-01      1000
2012-01-02        NA
2012-01-03        NA
2012-01-04      1200
2012-01-05      1215
2012-01-06        NA", header=TRUE)

library(zoo)
na.locf(DF)
        Date Balance
1 2012-01-01    1000
2 2012-01-02    1000
3 2012-01-03    1000
4 2012-01-04    1200
5 2012-01-05    1215
6 2012-01-06    1215
+7
source

Using data.tablewith is roll = TRUEalso great!

require(data.table)
# convert Date column to date format
df$Date <- as.Date(df$Date)
# keep this, as we'll remove rows with NA to use `roll`
dates   <- df$Date
# remove rows with NA
dt2     <- na.omit(data.table(df))
# set key to Date
setkey(dt2, "Date")
# use dates which has the NA rows that will be filled 
# with value from previous column with roll=T
dt2[J(dates), roll=T]

#          Date Balance
# 1: 2012-01-01    1000
# 2: 2012-01-02    1000
# 3: 2012-01-03    1000
# 4: 2012-01-04    1200
# 5: 2012-01-05    1215
# 6: 2012-01-06    1215
+5
source

More articles:


All Articles