Conditional count and group by in R

Question

I would like to count how many rows there are per type if they meet the condition x == 0. Sort of like a group by in SQL

Here is an example of the data

  type    x    
search    0 
NULL      0 
public    0
search    1
home      0
home      1
search    0

Could you include a data example with R code? I'm struggling to understand what you're asking for and how your example fits in... — CephBirk, Sep 25 '14 at 15:33
When including sample input, also include desired output. See [how to make a reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) — MrFlick, Sep 25 '14 at 15:35

score 11 · Accepted Answer · answered Sep 25 '14 at 15:43

I am assuming that you want to find the number of rows when a particular condition (when a variable is having some value) is met.

If this is the case, then I suppose you have "x" as a variable represented in a column. "x" can take multiple values. Suppose you want to find how many rows are there in your data when x is 0. This could be done by:

nrow(subset(data, x=="0")

'data' is the object name for your dataset in R

EDIT:

I am seeing your edited dataframe now. You could use this to solve your problem:

table(data$type, data$x)

i would like to count how often one variable (tasks) occurs in one month(month is a variable from 1 to 12), how would i do that? — Amy, May 26 '20 at 19:42

score 6 · Answer 2 · answered Sep 26 '14 at 02:14

You could also use the sqldf package:

library(sqldf)
df <- data.frame(type=c('search','NULL','public','search','home','home','search'),x=c(0,0,0,1,0,1,0))
sqldf("SELECT type, COUNT(*) FROM df WHERE x=0 GROUP BY type")

which gives the following result:

    type COUNT(*)
1   NULL        1
2   home        1
3 public        1
4 search        2

score 4 · Answer 3 · answered Sep 25 '14 at 16:01

4

You could also do this with the dplyr package:

library(dplyr)

df2 <- df %>% group_by(x,type) %>% tally()

which gives:

  x   type n
1 0   home 1
2 0   NULL 1
3 0 public 1
4 0 search 2
5 1   home 1
6 1 search 1

answered Sep 25 '14 at 16:01

Jaap

81,064
34
182
193

CephBirk · Answer 4 · 2014-09-25T15:48:40.720

2

Given the data frame, df=data.frame(type=c('search','NULL','public','search','home','home','search'),x=c(0,0,0,1,0,1,0))

If you want to know how many of each value in column 1 have a value in column 2 of zero then you can use: table(df)[,1]

as long as you are only working with 1's and 0's to get the answer:

  home   NULL public search 
     1      1      1      2

edited Sep 25 '14 at 15:48

answered Sep 25 '14 at 15:41

CephBirk

6,422
5
56
74

score 2 · Answer 5 · answered Apr 21 '16 at 09:34

Given your data is structured as a data frame, the following code has a better running time than the answers given above:

nrow(data[data$x=="0"])

You can test your run time using:

ptm <- proc.time()
nrow(subset(data, x == "0"))
proc.time() - ptm

ptm <- proc.time()
nrow(data[data$x=="0"]))
proc.time() - ptm

In my case, the running time was about 15 times faster, with 1 million rows.

Conditional count and group by in R

5 Answers5

Linked