R help - Jun 2007 - Preserving dates in Excel.

Hi,
	Quick question: Say I have a date variable in a data frame or
matrix, and I'd like to preserve the date format when using write.table.
However, when I export the data, I get the generic number underlying the
date, not the date per se, and a number such as 11323, 11324, etc are
not meaningful in Excel. Is there any way I can preserve the format of a
date on writing into a text-file?

TIA and best,
-Tir

Patnaik, Tirthankar wrote:
> Hi,
> 	Quick question: Say I have a date variable in a data frame or
> matrix, and I'd like to preserve the date format when using
write.table.
> However, when I export the data, I get the generic number underlying the
> date, not the date per se, and a number such as 11323, 11324, etc are
> not meaningful in Excel. Is there any way I can preserve the format of a
> date on writing into a text-file?
>
>   
Er, what is exactly the problem here?
> d <- data.frame(date=as.Date("2007-6-1")+1:5, x=rnorm(5))
> d
        date             x
1 2007-06-02  0.7987635130
2 2007-06-03 -0.7381623316
3 2007-06-04 -1.3626708691
4 2007-06-05  0.0007668082
5 2007-06-06  0.6719088533
> write.table(d)
"date" "x"
"1" 2007-06-02 0.798763513018864
"2" 2007-06-03 -0.738162331606612
"3" 2007-06-04 -1.36267086906438
"4" 2007-06-05 0.000766808196322155
"5" 2007-06-06 0.671908853312511
> write.csv(d)
"","date","x"
"1",2007-06-02,0.798763513018864
"2",2007-06-03,-0.738162331606612
"3",2007-06-04,-1.36267086906438
"4",2007-06-05,0.000766808196322155
"5",2007-06-06,0.671908853312511


-- 
   O__  ---- Peter Dalgaard             ?ster Farimagsgade 5, Entr.B
  c/ /'_ --- Dept. of Biostatistics     PO Box 2099, 1014 Cph. K
 (*) \(*) -- University of Copenhagen   Denmark          Ph:  (+45) 35327918
~~~~~~~~~~ - (p.dalgaard at biostat.ku.dk)                  FAX: (+45) 35327907

On Thu, 14 Jun 2007, Patnaik, Tirthankar  wrote:
> Hi,
> 	Quick question: Say I have a date variable in a data frame or
> matrix,
Which? It matters: see the help page.
> and I'd like to preserve the date format when using write.table.
> However, when I export the data, I get the generic number underlying the
> date, not the date per se, and a number such as 11323, 11324, etc are
> not meaningful in Excel. Is there any way I can preserve the format of a
> date on writing into a text-file?
Yes, see the help page and use a data frame.  E.g.
> write.table(data.frame(x=.leap.seconds[1:3]), "")
"x"
"1" 1972-07-01 01:00:00
"2" 1973-01-01 00:00:00
"3" 1974-01-01 00:00:00


-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595

Hi Peter, Prof. Ripley,

	You're right--I just realized I was exporting a matrix, after converting
the data-frame using data.matrix, where the date characteristics are lost.

 
Basically a function I was running converted the dataframe to a matrix
first--something I'd forgotten.
> z1 <- data.matrix(head(Banks.Index.daily[,1:5]))
Warning message:
class information lost from one or more columns in:
data.matrix(head(Banks.Index.daily[, 1:5])) 
> z1
            Date   SBI.BO HDFC.BO KTKM.BO YESB.BO
2001-01-01 11323 102944.3 64134.8  3576.4      NA
2001-01-02 11324 108812.5 64390.9  3606.2      NA
2001-01-03 11325 114101.8 65909.5  3528.2      NA
2001-01-04 11326 112180.8 65266.3  3691.2      NA
2001-01-05 11327 113365.0 69286.3  3700.3      NA
2001-01-08 11330 112180.8 70292.8  3682.0      NA
> write.table(z1)
"Date" "SBI.BO" "HDFC.BO" "KTKM.BO"
"YESB.BO"
"2001-01-01" 11323 102944.3 64134.8 3576.4 NA
"2001-01-02" 11324 108812.5 64390.9 3606.2 NA
"2001-01-03" 11325 114101.8 65909.5 3528.2 NA
"2001-01-04" 11326 112180.8 65266.3 3691.2 NA
"2001-01-05" 11327 113365 69286.3 3700.3 NA
"2001-01-08" 11330 112180.8 70292.8 3682 NA
> 
Apologies for the inconvenience.

Best,
-Tir

> -----Original Message-----
> From: Peter Dalgaard [mailto:P.Dalgaard at biostat.ku.dk] 
> Sent: Thursday, June 14, 2007 6:07 PM
> To: Patnaik, Tirthankar [GWM-CIR]
> Cc: r-help at stat.math.ethz.ch
> Subject: Re: [R] Preserving dates in Excel.
> 
> Patnaik, Tirthankar wrote:
> > Hi,
> > 	Quick question: Say I have a date variable in a data 
> frame or matrix, 
> > and I'd like to preserve the date format when using write.table.
> > However, when I export the data, I get the generic number 
> underlying 
> > the date, not the date per se, and a number such as 11323, 
> 11324, etc 
> > are not meaningful in Excel. Is there any way I can preserve the 
> > format of a date on writing into a text-file?
> >
> >   
> Er, what is exactly the problem here?
> 
> > d <- data.frame(date=as.Date("2007-6-1")+1:5, x=rnorm(5))
d
>         date             x
> 1 2007-06-02  0.7987635130
> 2 2007-06-03 -0.7381623316
> 3 2007-06-04 -1.3626708691
> 4 2007-06-05  0.0007668082
> 5 2007-06-06  0.6719088533
> > write.table(d)
> "date" "x"
> "1" 2007-06-02 0.798763513018864
> "2" 2007-06-03 -0.738162331606612
> "3" 2007-06-04 -1.36267086906438
> "4" 2007-06-05 0.000766808196322155
> "5" 2007-06-06 0.671908853312511
> > write.csv(d)
> "","date","x"
> "1",2007-06-02,0.798763513018864
> "2",2007-06-03,-0.738162331606612
> "3",2007-06-04,-1.36267086906438
> "4",2007-06-05,0.000766808196322155
> "5",2007-06-06,0.671908853312511
> 
> 
> -- 
>    O__  ---- Peter Dalgaard             ?ster Farimagsgade 5, Entr.B
>   c/ /'_ --- Dept. of Biostatistics     PO Box 2099, 1014 Cph. K
>  (*) \(*) -- University of Copenhagen   Denmark          Ph:  
> (+45) 35327918
> ~~~~~~~~~~ - (p.dalgaard at biostat.ku.dk)                  FAX: 
> (+45) 35327907
> 
> 
>