R › R help

Transform pairwise observations into a table

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

10 messages Options

AHJ

Transform pairwise observations into a table

Hi,

I have a table of pairs of individuals and a coefficient that belongs to the pair:

ind1 ind2 coef
1 1 1
1 2 0.25
1 3 0.125
1 4 0.5
2 2 1
2 1 0.25
2 3 0.125
2 4 0.5
3 3 1
3 1 0.125
3 2 0.125
3 4 0.5
4 4 1
4 1 0.5
4 2 0.5
4 3 0.5

And I want to convert it to a matrix where each individual is both a row and a column and at the intersection of each pair is the coefficient that belongs to that pair:

1 2 3 4
1 1 0.25 0.125 0.5
2 0.25 1 0.125 0.5
3 0.125 0.125 1 0.5
4 0.5 0.5 0.5 1

If table() would allow me to specify something other than frequencies to fill the table with, it would be what I need. I tried a few different combinations of t() and unique() but none of it made enough sense to post as my starting code... I am just lost. Any help would be greatly appreciated.

Thank you,
AHJ

arun kirshna

Re: Transform pairwise observations into a table

This post has NOT been accepted by the mailing list yet.

arun kirshna

Re: Transform pairwise observations into a table

In reply to this post by AHJ

Hi,
Try this:

dat1<-read.table(text="
ind1 ind2 coef
1 1 1
1 2 0.25
1 3 0.125
1 4 0.5
2 2 1
2 1 0.25
2 3 0.125
2 4 0.5
3 3 1
3 1 0.125
3 2 0.125
3 4 0.5
4 4 1
4 1 0.5
4 2 0.5
4 3 0.5
",sep="",header=TRUE)
mat1<-as.matrix(xtabs(coef~ind1+ind2,data=dat1))

# ind2
#ind1 1 2 3 4
# 1 1.000 0.250 0.125 0.500
#2 0.250 1.000 0.125 0.500
#3 0.125 0.125 1.000 0.500
#4 0.500 0.500 0.500 1.000

A.K.

----- Original Message -----
From: AHJ <[hidden email]>
To: [hidden email]
Cc:
Sent: Monday, October 1, 2012 12:17 PM
Subject: [R] Transform pairwise observations into a table

Hi,

I have a table of pairs of individuals and a coefficient that belongs to the
pair:

ind1    ind2    coef
1    1    1
1    2    0.25
1    3    0.125
1    4    0.5
2    2    1
2    1    0.25
2    3    0.125
2    4    0.5
3    3    1
3    1    0.125
3    2    0.125
3    4    0.5
4    4    1
4    1    0.5
4    2    0.5
4    3    0.5

And I want to convert it to a matrix where each individual is both a row and
a column and at the intersection of each pair is the coefficient that
belongs to that pair:

    1    2    3    4
1    1    0.25    0.125    0.5
2    0.25 1    0.125    0.5
3    0.125 0.125 1    0.5
4    0.5    0.5    0.5    1

If table() would allow me to specify something other than frequencies to
fill the table with, it would be what I need. I tried a few different
combinations of t() and unique() but none of it made enough sense to post as
my starting code... I am just lost. Any help would be greatly appreciated.

Thank you,
AHJ

--
View this message in context: http://r.789695.n4.nabble.com/Transform-pairwise-observations-into-a-table-tp4644706.html
Sent from the R help mailing list archive at Nabble.com.

______________________________________________
[hidden email] 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.

______________________________________________
[hidden email] 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.

AHJ

Re: Transform pairwise observations into a table

Thank you. I had looked at xtabs but misunderstood the syntax. This is great. :)

AHJ

On Oct 1, 2012, at 12:53 PM, "arun" <[hidden email]> wrote:

> Hi,
> Try this:
>
> dat1<-read.table(text="
> ind1 ind2 coef
> 1 1 1
> 1 2 0.25
> 1 3 0.125
> 1 4 0.5
> 2 2 1
> 2 1 0.25
> 2 3 0.125
> 2 4 0.5
> 3 3 1
> 3 1 0.125
> 3 2 0.125
> 3 4 0.5
> 4 4 1
> 4 1 0.5
> 4 2 0.5
> 4 3 0.5
> ",sep="",header=TRUE)
> mat1<-as.matrix(xtabs(coef~ind1+ind2,data=dat1))
>
> # ind2
> #ind1 1 2 3 4
> # 1 1.000 0.250 0.125 0.500
> #2 0.250 1.000 0.125 0.500
> #3 0.125 0.125 1.000 0.500
> #4 0.500 0.500 0.500 1.000
>
> A.K.
>
>
>
> ----- Original Message -----
> From: AHJ <[hidden email]>
> To: [hidden email]
> Cc:
> Sent: Monday, October 1, 2012 12:17 PM
> Subject: [R] Transform pairwise observations into a table
>
> Hi,
>
> I have a table of pairs of individuals and a coefficient that belongs to the
> pair:
>
> ind1 ind2 coef
> 1 1 1
> 1 2 0.25
> 1 3 0.125
> 1 4 0.5
> 2 2 1
> 2 1 0.25
> 2 3 0.125
> 2 4 0.5
> 3 3 1
> 3 1 0.125
> 3 2 0.125
> 3 4 0.5
> 4 4 1
> 4 1 0.5
> 4 2 0.5
> 4 3 0.5
>
> And I want to convert it to a matrix where each individual is both a row and
> a column and at the intersection of each pair is the coefficient that
> belongs to that pair:
>
> 1 2 3 4
> 1 1 0.25 0.125 0.5
> 2 0.25 1 0.125 0.5
> 3 0.125 0.125 1 0.5
> 4 0.5 0.5 0.5 1
>
> If table() would allow me to specify something other than frequencies to
> fill the table with, it would be what I need. I tried a few different
> combinations of t() and unique() but none of it made enough sense to post as
> my starting code... I am just lost. Any help would be greatly appreciated.
>
> Thank you,
> AHJ
>
>
>
> --
> View this message in context: http://r.789695.n4.nabble.com/Transform-pairwise-observations-into-a-table-tp4644706.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> [hidden email] 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.
>

______________________________________________
[hidden email] 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.

Rui Barradas

Re: Transform pairwise observations into a table

In reply to this post by AHJ

Hello,

Try the following.

dat <- read.table(text="
ind1 ind2 coef
1 1 1
1 2 0.25
1 3 0.125
1 4 0.5
2 2 1
2 1 0.25
2 3 0.125
2 4 0.5
3 3 1
3 1 0.125
3 2 0.125
3 4 0.5
4 4 1
4 1 0.5
4 2 0.5
4 3 0.5
", header=TRUE)
dat

reshape(dat, v.names = "coef", idvar = "ind1", timevar = "ind2",
direction = "wide")

Hope this helps,

Rui Barradas
Em 01-10-2012 17:17, AHJ escreveu:

> Hi,
>
> I have a table of pairs of individuals and a coefficient that belongs to the
> pair:
>
> ind1 ind2 coef
> 1 1 1
> 1 2 0.25
> 1 3 0.125
> 1 4 0.5
> 2 2 1
> 2 1 0.25
> 2 3 0.125
> 2 4 0.5
> 3 3 1
> 3 1 0.125
> 3 2 0.125
> 3 4 0.5
> 4 4 1
> 4 1 0.5
> 4 2 0.5
> 4 3 0.5
>
> And I want to convert it to a matrix where each individual is both a row and
> a column and at the intersection of each pair is the coefficient that
> belongs to that pair:
>
> 1 2 3 4
> 1 1 0.25 0.125 0.5
> 2 0.25 1 0.125 0.5
> 3 0.125 0.125 1 0.5
> 4 0.5 0.5 0.5 1
>
> If table() would allow me to specify something other than frequencies to
> fill the table with, it would be what I need. I tried a few different
> combinations of t() and unique() but none of it made enough sense to post as
> my starting code... I am just lost. Any help would be greatly appreciated.
>
> Thank you,
> AHJ
>
>
>
> --
> View this message in context: http://r.789695.n4.nabble.com/Transform-pairwise-observations-into-a-table-tp4644706.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> [hidden email] 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.

Bert Gunter

Re: Transform pairwise observations into a table

In reply to this post by arun kirshna

You might be amused by this alternative:

with(dat1,matrix(coef[order(ind2,ind1)],nr=4,nc=4))

It works because matrices are vectors stored in column major order.

Cheers,
Bert

On Mon, Oct 1, 2012 at 9:53 AM, arun <[hidden email]> wrote:

--

Bert Gunter
Genentech Nonclinical Biostatistics

Internal Contact Info:
Phone: 467-7374
Website:
http://pharmadevelopment.roche.com/index/pdb/pdb-functional-groups/pdb-biostatistics/pdb-ncb-home.htm

______________________________________________
[hidden email] 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.

arun kirshna

Re: Transform pairwise observations into a table

In reply to this post by AHJ

HI AHJ,
No problem.

One more way in addition to reshape() (Rui's suggestion) to get the same result.
library(reshape)

as.matrix(cast(melt(dat1,id=c("ind1","ind2")),ind1~ind2,value="value"))
# 1 2 3 4
#1 1.000 0.250 0.125 0.5
#2 0.250 1.000 0.125 0.5
#3 0.125 0.125 1.000 0.5
#4 0.500 0.500 0.500 1.0
A.K.

----- Original Message -----
From: "Hadjixenofontos, Athena" <[hidden email]>
To: arun <[hidden email]>
Cc: R help <[hidden email]>
Sent: Monday, October 1, 2012 12:59 PM
Subject: Re: [R] Transform pairwise observations into a table

Thank you. I had looked at xtabs but misunderstood the syntax. This is great. :)

AHJ

On Oct 1, 2012, at 12:53 PM, "arun" <[hidden email]> wrote:

David Winsemius

Re: Transform pairwise observations into a table

On Oct 1, 2012, at 2:30 PM, arun wrote:

> HI AHJ,
> No problem.
>
> One more way in addition to reshape() (Rui's suggestion) to get the same result.
> library(reshape)
>
> as.matrix(cast(melt(dat1,id=c("ind1","ind2")),ind1~ind2,value="value"))
> # 1 2 3 4
> #1 1.000 0.250 0.125 0.5
> #2 0.250 1.000 0.125 0.5
> #3 0.125 0.125 1.000 0.5
> #4 0.500 0.500 0.500 1.0
> A.K.
>

That looks a tad ... well, ... complicated. So perhaps these base-only solutions with tapply might be more accessible: Some of them do border on the whimsical, I will admit:

with (dat1, tapply(coef, list(ind1,ind2), I))

with (dat1, tapply(coef, list(ind1,ind2), c))

with (dat1, tapply(coef, list(ind1,ind2), "^", 1))

with (dat1, tapply(coef, list(ind1,ind2), "+", 0))

It is a specific response to the request for a `table`-like function tha twouldallow the application of other functions. Cnage the `1` to `2` in the third instance and you get the tabulated squares. And do not forget the availability of `ftable` to flatten the output of `tapply` retunred values.

--
David.

>
>
> ----- Original Message -----
> From: "Hadjixenofontos, Athena" <[hidden email]>
> To: arun <[hidden email]>
> Cc: R help <[hidden email]>
> Sent: Monday, October 1, 2012 12:59 PM
> Subject: Re: [R] Transform pairwise observations into a table
>
> Thank you. I had looked at xtabs but misunderstood the syntax. This is great. :)
>
> AHJ
>
>
>
> On Oct 1, 2012, at 12:53 PM, "arun" <[hidden email]> wrote:
>
>> Hi,
>> Try this:
>>
>> dat1<-read.table(text="
>> ind1 ind2 coef
>> 1 1 1
>> 1 2 0.25
>> 1 3 0.125
>> 1 4 0.5
>> 2 2 1
>> 2 1 0.25
>> 2 3 0.125
>> 2 4 0.5
>> 3 3 1
>> 3 1 0.125
>> 3 2 0.125
>> 3 4 0.5
>> 4 4 1
>> 4 1 0.5
>> 4 2 0.5
>> 4 3 0.5
>> ",sep="",header=TRUE)
>> mat1<-as.matrix(xtabs(coef~ind1+ind2,data=dat1))
>>
>> # ind2
>> #ind1 1 2 3 4
>> # 1 1.000 0.250 0.125 0.500
>> #2 0.250 1.000 0.125 0.500
>> #3 0.125 0.125 1.000 0.500
>> #4 0.500 0.500 0.500 1.000
>>
>> A.K.
>>
>>
>>
>> ----- Original Message -----
>> From: AHJ <[hidden email]>
>> To: [hidden email]
>> Cc:
>> Sent: Monday, October 1, 2012 12:17 PM
>> Subject: [R] Transform pairwise observations into a table
>>
>> Hi,
>>
>> I have a table of pairs of individuals and a coefficient that belongs to the
>> pair:
>>
>> ind1 ind2 coef
>> 1 1 1
>> 1 2 0.25
>> 1 3 0.125
>> 1 4 0.5
>> 2 2 1
>> 2 1 0.25
>> 2 3 0.125
>> 2 4 0.5
>> 3 3 1
>> 3 1 0.125
>> 3 2 0.125
>> 3 4 0.5
>> 4 4 1
>> 4 1 0.5
>> 4 2 0.5
>> 4 3 0.5
>>
>> And I want to convert it to a matrix where each individual is both a row and
>> a column and at the intersection of each pair is the coefficient that
>> belongs to that pair:
>>
>> 1 2 3 4
>> 1 1 0.25 0.125 0.5
>> 2 0.25 1 0.125 0.5
>> 3 0.125 0.125 1 0.5
>> 4 0.5 0.5 0.5 1
>>
>> If table() would allow me to specify something other than frequencies to
>> fill the table with, it would be what I need. I tried a few different
>> combinations of t() and unique() but none of it made enough sense to post as
>> my starting code... I am just lost. Any help would be greatly appreciated.
>>
>> Thank you,
>> AHJ
>>
>>
>>
>> --
>> View this message in context: http://r.789695.n4.nabble.com/Transform-pairwise-observations-into-a-table-tp4644706.html
>> Sent from the R help mailing list archive at Nabble.com.
>>
>> ______________________________________________
>> [hidden email] 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.
>>
>
>
> ______________________________________________
> [hidden email] 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.

David Winsemius, MD
Alameda, CA, USA

______________________________________________
[hidden email] 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.

arun kirshna

Re: Transform pairwise observations into a table

HI David,

Thanks for your input.
Third and fourth solutions looks very interesting.

with(dat1,tapply(coef,list(ind1,ind2),function(x) x))
# also gave the same result.
# I tried with aggregate() and ddply(), but may be not as elegant solution as yours.

matrix(with(dat1,aggregate(coef,list(ind1,ind2),FUN=function(x) x)[,3]),nc=4)
library(plyr)
matrix(ddply(dat1,.(ind1,ind2),FUN=function(x) x$coef)[,3],nc=4)
# [,1] [,2] [,3] [,4]
#[1,] 1.000 0.250 0.125 0.5
#[2,] 0.250 1.000 0.125 0.5
#[3,] 0.125 0.125 1.000 0.5
#[4,] 0.500 0.500 0.500 1.0
A.K.

----- Original Message -----
From: David Winsemius <[hidden email]>
To: arun <[hidden email]>
Cc: "Hadjixenofontos, Athena" <[hidden email]>; R help <[hidden email]>; Bert Gunter <[hidden email]>
Sent: Monday, October 1, 2012 10:33 PM
Subject: Re: [R] Transform pairwise observations into a table

On Oct 1, 2012, at 2:30 PM, arun wrote:

William Dunlap

Re: Transform pairwise observations into a table

In reply to this post by David Winsemius

Another base-R-only solution uses a 2-column matrix of subscripts
to fill a matrix. E.g.,

> f <- function(data) {
+ mat <- matrix(NA_real_, nrow=max(data[[1]]), ncol=max(data[[2]]))
+ mat[cbind(data[[1]], data[[2]])] <- data[[3]]
+ mat
+ }
>
> f(dat1)
[,1] [,2] [,3] [,4]
[1,] 1.000 0.250 0.125 0.5
[2,] 0.250 1.000 0.125 0.5
[3,] 0.125 0.125 1.000 0.5
[4,] 0.500 0.500 0.500 1.0

Bill Dunlap
Spotfire, TIBCO Software
wdunlap tibco.com

> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]] On Behalf
> Of David Winsemius
> Sent: Monday, October 01, 2012 7:33 PM
> To: arun
> Cc: R help; Bert Gunter; Hadjixenofontos, Athena
> Subject: Re: [R] Transform pairwise observations into a table
>
>
> On Oct 1, 2012, at 2:30 PM, arun wrote:
>
> > HI AHJ,
> > No problem.
> >
> > One more way in addition to reshape() (Rui's suggestion) to get the same result.
> > library(reshape)
> >
> > as.matrix(cast(melt(dat1,id=c("ind1","ind2")),ind1~ind2,value="value"))
> > # 1 2 3 4
> > #1 1.000 0.250 0.125 0.5
> > #2 0.250 1.000 0.125 0.5
> > #3 0.125 0.125 1.000 0.5
> > #4 0.500 0.500 0.500 1.0
> > A.K.
> >
> That looks a tad ... well, ... complicated. So perhaps these base-only solutions with tapply
> might be more accessible: Some of them do border on the whimsical, I will admit:
>
> with (dat1, tapply(coef, list(ind1,ind2), I))
>
> with (dat1, tapply(coef, list(ind1,ind2), c))
>
> with (dat1, tapply(coef, list(ind1,ind2), "^", 1))
>
> with (dat1, tapply(coef, list(ind1,ind2), "+", 0))
>
> It is a specific response to the request for a `table`-like function tha twouldallow the
> application of other functions. Cnage the `1` to `2` in the third instance and you get the
> tabulated squares. And do not forget the availability of `ftable` to flatten the output of
> `tapply` retunred values.
>
>
> --
> David.
> >
> >
> > ----- Original Message -----
> > From: "Hadjixenofontos, Athena" <[hidden email]>
> > To: arun <[hidden email]>
> > Cc: R help <[hidden email]>
> > Sent: Monday, October 1, 2012 12:59 PM
> > Subject: Re: [R] Transform pairwise observations into a table
> >
> > Thank you. I had looked at xtabs but misunderstood the syntax. This is great. :)
> >
> > AHJ
> >
> >
> >
> > On Oct 1, 2012, at 12:53 PM, "arun" <[hidden email]> wrote:
> >
> >> Hi,
> >> Try this:
> >>
> >> dat1<-read.table(text="
> >> ind1 ind2 coef
> >> 1 1 1
> >> 1 2 0.25
> >> 1 3 0.125
> >> 1 4 0.5
> >> 2 2 1
> >> 2 1 0.25
> >> 2 3 0.125
> >> 2 4 0.5
> >> 3 3 1
> >> 3 1 0.125
> >> 3 2 0.125
> >> 3 4 0.5
> >> 4 4 1
> >> 4 1 0.5
> >> 4 2 0.5
> >> 4 3 0.5
> >> ",sep="",header=TRUE)
> >> mat1<-as.matrix(xtabs(coef~ind1+ind2,data=dat1))
> >>
> >> # ind2
> >> #ind1 1 2 3 4
> >> # 1 1.000 0.250 0.125 0.500
> >> #2 0.250 1.000 0.125 0.500
> >> #3 0.125 0.125 1.000 0.500
> >> #4 0.500 0.500 0.500 1.000
> >>
> >> A.K.
> >>
> >>
> >>
> >> ----- Original Message -----
> >> From: AHJ <[hidden email]>
> >> To: [hidden email]
> >> Cc:
> >> Sent: Monday, October 1, 2012 12:17 PM
> >> Subject: [R] Transform pairwise observations into a table
> >>
> >> Hi,
> >>
> >> I have a table of pairs of individuals and a coefficient that belongs to the
> >> pair:
> >>
> >> ind1 ind2 coef
> >> 1 1 1
> >> 1 2 0.25
> >> 1 3 0.125
> >> 1 4 0.5
> >> 2 2 1
> >> 2 1 0.25
> >> 2 3 0.125
> >> 2 4 0.5
> >> 3 3 1
> >> 3 1 0.125
> >> 3 2 0.125
> >> 3 4 0.5
> >> 4 4 1
> >> 4 1 0.5
> >> 4 2 0.5
> >> 4 3 0.5
> >>
> >> And I want to convert it to a matrix where each individual is both a row and
> >> a column and at the intersection of each pair is the coefficient that
> >> belongs to that pair:
> >>
> >> 1 2 3 4
> >> 1 1 0.25 0.125 0.5
> >> 2 0.25 1 0.125 0.5
> >> 3 0.125 0.125 1 0.5
> >> 4 0.5 0.5 0.5 1
> >>
> >> If table() would allow me to specify something other than frequencies to
> >> fill the table with, it would be what I need. I tried a few different
> >> combinations of t() and unique() but none of it made enough sense to post as
> >> my starting code... I am just lost. Any help would be greatly appreciated.
> >>
> >> Thank you,
> >> AHJ
> >>
> >>
> >>
> >> --
> >> View this message in context: http://r.789695.n4.nabble.com/Transform-pairwise-
> observations-into-a-table-tp4644706.html
> >> Sent from the R help mailing list archive at Nabble.com.
> >>
> >> ______________________________________________
> >> [hidden email] 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.
> >>
> >
> >
> > ______________________________________________
> > [hidden email] 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.
>
> David Winsemius, MD
> Alameda, CA, USA
>
> ______________________________________________
> [hidden email] 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.