How to do Countifs in R

Question

Data:

set.seed(42)
df1 = data.frame(
  Date = seq.Date(as.Date("2018-01-01"),as.Date("2018-01-30"),1),
  value = sample(1:30),
  Y = sample(c("yes", "no"), 30, replace = TRUE)
)

df2 = data.frame(
  Date = seq.Date(as.Date("2018-01-01"),as.Date("2018-01-30"),7)
)

For sum if data falls within range this works (from my previous question):

library(data.table)

df1$start <- df1$Date
df1$end <- df1$Date

df2$start <- df2$Date
df2$end <- df2$Date + 6

setDT(df1, key = c("start", "end"))
setDT(df2, key = c("start", "end"))

d = foverlaps(df1, df2)[, list(mySum = sum(value)), by = Date ]

How can I do countif ?

because when I try

d = foverlaps(df1, df2)[, list(mySum = count(value)), by = Date ]

I get error

no applicable method for 'groups' applied to an object of class "c('double', 'numeric')"

Where is that `count` function from? `dplyr`? Related, possible duplicate: https://stackoverflow.com/questions/9809166/count-number-of-rows-within-each-group — zx8754, Mar 27 '19 at 08:49

score 3 · Answer 1 · answered Mar 27 '19 at 08:45

3

We can use .N:

foverlaps(df1, df2)[, list(myCount = .N), by = Date ]
#          Date myCount
# 1: 2018-01-01       7
# 2: 2018-01-08       7
# 3: 2018-01-15       7
# 4: 2018-01-22       7
# 5: 2018-01-29       2

answered Mar 27 '19 at 08:45

zx8754

52,746
12
114
209

score 2 · Accepted Answer · answered Mar 27 '19 at 08:44

2

d = foverlaps(df1, df2)[, .N, by = Date]

answered Mar 27 '19 at 08:44

lypskee

342
1
11

score 2 · Answer 3 · answered Mar 27 '19 at 08:49

If you want to count the number of rows per Date, you can try .N

foverlaps(df1, df2)[, .(mysum = .N), by = Date ]
         Date mysum
1: 2018-01-01     7
2: 2018-01-08     7
3: 2018-01-15     7
4: 2018-01-22     7
5: 2018-01-29     2

If you want the count of unique values per Date you can try uniqueN()

foverlaps(df1, df2)[, .(mysum = uniqueN(value)), by = Date ]
         Date mysum
1: 2018-01-01     7
2: 2018-01-08     7
3: 2018-01-15     7
4: 2018-01-22     7
5: 2018-01-29     2

Both .N and uniqueN() are from {data.table}.

score 1 · Answer 4 · answered Mar 27 '19 at 08:37

1

Instead of list(mySum = count(value)) try c(mySum = count(value)). The Code runs for me then.

d2 <-  foverlaps(df1, df2)[, c(mySum = count(value)), by = Date ]

answered Mar 27 '19 at 08:37

Julian_Hn

2,086
1
8
18

Runs for me without problems. What version of data.table are you using? – Julian_Hn Mar 27 '19 at 08:40
latest version. – CapaNif Mar 27 '19 at 08:42
Ah. I see my problem, you are using count() from dplyr while I had the plyr count() loaded. With count() from plyr it works. Please add all necessary packages for the example to work next time – Julian_Hn Mar 27 '19 at 08:44

How to do Countifs in R

4 Answers4