Re: [R] How to store an array in MySQL

From: Chris Stubben <stubben_at_lanl.gov>
Date: Wed 20 Sep 2006 - 20:15:43 GMT

 

> Hi all, does somebody know how to store an array in MySQL with the
> package RMySQL. Thanks in advance.
> 

A similar question was asked last month.

http://finzi.psych.upenn.edu/R/Rhelp02a/archive/81429.html

In a normalized database, you should store the index and value in separate columns. Try this...

a<-array(1:3, c(2, 4))

     [,1] [,2] [,3] [,4]
[1,] 1 3 2 1
[2,] 2 1 3 2

d <-dim(a)

z<-cbind(expand.grid(r=1:d[1],c=1:d[2]),x=as.vector(a))   r c x
1 1 1 1
2 2 1 2
3 1 2 3
4 2 2 1
5 1 3 2
6 2 3 3
7 1 4 1
8 2 4 2

library(RMySQL)

con<-dbConnect(MySQL(), dbname="test")

dbWriteTable(con, "array", z, row.names=FALSE) [1] TRUE

---

Now in Mysql 

select * from array;
+------+------+------+

| r | c | x |
+------+------+------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 1 | 2 | 3 |
| 2 | 2 | 1 |

| 1 | 3 | 2 |
| 2 | 3 | 3 |
| 1 | 4 | 1 |
| 2 | 4 | 2 |
+------+------+------+ select group_concat(x order by c separator ' ' ) as a from array group by r; +---------+
| a |
+---------+
| 1 3 2 1 |
| 2 1 3 2 |
+---------+ Chris Stubben ______________________________________________ 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 and provide commented, minimal, self-contained, reproducible code.
Received on Thu Sep 21 06:35:20 2006

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.1.8, at Wed 20 Sep 2006 - 21:30:16 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.