Dear R-Users, Have a question about reading in some data and manipulating dates. I have a data set in excel which looks like this: Date PEG ETN HSP PTC 13/10/2004 41.92 64.75 29.86 9.27 14/10/2004 41.93 61.79 29.98 9.14 15/10/2004 41.69 62.7 30.09 9.04 18/10/2004 41.37 62.14 30.39 8.96 19/10/2004 41.01 61.98 29.61 9.02 20/10/2004 41.01 61.98 30.25 9 I have used read.table by saving the sheety above in tab format to read this in but am having some difficulties: - the dates above do not seem to be getting read in in date format: how can I force this ? - I actually have two, and not one data set, and would like to synchronise them by date (exclude from both dates which are not in both): is there an easy way to do this ? Thanks, Tolga
On Tue, 2007-09-11 at 19:56 +0300, Tolga Uzuner wrote:> Dear R-Users, > > Have a question about reading in some data and manipulating dates. I > have a data set in excel which looks like this: > > Date PEG ETN HSP PTC > 13/10/2004 41.92 64.75 29.86 9.27 > 14/10/2004 41.93 61.79 29.98 9.14 > 15/10/2004 41.69 62.7 30.09 9.04 > 18/10/2004 41.37 62.14 30.39 8.96 > 19/10/2004 41.01 61.98 29.61 9.02 > 20/10/2004 41.01 61.98 30.25 9 > > I have used read.table by saving the sheety above in tab format to > read this in but am > having some difficulties: > - the dates above do not seem to be getting read in in date format: > how can I force this ?For the first question (I'm not sure how to do the second): We use read.table() to read in the data from file (here I saved your data as tab delimited file 'temp.csv'. Argument as.is = TRUE is used to stop R converting character strings (i.e. the Date column here) to factors; this saves us a step later converting them back.> dat <- read.table("temp.csv", sep = "\t", as.is = TRUE, header = TRUE)Data read in OK:> datDate PEG ETN HSP PTC 1 13/10/2004 41.92 64.75 29.86 9.27 2 14/10/2004 41.93 61.79 29.98 9.14 3 15/10/2004 41.69 62.70 30.09 9.04 4 18/10/2004 41.37 62.14 30.39 8.96 5 19/10/2004 41.01 61.98 29.61 9.02 6 20/10/2004 41.01 61.98 30.25 9.00 Use as.Date() to convert the character strings to a vector of class 'Date'. The format argument tells R how the dates are formatted in the character strings you just read in. The %d, %m bits etc are place holders describing the date parts, but the "/" characters are literal - if your dates were formatted "20-10-2004", you would use format = "%d-% m-%Y". See ?strftime for how to specify 'format' if you have dates formatted in other ways (say with month name instead of number).> dat$Date <- as.Date(dat$Date, format = "%d/%m/%Y")Note that now dat$Date is now an object of class 'Date':> str(dat)'data.frame': 6 obs. of 5 variables: $ Date:Class 'Date' num [1:6] 12704 12705 12706 12709 12710 ... $ PEG : num 41.9 41.9 41.7 41.4 41.0 ... $ ETN : num 64.8 61.8 62.7 62.1 62.0 ... $ HSP : num 29.9 30.0 30.1 30.4 29.6 ... $ PTC : num 9.27 9.14 9.04 8.96 9.02 9 Which R will treat appropriately in plots for example:> plot(PEG ~ Date, data = dat, type = "l")HTH G -- %~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~% Gavin Simpson [t] +44 (0)20 7679 0522 ECRC, UCL Geography, [f] +44 (0)20 7679 0565 Pearson Building, [e] gavin.simpsonATNOSPAMucl.ac.uk Gower Street, London [w] http://www.ucl.ac.uk/~ucfagls/ UK. WC1E 6BT. [w] http://www.freshwaters.org.uk %~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%
Try using the zoo package like this:
Lines <- " Date PEG ETN HSP PTC
13/10/2004 41.92 64.75 29.86 9.27
14/10/2004 41.93 61.79 29.98 9.14
15/10/2004 41.69 62.7 30.09 9.04
18/10/2004 41.37 62.14 30.39 8.96
19/10/2004 41.01 61.98 29.61 9.02
20/10/2004 41.01 61.98 30.25 9
"
Lines2 <- " Date A B
13/10/2004 64.75 29.86
15/10/2004 62.7 30.09
19/10/2004 61.98 29.61
"
library(zoo)
# replace with a line such as:
# z <- read.zoo("myfile.dat", header = TRUE, format =
"%d/%m/%Y")
z <- read.zoo(textConnection(Lines), header = TRUE, format =
"%d/%m/%Y")
# replace with a line such as:
# z <- read.zoo("myfile2.dat", header = TRUE, format =
"%d/%m/%Y")
z2 <- read.zoo(textConnection(Lines2), header = TRUE, format =
"%d/%m/%Y")
both <- merge(z, z2) # this uses merge.zoo
plot(na.approx(both))
To learn more about zoo try:
vignette("zoo")
vignette("zoo-quickref")
and to learn about dates read the help desk in R News 4/1
by googling for CRAN News
On 9/11/07, Tolga Uzuner <tolga.uzuner at gmail.com>
wrote:> Dear R-Users,
>
> Have a question about reading in some data and manipulating dates. I
> have a data set in excel which looks like this:
>
> Date PEG ETN HSP PTC
> 13/10/2004 41.92 64.75 29.86 9.27
> 14/10/2004 41.93 61.79 29.98 9.14
> 15/10/2004 41.69 62.7 30.09 9.04
> 18/10/2004 41.37 62.14 30.39 8.96
> 19/10/2004 41.01 61.98 29.61 9.02
> 20/10/2004 41.01 61.98 30.25 9
>
> I have used read.table by saving the sheety above in tab format to
> read this in but am
> having some difficulties:
> - the dates above do not seem to be getting read in in date format:
> how can I force this ?
> - I actually have two, and not one data set, and would like to
> synchronise them by date
> (exclude from both dates which are not in both): is there an easy way
> to do this ?
>
> Thanks,
> Tolga
>
> ______________________________________________
> 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.
>