I have a table with two columns: A 1 A 1 A 2 B 2 C 0 I would like to produce a third column that contains the counts of each unique combination of col1 and col2: A 1 2 A 1 2 A 2 1 B 2 1 C 0 1 How can I do this in R? Thanks in advance ... -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
Hi Chris, This is a very rough first conceptual program you could use --
double check all the syntax as I know it won't work at first go. It's
untested but can be jiggered to work! Good luck! Anders
Use unique() to get your unique combinations, then loop or vectorize
through each unique combination to count the combos.
unique(data)->x
thirdcol<-NULL
for(i in 1:dim(data)[1]){
for(k in 1:dim(x)[1]{
thirdcol<-cbind(thirdcol,dim(data[data[i,] %in% x[k,]])[1])
}}
newdata<-cbind(data,thirdcol)
On Sun, 9 Dec 2007, christopher snow wrote:
> I have a table with two columns:
>
> A 1
> A 1
> A 2
> B 2
> C 0
>
> I would like to produce a third column that contains the counts of each
> unique combination of col1 and col2:
>
> A 1 2
> A 1 2
> A 2 1
> B 2 1
> C 0 1
>
> How can I do this in R?
>
> Thanks in advance ...
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
> ______________________________________________
> 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.
>
One way is to use the following functions: ?unique ?aggregate ?merge For example,> x.df <- data.frame(Group=LETTERS[c(1, 1, 1, 2, 3)], Value=c(1, 1, 2, 2,0))> x.agg <- aggregate(x$Value, list(Group=x$Group),function(x){length(unique(x))})> x.aggGroup x 1 A 2 2 B 1 3 C 1> x.merged <- merge(x.df, x.agg, by="Group") > x.mergedGroup Value x 1 A 1 2 2 A 1 2 3 A 2 2 4 B 2 1 5 C 0 1>On Dec 9, 2007 1:35 AM, christopher snow <snowch@coralms.com> wrote:> I have a table with two columns: > > A 1 > A 1 > A 2 > B 2 > C 0 > > I would like to produce a third column that contains the counts of each > unique combination of col1 and col2: > > A 1 2 > A 1 2 > A 2 1 > B 2 1 > C 0 1 > > How can I do this in R? > > Thanks in advance ... > > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > > ______________________________________________ > 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 > and provide commented, minimal, self-contained, reproducible code. >-- =====================================T.K. (Tae-kyun) Kim Ph.D. student Department of Marketing Marshall School of Business University of Southern California ===================================== [[alternative HTML version deleted]]
split the data frame, cbind the count and unsplit back:
DF <- data.frame(A = c("A", "A", "A",
"B", "C"), B = c(1, 1, 2, 2, 0))
> g <- paste(DF$A, DF$B)
> s <- split(DF, g)
> u <- lapply(s, function(x) cbind(x, Occurs = nrow(x)))
> unsplit(u, g)
A B Occurs
1 A 1 2
2 A 1 2
3 A 2 1
4 B 2 1
5 C 0 1
See ?split where there is a very similar example.
On Dec 9, 2007 4:35 AM, christopher snow <snowch at coralms.com>
wrote:> I have a table with two columns:
>
> A 1
> A 1
> A 2
> B 2
> C 0
>
> I would like to produce a third column that contains the counts of each
> unique combination of col1 and col2:
>
> A 1 2
> A 1 2
> A 2 1
> B 2 1
> C 0 1
>
> How can I do this in R?
>
> Thanks in advance ...
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
> ______________________________________________
> 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.
>
?ave> xV1 V2 1 A 1 2 A 1 3 A 2 4 B 2 5 C 0> x$len <- ave(seq_along(x$V1), x$V1, x$V2, FUN=length) > xV1 V2 len 1 A 1 2 2 A 1 2 3 A 2 1 4 B 2 1 5 C 0 1>On Dec 9, 2007 1:35 AM, christopher snow <snowch at coralms.com> wrote:> I have a table with two columns: > > A 1 > A 1 > A 2 > B 2 > C 0 > > I would like to produce a third column that contains the counts of each > unique combination of col1 and col2: > > A 1 2 > A 1 2 > A 2 1 > B 2 1 > C 0 1 > > How can I do this in R? > > Thanks in advance ... > > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > > ______________________________________________ > 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 Cincinnati, OH +1 513 646 9390 What is the problem you are trying to solve?