How to count instances per ID per day in R?

I want to count all the unique ID's per Date in R. However, I don't know how I can achieve this.

Currently I have a dataset that looks like this:

ID DateTime
2  2019-10-6
4  2019-10-6
3  2019-10-6
4  2019-10-6
3  2019-10-9
2  2019-10-9
2  2019-10-9

From this, I tried to get a dataframe to get the following result:

ID DateTime    Count
2  2019-10-6   1
3  2019-10-6   1
4  2019-10-6   2
2  2019-10-9   2
3  2019-10-9   1

However, I tried many variations working with aggregate aggregate functions, but whenever I try them I get errors like the following:

  Error in UseMethod("summarise_") : 
  no applicable method for 'summarise_' applied to an object of class "Date" 

My R is a little bit rusty, but I have no idea how to achieve this and searching the internet didn't help either.

Thanks in advance!


Easy solution with dplyr:

library(dplyr)

dates <- c("2019-10-6","2019-10-6","2019-10-6","2019-10-6","2019-10-9","2019-10-9","2019-10-9")
id <- c(2,4,3,4,3,2,2)

dat<-tibble(id,dates)

dat %>%
  count(id,dates)

Counting and aggregating in R 24 September 2012 r A short post about counting and aggregating in R, because I learned a couple of things while improving the work I did earlier in the year about analyzing reference desk statistics.


And this can (of course) also be done the data.table way

dt <- fread("ID DateTime
2  2019-10-6
4  2019-10-6
3  2019-10-6
4  2019-10-6
3  2019-10-9
2  2019-10-9
2  2019-10-9")

dt[, .( count = .N), by = .(ID, DateTime)][]

#    ID  DateTime count
# 1:  2 2019-10-6     1
# 2:  4 2019-10-6     2
# 3:  3 2019-10-6     1
# 4:  3 2019-10-9     1
# 5:  2 2019-10-9     2

In my dataset an observation is a firm (f_id), product (p_id), country (c_id). I want to count the number of products per firm (regardless of how many countries it is shipped). One solution is to create a firm-product identifier, then drop all duplicates and count firm-product observations per firm using "collapse" .


aggregate is not that difficult to use, just remember that you need something to aggregate, in the case below, the data frame's row names. I coerce the dates column to class "Date".

dates <- as.Date(c("2019-10-6","2019-10-6","2019-10-6","2019-10-6","2019-10-9","2019-10-9","2019-10-9"))
id <- c(2,4,3,4,3,2,2)

dat<-data.frame(id,dates)
aggregate(row.names(dat) ~ id + dates, dat, length)
#  id      dates row.names(dat)
#1  2 2019-10-06              1
#2  3 2019-10-06              1
#3  4 2019-10-06              2
#4  2 2019-10-09              2
#5  3 2019-10-09              1

frequency of a variable per column with R. Sep 15, 2014. Count the number of times a certain value occurs in each column of a data frame. Imagine a set of columns that work like a set of tick boxes, for each row they can show true or false, 0 or 1, cat or dog or zebra etc.


If you have Kutools for Excel installed, you can apply its utilities to count the number of occurrences per year/month/weekday/day, or year and month, or month and day, etc. Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now. 1.


ID EquipmentName Checkout CheckIn. 1 Equipment1 1/1/2017 1/5/2017. 2 Equipment2 1/1/2017 1/3/2017. 1 Equipment1 1/5/2017 1/7/2017 . I would like to show the total counts per day. Is there anyway to do this with the data formatted in above manner? ie . 1/1 - 1/3 = 2 per day. 1/3 - 1/7 = 1 per day . Thanks!


SQL COUNT(*) with ORDER BY clause example. You can use the COUNT(*) function in the ORDER BY clause to sort the number of rows per group. For example, the following statement gets the number of employees for each department and sorts the result set based on the number of employees in descending order.