CODEWITHSUNDEEP
rtime-serieszoo
Chaturvedi Dewashish
Chaturvedi Dewashish

Reputation: 1469

Replacing zero with previous value in time series R

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

Upvotes: 0

Views: 1308

Answers (2)

G. Grothendieck
G. Grothendieck

Reputation: 270020

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")

Upvotes: 4

Mika Prouk
Mika Prouk

Reputation: 515

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.

Upvotes: 0

Related Questions