[R] difficulty in Formatting time series data
David Winsemius
dwinsemius at comcast.net
Sun Apr 22 20:30:12 CEST 2012
On Apr 22, 2012, at 2:12 PM, Raghuraman Ramachandran wrote:
> I tried downloading using as.is and have also provided the dput
> below. The
> date for example is 20/4/2012 and wday gives 2 instead of 6? Thanks
> for all
> your help.
>
>> str(test1)
> 'data.frame': 1825 obs. of 7 variables:
> $ Date : chr "20/04/2012" "19/04/2012" "18/04/2012"
> "17/04/2012" ...
> $ Open : num 2.33 2.35 2.35 2.34 2.32 2.34 2.3 2.28 2.29 2.28 ...
> $ High : num 2.34 2.36 2.38 2.34 2.35 2.36 2.32 2.29 2.33 2.3 ...
> $ Low : num 2.31 2.33 2.34 2.3 2.31 2.32 2.29 2.25 2.28 2.28 ...
> $ Close : num 2.31 2.35 2.36 2.33 2.31 2.32 2.31 2.26 2.3 2.29 ...
> $ Volume : int 5366000 5382000 9606000 9596000 5941000 10332000
> 7000000
> 9636000 6019000 3279000 ...
> $ Adj.Close: num 2.31 2.35 2.36 2.33 2.31 2.32 2.31 2.26 2.3 2.29 ...
>> wday(test$Date[1])
> [1] 2
You are skipping a couple of essential steps. The wday() function is
unable to infer that you are using a non-standard date format. (I
don't think it would even work if you were using YYYY-MM-DD.) Read up
on :
?DateTimeClasses
?as.Date
?strptime
--
David.
>> wday(test1$Date[1])
> [1] 2
>> test1%Date[1]
> Error: unexpected input in "test1%Date[1]"
>> test1$Date[1]
> [1] "20/04/2012"
>>
>> dput(test1)
> structure(list(Date = c("20/04/2012", "19/04/2012", "18/04/2012",
> "17/04/2012", "16/04/2012", "13/04/2012", "12/04/2012", "11/04/2012",
> "10/04/2012", "09/04/2012", "05/04/2012", "04/04/2012", "03/04/2012",
> "02/04/2012", "30/03/2012", "29/03/2012", "28/03/2012", "27/03/2012",
> "26/03/2012", "23/03/2012", "21/03/2012", "20/03/2012", "19/03/2012",
> "16/03/2012", "15/03/2012", "14/03/2012", "13/03/2012", "12/03/2012",
> "09/03/2012", "08/03/2012", "07/03/2012", "06/03/2012", "05/03/2012",
> "02/03/2012", "01/03/2012", "29/02/2012", "28/02/2012", "27/02/2012",
> "24/02/2012", "23/02/2012", "22/02/2012", "21/02/2012", "20/02/2012",
> "17/02/2012", "16/02/2012", "15/02/2012", "14/02/2012", "13/02/2012",
> "10/02/2012", "09/02/2012", "08/02/2012", "07/02/2012", "06/02/2012",
> "03/02/2012", "02/02/2012", "01/02/2012", "31/01/2012", "30/01/2012",
> "27/01/2012", "26/01/2012", "25/01/2012", "20/01/2012", "19/01/2012",
> "18/01/2012", "17/01/2012", "16/01/2012", "13/01/2012", "12/01/2012",
> "11/01/2012", "10/01/2012", "09/01/2012", "06/01/2012", "05/01/2012",
> "04/01/2012", "03/01/2012", "30/12/2011", "29/12/2011", "28/12/2011",
> "27/12/2011", "23/12/2011", "22/12/2011", "21/12/2011", "20/12/2011",
> "19/12/2011", "16/12/2011", "15/12/2011", "14/12/2011", "13/12/2011",
> "12/12/2011", "09/12/2011", "08/12/2011", "07/12/2011", "06/12/2011",
> "05/12/2011", "02/12/2011", "01/12/2011", "30/11/2011", "29/11/2011",
> "28/11/2011", "25/11/2011", "24/11/2011", "23/11/2011", "22/11/2011",
> "21/11/2011", "18/11/2011", "17/11/2011", "16/11/2011", "15/11/2011",
> "14/11/2011", "11/11/2011", "10/11/2011", "09/11/2011", "08/11/2011",
> "04/11/2011", "03/11/2011", "02/11/2011", "01/11/2011", "31/10/2011",
> "28/10/2011", "27/10/2011", "25/10/2011", "24/10/2011", "21/10/2011",
> "20/10/2011", "19/10/2011", "18/10/2011", "17/10/2011", "14/10/2011",
> "13/10/2011", "12/10/2011", "11/10/2011", "10/10/2011", "07/10/2011",
> "06/10/2011", "05/10/2011", "04/10/2011", "03/10/2011", "30/09/2011",
> "29/09/2011", "28/09/2011", "27/09/2011", "26/09/2011", "23/09/2011",
> "22/09/2011", "21/09/2011", "20/09/2011", "19/09/2011", "16/09/2011",
> "15/09/2011", "14/09/2011", "13/09/2011", "12/09/2011", "09/09/2011",
> "08/09/2011", "07/09/2011", "06/09/2011", "05/09/2011", "02/09/2011",
> "01/09/2011", "31/08/2011", "29/08/2011", "26/08/2011", "25/08/2011",
> "24/08/2011", "23/08/2011", "22/08/2011", "19/08/2011", "18/08/2011",
> "17/08/2011", "16/08/2011", "15/08/2011", "12/08/2011", "11/08/2011",
> "10/08/2011", "08/08/2011", "05/08/2011", "04/08/2011", "03/08/2011",
> "02/08/2011", "01/08/2011", "29/07/2011", "28/07/2011", "27/07/2011",
> "26/07/2011", "25/07/2011", "22/07/2011", "21/07/2011", "20/07/2011",
> "19/07/2011", "18/07/2011", "15/07/2011", "14/07/2011", "13/07/2011",
> "12/07/2011", "11/07/2011", "08/07/2011", "07/07/2011", "06/07/2011",
> "05/07/2011", "04/07/2011", "01/07/2011", "30/06/2011", "29/06/2011",
> "28/06/2011", "27/06/2011", "24/06/2011", "23/06/2011", "22/06/2011",
> "21/06/2011", "20/06/2011", "17/06/2011", "16/06/2011", "15/06/2011",
> "14/06/2011", "13/06/2011", "10/06/2011", "09/06/2011", "08/06/2011",
> "07/06/2011", "06/06/2011", "03/06/2011", "02/06/2011", "01/06/2011",
> "31/05/2011", "30/05/2011", "27/05/2011", "26/05/2011", "25/05/2011",
> "24/05/2011", "23/05/2011", "20/05/2011", "19/05/2011", "18/05/2011",
> "16/05/2011", "13/05/2011", "12/05/2011", "11/05/2011", "10/05/2011",
> "09/05/2011", "06/05/2011", "05/05/2011", "04/05/2011", "03/05/2011",
> "29/04/2011", "28/04/2011", "27/04/2011", "26/04/2011", "25/04/2011",
> "21/04/2011", "20/04/2011", "19/04/2011", "18/04/2011", "15/04/2011",
> "14/04/2011", "13/04/2011", "12/04/2011", "11/04/2011", "08/04/2011",
> "07/04/2011", "06/04/2011", "05/04/2011", "04/04/2011", "01/04/2011",
> "31/03/2011", "30/03/2011", "29/03/2011", "28/03/2011", "25/03/2011",
> "24/03/2011", "23/03/2011", "22/03/2011", "21/03/2011", "18/03/2011",
> "17/03/2011", "16/03/2011", "15/03/2011", "14/03/2011", "11/03/2011",
> "10/03/2011", "09/03/2011", "08/03/2011", "07/03/2011", "04/03/2011",
> "03/03/2011", "02/03/2011", "01/03/2011", "28/02/2011", "25/02/2011",
> "24/02/2011", "23/02/2011", "22/02/2011", "21/02/2011", "18/02/2011",
> "17/02/2011", "16/02/2011", "15/02/2011", "14/02/2011", "11/02/2011",
> "10/02/2011", "09/02/2011", "08/02/2011", "07/02/2011", "02/02/2011",
> "01/02/2011", "31/01/2011", "28/01/2011", "27/01/2011", "26/01/2011",
> "25/01/2011", "24/01/2011", "21/01/2011", "20/01/2011", "19/01/2011",
> "18/01/2011", "17/01/2011", "14/01/2011", "13/01/2011", "12/01/2011",
> "11/01/2011", "10/01/2011", "07/01/2011", "06/01/2011", "05/01/2011",
> "04/01/2011", "03/01/2011", "31/12/2010", "30/12/2010", "29/12/2010",
> "28/12/2010", "27/12/2010", "24/12/2010", "23/12/2010", "22/12/2010",
> "21/12/2010", "20/12/2010", "17/12/2010", "16/12/2010", "15/12/2010",
> "14/12/2010", "13/12/2010", "10/12/2010", "09/12/2010", "08/12/2010",
> "07/12/2010", "06/12/2010", "03/12/2010", "02/12/2010", "01/12/2010",
> "30/11/2010", "29/11/2010", "26/11/2010", "25/11/2010", "24/11/2010",
> "23/11/2010", "22/11/2010", "19/11/2010", "18/11/2010", "16/11/2010",
> "15/11/2010", "12/11/2010", "11/11/2010", "10/11/2010", "09/11/2010",
> "08/11/2010", "04/11/2010", "03/11/2010", "02/11/2010", "01/11/2010",
> "29/10/2010", "28/10/2010", "27/10/2010", "26/10/2010", "25/10/2010",
> "22/10/2010", "21/10/2010", "20/10/2010", "19/10/2010", "18/10/2010",
> "15/10/2010", "14/10/2010", "13/10/2010", "12/10/2010", "11/10/2010",
> "08/10/2010", "07/10/2010", "06/10/2010", "05/10/2010", "04/10/2010",
> "01/10/2010", "30/09/2010", "29/09/2010", "28/09/2010", "27/09/2010",
> "24/09/2010", "23/09/2010", "22/09/2010", "21/09/2010", "20/09/2010",
> "17/09/2010", "16/09/2010", "15/09/2010", "14/09/2010", "13/09/2010",
> "09/09/2010", "08/09/2010", "07/09/2010", "06/09/2010", "03/09/2010",
> "02/09/2010", "01/09/2010", "31/08/2010", "30/08/2010", "27/08/2010",
> "26/08/2010", "25/08/2010", "24/08/2010", "23/08/2010", "20/08/2010",
> "19/08/2010", "18/08/2010", "17/08/2010", "16/08/2010", "13/08/2010",
> "12/08/2010", "11/08/2010", "10/08/2010", "06/08/2010", "05/08/2010",
> "04/08/2010", "03/08/2010", "02/08/2010", "30/07/2010", "29/07/2010",
> "28/07/2010", "27/07/2010", "26/07/2010", "23/07/2010", "22/07/2010",
> "21/07/2010", "20/07/2010", "19/07/2010", "16/07/2010", "15/07/2010",
> "14/07/2010", "13/07/2010", "12/07/2010", "09/07/2010", "08/07/2010",
> "07/07/2010", "06/07/2010", "05/07/2010", "02/07/2010", "01/07/2010",
> "30/06/2010", "29/06/2010", "28/06/2010", "25/06/2010", "24/06/2010",
> "23/06/2010", "22/06/2010", "21/06/2010", "18/06/2010", "17/06/2010",
> "16/06/2010", "15/06/2010", "14/06/2010", "11/06/2010", "10/06/2010",
> "09/06/2010", "08/06/2010", "07/06/2010", "04/06/2010", "03/06/2010",
> "02/06/2010", "01/06/2010", "31/05/2010", "27/05/2010", "26/05/2010",
> "25/05/2010", "24/05/2010", "21/05/2010", "20/05/2010", "19/05/2010",
> "18/05/2010", "17/05/2010", "14/05/2010", "13/05/2010", "12/05/2010",
> "11/05/2010", "10/05/2010", "07/05/2010", "06/05/2010", "05/05/2010",
> "04/05/2010", "03/05/2010", "30/04/2010", "29/04/2010", "28/04/2010",
> "27/04/2010", "26/04/2010", "23/04/2010", "22/04/2010", "21/04/2010",
> "20/04/2010", "19/04/2010", "16/04/2010", "15/04/2010", "14/04/2010",
> "13/04/2010", "12/04/2010", "09/04/2010", "08/04/2010", "07/04/2010",
> "06/04/2010", "05/04/2010", "01/04/2010", "31/03/2010", "30/03/2010",
> "29/03/2010", "26/03/2010", "25/03/2010", "24/03/2010", "23/03/2010",
> "22/03/2010", "19/03/2010", "18/03/2010", "17/03/2010", "16/03/2010",
> "15/03/2010", "12/03/2010", "11/03/2010", "10/03/2010", "09/03/2010",
> "08/03/2010", "05/03/2010", "04/03/2010", "03/03/2010", "02/03/2010",
> "01/03/2010", "26/02/2010", "25/02/2010", "24/02/2010", "23/02/2010",
> "22/02/2010", "19/02/2010", "18/02/2010", "17/02/2010", "12/02/2010",
> "11/02/2010", "10/02/2010", "09/02/2010", "08/02/2010", "05/02/2010",
> "04/02/2010", "03/02/2010", "02/02/2010", "01/02/2010", "29/01/2010",
> "28/01/2010", "27/01/2010", "26/01/2010", "25/01/2010", "22/01/2010",
> "21/01/2010", "20/01/2010", "19/01/2010", "18/01/2010", "15/01/2010",
> "14/01/2010", "13/01/2010", "12/01/2010", "11/01/2010", "08/01/2010",
> "07/01/2010", "06/01/2010", "05/01/2010", "04/01/2010", "31/12/2009",
> "30/12/2009", "29/12/2009", "28/12/2009", "24/12/2009", "23/12/2009",
> "22/12/2009", "21/12/2009", "18/12/2009", "17/12/2009", "16/12/2009",
> "15/12/2009", "14/12/2009", "11/12/2009", "10/12/2009", "09/12/2009",
> "08/12/2009", "07/12/2009", "04/12/2009", "03/12/2009", "02/12/2009",
> "01/12/2009", "30/11/2009", "26/11/2009", "25/11/2009", "24/11/2009",
> "23/11/2009", "20/11/2009", "19/11/2009", "18/11/2009", "17/11/2009",
> "16/11/2009", "13/11/2009", "12/11/2009", "11/11/2009", "10/11/2009",
> "09/11/2009", "06/11/2009", "05/11/2009", "04/11/2009", "03/11/2009",
> "02/11/2009", "30/10/2009", "29/10/2009", "28/10/2009", "27/10/2009",
> "26/10/2009", "23/10/2009", "22/10/2009", "21/10/2009", "20/10/2009",
> "19/10/2009", "16/10/2009", "15/10/2009", "14/10/2009", "13/10/2009",
> "12/10/2009", "09/10/2009", "08/10/2009", "07/10/2009", "06/10/2009",
> "05/10/2009", "02/10/2009", "01/10/2009", "30/09/2009", "29/09/2009",
> "28/09/2009", "25/09/2009", "24/09/2009", "23/09/2009", "22/09/2009",
> "18/09/2009", "17/09/2009", "16/09/2009", "15/09/2009", "14/09/2009",
> "11/09/2009", "10/09/2009", "09/09/2009", "08/09/2009", "07/09/2009",
> "04/09/2009", "03/09/2009", "02/09/2009", "01/09/2009", "31/08/2009",
> "28/08/2009", "27/08/2009", "26/08/2009", "25/08/2009", "24/08/2009",
> "21/08/2009", "20/08/2009", "19/08/2009", "18/08/2009", "17/08/2009",
> "14/08/2009", "13/08/2009", "12/08/2009", "11/08/2009", "07/08/2009",
> "06/08/2009", "05/08/2009", "04/08/2009", "03/08/2009", "31/07/2009",
> "30/07/2009", "29/07/2009", "28/07/2009", "27/07/2009", "24/07/2009",
> "23/07/2009", "22/07/2009", "21/07/2009", "20/07/2009", "17/07/2009",
> "16/07/2009", "15/07/2009", "14/07/2009", "13/07/2009", "10/07/2009",
> "09/07/2009", "08/07/2009", "07/07/2009", "06/07/2009", "03/07/2009",
> "02/07/2009", "01/07/2009", "30/06/2009", "29/06/2009", "26/06/2009",
> "25/06/2009", "24/06/2009", "23/06/2009", "22/06/2009", "19/06/2009",
> "18/06/2009", "17/06/2009", "16/06/2009", "15/06/2009", "12/06/2009",
> "11/06/2009", "10/06/2009", "09/06/2009", "08/06/2009", "05/06/2009",
> "04/06/2009", "03/06/2009", "02/06/2009", "01/06/2009", "29/05/2009",
> "28/05/2009", "27/05/2009", "26/05/2009", "25/05/2009", "22/05/2009",
> "21/05/2009", "20/05/2009", "19/05/2009", "18/05/2009", "15/05/2009",
> "14/05/2009", "13/05/2009", "12/05/2009", "11/05/2009", "08/05/2009",
> "07/05/2009", "06/05/2009", "05/05/2009", "04/05/2009", "30/04/2009",
> "29/04/2009", "28/04/2009", "27/04/2009", "24/04/2009", "23/04/2009",
> "22/04/2009", "21/04/2009", "20/04/2009", "17/04/2009", "16/04/2009",
> "15/04/2009", "14/04/2009", "13/04/2009", "09/04/2009", "08/04/2009",
>
>
>
>
>
> On Sun, Apr 22, 2012 at 6:41 PM, Jeff Newmiller <jdnewmil at dcn.davis.ca.us
> >wrote:
>
>> On Sun, 22 Apr 2012, Hasan Diwan wrote:
>>
>> Raghu,
>>>
>>> On 22 April 2012 09:53, Raghuraman Ramachandran <optionsraghu at gmail.com
>>>> wrote:
>>>
>>> I have a data frame (from CSV file) which has its first column
>>> called
>>>> Date.
>>>> The Date is in the format mm/dd/yyyy. I was trying to get the
>>>> weekday for
>>>> these dates and I tried using wday() and day.of.week() functions
>>>> and both
>>>> of them gave me precisely the wrong answers. I think the issue
>>>> lies in
>>>> the
>>>> proper formatting of dates. The class of this column is a factor
>>>> class
>>>> and
>>>> hence I tried converting into POSIXlt, xts, zoo objects and yet I
>>>> could
>>>> not
>>>> get the weekday correctly. Anyone has any suggestions please?
>>>>
>>>>
>>> Try this:
>>> # assume dataIn is where the CSV files data is...
>>> dataIn$Date <- as.POSIXct(dataIn$Date, format='%m/%d/%y')
>>>
>>
>> By far the most common error I see is failing to import the Date
>> column as
>> character, instead allowing the import function to convert it to
>> factor,
>> after which computations (such as the above suggestion) use the
>> hidden
>> factor index instead of the visible character representation, which
>> further
>> mystifies beginners. The conversion above will only work correctly
>> if the
>> column was imported as character. E.g.
>>
>> dataIn <- read.csv( file="yourdatafile", as.is=TRUE )
>>
>> OP: Use the str() function to see what types you are working with,
>> and in
>> future R-help queries send dput() of the data and code you have
>> tried if we
>> are to be able to reproduce your attempts effectively rather than
>> reading
>> your mind.
>>
>>
>> dataIn <- cbind(dataIn, day.of.week = format(dataIn$Date,
>> format='%A')
>>>
>>
>> Why not just
>>
>> dataIn$day.of.week <- weekdays( dataIn$Date )
>>
>> ?
>>
>> ------------------------------**------------------------------**
>> ---------------
>> Jeff Newmiller The ..... ..... Go
>> Live...
>> DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live
>> Go...
>> Live: OO#.. Dead: OO#..
>> Playing
>> Research Engineer (Solar/Batteries O.O#. #.O#. with
>> /Software/Embedded Controllers) .OO#. .OO#.
>> rocks...1k
>>
>>
>> ______________________________**________________
>> R-help at r-project.org mailing list
>> https://stat.ethz.ch/mailman/**listinfo/r-help<https://stat.ethz.ch/mailman/listinfo/r-help
>> >
>> PLEASE do read the posting guide http://www.R-project.org/**
>> posting-guide.html <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
> 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.
David Winsemius, MD
West Hartford, CT
More information about the R-help
mailing list