Hello R-users, I've got a huge table with about 20.00 rows and 50 columns. The table now has headers as Members1, Members2 etc. My data are 8 different individuals. And I've got a column with clusters. So each individual belongs to different clusters and can occurs multiple times within a cluster (that's the reason that there can be more than 8 members). I want a presence/ absence table for each individual within each cluster. So I want to go from: Cluster Member1 Member2 etc. 1 ind1 ind2 2 ind3 ind1 3 ind2 ind1 to cluster ind1 ind2 ind3 1 1 1 0 2 1 0 1 3 1 1 0 Has anybody any idea how I can do this? I already tried alot of things with pivottables (using cast()) But I think I'm missing out on something. thank you -- View this message in context: http://r.789695.n4.nabble.com/pivot-table-help-tp4155144p4155144.html Sent from the R help mailing list archive at Nabble.com.
pivot table help
5 messages · set, Richard M. Heiberger, jim holtman
An embedded and charset-unspecified text was scrubbed... Name: not available URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20111203/68ac59e1/attachment.pl>
try this:
x <- read.table(text = "Cluster Member1 Member2
+ 1 ind1 ind2 + 2 ind3 ind1 + 3 ind2 ind1", as.is = TRUE, header = TRUE)
require(reshape2) x.m <- melt(x, id = "Cluster") x.m
Cluster variable value 1 1 Member1 ind1 2 2 Member1 ind3 3 3 Member1 ind2 4 1 Member2 ind2 5 2 Member2 ind1 6 3 Member2 ind1
table(x.m$Cluster, x.m$value)
ind1 ind2 ind3 1 1 1 0 2 1 0 1 3 1 1 0
On Sat, Dec 3, 2011 at 5:53 PM, Richard M. Heiberger <rmh at temple.edu> wrote:
Pivot tables are an Excel concept, not an R concept.
That means you must give an example of your starting pivot table as an R
object (use dump() so we can pick it up from the email and execute it
immediately).
and an example of the R object you want as the result.
Use a trivial but complete example.
An example of dump
tmp <- matrix(1:6,2,3)
tmp
dump("tmp","")
Be sure to read the posting guide before sending your revised request.
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. Rich On Sat, Dec 3, 2011 at 5:27 PM, set <astareh at hotmail.com> wrote: Hello R-users, I've got a huge table with about 20.00 rows and 50 columns. The table now has headers as Members1, Members2 etc. My data are 8 different individuals. And I've got a column with clusters. So each individual belongs to different clusters and can occurs multiple times within a cluster (that's the reason that there can be more than 8 members). I want a presence/ absence table for each individual within each cluster. So I want to go from: Cluster ? Member1 ? ?Member2 ?etc. 1 ? ? ? ? ? ?ind1 ? ? ? ? ? ind2 2 ? ? ? ? ? ?ind3 ? ? ? ? ? ind1 3 ? ? ? ? ? ?ind2 ? ? ? ? ? ind1 to cluster ? ?ind1 ? ? ? ? ?ind2 ? ? ?ind3 1 ? ? ? ? ? ?1 ? ? ? ? ? ? ? 1 ? ? ? ? ?0 2 ? ? ? ? ? ?1 ? ? ? ? ? ? ? ?0 ? ? ? ? 1 3 ? ? ? ? ? ?1 ? ? ? ? ? ? ? 1 ? ? ? ? ?0 Has anybody any idea how I can do this? I already tried alot of things with pivottables (using cast()) But I think I'm missing out on something. thank you -- View this message in context: http://r.789695.n4.nabble.com/pivot-table-help-tp4155144p4155144.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<http://www.r-project.org/posting-guide.html> and provide commented, minimal, self-contained, reproducible code. ? ? ? ?[[alternative HTML version deleted]] ______________________________________________ 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.
Jim Holtman Data Munger Guru What is the problem that you are trying to solve? Tell me what you want to do, not how you want to do it.
Forgot, you can also do this:
dcast(x.m, Cluster ~ value, fun = length)
Cluster ind1 ind2 ind3 1 1 1 1 0 2 2 1 0 1 3 3 1 1 0
On Sat, Dec 3, 2011 at 7:02 PM, jim holtman <jholtman at gmail.com> wrote:
try this:
x <- read.table(text = "Cluster ? Member1 ? ?Member2
+ 1 ? ? ? ? ? ?ind1 ? ? ? ? ? ind2 + 2 ? ? ? ? ? ?ind3 ? ? ? ? ? ind1 + 3 ? ? ? ? ? ?ind2 ? ? ? ? ? ind1", as.is = TRUE, header = TRUE)
require(reshape2) x.m <- melt(x, id = "Cluster") x.m
?Cluster variable value 1 ? ? ? 1 ?Member1 ?ind1 2 ? ? ? 2 ?Member1 ?ind3 3 ? ? ? 3 ?Member1 ?ind2 4 ? ? ? 1 ?Member2 ?ind2 5 ? ? ? 2 ?Member2 ?ind1 6 ? ? ? 3 ?Member2 ?ind1
table(x.m$Cluster, x.m$value)
? ?ind1 ind2 ind3 ?1 ? ?1 ? ?1 ? ?0 ?2 ? ?1 ? ?0 ? ?1 ?3 ? ?1 ? ?1 ? ?0
On Sat, Dec 3, 2011 at 5:53 PM, Richard M. Heiberger <rmh at temple.edu> wrote:
Pivot tables are an Excel concept, not an R concept.
That means you must give an example of your starting pivot table as an R
object (use dump() so we can pick it up from the email and execute it
immediately).
and an example of the R object you want as the result.
Use a trivial but complete example.
An example of dump
tmp <- matrix(1:6,2,3)
tmp
dump("tmp","")
Be sure to read the posting guide before sending your revised request.
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. Rich On Sat, Dec 3, 2011 at 5:27 PM, set <astareh at hotmail.com> wrote: Hello R-users, I've got a huge table with about 20.00 rows and 50 columns. The table now has headers as Members1, Members2 etc. My data are 8 different individuals. And I've got a column with clusters. So each individual belongs to different clusters and can occurs multiple times within a cluster (that's the reason that there can be more than 8 members). I want a presence/ absence table for each individual within each cluster. So I want to go from: Cluster ? Member1 ? ?Member2 ?etc. 1 ? ? ? ? ? ?ind1 ? ? ? ? ? ind2 2 ? ? ? ? ? ?ind3 ? ? ? ? ? ind1 3 ? ? ? ? ? ?ind2 ? ? ? ? ? ind1 to cluster ? ?ind1 ? ? ? ? ?ind2 ? ? ?ind3 1 ? ? ? ? ? ?1 ? ? ? ? ? ? ? 1 ? ? ? ? ?0 2 ? ? ? ? ? ?1 ? ? ? ? ? ? ? ?0 ? ? ? ? 1 3 ? ? ? ? ? ?1 ? ? ? ? ? ? ? 1 ? ? ? ? ?0 Has anybody any idea how I can do this? I already tried alot of things with pivottables (using cast()) But I think I'm missing out on something. thank you -- View this message in context: http://r.789695.n4.nabble.com/pivot-table-help-tp4155144p4155144.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<http://www.r-project.org/posting-guide.html> and provide commented, minimal, self-contained, reproducible code. ? ? ? ?[[alternative HTML version deleted]] ______________________________________________ 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.
-- Jim Holtman Data Munger Guru What is the problem that you are trying to solve? Tell me what you want to do, not how you want to do it.
Jim Holtman Data Munger Guru What is the problem that you are trying to solve? Tell me what you want to do, not how you want to do it.
Thank you very much! works perfectly! -- View this message in context: http://r.789695.n4.nabble.com/pivot-table-help-tp4155144p4156393.html Sent from the R help mailing list archive at Nabble.com.