CODEWITHSUNDEEP
rdataframecountapply
Bulls
Bulls

Reputation: 75

Count of unique values across all columns in a data frame

We have a data frame as below : 

raw<-data.frame(v1=c("A","B","C","D"),v2=c(NA,"B","C","A"),v3=c(NA,"A",NA,"D"),v4=c(NA,"D",NA,NA))

I need a result data frame in the following format : 

result<-data.frame(v1=c("A","B","C","D"), v2=c(3,2,2,3))

Used the following code to get the count across one particular column :

count_raw<-sqldf("SELECT DISTINCT(v1) AS V1, COUNT(v1) AS count FROM raw GROUP BY v1")

This would return count of unique values across an individual column.

Any help would be highly appreciated.

Upvotes: 3

Views: 2287

Answers (4)

akrun
akrun

Reputation: 887831

We can use apply with MARGIN = 1

cbind(raw[1], v2=apply(raw, 1, function(x) length(unique(x[!is.na(x)]))))

If it is for each column

sapply(raw, function(x) length(unique(x[!is.na(x)])))

Or if we need the count based on all the columns, convert to matrix and use the table

table(as.matrix(raw))
#  A B C D 
#  3 2 2 3

Upvotes: 1

user2100721
user2100721

Reputation: 3597

Use this

table(unlist(raw))

Output

A B C D 
3 2 2 3

For data frame type output wrap this with as.data.frame.table

as.data.frame.table(table(unlist(raw)))

Output

   Var1 Freq
1    A    3
2    B    2
3    C    2
4    D    3

Upvotes: 12

Virag Swami
Virag Swami

Reputation: 197

If you have only character values in your dataframe as you've provided, you can unlist it and use unique or to count the freq, use count

> library(plyr)
> raw<-data.frame(v1=c("A","B","C","D"),v2=c(NA,"B","C","A"),v3=c(NA,"A",NA,"D"),v4=c(NA,"D",NA,NA))
> unique(unlist(raw))
[1] A    B    C    D    <NA>
Levels: A B C D
> count(unlist(raw))
     x freq
1    A    3
2    B    2
3    C    2
4    D    3
5 <NA>    6

Upvotes: 1

Sotos
Sotos

Reputation: 51592

If you want a total count,

sapply(unique(raw[!is.na(raw)]), function(i) length(which(raw == i)))
#A B C D 
#3 2 2 3

Upvotes: 3

Related Questions