CODEWITHSUNDEEP
rreshape
user3664020
user3664020

Reputation: 3020

count unique values of a column for a given column in r

I have a data frame like

col1 col2  col3
A      2    b1 
A      3    b2
A      2    b2
A      2    b1
A      3    b2

I want to get the count of unique values of col3 for each combination of col1 and col2 as following

col1  col2 count_unique
 A       2         2
 A       3         1

What is the best one line solution to this?

Upvotes: 1

Views: 1803

Answers (2)

mpalanco
mpalanco

Reputation: 13570

Other two options:

plyr

library(plyr)
count(unique(df), vars = c("col1", "col2"))

Output:

 col1 col2 freq
1    A    2    2
2    A    3    1

sqldf

library(sqldf)
sqldf("SELECT col1, col2, COUNT(DISTINCT(col3)) n 
      FROM df GROUP BY col1, col2")

Output:

  col1 col2 n
1    A    2 2
2    A    3 1

Upvotes: 1

maj
maj

Reputation: 2529

As @Frank and @akrun pointed out in their comments, there are several possible solutions to your question - here are three of the most used ones:

in base R:

aggregate(col3~., df, function(x) length(unique(x)) )

using the data.table package (v1.9.5 and higher):

setDT(df)[, uniqueN(col3), by=.(col1,col2)]

using the dplyr package:

df %>% group_by(col1, col2) %>% summarise(col3=n_distinct(col3))

Upvotes: 3

Related Questions