R help - Apr 2005 - 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

simone gabbriellini <ogabbrie at tin.it> 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
~~~~~~~~~~ - (p.dalgaard at biostat.ku.dk)             FAX: (+45) 35327907

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