[R] Problems plotting date and time column from excel using R

David Scott d.scott at auckland.ac.nz
Tue May 11 22:43:11 CEST 2010

David Winsemius wrote:
> On May 11, 2010, at 9:00 AM, Terry Bassett wrote:
>> I am using R to read from an excel(csv) file. Within the excel file  
>> is a column with the date set that looks likes this:
>>  53:40.2
>> and in the Insert function box it looks likes this:
>> 9/21/2006  4:53:40 PM
> This is really an Excel question, rather than an R one. You need to  
> format the data in Excel so that it gets output to the CSV properly.
>> I tired separating the time and date using the function below and  
>> then plotting again which fail to read properly
>> =TEXT(B2,"hh:mm:ss")   and  =TEXT(B9,"mm/dd/yyyy")
> As I said, it's an Excel question.
>> The R script am using is
>> plot.time_vs_id<- function(start,end){
>> setwd("C:/Documents and Settings/terry/Desktop/r_stuff")
>> x=read.table("MAV.csv",header=T, sep=",")
>> plot(x$time[start:end],x$packet_id[start:end],ylab="Packet ID",  
>> xlab="Time", type="p")
>> }
>> and
>> plot.hov_Points <- function(start,end){
>> setwd("C:/Documents and Settings/terry/Desktop/r_stuff")
>> library(rgl)
>> t=read.table("MAV.csv",header=T, sep=",")
>> plot3d(t$time[start:end],t$packet_length[start:end],t 
>> $date[start:end], main="potential", xlab="time",  
>> ylab="length",zlab="date", type="p")
>> }
>> How can i get R to properly display the time in my graphs
>> Thanks
>> Terry Bassett
> David Winsemius, MD
> West Hartford, CT

Excel has a mind of its own in many things, particularly dates.
You need to look at exactly what is coming in to R as x.

Do str(x) and let us know what it is then you might get some useful advice.

As a general rule these days, when using Windows, I prefer to avoid the 
.xls or .xlsx to .csv route to getting data into R from Excel and use 
either RODBC or xlsReadWrite. Getting Excel to write a .csv file is just 
adding some random noise to the process.

David Scott

David Scott	Department of Statistics
		The University of Auckland, PB 92019
		Auckland 1142,    NEW ZEALAND
Phone: +64 9 923 5055, or +64 9 373 7599 ext 85055
Email:	d.scott at auckland.ac.nz,  Fax: +64 9 373 7018

Director of Consulting, Department of Statistics

More information about the R-help mailing list