Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

r - Count number of unique rows based on two columns, by group

I have a data.table in r

    col1 col2 col3   col4
 1:  5.1  3.5  1.4 setosa
 2:  5.1  3.5  1.4 setosa
 3:  4.7  3.2  1.3 setosa
 4:  4.6  3.1  1.5 setosa
 5:  5.0  3.6  1.4 setosa
 6:  5.1  3.5  3.4    eer
 7:  5.1  3.5  3.4    eer
 8:  5.1  3.2  1.3    eer
 9:  5.1  3.5  1.5    eer
10:  5.1  3.5  1.4    eer


DT <- structure(list(col1 = c(5.1, 5.1, 4.7, 4.6, 5, 5.1, 5.1, 5.1, 
5.1, 5.1), col2 = c(3.5, 3.5, 3.2, 3.1, 3.6, 3.5, 3.5, 3.2, 3.5, 
3.5), col3 = c(1.4, 1.4, 1.3, 1.5, 1.4, 3.4, 3.4, 1.3, 1.5, 1.4
), col4 = structure(c(1L, 1L, 1L, 1L, 1L, 4L, 4L, 4L, 4L, 4L), .Label = c("setosa", 
"versicolor", "virginica", "eer"), class = "factor")), .Names = c("col1", 
"col2", "col3", "col4"), row.names = c(NA, -10L), class = c("data.table", 
"data.frame"))

I want to count unique (distinct) combinations of col1 and col2 for each value of col4.

Expected output is

   col1 col2 col3   col4 count
 1:  5.1  3.5  1.4 setosa     4
 2:  5.1  3.5  1.4 setosa     4
 3:  4.7  3.2  1.3 setosa     4
 4:  4.6  3.1  1.5 setosa     4
 5:  5.0  3.6  1.4 setosa     4
 6:  5.1  3.5  3.4    eer     2
 7:  5.1  3.5  3.4    eer     2
 8:  5.1  3.2  1.3    eer     2
 9:  5.1  3.5  1.5    eer     2
10:  5.1  3.5  1.4    eer     2

How can I do this in 1 data.table syntax only?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

I had to go through a few attempts first, and ended up with this. Any good?

DT[, count:=nrow(unique(.SD)), by=col4, .SDcols=c("col1","col2")]
DT
    col1 col2 col3   col4 count
 1:  5.1  3.5  1.4 setosa     4
 2:  5.1  3.5  1.4 setosa     4
 3:  4.7  3.2  1.3 setosa     4
 4:  4.6  3.1  1.5 setosa     4
 5:  5.0  3.6  1.4 setosa     4
 6:  5.1  3.5  3.4    eer     2
 7:  5.1  3.5  3.4    eer     2
 8:  5.1  3.2  1.3    eer     2
 9:  5.1  3.5  1.5    eer     2
10:  5.1  3.5  1.4    eer     2
> 

and the same but faster thanks to Procrastinatus comment below :

DT[, count:=uniqueN(.SD), by=col4, .SDcols=c("col1","col2")]

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...