CODEWITHSUNDEEP
r
firmo23
firmo23

Reputation: 8454

Summarize value based on other column based on specific data range

I have the dataset below:

name<-c("John","John","John","John","John","John","John")
Dealer<-c("ASD","ASD","ASD","ASD","ASD","ASD","ASD")
Date<-c("2020-01-03","2020-01-04","2020-01-05","2020-01-06","2020-01-07","2020-01-08","2020-01-09")
dataset<-data.frame(name,Dealer,Date)

and I want to create a dataframe which summarizes by Dealer showing previous day number of name and last 7 days number of name based on the fact that we are in the most recent date ("2020-01-09"). So the new dataframe will be like:

Dealer2 PreviousDay PreviousWeek
     ASD           1            7

Upvotes: 0

Views: 39

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 389265

We can arrange the data based on Date and count number of entries we have for second last day and number of entries we have for last 7 days for each Dealer. 

library(dplyr)

dataset %>%
  mutate(Date = as.Date(Date)) %>%
  arrange_all %>%
  group_by(Dealer) %>%
  summarise(PreviousDay = sum(Date == last(Date) - 1), 
            PreviousWeek = sum(Date %in% (last(Date) - 7) : last(Date)))

# A tibble: 1 x 3
#  Dealer PreviousDay PreviousWeek
#  <fct>        <int>        <int>
#1 ASD              1            7

Upvotes: 1

Related Questions