R equivalent of SQL SELECT COUNT(*) ... GROUP BY

10,274

Solution 1

aggregate is very handy in this situation

> aggregate(data.frame(count = test_vec), list(value = test_vec), length)

  value count
1     1    10
2     2     7
3     3     4

Solution 2

you can use table

table(test_vec)
test_vec
 1  2  3 
10  7  4 

Solution 3

You can use data.table package as well to count the number of elements in each group.

library(data.table)
as.data.table(x = test_vec)[, .N, by=x]
#   x  N
#1: 1 10
#2: 2  7
#3: 3  4
#4: 4  3

.N is a special in-built variable and is a length-1 integer. It holds the number of observations in each group.

Solution 4

The dplyr approach:

test_vec = c(1,2,3,1,2,1,2,1,1,1,2,1,3,1,2,3,2,1,2,1,3)
library(dplyr)
df <- data_frame(test_vec)

df %>% 
    count(test_vec)

# Alternative that shows group_by
df %>%
    group_by(test_vec) %>%
    summarise(n = n()) # or tally()

#   test_vec  n
# 1        1 10
# 2        2  7
# 3        3  4

Solution 5

to the second part of your question

> which(test_vec == 4)

[1] 22 23 24  # gives you their position in the vector in order to "identify" them

> sum(test_vec == 4) 

[1] 3 # counts the 4's in the vector

edit: as we mention everything here,

tapply(test_vec, test_vec, length)

would also work

 1  2  3 
10  7  4
Share:
10,274
Escher
Author by

Escher

Updated on July 13, 2022

Comments

  • Escher
    Escher over 1 year

    I'm trying to find how to count the number of integers of each type in a vector. Eg, how many 1, 2, and 3 are there (without hard-coding == 1,2,3):

    test_vec = c(1,2,3,1,2,1,2,1,1,1,2,1,3,1,2,3,2,1,2,1,3)
    

    And, how to identify that I added some 4s to the vector and count them?

    test_vec = c(test_vec,4,4,4)
    

    I could do this with range() and a loop, but wondered if there is a general vectorised solution?

    Edit: not the same question as this because that question doesn't ask about a generalised table situation (though the answers sensibly suggests it) but rather checking hard-coded equality sum(test_vec==x)