Re: [R] Can R fill in missing values?

From: Gabor Grothendieck <ggrothendieck_at_gmail.com>
Date: Thu, 24 Jul 2008 00:08:33 -0400

This can be done readily with zoo. See zoo's 3 vignettes and its help files for commands used below.

# setwd("...directory where we have write permission...") # can alternately manually download these two files download.file("https://stat.ethz.ch/pipermail/r-help/attachments/20080723/aa0e5d29/attachment.txt", "Testdate.txt", method = "wget")
download.file("https://stat.ethz.ch/pipermail/r-help/attachments/20080723/aa0e5d29/attachment-0001.txt", "Testinput.txt", method = "wget")

fmt <- "%m/%d/%y"

dd <- read.csv("Testdate.txt")
dd$Date <- as.Date(dd$Date, fmt)

library(zoo)
z <- read.zoo("Testinput.txt", header = TRUE, format = fmt) z2 <- merge(z, zoo(,dd$Date))
na.locf(z2, fromLast = TRUE)

# alternately if its only needed to fill in the gaps in z
# then we can eliminate Testdate.txt and calculate z2
# (call it z2a this time) directly from z as:

rng <- range(time(z))
z2a <- merge(z, zoo(, seq(rng[1], rng[2], "day"))) # continue as previously

2008/7/23 Jia Ying Mei <jiamei_at_princeton.edu>:
> Hi,
>
> I know this can be done in Stata (which is quite messy) but I wanted to know
> if it can be done in R. So lets say I have a merged data set (I used the
> merge function by date for the attached two files), where all the missing
> values are filled with NAs (which is what the all.x=TRUE does).
>
> Is there any way to replace those NAs with the value of the latest row that
> contains a value?
>
> For example:
>
>> Date<-read.table("Desktop/R/Testdate.txt", head=T, sep="\t")
>> Data<-read.table("Desktop/R/Testinput.txt", head=T, sep="\t")
>> Merged<-merge(Date, Data, all.x=TRUE)
>> Merged
> Date France Germany
> 1 3/10/07 2 4
> 2 3/11/07 NA NA
> 3 3/12/07 NA NA
> 4 3/13/07 NA NA
> 5 3/14/07 NA NA
> 6 3/15/07 1 2
>
> Given this Merged data, is there a way to replace every NA value from 3/11
> to 3/14 with that of 3/15? But then say there are multiple intervals with
> NAs that I want to fill with the last given value?
>
> Technically, I know I could just manually edit the data, but the real files
> I need to work with has thousands of rows, so I was wondering if there was a
> way to do this (probably a loop?). Thanks a bunch.
>
> Jia Ying Mei
>
> Date
> 3/15/07
> 3/14/07
> 3/13/07
> 3/12/07
> 3/11/07
> 3/10/07
>
> Date France Germany
> 3/15/07 1 2
> 3/10/07 2 4
>
> ______________________________________________
> R-help_at_r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
>



R-help_at_r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code. Received on Thu 24 Jul 2008 - 04:15:13 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Thu 24 Jul 2008 - 06:32:02 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.

list of date sections of archive