Help converting file to XTS

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Help converting file to XTS

Johannes Møllerhagen
Hello there! I am a master student working on my master thesis, and I am trying to convert some data to xts so I can apply a highfrequency package to it.

At the moment I am trying to use a POSIXct function. I am quite new at this program and I am having some issue. The file is  attached.


The current coding is:


dat<-read_csv("TEL5minint.csv")
xts(dat,order.by=as.POSIXct(dat),"%d/%m/%Y %H:%M")


And the error is:

Error in as.POSIXct.default(dat) :
  do not know how to convert 'dat' to class “POSIXct”


Any help is appreciated!


Kind Regards

Johannes
______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Help converting file to XTS

Bert Gunter-2
Hard to say without knowing what dat looks like.

Can you show us a small sample, perhaps via dput( head( dat))  ?
See ?dput, ?head for details.

A guess would be that dat is a data frame and not a character string, but
????


Bert Gunter

"The trouble with having an open mind is that people keep coming along and
sticking things into it."
-- Opus (aka Berkeley Breathed in his "Bloom County" comic strip )


On Tue, Feb 5, 2019 at 9:24 AM Johannes Møllerhagen <[hidden email]>
wrote:

> Hello there! I am a master student working on my master thesis, and I am
> trying to convert some data to xts so I can apply a highfrequency package
> to it.
>
> At the moment I am trying to use a POSIXct function. I am quite new at
> this program and I am having some issue. The file is  attached.
>
>
> The current coding is:
>
>
> dat<-read_csv("TEL5minint.csv")
> xts(dat,order.by=as.POSIXct(dat),"%d/%m/%Y %H:%M")
>
>
> And the error is:
>
> Error in as.POSIXct.default(dat) :
>   do not know how to convert 'dat' to class “POSIXct”
>
>
> Any help is appreciated!
>
>
> Kind Regards
>
> Johannes
> ______________________________________________
> [hidden email] 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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Help converting file to XTS

David Winsemius
In reply to this post by Johannes Møllerhagen

On 2/5/19 6:06 AM, Johannes Møllerhagen wrote:
> Hello there! I am a master student working on my master thesis, and I am trying to convert some data to xts so I can apply a highfrequency package to it.
>
> At the moment I am trying to use a POSIXct function. I am quite new at this program and I am having some issue. The file is  attached.
>
>
> The current coding is:
>
>
> dat<-read_csv("TEL5minint.csv")


So dat is now a dataframe

> xts(dat,order.by=as.POSIXct(dat),"%d/%m/%Y %H:%M")


And you are supplying a list (which is what dataframes are) to the
order.by parameter.

>
>
> And the error is:
>
> Error in as.POSIXct.default(dat) :
>    do not know how to convert 'dat' to class “POSIXct”

The function is telling you that dat is the wrong type of object to be
converted to the rownames of a matrix (which is what xts objects are.


--

David

>
>
> Any help is appreciated!
>
>
> Kind Regards
>
> Johannes
> ______________________________________________
> [hidden email] 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.

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Help converting file to XTS

Jeff Newmiller
In reply to this post by Johannes Møllerhagen
One thing about POSIXct or POSIXlt... you always need to address the issue of timezone. If your timestamp data are simple (no daylight savings) you may be able to get away with a simple

Sys.setenv( TZ="GMT" )

sometime in each R session prior to converting anything to this type (e.g at the beginning of your script).

You should read the help pages for read_csv

?read_csv

to find out that the object created by that function is a tibble (a variety of data frame). Then read

?xts

to find out that the order.by must be a time-based class (which requirement POSIXct meets), and then

?as.POSIXct

which sadly only says that x has to be an R object. There are a variety of specializations for this function, e.g. as.POSIXct.Date, as.POSIXct.numeric, as.POSIXct.POSIXlt, and as.POSIXct.default, the last of which handles conversion from character or factor data types. Note that none of these options include converting an entire data frame.

Since dat is a tibble you will need to use $ or `[[` indexing to extract the one column that contains the timestamps:

dat[[ 1 ]]

and convert it

as.POSIXct( dat[[ 1 ]], format="%d/%m/%Y %H:%M" )

and use that as the order.by argument

result <- xts( dat[-1] ,order.by= as.POSIXct( dat[[ 1 ]], format="%d/%m/%Y %H:%M" ) )

[1] https://stackoverflow.com/questions/9327700/read-data-from-excel-to-r-and-convert-to-xts

On February 5, 2019 6:06:15 AM PST, "Johannes Møllerhagen" <[hidden email]> wrote:

>Hello there! I am a master student working on my master thesis, and I
>am trying to convert some data to xts so I can apply a highfrequency
>package to it.
>
>At the moment I am trying to use a POSIXct function. I am quite new at
>this program and I am having some issue. The file is  attached.
>
>
>The current coding is:
>
>
>dat<-read_csv("TEL5minint.csv")
>xts(dat,order.by=as.POSIXct(dat),"%d/%m/%Y %H:%M")
>
>
>And the error is:
>
>Error in as.POSIXct.default(dat) :
>  do not know how to convert 'dat' to class “POSIXct”
>
>
>Any help is appreciated!
>
>
>Kind Regards
>
>Johannes
>______________________________________________
>[hidden email] 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.

--
Sent from my phone. Please excuse my brevity.

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Help converting file to XTS

Jeff Newmiller
Please use "reply-all" to keep the mailing list included in the conversation.

You need to closely examine your format string ... read

?strptime

and adjust your format to match the data you have. In particular the %Y you are using is wrong, but only you can tell in what order the day, month and year should be.

On February 6, 2019 3:31:05 AM PST, "Johannes Møllerhagen" <[hidden email]> wrote:

>Thank you guys! I think I'm getting it. The problem now is that when I
>import my data it is given as:
>
>print(dat)
>
>                         V1   V2
>1   02.01.03 10:00 26.8
>2   02.01.03 10:05 26.8
>3   02.01.03 10:10 26.9
>4   02.01.03 10:15 27.0
>5   02.01.03 10:20 26.9
>6   02.01.03 10:25 26.9
>7   02.01.03 10:30 26.9
>8   02.01.03 10:35 26.8
>9   02.01.03 10:40 26.7
>10  02.01.03 10:45 26.6
>11  02.01.03 10:50 26.6
>....
>
>
>However, when I call: as.POSIXct( dat[[ 1]], format="%d/%m/%Y %H:%M" )
>I get:
>
>[1] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
>NA NA NA NA NA NA NA
>[30] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
>NA NA NA NA NA NA NA
>[59] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
>NA NA NA NA NA NA NA
>[88] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
>NA NA NA NA NA NA NA
>[117] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
>NA NA NA NA NA NA NA
>[146] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
>NA NA NA NA NA NA NA
>[175] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
>NA NA NA NA NA NA NA
>[204] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
>NA NA NA NA NA NA NA
>[233] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
>NA NA NA NA NA NA N
>
>
>It seems like the indexing is somehow wrong, or am I missing something?
>:)
>
>________________________________
>Fra: Jeff Newmiller <[hidden email]>
>Sendt: tirsdag 5. februar 2019 19.39.14
>Til: [hidden email]; Johannes Møllerhagen; [hidden email]
>Emne: Re: [R] Help converting file to XTS
>
>One thing about POSIXct or POSIXlt... you always need to address the
>issue of timezone. If your timestamp data are simple (no daylight
>savings) you may be able to get away with a simple
>
>Sys.setenv( TZ="GMT" )
>
>sometime in each R session prior to converting anything to this type
>(e.g at the beginning of your script).
>
>You should read the help pages for read_csv
>
>?read_csv
>
>to find out that the object created by that function is a tibble (a
>variety of data frame). Then read
>
>?xts
>
>to find out that the order.by must be a time-based class (which
>requirement POSIXct meets), and then
>
>?as.POSIXct
>
>which sadly only says that x has to be an R object. There are a variety
>of specializations for this function, e.g. as.POSIXct.Date,
>as.POSIXct.numeric, as.POSIXct.POSIXlt, and as.POSIXct.default, the
>last of which handles conversion from character or factor data types.
>Note that none of these options include converting an entire data
>frame.
>
>Since dat is a tibble you will need to use $ or `[[` indexing to
>extract the one column that contains the timestamps:
>
>dat[[ 1 ]]
>
>and convert it
>
>as.POSIXct( dat[[ 1 ]], format="%d/%m/%Y %H:%M" )
>
>and use that as the order.by argument
>
>result <- xts( dat[-1] ,order.by= as.POSIXct( dat[[ 1 ]],
>format="%d/%m/%Y %H:%M" ) )
>
>[1]
>https://stackoverflow.com/questions/9327700/read-data-from-excel-to-r-and-convert-to-xts
>
>On February 5, 2019 6:06:15 AM PST, "Johannes Møllerhagen"
><[hidden email]> wrote:
>>Hello there! I am a master student working on my master thesis, and I
>>am trying to convert some data to xts so I can apply a highfrequency
>>package to it.
>>
>>At the moment I am trying to use a POSIXct function. I am quite new at
>>this program and I am having some issue. The file is  attached.
>>
>>
>>The current coding is:
>>
>>
>>dat<-read_csv("TEL5minint.csv")
>>xts(dat,order.by=as.POSIXct(dat),"%d/%m/%Y %H:%M")
>>
>>
>>And the error is:
>>
>>Error in as.POSIXct.default(dat) :
>>  do not know how to convert 'dat' to class “POSIXct”
>>
>>
>>Any help is appreciated!
>>
>>
>>Kind Regards
>>
>>Johannes
>>______________________________________________
>>[hidden email] 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.
>
>--
>Sent from my phone. Please excuse my brevity.

--
Sent from my phone. Please excuse my brevity.

______________________________________________
[hidden email] 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.