Proper calendar dates in R are great for plotting and calculating. However for the non-wonks among us, they can be very frustrating. I have recently discussed the pains that people in my lab have had with dates in R. Especially the frustration of bringing date data into R from Excel, which we have to do a lot. Please find below a simple analgesic for R date importation that I discovered over the last 1.5 days (Learning new stuff in R is calculated in 1/2 days). The function dates() gives the simplest way to get calendar dates into R from Excel that I can find. But straight importation of Excel dates, via a csv or txt file, can be a a huge pain (I'll give details for anyone who cares to know). My pain killer is: Consider that you have Excel columns in month, day, year format. Note that R hates date data that does not lead with the year. a. Load the chron library by typing library(chron) in the console. You know that you need this library from information revealed by performing the query, ?dates()" in the Console window. This gives the R documentation help file for this and related time, date functions. In the upper left of the documentation, one sees "dates(chron)". This tells you that you need the library chron. b. Change the format "dates" in Excel to format "general", which gives 5 digit Julian dates. Import the csv file (I use read.csv() with the Julian dates and other data of interest. c. Now, change the Julian dates that came in with the csv file into calendar dates with the dates() function. Below is my code for performing this activity, concerning an R data file called ss, ss holds the Julian dates, illustrated below from the column MPdate,>ss$MPdate[1:5][1] 34252 34425 34547 34759 34773 The dates() function makes calendar dates from Julian dates,>dmp<-dates(ss$MPdate,origin=c(month = 1, day = 1, year = 1900))> dmp[1:5][1] 10/12/93 04/03/94 08/03/94 03/03/95 03/17/95 I would appreciate the comments of more sophisticated programmers who can suggest streamlining or shortcutting this operation. regards, Don -- View this message in context: http://www.nabble.com/dates%28%29-is-a-great-date-function-in-R-tf4105322.html#a11675205 Sent from the R help mailing list archive at Nabble.com.
See the Other Applications section of the R News 4/1 help desk article on dates. On 7/18/07, Mr Natural <drstrong at ucdavis.edu> wrote:> > Proper calendar dates in R are great for plotting and calculating. > However for the non-wonks among us, they can be very frustrating. > I have recently discussed the pains that people in my lab have had > with dates in R. Especially the frustration of bringing date data into R > from Excel, which we have to do a lot. > > Please find below a simple analgesic for R date importation that I > discovered > over the last 1.5 days (Learning new stuff in R is calculated in 1/2 days). > > The function dates() gives the simplest way to get calendar dates into > R from Excel that I can find. > But straight importation of Excel dates, via a csv or txt file, can be a a > huge pain (I'll give details for anyone who cares to know). > > My pain killer is: > Consider that you have Excel columns in month, day, year format. Note that R > hates date data that does not lead with the year. > > a. Load the chron library by typing library(chron) in the console. > You know that you need this library from information revealed by > performing the query, > ?dates()" in the Console window. This gives the R documentation > help file for this and related time, date functions. In the upper left > of the documentation, one sees "dates(chron)". This tells you that you > need the library chron. > > b. Change the format "dates" in Excel to format "general", which gives > 5 digit Julian dates. Import the csv file (I use read.csv() with the > Julian dates and other data of interest. > > c. Now, change the Julian dates that came in with the csv file into > calendar dates with the dates() function. Below is my code for performing > this activity, concerning an R data file called ss, > > ss holds the Julian dates, illustrated below from the column MPdate, > > >ss$MPdate[1:5] > [1] 34252 34425 34547 34759 34773 > > The dates() function makes calendar dates from Julian dates, > > >dmp<-dates(ss$MPdate,origin=c(month = 1, day = 1, year = 1900)) > > > dmp[1:5] > [1] 10/12/93 04/03/94 08/03/94 03/03/95 03/17/95 > > I would appreciate the comments of more sophisticated programmers who > can suggest streamlining or shortcutting this operation. > > regards, Don > > > > > -- > View this message in context: http://www.nabble.com/dates%28%29-is-a-great-date-function-in-R-tf4105322.html#a11675205 > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > R-help at stat.math.ethz.ch 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. >
On Wed, 2007-07-18 at 12:14 -0700, Mr Natural wrote:> Proper calendar dates in R are great for plotting and calculating. > However for the non-wonks among us, they can be very frustrating. > I have recently discussed the pains that people in my lab have had > with dates in R. Especially the frustration of bringing date data into R > from Excel, which we have to do a lot.I've always found the following reasonably intuitive: Given the csv file that I've pasted in below, the following reads the csv file in, formats the dates and class Date and then draws a plot. I have dates in DD/MM/YYYY format so year is not first - thus attesting to R not hating dates in this format ;-) ## read in csv data ## as.is = TRUE stops characters being converted to factors ## thus saving us an extra step to convert them back dat <- read.csv("date_data.csv", as.is = TRUE) ## we convert to class Date ## format tells R how the dates are formatted in our character strings ## see ?strftime for the meaning and available codes dat$Date <- as.Date(dat$Date, format = "%d/%m/%Y") ## check this worked ok str(dat$Date) dat$Date ## see nicely formatted dates and not a drop of R-related hatred ## but just about the most boring graph I could come up with plot(Data ~ Date, dat, type = "l") And you can keep your Excel file formatted as dates as well - bonus! Oh, and before you get "Martin'd", it is the chron *package*! HTH G CSV file I used, generated in OpenOffice.org, but I presume it stores Dates in the same way as Excel?: "Data","Date" 1,01/01/2007 2,02/01/2007 3,03/01/2007 4,04/01/2007 5,05/01/2007 6,06/01/2007 7,07/01/2007 8,08/01/2007 9,09/01/2007 10,10/01/2007 11,11/01/2007 10,12/01/2007 9,13/01/2007 8,14/01/2007 7,15/01/2007 6,16/01/2007 5,17/01/2007 4,18/01/2007 3,19/01/2007 2,20/01/2007 1,21/01/2007 1,22/01/2007 2,23/01/2007 3,24/01/2007> Please find below a simple analgesic for R date importation that I > discovered > over the last 1.5 days (Learning new stuff in R is calculated in 1/2 days). > > The function dates() gives the simplest way to get calendar dates into > R from Excel that I can find. > But straight importation of Excel dates, via a csv or txt file, can be a a > huge pain (I'll give details for anyone who cares to know). > > My pain killer is: > Consider that you have Excel columns in month, day, year format. Note that R > hates date data that does not lead with the year. > > a. Load the chron library by typing library(chron) in the console. > You know that you need this library from information revealed by > performing the query, > ?dates()" in the Console window. This gives the R documentation > help file for this and related time, date functions. In the upper left > of the documentation, one sees "dates(chron)". This tells you that you > need the library chron. > > b. Change the format "dates" in Excel to format "general", which gives > 5 digit Julian dates. Import the csv file (I use read.csv() with the > Julian dates and other data of interest. > > c. Now, change the Julian dates that came in with the csv file into > calendar dates with the dates() function. Below is my code for performing > this activity, concerning an R data file called ss, > > ss holds the Julian dates, illustrated below from the column MPdate, > > >ss$MPdate[1:5] > [1] 34252 34425 34547 34759 34773 > > The dates() function makes calendar dates from Julian dates, > > >dmp<-dates(ss$MPdate,origin=c(month = 1, day = 1, year = 1900)) > > > dmp[1:5] > [1] 10/12/93 04/03/94 08/03/94 03/03/95 03/17/95 > > I would appreciate the comments of more sophisticated programmers who > can suggest streamlining or shortcutting this operation. > > regards, Don > > > >-- %~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~% 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 %~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%~%
Mr Natural <drstrong at ucdavis.edu> writes: Just save the spreadsheet as a csv file and use tisFromCsv() in the fame package. One of the arguments tisFromCsv() takes is a dateFormat, so you can tell it what format the date column is in. You can also tell it the name of the date column if it isn't some variation of DATE, Date, or date. tisFromCsv() looks at the dates coming in and automatically figures out what frequency the data are (quarterly, monthly, weekly, daily, etc.) and creates a univariate or multivariate (if the spreadsheet has more than one data column) 'tis' (Time Indexed Series) object. Jeff> Proper calendar dates in R are great for plotting and calculating. > However for the non-wonks among us, they can be very frustrating. > I have recently discussed the pains that people in my lab have had > with dates in R. Especially the frustration of bringing date data into R > from Excel, which we have to do a lot. > > Please find below a simple analgesic for R date importation that I > discovered > over the last 1.5 days (Learning new stuff in R is calculated in 1/2 days). > > The function dates() gives the simplest way to get calendar dates into > R from Excel that I can find. > But straight importation of Excel dates, via a csv or txt file, can be a a > huge pain (I'll give details for anyone who cares to know). > > My pain killer is: > Consider that you have Excel columns in month, day, year format. Note that R > hates date data that does not lead with the year. > > a. Load the chron library by typing library(chron) in the console. > You know that you need this library from information revealed by > performing the query, > ?dates()" in the Console window. This gives the R documentation > help file for this and related time, date functions. In the upper left > of the documentation, one sees "dates(chron)". This tells you that you > need the library chron. > > b. Change the format "dates" in Excel to format "general", which gives > 5 digit Julian dates. Import the csv file (I use read.csv() with the > Julian dates and other data of interest. > > c. Now, change the Julian dates that came in with the csv file into > calendar dates with the dates() function. Below is my code for performing > this activity, concerning an R data file called ss, > > ss holds the Julian dates, illustrated below from the column MPdate, > > >ss$MPdate[1:5] > [1] 34252 34425 34547 34759 34773 > > The dates() function makes calendar dates from Julian dates, > > >dmp<-dates(ss$MPdate,origin=c(month = 1, day = 1, year = 1900)) > > > dmp[1:5] > [1] 10/12/93 04/03/94 08/03/94 03/03/95 03/17/95 > > I would appreciate the comments of more sophisticated programmers who > can suggest streamlining or shortcutting this operation. > > regards, Don > > > > > -- > View this message in context: http://www.nabble.com/dates%28%29-is-a-great-date-function-in-R-tf4105322.html#a11675205 > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > R-help at stat.math.ethz.ch 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. >-- Jeff