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.


1 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 :

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 package:

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

or using package:

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

or :

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 pipingpersonal opinion.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.