[R] mysql retrive question
hello R-Users, I have this simple but not for me question: I do: res-dbSendQuery(con, SELECT * FROM tabellaProva) myDataFrame-fetch(res) myDataMatrix-as.matrix(myDataFrame[,-1]) namerows(myDataMatrix)-as.character(myDataFrame[,1]) and I have: io tu io 0 1 tu 1 0 my problem is that the content of the matrix is interpreted by R as strings, not as numbers. Is there a way to convert those characters to numbers like io tu io 0 1 tu 1 0 thanx in advance, simone __ R-help@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
Re: [R] mysql retrive question
simone gabbriellini [EMAIL PROTECTED] writes: hello R-Users, I have this simple but not for me question: I do: res-dbSendQuery(con, SELECT * FROM tabellaProva) myDataFrame-fetch(res) myDataMatrix-as.matrix(myDataFrame[,-1]) namerows(myDataMatrix)-as.character(myDataFrame[,1]) and I have: io tu io 0 1 tu 1 0 my problem is that the content of the matrix is interpreted by R as strings, not as numbers. Is there a way to convert those characters to numbers like io tu io 0 1 tu 1 0 mode(m)-numeric should do the trick It looks a bit odd that you seem get numeric data from mySql as mode character, but I don't know enough about the interface to say why. -- O__ Peter Dalgaard Blegdamsvej 3 c/ /'_ --- Dept. of Biostatistics 2200 Cph. N (*) \(*) -- University of Copenhagen Denmark Ph: (+45) 35327918 ~~ - ([EMAIL PROTECTED]) FAX: (+45) 35327907 __ R-help@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
Re: [R] mysql retrive question
simone gabbriellini wrote: hello R-Users, I have this simple but not for me question: I do: res-dbSendQuery(con, SELECT * FROM tabellaProva) myDataFrame-fetch(res) myDataMatrix-as.matrix(myDataFrame[,-1]) namerows(myDataMatrix)-as.character(myDataFrame[,1]) and I have: io tu io 0 1 tu 1 0 my problem is that the content of the matrix is interpreted by R as strings, not as numbers. Is there a way to convert those characters to numbers like io tu io 0 1 tu 1 0 thanx in advance, simone Hi Simone, If you use dbReadTable, as I mentioned in my previous email, you should be able to coerce myDataFrame to a numeric matrix. A couple of extra observations: (1) If you really want to use fetch() to extract all the rows resulting from a SELECT statement in a single fetch, you may need to specify n=-1, e.g., fetch(res, n = -1) otherwise you may only get the first 500 rows. (See ?fetch, ?MySQL, and ?dbHasCompleted.) The reason there is this default is to prevent crashing R with a very large and unexpected amount of data. By specifying n=-1 you're effectively asserting that the output of SELECT can be properly handled by R. (2) Tables in a relational database are only superficially similar to data.frames (the SQL term relation for tables conveys semantics that do not exist in R), thus fetch() and dbReadTable() do not coerce their columns to factors. Clearly, there is a need to allow users to specify their own converters, as other interfaces (e.g., RSPython, RSPerl), and functions (e.g., read.table) actually provide. Hope this helps, -- David __ R-help@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
