David L. Van Brunt, Ph.D.
2009-Dec-19 23:11 UTC
[R] as.xts convert all my numeric data to character
Hello, all... I've been playing with the TTR package and quantmod, and I'm loading the Chicago Board of Exchange put/call ratio data via a simple read.csv call... CBOEtotal<-read.csv(file=" http://www.cboe.com/publish/ScheduledTask/MktData/datahouse/totalpc.csv ",skip=1) this gives me a data frame with columns....> names(CBOEtotal)[1] "Trade_date" "Call" "Put" "Total" "P.C.Ratio"> head(CBOEtotal)Trade_date Call Put Total P.C.Ratio 1 2003-10-17 1152086 733258 1885344 0.64 2 2003-10-21 773759 540023 1313782 0.70 3 2003-10-22 663452 646991 1310443 0.98 4 2003-10-23 579837 493493 1073330 0.85 5 2003-10-24 690948 630758 1321706 0.91 6 2003-10-27 512226 328592 840818 0.64 Now I'd like to merge this with the data I've downloaded using TTR.> Hx<- getYahooData("AAPL", "20030101", "20091130") > head(Hx)Open High Low Close Volume Unadj.Close Div Split Adj.Div 2003-01-02 7.180 7.460 7.175 7.400 6479600 14.80 NA NA NA 2003-01-03 7.400 7.465 7.295 7.450 5266200 14.90 NA NA NA 2003-01-06 7.515 7.690 7.440 7.450 13947600 14.90 NA NA NA 2003-01-07 7.395 7.500 7.235 7.425 12226600 14.85 NA NA NA 2003-01-08 7.290 7.355 7.220 7.275 8201600 14.55 NA NA NA 2003-01-09 7.310 7.460 7.250 7.340 7687600 14.68 NA NA NA> is.xts(Hx)[1] TRUE In order to merge by date, I am trying to convert the first data frame in to an xts matrix first, but when I try, all my data gets converted to character data....> CBOEtotal$Trade_date<-as.POSIXct(paste(CBOEtotal$Trade_date,"0:0:0"),format = "%m/%d/%Y %H:%M:%S",tz="EST")> CBOEtotal<-xts(CBOEtotal,order.by=CBOEtotal$Trade_date) > head(CBOEtotal)Trade_date Call Put Total P.C.Ratio 2003-10-17 "2003-10-17" "1152086" " 733258" " 1885344" "0.64" 2003-10-21 "2003-10-21" " 773759" " 540023" " 1313782" "0.70" 2003-10-22 "2003-10-22" " 663452" " 646991" " 1310443" "0.98" 2003-10-23 "2003-10-23" " 579837" " 493493" " 1073330" "0.85" 2003-10-24 "2003-10-24" " 690948" " 630758" " 1321706" "0.91" 2003-10-27 "2003-10-27" " 512226" " 328592" " 840818" "0.64" And nothing I try will make those things numbers again. (not "as.double" or "as.numeric" to each column) What am I missing? Using R 2.10.1 on Mac OS X [[alternative HTML version deleted]]
Here is what worked for me:
1) Create a single xts object using one column and the index
2) Merge with the other columns
tt = read.csv("c:/ttt/totalpc.csv", skip=1)
xx = xts(tt$Call, order.by=as.Date(tt$Trade_date, format="%m/%d/%Y"))
yy = merge(xx, tt$Put, tt$Total, tt$P.C.Ratio)
colnames(yy) = c("Call", "Put", "Total",
"P.C.Ratio")
The last line resets the column names.
HTH
> From: dlvanbrunt@gmail.com
> Date: Sat, 19 Dec 2009 18:11:50 -0500
> To: r-help@r-project.org
> Subject: [R] as.xts convert all my numeric data to character
>
> Hello, all... I've been playing with the TTR package and quantmod, and
I'm
> loading the Chicago Board of Exchange put/call ratio data via a simple
> read.csv call...
>
> CBOEtotal<-read.csv(file="
> http://www.cboe.com/publish/ScheduledTask/MktData/datahouse/totalpc.csv
> ",skip=1)
>
> this gives me a data frame with columns....
> > names(CBOEtotal)
> [1] "Trade_date" "Call" "Put"
"Total" "P.C.Ratio"
>
> > head(CBOEtotal)
>
> Trade_date Call Put Total P.C.Ratio
> 1 2003-10-17 1152086 733258 1885344 0.64
> 2 2003-10-21 773759 540023 1313782 0.70
> 3 2003-10-22 663452 646991 1310443 0.98
> 4 2003-10-23 579837 493493 1073330 0.85
> 5 2003-10-24 690948 630758 1321706 0.91
> 6 2003-10-27 512226 328592 840818 0.64
>
> Now I'd like to merge this with the data I've downloaded using TTR.
> > Hx<- getYahooData("AAPL", "20030101",
"20091130")
> > head(Hx)
> Open High Low Close Volume Unadj.Close Div Split Adj.Div
> 2003-01-02 7.180 7.460 7.175 7.400 6479600 14.80 NA NA NA
> 2003-01-03 7.400 7.465 7.295 7.450 5266200 14.90 NA NA NA
> 2003-01-06 7.515 7.690 7.440 7.450 13947600 14.90 NA NA NA
> 2003-01-07 7.395 7.500 7.235 7.425 12226600 14.85 NA NA NA
> 2003-01-08 7.290 7.355 7.220 7.275 8201600 14.55 NA NA NA
> 2003-01-09 7.310 7.460 7.250 7.340 7687600 14.68 NA NA NA
>
> > is.xts(Hx)
> [1] TRUE
>
> In order to merge by date, I am trying to convert the first data frame in
to
> an xts matrix first, but when I try, all my data gets converted to
character
> data....
>
> >
CBOEtotal$Trade_date<-as.POSIXct(paste(CBOEtotal$Trade_date,"0:0:0"),
> format = "%m/%d/%Y %H:%M:%S",tz="EST")
> > CBOEtotal<-xts(CBOEtotal,order.by=CBOEtotal$Trade_date)
> > head(CBOEtotal)
> Trade_date Call Put Total P.C.Ratio
> 2003-10-17 "2003-10-17" "1152086" " 733258"
" 1885344" "0.64"
> 2003-10-21 "2003-10-21" " 773759" " 540023"
" 1313782" "0.70"
> 2003-10-22 "2003-10-22" " 663452" " 646991"
" 1310443" "0.98"
> 2003-10-23 "2003-10-23" " 579837" " 493493"
" 1073330" "0.85"
> 2003-10-24 "2003-10-24" " 690948" " 630758"
" 1321706" "0.91"
> 2003-10-27 "2003-10-27" " 512226" " 328592"
" 840818" "0.64"
>
> And nothing I try will make those things numbers again. (not
"as.double" or
> "as.numeric" to each column)
>
> What am I missing? Using R 2.10.1 on Mac OS X
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help@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.
_________________________________________________________________
[[alternative HTML version deleted]]