Hi Group
I have a large data set of individual pairwise values (250,000 rows) that I
need to reshape into a pairwise matrix for mantel tests of these values
versus genetic distances.
the data are currently in columns formatted like so
AnimalA, AnimalB, Score
A1, A2, S1
A1, A3, S2
A1, A4, S3
A2, A3, S4
A2, A4, S5
A3, A4, S6
...,....,....
I need the final matrix to be formatted as
A1 A2 A3 A4
A1 0 S1 S2 S3
A2 S1 0 S4 S5
A3 S2 S4 0 S6
A4 S3 S4 S6 0
are there any functions/packages that will make this easier?
Thanks
Ken
--
View this message in context:
http://www.nabble.com/Create-pairwise-table-from-columns--tp17158538p17158538.html
Sent from the R help mailing list archive at Nabble.com.
Here is a suggestion> con <- textConnection("AnimalA,AnimalB,ScoreA1,A2,1 A1,A3,2 A1,A4,3 A2,A3,4 A2,A4,5 A3,A4,6 ")> tst <- read.csv(con) > close(con) > an <- with(tst, sort(unique(c(as.character(AnimalA),as.character(AnimalB)))))> M <- array(0, c(length(an), length(an)), list(an, an)) > i <- match(tst$AnimalA, an) > j <- match(tst$AnimalB, an) > M[cbind(i,j)] <- M[cbind(j,i)] <- tst$Score > > MA1 A2 A3 A4 A1 0 1 2 3 A2 1 0 4 5 A3 2 4 0 6 A4 3 5 6 0>Bill Venables CSIRO Laboratories PO Box 120, Cleveland, 4163 AUSTRALIA Office Phone (email preferred): +61 7 3826 7251 Fax (if absolutely necessary): +61 7 3826 7304 Mobile: +61 4 8819 4402 Home Phone: +61 7 3286 7700 mailto:Bill.Venables at csiro.au http://www.cmis.csiro.au/bill.venables/ -----Original Message----- From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] On Behalf Of knussear Sent: Saturday, 10 May 2008 10:18 AM To: r-help at r-project.org Subject: [R] Create pairwise table from columns? Hi Group I have a large data set of individual pairwise values (250,000 rows) that I need to reshape into a pairwise matrix for mantel tests of these values versus genetic distances. the data are currently in columns formatted like so AnimalA, AnimalB, Score A1, A2, S1 A1, A3, S2 A1, A4, S3 A2, A3, S4 A2, A4, S5 A3, A4, S6 ...,....,.... I need the final matrix to be formatted as A1 A2 A3 A4 A1 0 S1 S2 S3 A2 S1 0 S4 S5 A3 S2 S4 0 S6 A4 S3 S4 S6 0 are there any functions/packages that will make this easier? Thanks Ken -- View this message in context: http://www.nabble.com/Create-pairwise-table-from-columns--tp17158538p171 58538.html Sent from the R help mailing list archive at Nabble.com. ______________________________________________ R-help at 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.
Fantastic! Thanks very much! Where can I read more about the double assignment statement at the bottom? Closest I could get was a double for loop, which takes a long time.... Bill.Venables wrote:> > Here is a suggestion > >> con <- textConnection("AnimalA,AnimalB,Score > A1,A2,1 > A1,A3,2 > A1,A4,3 > A2,A3,4 > A2,A4,5 > A3,A4,6 > ") >> tst <- read.csv(con) >> close(con) >> an <- with(tst, sort(unique(c(as.character(AnimalA), > as.character(AnimalB))))) >> M <- array(0, c(length(an), length(an)), list(an, an)) >> i <- match(tst$AnimalA, an) >> j <- match(tst$AnimalB, an) >> M[cbind(i,j)] <- M[cbind(j,i)] <- tst$Score >> >> M > A1 A2 A3 A4 > A1 0 1 2 3 > A2 1 0 4 5 > A3 2 4 0 6 > A4 3 5 6 0 >> > > > Bill Venables > CSIRO Laboratories > PO Box 120, Cleveland, 4163 > AUSTRALIA > Office Phone (email preferred): +61 7 3826 7251 > Fax (if absolutely necessary): +61 7 3826 7304 > Mobile: +61 4 8819 4402 > Home Phone: +61 7 3286 7700 > mailto:Bill.Venables at csiro.au > http://www.cmis.csiro.au/bill.venables/ > > -----Original Message----- > From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] > On Behalf Of knussear > Sent: Saturday, 10 May 2008 10:18 AM > To: r-help at r-project.org > Subject: [R] Create pairwise table from columns? > > > Hi Group > > I have a large data set of individual pairwise values (250,000 rows) > that I > need to reshape into a pairwise matrix for mantel tests of these values > versus genetic distances. > > the data are currently in columns formatted like so > AnimalA, AnimalB, Score > A1, A2, S1 > A1, A3, S2 > A1, A4, S3 > A2, A3, S4 > A2, A4, S5 > A3, A4, S6 > ...,....,.... > > > I need the final matrix to be formatted as > A1 A2 A3 A4 > A1 0 S1 S2 S3 > A2 S1 0 S4 S5 > A3 S2 S4 0 S6 > A4 S3 S4 S6 0 > > are there any functions/packages that will make this easier? > > Thanks > > Ken > -- > View this message in context: > http://www.nabble.com/Create-pairwise-table-from-columns--tp17158538p171 > 58538.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > R-help at 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. > > ______________________________________________ > R-help at 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. > >-- View this message in context: http://www.nabble.com/Create-pairwise-table-from-columns--tp17158538p17162698.html Sent from the R help mailing list archive at Nabble.com.
On Fri, May 9, 2008 at 7:17 PM, knussear <knussear at mac.com> wrote:> > Hi Group > > I have a large data set of individual pairwise values (250,000 rows) that I > need to reshape into a pairwise matrix for mantel tests of these values > versus genetic distances. > > the data are currently in columns formatted like so > AnimalA, AnimalB, Score > A1, A2, S1 > A1, A3, S2 > A1, A4, S3 > A2, A3, S4 > A2, A4, S5 > A3, A4, S6 > ...,....,.... > > > I need the final matrix to be formatted as > A1 A2 A3 A4 > A1 0 S1 S2 S3 > A2 S1 0 S4 S5 > A3 S2 S4 0 S6 > A4 S3 S4 S6 0One approach would be to use the reshape package (http://had.co.nz/reshape) - dfm <- melt(mydf, id = c("AnimalA", "AnimalB")) cast(dfm, AnimalA ~ AnimalB) Hadley -- http://had.co.nz/