Hi all,
I want to make extra columns in my datafile where the id of every
groupmember is mentioned in separate columns. To explain it better see the
example:
id<-c(1,2,3,4,5,6,7,8,9,10,11,12)
group<-c(1,1,1,1,2,2,3,3,3,3,3,3)
a<-as.data.frame(cbind(id,group))
a
id group
1 1 1
2 2 1
3 3 1
4 4 1
5 5 2
6 6 2
7 7 3
8 8 3
9 9 3
10 10 3
11 11 3
12 12 3
Result should be (gm = groupmember)
id group gm1 gm2 gm3 gm4 gm5 gm6
gm7 etc.
1 1 2 3 4 NA NA
NA NA
2 1 1 3 4 NA NA
NA NA
3 1 1 2 4 NA NA
NA NA
4 1 1 2 3 NA NA
NA NA
5 2 6 NA NA NA NA
NA NA
6 2 5 NA NA NA NA
NA NA
7 3 8 9 10 11 12
NA NA
8 3 7 9 10 11
12 NA NA
9 3 7 8 10 11
12 NA NA
10 3 7 8 9 11
12 NA NA
11 3 7 8 9 10
12 NA NA
12 3 7 8 9 10
11 NA NA
What I have been trying so far is reshape, but this doesn???t put the id???s
of
the groupmembers of different groups under the same columnname. It makes new
columnnames per group.
Couls somebody help me out with this? I really appreciate it
Regards,
Naomi Duijvesteijn
Disclaimer: De informatie opgenomen in dit bericht (en bijlagen) kan
vertrouwelijk zijn en is uitsluitend bestemd voor de geadresseerde(n).
Indien u dit bericht ten onrechte ontvangt, wordt u geacht de inhoud niet te
gebruiken, de afzender direct te informeren en het bericht te vernietigen.
Aan dit bericht kunnen geen rechten of plichten worden ontleend.
----------------------------------------------------------------------------
----------------------------
Disclaimer: The information contained in this message may be confidential
and is intended to be exclusively for the addressee. Should you receive this
message unintentionally, you are expected not to use the contents herein, to
notify the sender immediately and to destroy the message. No rights can be
derived from this message.
P Please consider the environment before printing this email
try this:> x <- read.table(textConnection(" id group+ 1 1 1 + 2 2 1 + 3 3 1 + 4 4 1 + 5 5 2 + 6 6 2 + 7 7 3 + 8 8 3 + 9 9 3 + 10 10 3 + 11 11 3 + 12 12 3"), header=TRUE)> # split by group and process > result <- lapply(split(x, x$group), function(i){+ # create combinations + com <- combn(i$id, length(i$id) - 1L) + t(apply(com, 2, function(.col){ + c(setdiff(i$id, .col), i$group[1L], .col) + })) + })> # determine maximum number of column > col.max <- max(sapply(result, ncol)) > # now create output with proper number of columns > output <- do.call(rbind, lapply(result, function(.grp){+ while (ncol(.grp) < col.max) .grp <- cbind(.grp, NA) + .grp + }))> > # add column names > colnames(output) <- c('id', 'group', paste('gm', seq(col.max - 2),sep=''))> outputid group gm1 gm2 gm3 gm4 gm5 [1,] 4 1 1 2 3 NA NA [2,] 3 1 1 2 4 NA NA [3,] 2 1 1 3 4 NA NA [4,] 1 1 2 3 4 NA NA [5,] 6 2 5 NA NA NA NA [6,] 5 2 6 NA NA NA NA [7,] 12 3 7 8 9 10 11 [8,] 11 3 7 8 9 10 12 [9,] 10 3 7 8 9 11 12 [10,] 9 3 7 8 10 11 12 [11,] 8 3 7 9 10 11 12 [12,] 7 3 8 9 10 11 12>On Tue, Apr 13, 2010 at 8:04 AM, Duijvesteijn, Naomi < Naomi.Duijvesteijn@ipg.nl> wrote:> > Hi all, > > > > I want to make extra columns in my datafile where the id of every > groupmember is mentioned in separate columns. To explain it better see > the > example: > > > > id<-c(1,2,3,4,5,6,7,8,9,10,11,12) > > group<-c(1,1,1,1,2,2,3,3,3,3,3,3) > > a<-as.data.frame(cbind(id,group)) > > a > > id group > > 1 1 1 > > 2 2 1 > > 3 3 1 > > 4 4 1 > > 5 5 2 > > 6 6 2 > > 7 7 3 > > 8 8 3 > > 9 9 3 > > 10 10 3 > > 11 11 3 > > 12 12 3 > > > > Result should be (gm = groupmember) > > > id group gm1 gm2 gm3 gm4 gm5 gm6 > gm7 etc. > > 1 1 2 3 4 NA NA > NA NA > > 2 1 1 3 4 NA NA > NA NA > > 3 1 1 2 4 NA NA > NA NA > > 4 1 1 2 3 NA NA > NA NA > > 5 2 6 NA NA NA NA > NA NA > > 6 2 5 NA NA NA NA > NA NA > > 7 3 8 9 10 11 12 > NA NA > > 8 3 7 9 10 11 > 12 NA NA > > 9 3 7 8 10 11 > 12 NA NA > > 10 3 7 8 9 11 > 12 NA NA > > 11 3 7 8 9 10 > 12 NA NA > > 12 3 7 8 9 10 > 11 NA NA > > > > What I have been trying so far is reshape, but this doesn’t put the id’s > of > the groupmembers of different groups under the same columnname. It makes > new > columnnames per group. > > > Couls somebody help me out with this? I really appreciate it > > > Regards, > > Naomi Duijvesteijn > > > > > Disclaimer: De informatie opgenomen in dit bericht (en bijlagen) kan > vertrouwelijk zijn en is uitsluitend bestemd voor de geadresseerde(n). > Indien u dit bericht ten onrechte ontvangt, wordt u geacht de inhoud niet > te > gebruiken, de afzender direct te informeren en het bericht te > vernietigen. > Aan dit bericht kunnen geen rechten of plichten worden ontleend. > > > ---------------------------------------------------------------------------- > ---------------------------- > > Disclaimer: The information contained in this message may be confidential > and is intended to be exclusively for the addressee. Should you receive > this > message unintentionally, you are expected not to use the contents herein, > to > notify the sender immediately and to destroy the message. No rights can > be > derived from this message. > > > P Please consider the environment before printing this email > > ______________________________________________ > R-help@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<http://www.r-project.org/posting-guide.html> > and provide commented, minimal, self-contained, reproducible code. > >-- Jim Holtman Cincinnati, OH +1 513 646 9390 What is the problem that you are trying to solve? [[alternative HTML version deleted]]
Try this. First we use outer to form a matrix such that i,j-th entry is TRUE if i, j are in the same group and unequal and FALSE otherwise. We apply which on the rows of that matrix to get the groups that are associated with the row group. We then cbind the components together using ts class to prevent recycling and cbind that to a. Finally we remove the ugly rownames. Note that if a has N rows then an NxN matrix will be created gps <- apply(outer(a$group, a$group, "==") & !diag(nrow(a)), 1, which) out <- cbind(a, t(do.call(cbind, lapply(gps, ts)))) rownames(out) <- NULL On Tue, Apr 13, 2010 at 8:04 AM, Duijvesteijn, Naomi <Naomi.Duijvesteijn at ipg.nl> wrote:> > ? Hi all, > > > > ? I ?want ?to ?make ?extra ?columns in my datafile where the id of every > ? groupmember is mentioned in separate columns. To explain it better see the > ? example: > > > > ? id<-c(1,2,3,4,5,6,7,8,9,10,11,12) > > ? group<-c(1,1,1,1,2,2,3,3,3,3,3,3) > > ? a<-as.data.frame(cbind(id,group)) > > ? a > > ? ? ?id group > > ? 1 ? 1 ? ? 1 > > ? 2 ? 2 ? ? 1 > > ? 3 ? 3 ? ? 1 > > ? 4 ? 4 ? ? 1 > > ? 5 ? 5 ? ? 2 > > ? 6 ? 6 ? ? 2 > > ? 7 ? 7 ? ? 3 > > ? 8 ? 8 ? ? 3 > > ? 9 ? 9 ? ? 3 > > ? 10 10 ? ? 3 > > ? 11 11 ? ? 3 > > ? 12 12 ? ? 3 > > > > ? Result should be (gm = groupmember) > > > ? id ?group ? gm1 ? ? ? ? gm2 ? ? ? gm3 ? ? ?gm4 ? ? ? ? ? gm5 ? ? ? gm6 > ? ? gm7 ? ? ? ? ? ?etc. > > ? 1 ? ? 1 ? ? ? ? ?2 ? ? ? ? ? ? 3 ? ? ? ? ? 4 ? ? ? ? ?NA ? ? ? ? ? ?NA > ? NA ? ? ? ? ? ?NA > > ? 2 ? ? 1 ? ? ? ? ?1 ? ? ? ? ? ? 3 ? ? ? ? ? 4 ? ? ? ? ?NA ? ? ? ? ? ?NA > ? NA ? ? ? ? ? ?NA > > ? 3 ? ? 1 ? ? ? ? ?1 ? ? ? ? ? ? 2 ? ? ? ? ? 4 ? ? ? ? ?NA ? ? ? ? ? ?NA > ? NA ? ? ? ? ? ?NA > > ? 4 ? ? 1 ? ? ? ? ?1 ? ? ? ? ? ? 2 ? ? ? ? ? 3 ? ? ? ? ?NA ? ? ? ? ? ?NA > ? NA ? ? ? ? ? ?NA > > ? 5 ? ? 2 ? ? ? ? ?6 ? ? ? ? ? ? NA ? ? ? ? ?NA ? ? ? ?NA ? ? ? ? ? ? NA > ? NA ? ? ? ? ? ?NA > > ? 6 ? ? 2 ? ? ? ? ?5 ? ? ? ? ? ? NA ? ? ? ? ?NA ? ? ? ?NA ? ? ? ? ? ? NA > ? NA ? ? ? ? ? ?NA > > ? 7 ? ? 3 ? ? ? ? ?8 ? ? ? ? ? ?9 ? ? ? ? ? 10 ? ? ? ? ?11 ? ? ? ? ? ?12 > ? ? NA ? ? ? ? ? ? NA > > ? 8 ? ? ? ?3 ? ? ? ? ? ?7 ? ? ? ? ? ? ? 9 ? ? ? ? ? ? ?10 ? ? ? ? ? ? 11 > ? 12 ? ? ? ? ?NA ? ? ? ? ? ? NA > > ? 9 ? ? ? ?3 ? ? ? ? ? ?7 ? ? ? ? ? ? ? 8 ? ? ? ? ? ? ?10 ? ? ? ? ? ? 11 > ? 12 ? ? ? ? ?NA ? ? ? ? ? ? NA > > ? 10 ? ? ? 3 ? ? ? ? ? ?7 ? ? ? ? ? ? ? 8 ? ? ? ? ? ? ?9 ? ? ? ? ? ? ?11 > ? 12 ? ? ? ? ?NA ? ? ? ? ? ? NA > > ? 11 ? ? ? 3 ? ? ? ? ? ?7 ? ? ? ? ? ? ? 8 ? ? ? ? ? ? ?9 ? ? ? ? ? ? ?10 > ? 12 ? ? ? ? ?NA ? ? ? ? ? ? NA > > ? 12 ? ? ? 3 ? ? ? ? ? ?7 ? ? ? ? ? ? ? 8 ? ? ? ? ? ? ?9 ? ? ? ? ? ? ?10 > ? 11 ? ? ? ? ?NA ? ? ? ? ? ? NA > > > > ? What I have been trying so far is reshape, but this doesn?t put the id?s of > ? the groupmembers of different groups under the same columnname. It makes new > ? columnnames per group. > > > ? Couls somebody help me out with this? I really appreciate it > > > ? Regards, > > ? Naomi Duijvesteijn > > > > > ? Disclaimer: ?De ?informatie opgenomen in dit bericht (en bijlagen) kan > ? vertrouwelijk zijn en is uitsluitend bestemd voor de geadresseerde(n). > ? Indien u dit bericht ten onrechte ontvangt, wordt u geacht de inhoud niet te > ? gebruiken, de afzender direct te informeren en het bericht te vernietigen. > ? Aan dit bericht kunnen geen rechten of plichten worden ontleend. > > ? ---------------------------------------------------------------------------- > ? ---------------------------- > > ? Disclaimer: The information contained in this message may be confidential > ? and is intended to be exclusively for the addressee. Should you receive this > ? message unintentionally, you are expected not to use the contents herein, to > ? notify the sender immediately and to destroy the message. No rights can be > ? derived from this message. > > > ? P Please consider the environment before printing this email > > ______________________________________________ > 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. > >