[R] Date Time in R
William Dunlap
wdunlap at tibco.com
Tue Jul 26 21:56:19 CEST 2016
Your original mail said
Example of date - 05-30-16
To change this i have used eir$date<- as.Date(eir$date, "%m-%d-%y")
but by showing us the actual code I see you reversed the m and d and
capitalized the Y in your first attempt, causing NA's since your data was
not in that format.
The big mistake was overwriting the raw data with this mistakenly processed
data so subsequent attempts at processing it failed.
I recommend never overwriting the raw data - read the data into a
data.frame called, say, 'raw' and make a new data.frame called, say,
'cleaned' with all the transformations based on the raw data. Iterate the
cleaning step until you have it right but don't alter the raw data. Use
functions like head(), str(), plot(), and summary() to see if things look
basically ok. The transform() function may help with that. E.g.,
raw <- read.table(text="date\n05-30-16\n06-01-16\n06-27-16",
header=TRUE)
str(raw)
#'data.frame': 3 obs. of 1 variable:
#$ date: Factor w/ 3 levels "05-30-16","06-01-16",..: 1 2 3
cleaned <- transform(raw, date=as.Date(date, format="%d-%m-%Y"))
head(cleaned)
# date
#1 <NA>
#2 0016-01-06
#3 <NA>
cleaned <- transform(raw, date=as.Date(date, format="%d-%m-%y"))
head(cleaned)
# date
#1 <NA>
#2 2016-01-06
#3 <NA>
cleaned <- transform(raw, date=as.Date(date, format="%m-%d-%y"))
head(cleaned)
# date
#1 2016-05-30
#2 2016-06-01
#3 2016-06-27
That last one looks ok, so move on to your next tranformation step.
Bill Dunlap
TIBCO Software
wdunlap tibco.com
On Tue, Jul 26, 2016 at 9:59 AM, Shivi Bhatia <shivipmp82 at gmail.com> wrote:
> These are some of the codes will i have
> eir$date<- as.Date(eir$date, "%d-%m-%Y")
> class(eir$date)
> eir$week<- (eir$date)
> eir$week<- weekdays(as.Date(eir$week))
> eir$week<- as.factor(eir$week)
>
> On Tue, Jul 26, 2016 at 10:24 PM, Shivi Bhatia <shivipmp82 at gmail.com>
> wrote:
>
>> Hi Bill,
>>
>> I have mentioned the transformation all on the email above.
>>
>> Regards, Shivi
>>
>> On Tue, Jul 26, 2016 at 10:19 PM, William Dunlap <wdunlap at tibco.com>
>> wrote:
>>
>>> Can you show us what transformations you tried when
>>> you saw the column called 'date' was a factor?
>>>
>>> Bill Dunlap
>>> TIBCO Software
>>> wdunlap tibco.com
>>>
>>> On Tue, Jul 26, 2016 at 9:33 AM, Shivi Bhatia <shivipmp82 at gmail.com>
>>> wrote:
>>>
>>>> Thanks Tom for the recommendation. This is now working.
>>>>
>>>> Apologies if this sounds like a juvenile but i am not very good with
>>>> dates
>>>> in R. What i initially did when i saw date as factor and then tried
>>>> converting with lot many transformation that is where it went bad.
>>>>
>>>> But thanks to all this is really what i wanted.
>>>>
>>>> On Tue, Jul 26, 2016 at 9:58 PM, Tom Wright <tom at maladmin.com> wrote:
>>>>
>>>> > So this looks correct so far.
>>>> >
>>>> >
>>>> >
>>>> > # convert strings (or factors) to a date object
>>>> >
>>>> > a1$date <- as.Date(a1$date, “%m-%d-%y”)
>>>> >
>>>> >
>>>> >
>>>> > # Note the date object is now displayed in the format %Y-%m-%d by
>>>> default
>>>> >
>>>> > # so no need for the mdy() or ymd() function
>>>> >
>>>> >
>>>> >
>>>> > # extract the weekdays as number
>>>> >
>>>> > weekdays <- wday(a1$date)
>>>> >
>>>> >
>>>> >
>>>> > # note can’t have spaces in variable names
>>>> >
>>>> > week_names <- wday(a1$date, label=TRUE)
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> > *From:* Shivi Bhatia [mailto:shivipmp82 at gmail.com]
>>>> > *Sent:* July 26, 2016 12:16 PM
>>>> > *To:* Tom Wright <tom at maladmin.com>
>>>> > *Cc:* David L Carlson <dcarlson at tamu.edu>; r-help <
>>>> r-help at r-project.org>
>>>>
>>>> >
>>>> > *Subject:* Re: [R] Date Time in R
>>>> >
>>>> >
>>>> >
>>>> > Hello Tom,
>>>> >
>>>> >
>>>> >
>>>> > Please find the details: (i have changed name from eir to a1, rest
>>>> all is
>>>> > same)
>>>> >
>>>> >
>>>> >
>>>> > str(a1$date)
>>>> >
>>>> > Factor w/ 32 levels "05-30-16","05-31-16",..: 1 1 1
>>>> >
>>>> >
>>>> >
>>>> > head(a1$date)
>>>> >
>>>> > 05-30-16 05-30-16 05-30-16 05-30-16 05-30-16 05-30-16
>>>> >
>>>> > 32 Levels: 05-30-16 05-31-16 06-01-16 06-02-16 06-03-16 06-04-16
>>>> 06-05-16
>>>> > 06-06-16 06-07-16 06-08-16
>>>> >
>>>> >
>>>> >
>>>> > Thanks again!!!
>>>> >
>>>> >
>>>> >
>>>> > On Tue, Jul 26, 2016 at 9:38 PM, Tom Wright <tom at maladmin.com> wrote:
>>>> >
>>>> > Hi again Shiva,
>>>> > I think what we need to see is the output from:
>>>> >
>>>> > str(eid$date)
>>>> >
>>>> > and perhaps
>>>> > head(eid$date)
>>>> >
>>>> > If you can send this information before doing any processing on the
>>>> date
>>>> > (i.e. before the as.Date() function) we may be able to help.
>>>> >
>>>> >
>>>> >
>>>> > -----Original Message-----
>>>> > From: R-help [mailto:r-help-bounces at r-project.org] On Behalf Of Shivi
>>>> > Bhatia
>>>> > Sent: July 26, 2016 11:46 AM
>>>> > To: David L Carlson <dcarlson at tamu.edu>
>>>> > Cc: r-help <r-help at r-project.org>
>>>> > Subject: Re: [R] Date Time in R
>>>> >
>>>> > Hi David please see the code and some reproducible data:
>>>> > eir$date<- as.Date(eir$date,format = "%m-%d-%y") then i had used the
>>>> > lubridate library to help with the dates:
>>>> > install.packages("lubridate")
>>>> > library(lubridate)
>>>> > eir$date <- mdy(eir$date)
>>>> > weekdays <- wdy(eir$date)
>>>> > week names <- wdy(eir$date, label = TRUE)
>>>> >
>>>> > This the output from the file:
>>>> >
>>>> > structure(list(date = structure(c(NA_real_, NA_real_, NA_real_),
>>>> class =
>>>> > "Date"),
>>>> >
>>>> > month = structure(c(2L, 2L, 2L), .Label = c("Jun","May"), class =
>>>> > "factor"),
>>>> >
>>>> > day = c(30L, 30L, 30L),
>>>> >
>>>> > weekday = structure(c(2L,2L, 2L), .Label = c("Fri", "Mon", "Sat",
>>>> "Sun",
>>>> > "Thu", "Tue","Wed"), class = "factor"),
>>>> >
>>>> > survey_rating = c(3L, 2L, 3L), query_status = c("Yes", "Don't
>>>> know","No"),
>>>> >
>>>> > a = c("05-30-16", "05-30-16", "05-30-16")), .Names =
>>>> c("date","month",
>>>> > "day", "weekday", "survey_rating"), row.names = c(NA, 3L), class =
>>>> > "data.frame")
>>>> >
>>>> > There are several other variables that i have removed which are not
>>>> > relevant
>>>> > in this context.
>>>> >
>>>> > On Tue, Jul 26, 2016 at 8:55 PM, David L Carlson <dcarlson at tamu.edu>
>>>> > wrote:
>>>> >
>>>> > > Show us the output, don’t just tell us what you are seeing. If the
>>>> > > dates are correct in the csv file, show us the structure of the data
>>>> > > frame you created with read.csv() and show the command(s) you used
>>>> to
>>>> > > convert the character data to date format. The solution is likely to
>>>> > > be simple if you will cut/paste the R console and not just describe
>>>> what
>>>> > > is happening.
>>>> > >
>>>> > >
>>>> > >
>>>> > > David C
>>>> > >
>>>> > >
>>>> > >
>>>> >
>>>> > > *From:* Shivi Bhatia [mailto:shivipmp82 at gmail.com]
>>>> > > *Sent:* Tuesday, July 26, 2016 10:08 AM
>>>> > > *To:* David L Carlson
>>>> > >
>>>> > > *Subject:* Re: [R] Date Time in R
>>>> >
>>>> > >
>>>> > >
>>>> > >
>>>> > > Hi David,
>>>> > >
>>>> > > This gives the results accurately. The first line shows all the
>>>> > > variable names and the rest shows all values stored for each of the
>>>> > > variable. Here date is appearing as correct.
>>>> > >
>>>> > >
>>>> > >
>>>> > > Thanks, Shivi
>>>> > >
>>>> > >
>>>> > >
>>>> > > On Tue, Jul 26, 2016 at 7:39 PM, David L Carlson <dcarlson at tamu.edu
>>>> >
>>>> > > wrote:
>>>> > >
>>>> > > What does this produce?
>>>> > >
>>>> > > > readLines("YourCSVfilename.csv", n=5)
>>>> > >
>>>> > > If the data are in Excel, the date format used in .csv files is not
>>>> > > always in the same as the format used when viewing dates in the
>>>> > > spreadsheet.
>>>> > >
>>>> > > -------------------------------------
>>>> > > David L Carlson
>>>> > > Department of Anthropology
>>>> > > Texas A&M University
>>>> > > College Station, TX 77840-4352
>>>> > >
>>>> > >
>>>> > >
>>>> > > -----Original Message-----
>>>> > > From: R-help [mailto:r-help-bounces at r-project.org] On Behalf Of
>>>> Shivi
>>>> > > Bhatia
>>>> > > Sent: Tuesday, July 26, 2016 7:42 AM
>>>> > > To: Marc Schwartz
>>>> > > Cc: R-help
>>>> > > Subject: Re: [R] Date Time in R
>>>> > >
>>>> > > Thanks Marc for the help. this really helps.
>>>> > > I think there is some issue with the data saved in csv format for
>>>> this
>>>> > > variable as when i checked:
>>>> > > str(eir$date)- this results in :-
>>>> > > Date[1:5327], format: NA NA NA NA NA.
>>>> > >
>>>> > > Thanks again.
>>>> > >
>>>> > > On Tue, Jul 26, 2016 at 5:58 PM, Marc Schwartz <
>>>> marc_schwartz at me.com>
>>>> > > wrote:
>>>> > >
>>>> > > > Hi,
>>>> > > >
>>>> > > > That eir$date might be a factor is irrelevant. There is an
>>>> as.Date()
>>>> > > > method for factors, which does the factor to character coercion
>>>> > > internally
>>>> > > > and then calls as.Date.character() on the result.
>>>> > > >
>>>> > > > Using the example data below:
>>>> > > >
>>>> > > > eir <- data.frame(date = c("05-30-16", "05-30-16", "05-30-16",
>>>> > > > "05-30-16", "05-30-16", "05-30-16"))
>>>> > > >
>>>> > > > > str(eir)
>>>> > > > 'data.frame': 6 obs. of 1 variable:
>>>> > > > $ date: Factor w/ 1 level "05-30-16": 1 1 1 1 1 1
>>>> > > >
>>>> > > > > eir
>>>> > > > date
>>>> > > > 1 05-30-16
>>>> > > > 2 05-30-16
>>>> > > > 3 05-30-16
>>>> > > > 4 05-30-16
>>>> > > > 5 05-30-16
>>>> > > > 6 05-30-16
>>>> > > >
>>>> > > > eir$date <- as.Date(eir$date, format = "%m-%d-%y")
>>>> > > >
>>>> > > > > str(eir)
>>>> > > > 'data.frame': 6 obs. of 1 variable:
>>>> > > > $ date: Date, format: "2016-05-30" ...
>>>> > > >
>>>> > > > > eir
>>>> > > > date
>>>> > > > 1 2016-05-30
>>>> > > > 2 2016-05-30
>>>> > > > 3 2016-05-30
>>>> > > > 4 2016-05-30
>>>> > > > 5 2016-05-30
>>>> > > > 6 2016-05-30
>>>> > > >
>>>> > > > eir$days <- weekdays(eir$date)
>>>> > > >
>>>> > > > > str(eir)
>>>> > > > 'data.frame': 6 obs. of 2 variables:
>>>> > > > $ date: Date, format: "2016-05-30" ...
>>>> > > > $ days: chr "Monday" "Monday" "Monday" "Monday" ...
>>>> > > >
>>>> > > > > eir
>>>> > > > date days
>>>> > > > 1 2016-05-30 Monday
>>>> > > > 2 2016-05-30 Monday
>>>> > > > 3 2016-05-30 Monday
>>>> > > > 4 2016-05-30 Monday
>>>> > > > 5 2016-05-30 Monday
>>>> > > > 6 2016-05-30 Monday
>>>> > > >
>>>> > > >
>>>> > > > I would check to be sure that you do not have any typos in your
>>>> code.
>>>> > > >
>>>> > > > Regards,
>>>> > > >
>>>> > > > Marc Schwartz
>>>> > > >
>>>> > > >
>>>> > > > > On Jul 26, 2016, at 6:58 AM, Shivi Bhatia <shivipmp82 at gmail.com
>>>> >
>>>> > > wrote:
>>>> > > > >
>>>> > > > > Hello Again,
>>>> > > > >
>>>> > > > > While i tried your solution as you suggested above it seems to
>>>> be
>>>> > > > working.
>>>> > > > > Here is the output
>>>> > > > > temp<- dput(head(eir$date))
>>>> > > > > c("05-30-16", "05-30-16", "05-30-16", "05-30-16", "05-30-16",
>>>> > > "05-30-16")
>>>> > > > > however it still shows class(eir$date) as character and hence i
>>>> > > > > cannot
>>>> > > > find
>>>> > > > > weekdays from this variable.
>>>> > > > >
>>>> > > > > Sorry but i still dont understand in totality how R reads dates
>>>> > > > > even
>>>> > > > though
>>>> > > > > have tried enough.
>>>> > > > >
>>>> > > > > Regards, Shivi
>>>> > > > >
>>>> > > > >
>>>> > > > > On Tue, Jul 26, 2016 at 5:12 PM, Shivi Bhatia
>>>> > > > > <shivipmp82 at gmail.com>
>>>> > > > wrote:
>>>> > > > >
>>>> > > > >> Thanks Duncan for the quick response. I will check again as you
>>>> > > > suggested.
>>>> > > > >> If that doesn't work i will share a reproducible example.
>>>> > > > >>
>>>> > > > >> Thanks again!!!!
>>>> > > > >>
>>>> > > > >> On Tue, Jul 26, 2016 at 4:43 PM, Duncan Murdoch <
>>>> > > > murdoch.duncan at gmail.com>
>>>> > > > >> wrote:
>>>> > > > >>
>>>> > > > >>> On 26/07/2016 7:05 AM, Shivi Bhatia wrote:
>>>> > > > >>>
>>>> > > > >>>> Hi Team,
>>>> > > > >>>>
>>>> > > > >>>> This scenario may have come across a number of times however
>>>> i
>>>> > > checked
>>>> > > > >>>> nabble & SO and couldn't find a solution hence request
>>>> assistance.
>>>> > > > >>>>
>>>> > > > >>>> I have a date variable in my data-set eir. The class of this
>>>> > > > >>>> var was character while i had read the file in r studio.
>>>> > > > >>>> Example of date -
>>>> > > > >>>> 05-30-16
>>>> > > > >>>>
>>>> > > > >>>> To change this i have used eir$date<- as.Date(eir$date,
>>>> > > > >>>> "%m-%d-%y").
>>>> > > > This
>>>> > > > >>>> converts it to a date variable. However when i check few obs
>>>> > > > >>>> with head(eir$date) all the results are <NA>.
>>>> > > > >>>>
>>>> > > > >>>
>>>> > > > >>> I think you don't have character data like that, because I see
>>>> > > > >>>
>>>> > > > >>>> as.Date("05-30-16", "%m-%d-%y")
>>>> > > > >>> [1] "2016-05-30"
>>>> > > > >>>
>>>> > > > >>> I'd guess eir$date is really a factor, because character data
>>>> is
>>>> > > > >>> frequently changed to factor automatically. If that's the
>>>> case,
>>>> > > > >>> this should work for the conversion:
>>>> > > > >>>
>>>> > > > >>> as.Date(as.character(eir$date), "%m-%d-%y")
>>>> > > > >>>
>>>> > > > >>> If that doesn't work, you'll need to post something
>>>> reproducible.
>>>> > > > >>>
>>>> > > > >>> Duncan Murdoch
>>>> > > > >>>
>>>> > > > >>> I also need to create weekdays from this date variable but
>>>> until
>>>> > > > >>> i
>>>> > > get
>>>> > > > >>>> this
>>>> > > > >>>> resolved i cant find a weekday. For weekday i have used:
>>>> > > > >>>> eir$week<- (eir$date)
>>>> > > > >>>> eir$week<- weekdays(as.Date(eir$week))
>>>> > > > >>>> class(eir$week)
>>>> > > > >>>> eir$week<- as.factor(eir$week)
>>>> > > > >>>> head(eir$week)
>>>> > > > >>>>
>>>> > > > >>>> Head of this eir$week results again as expected in <NA> but
>>>> > > > >>>> shows
>>>> > > > Levels:
>>>> > > > >>>> Friday Monday Saturday Sunday Thursday Tuesday Wednesday
>>>> > > > >>>>
>>>> > > > >>>> Not sure what i should do here. Kindly suggest.
>>>> > > >
>>>> > > >
>>>> > >
>>>> > > [[alternative HTML version deleted]]
>>>> > >
>>>> > > ______________________________________________
>>>> > > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>>>> > > https://stat.ethz.ch/mailman/listinfo/r-help
>>>> >
>>>> > > <
>>>> https://urldefense.proofpoint.com/v2/url?u=https-3A__stat.ethz.ch_mai
>>>> > >
>>>> lman_listinfo_r-2Dhelp&d=CwMFaQ&c=ODFT-G5SujMiGrKuoJJjVg&r=veMGHMCNZSh
>>>> > >
>>>> ld-KX-bIj4jRE_tP9ojUvB_Lqp0ieSdk&m=s9RjgM0-LqObg32B_ODUoHMjaBJSYFn0ccx
>>>> > > orF0VRaQ&s=-2AvIVGKBvHIg4b3KBUCarOh98Mq95XbBM6rQR5o_Qw&e=>
>>>> > > PLEASE do read the posting guide
>>>> > > http://www.R-project.org/posting-guide.html
>>>> > > <
>>>> https://urldefense.proofpoint.com/v2/url?u=http-3A__www.R-2Dproject.o
>>>> > >
>>>> rg_posting-2Dguide.html&d=CwMFaQ&c=ODFT-G5SujMiGrKuoJJjVg&r=veMGHMCNZS
>>>> > >
>>>> hld-KX-bIj4jRE_tP9ojUvB_Lqp0ieSdk&m=s9RjgM0-LqObg32B_ODUoHMjaBJSYFn0cc
>>>> > > xorF0VRaQ&s=Ldb33UyU9KMUXEsxxzwItyCFjZInukICij2jo66xaKg&e=>
>>>> >
>>>> > > and provide commented, minimal, self-contained, reproducible code.
>>>> > >
>>>> > >
>>>> > >
>>>> >
>>>> > [[alternative HTML version deleted]]
>>>> >
>>>> > ______________________________________________
>>>> > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>>>> > 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.
>>>> >
>>>> >
>>>> >
>>>>
>>>> [[alternative HTML version deleted]]
>>>>
>>>> ______________________________________________
>>>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>>>> 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.
>>>>
>>>
>>>
>>
>
[[alternative HTML version deleted]]
More information about the R-help
mailing list