CODEWITHSUNDEEP
rdatecount
Williams86
Williams86

Reputation: 321

How could I form date interval with counts in R?

I have a date variable called DATE as follows:

DATE
2019-12-31
2020-01-01
2020-01-05
2020-01-09
2020-01-25

I am trying to return a result that counts the number of times the date occur in a week considering the Week variable starts from the minimum of DATE variable. So it would look something like this:

Week   Count
 1       3
 2       1
 3       0 
 4       1

Thanks in advance.

Upvotes: 0

Views: 86

Answers (2)

reusen
reusen

Reputation: 511

From base R

dates <- c('2019-12-31','2020-01-01','2020-01-05','2020-01-09','2020-01-25')
weeks <- strftime(dates, format = "%V")
table(weeks)

Upvotes: 2

Ronak Shah
Ronak Shah

Reputation: 389235

We subtract DATE values with minimum DATE value to get the difference in days between DATES. We divide the difference by 7 to get it in weeks and count it. We then use complete to fill the missing week information.

df %>%
  dplyr::count(week = floor(as.integer(DATE - min(DATE))/7) + 1) %>%
  tidyr::complete(week = min(week):max(week), fill = list(n = 0))

#   week     n
#  <dbl> <dbl>
#1     1     3
#2     2     1
#3     3     0
#4     4     1

If your DATE column is not of date class, first run this :

df$DATE <- as.Date(df$DATE)

Upvotes: 1

Related Questions