Count and summarise ID's each day while creating a new column that shows the accumulated ID's

Question

I have two column, first one being the ID of a customer and second one being the Date of purchase.

    ID       Date
     1 2017-01-17
     2 2017-01-17
     3 2017-01-17
     4 2017-01-17
     5 2017-01-17
     1 2017-01-17
     7 2017-01-17
     1 2017-01-17
     9 2017-01-18
     2 2017-01-18
     3 2017-01-18
     5 2017-01-18
     1 2017-01-18
     2 2017-01-18

I would like to summarise the Purchases made by a Customer at one day and create a third column that shows the amount of purchases for the customer on that date.

Answer 1

This is a base solution for your problem:

aggregate(paste(ID , Date) ~ ID + Date, data = df, FUN = length)

there are many more solutions like any of the ones below, using dplyr:

library(dplyr)
df %>%  group_by(ID, Date) %>% summarise(PurchaseCount = n())
df %>% group_by(ID, Date) %>% tally(name="PurchaseCount")
df %>% group_by(ID, Date) %>% count(name="PurchaseCount")
df %>% group_by(ID, Date) %>% add_tally(name="PurchaseCount")
df %>% group_by(ID, Date) %>% add_count(name="PurchaseCount")

or by using data.table package:

library(data.table)
setDT(df)[, PurchaseCount:=.N, by = list(ID, Date)]

or using sqldf package:

library(sqldf)
sqldf("SELECT ID, Date, COUNT(*) as PurchaseCount
       FROM df
       GROUP BY Date, ID")

or plyr:

plyr::count(df, c('ID','Date'))

I personally prefer data.table as it is directly writes to the dataframe and often is time efficient. aggregate is also favorable when you wanna avoid loading new libraries. dplyr generally make your code more legible as it uses piping^{personal opinion}.