Group by all columns in a data.table

user_7346393 0
Viewed 124

I'm working with iris data.table in R.

To remind how it looks I paste six five rows here

   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1:          5.1         3.5          1.4         0.2  setosa
2:          4.9         3.0          1.4         0.2  setosa
3:          4.7         3.2          1.3         0.2  setosa
4:          4.6         3.1          1.5         0.2  setosa
5:          5.0         3.6          1.4         0.2  setosa
6:          5.4         3.9          1.7         0.4  setosa

I would like to calculate the number of rows, grouped by all columns. Of course we may write all variables in by, like this:

iris[, .(Freq = .N), by = .(Sepal.Length, Sepal.Width, Petal.Length, Petal.Width, Species)]



   Sepal.Length Sepal.Width Petal.Length Petal.Width Species Freq
1:          5.1         3.5          1.4         0.2  setosa    1
2:          4.9         3.0          1.4         0.2  setosa    1
3:          4.7         3.2          1.3         0.2  setosa    1
4:          4.6         3.1          1.5         0.2  setosa    1
5:          5.0         3.6          1.4         0.2  setosa    1
6:          5.4         3.9          1.7         0.4  setosa    1

However, I wonder if there is a method to group by all variables without needing to type all the columns names?

data.table r
3 Answers

In case you are looking for duplicates, uniqueN will default to using all columns:

uniqueN(as.data.table(iris))
# [1] 149

This doesn't answer your question directly, but it might be a more direct way of accomplishing what you were trying to do in the first place.

Similarly, if you're looking for which rows are duplicated, you can use duplicated's data.table method which similarly defaults to using all columns:

iris[duplicated(iris)]
#    Sepal.Length Sepal.Width Petal.Length Petal.Width   Species
# 1:          5.8         2.7          5.1         1.9 virginica
user_35769840

We can use

library(data.table)
out1 <- as.data.table(iris)[, .N, by = names(iris)]

-checking with OP's approach

out2 <-  as.data.table(iris)[,  .N, by = .(Sepal.Length, 
      Sepal.Width, Petal.Length, Petal.Width, Species)]
identical(out1, out2)
#[1] TRUE
user_37322710

Here is an approach in Base-R 

Freq <- table(apply(iris,1,paste0, collapse=" "))
iris$Freq <- apply(iris,1, function(x) Freq[names(Freq) %in% paste0(x,collapse=" ")])

output:

> iris
    Sepal.Length Sepal.Width Petal.Length Petal.Width    Species Freq
...          ...         ...          ...         ...  ...        ...
140          6.9         3.1          5.4         2.1  virginica    1
141          6.7         3.1          5.6         2.4  virginica    1
142          6.9         3.1          5.1         2.3  virginica    1
143          5.8         2.7          5.1         1.9  virginica    2
144          6.8         3.2          5.9         2.3  virginica    1
145          6.7         3.3          5.7         2.5  virginica    1
user_104424230
Related