An embedded and charset-unspecified text was scrubbed... Name: not available URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20120301/3090397b/attachment.pl>
fill data forward in data frame.
3 messages · Ben quant, Petr Savicky
On Thu, Mar 01, 2012 at 02:31:01PM -0700, Ben quant wrote:
Hello,
My direct desire is a good (fast) way to fill values forward until there is
another value then fill that value foward in the data xx (at the bottom of
this email). For example, from row 1 to row 45 should be NA (no change),
but from row 46 row 136 the value should be 12649, and from row 137 to the
next value should be 13039.00. The last line of code is all you need for
this part.
If you are so inclined, my goal is this: I want to create a weekly time
series out of some data based on the report date. The report date is 'rd'
below, and is the correct date for the time series. My idea (in part seen
below) is to align rd and ua via the incorrect date (the time series date),
then merge that using the report date (rd) and a daily series (so I capture
all of the dates) of dates (dt). That gets the data in the right start
period. I've done all of this so far below and it looks fine. Then I plan
to roll all of those values forward to the next value (see question above),
then I'll do something like this:
xx[weekdays(xx[,1]) == "Friday",]
...to get a weekly series of Friday values. I'm thinking someone probably
has a faster way of doing this. I have to do this many times, so speed is
important. Thanks!
Here is what I have done so far:
dt <- seq(from =as.Date("2009-06-01"), to = Sys.Date(), by = "day")
nms
[1] "2009-06-30" "2009-09-30" "2009-12-31" "2010-03-31" "2010-06-30" "2010-09-30" "2010-12-31" "2011-03-31" "2011-06-30" "2011-09-30" [11] "2011-12-31"
rd
2009-06-30 2009-09-30 2009-12-31 2010-03-31 2010-06-30 2010-09-30 2010-12-31 2011-03-31 2011-06-30 2011-09-30 "2009-07-16" "2009-10-15" "2010-01-19" "2010-04-19" "2010-07-19" "2010-10-18" "2011-01-18" "2011-04-19" "2011-07-18" "2011-10-17" 2011-12-31 "2012-01-19"
ua
2009-06-30 2009-09-30 2009-12-31 2010-03-31 2010-06-30 2010-09-30 2010-12-31 2011-03-31 2011-06-30 2011-09-30 2011-12-31 12649.00 13039.00 13425.00 13731.00 14014.00 14389.00 14833.00 15095.00 15481.43 15846.43 16186.43
x = merge(ua,rd,by='row.names')
names(x) = c('z.date','val','rt_date')
xx = merge(dt,x,by.y= 'rt_date',by.x=1,all.x=T)
xx
x z.date val 1 2009-06-01 <NA> NA 2 2009-06-02 <NA> NA 3 2009-06-03 <NA> NA 4 2009-06-04 <NA> NA 5 2009-06-05 <NA> NA ...ect.... 36 2009-07-06 <NA> NA 37 2009-07-07 <NA> NA 38 2009-07-08 <NA> NA 39 2009-07-09 <NA> NA 40 2009-07-10 <NA> NA 41 2009-07-11 <NA> NA 42 2009-07-12 <NA> NA 43 2009-07-13 <NA> NA 44 2009-07-14 <NA> NA 45 2009-07-15 <NA> NA 46 2009-07-16 2009-06-30 12649 47 2009-07-17 <NA> NA 48 2009-07-18 <NA> NA 49 2009-07-19 <NA> NA 50 2009-07-20 <NA> NA 51 2009-07-21 <NA> NA 52 2009-07-22 <NA> NA 53 2009-07-23 <NA> NA 54 2009-07-24 <NA> NA 55 2009-07-25 <NA> NA 56 2009-07-26 <NA> NA 57 2009-07-27 <NA> NA 58 2009-07-28 <NA> NA ...ect.... 129 2009-10-07 <NA> NA 130 2009-10-08 <NA> NA 131 2009-10-09 <NA> NA 132 2009-10-10 <NA> NA 133 2009-10-11 <NA> NA 134 2009-10-12 <NA> NA 135 2009-10-13 <NA> NA 136 2009-10-14 <NA> NA 137 2009-10-15 2009-09-30 13039.00 138 2009-10-16 <NA> NA 139 2009-10-17 <NA> NA 140 2009-10-18 <NA> NA 141 2009-10-19 <NA> NA 142 2009-10-20 <NA> NA 143 2009-10-21 <NA> NA
Hi.
Try first the following simpler version.
# an input vector
x <- rep(NA, times=20)
x[4] <- "A"
x[9] <- "B"
x[17] <- "C"
# extending the values forward
values <- c(NA, x[!is.na(x)])
ind <- cumsum(!is.na(x)) + 1
y <- values[ind]
# compare with the original
cbind(x, y)
x y
[1,] NA NA
[2,] NA NA
[3,] NA NA
[4,] "A" "A"
[5,] NA "A"
[6,] NA "A"
[7,] NA "A"
[8,] NA "A"
[9,] "B" "B"
[10,] NA "B"
[11,] NA "B"
[12,] NA "B"
[13,] NA "B"
[14,] NA "B"
[15,] NA "B"
[16,] NA "B"
[17,] "C" "C"
[18,] NA "C"
[19,] NA "C"
[20,] NA "C"
This could be applied directly to the last two columns of your
data frame "xx". However, it may be more natural to obtain the
vector "values" from the input data and not from their sparse
form, which is the data frame. Also, the logical vector !is.na(x)
is the same for the last two columns of your data frame, so
it may be computed only once.
Hope this helps.
Petr Savicky.
An embedded and charset-unspecified text was scrubbed... Name: not available URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20120301/1b94d204/attachment.pl>