Replacing zero with previous value in time series R

Question

I have this time series as

               Quant1       Quant2
2013-01-23      400         200
2013-01-22        0         0
2013-01-21        0         0
2013-01-20      125         100
2013-01-18      120         0

And wants output as

               Quant1       Quant2
2013-01-23      400         200
2013-01-22      125         100
2013-01-21      125         100
2013-01-20      125         100
2013-01-18      120         0

I am trying this, but it does not seem to work. I am getting null error NULL Warning encountered while processing method

replace(df,df == 0, NA)    
df <- na.locf(df)
df[is.na(df)] <- 0

Any suggestions?

Update
As per most voted answer I tried (I modified input dates)

> z <- structure(c(400L, 0L, 0L, 125L, 120L, 200L, 0L, 0L, 100L, 
+ 0L), .Dim = c(5L, 2L), .Dimnames = list(NULL, c("Quant1", "Quant2"
+ )), index = structure(c(15728, 15727, 15726, 15725, 15723), class = "Date"), 
+ class = "zoo")
> z
           Quant1 Quant2
2013-01-23    400    200
2013-01-22      0      0
2013-01-21      0      0
2013-01-20    125    100
2013-01-18    120      0
> L <- rowSums(z != 0) > 0
> z[] <- coredata(z)[which(L)[cumsum(L)],]
> z
           Quant1 Quant2
2013-01-23    400    200
2013-01-22      0      0
2013-01-21      0      0
2013-01-20      0      0
2013-01-18    120      0

Is `df` a data.frame or an `xts` object? Edit the output of `dput(df)` into your question. Also, add all relevant `library` calls. You should read [this FAQ](http://stackoverflow.com/a/5963610/1412059). — Roland, Jan 10 '15 at 10:42
It is time series data. I have converted data frame using read.zoo(mydataframe, header = TRUE, format = "%Y-%m-%d",sep="\t") — Chaturvedi Dewashish, Jan 10 '15 at 10:47
as alternative an interpolation may be of interest? than you can replace all 0 with NA and run `na.approx` {zoo}. Perhaps dplyr windows functions like `lag`may be interesting too — ckluss, Jan 10 '15 at 12:43

G. Grothendieck · Answer 1 · 2015-01-10T12:11:12.247

4

In the future please make your questions self-contained including the library calls and dput(x) output of any input x.

We assume this is a zoo object as shown at the end. We will call it z since df suggests that its a data frame.

library(zoo)

L <- rowSums(z != 0) > 0
z[] <- coredata(z)[which(L)[cumsum(L)],]

giving:

> z
           Quant1 Quant2
2013-01-18    400    200
2013-01-20    400    200
2013-01-21    400    200
2013-01-22    125    100
2013-01-23    120      0

Note: This input was used:

z <- structure(c(400L, 400L, 400L, 125L, 120L, 200L, 200L, 200L, 100L, 
0L), .Dim = c(5L, 2L), .Dimnames = list(NULL, c("Quant1", "Quant2"
)), index = structure(c(15723, 15725, 15726, 15727, 15728), class = "Date"), 
class = "zoo")

edited Jan 10 '15 at 12:11

answered Jan 10 '15 at 11:13

G. Grothendieck

254,981
17
203
341

Sorry about the input/output data. Thanks for looking into it. I have corrected it. – Chaturvedi Dewashish Jan 10 '15 at 12:09
Removed non-negative assumption. – G. Grothendieck Jan 10 '15 at 12:18
I have updated the question. Although I tried using your solution, but somehow could not get the expected result. – Chaturvedi Dewashish Jan 12 '15 at 05:59

Mika Prouk · Answer 2 · 2015-01-10T11:43:20.643

I also assumed it to be a zoo-object and build the following function by hand which only cares about Quant1 to be zero or not. It is less elegant and probably slower (one should replace the for loop by some apply-function) than the previous approach by Grothendieck but maybe is somewhat instructive to you.

require(zoo)
times <- as.POSIXct(c("2013-01-18", "2013-01-20", "2013-01-21", "2013-01-22", "2013-01-23", "2013-01-25",  "2013-01-29",  "2013-02-02", "2013-02-04"))
quant1 <- c(400,0,0,125,120,0,70,0,0)
quant2 <- c(200,0,0,100,0,300,150,80, 200)

z <- zoo(data.frame(Quant1 = quant1, Quant2 = quant2), order.by = times)
repl_zeros <- function (z) {
  diffs <- c(0, diff(as.numeric(z$Quant1 == 0)))
  beginnings <- which(diffs == 1) 
  ends <- which(diffs == -1) - 1
  valueindices <- ends + 1
  for (i in 1:length(valueindices)) {
    z[beginnings[i]:ends[i],]$Quant1 <- z[valueindices[i],]$Quant1
    z[beginnings[i]:ends[i],]$Quant2 <- z[valueindices[i],]$Quant2
  }
  z
}

Note: repl_zeros replaces zeros by following values as in your example, where you said you want to replace by previous values in the title of your question. Adjusting it to what you really meant should be easy though.

Replacing zero with previous value in time series R

2 Answers2

Linked