How to get week numbers from dates?

Question

Looking for a function in R to convert dates into week numbers (of year) I went for week from package data.table. However, I observed some strange behaviour:

> week("2014-03-16") # Sun, expecting 11
[1] 11
> week("2014-03-17") # Mon, expecting 12
[1] 11
> week("2014-03-18") # Tue, expecting 12
[1] 12

Why is the week number switching to 12 on tuesday, instead of monday? What am I missing? (Timezone should be irrelevant as there are just dates?!)

Other suggestions for (base) R functions are appreciated as well.

Newhall

Try format(as.Date("2014-03-16"), "%U") or format(as.Date("2014-03-16"), "%W") – Dmso

@Dmso thanks, but that returns 11 instead 12 for the following: format(as.Date("2014-03-17"), "%U") and format(as.Date("2014-03-17"), "%W") !? – Newhall

so, convert to integer and add 1. See ?strptime – Dmso

That's what I am doing right now, actually. I was just wondering, why I have to make this workaround? I would expect the week to begin on monday (EU) or sunday (US), but not on tuesday? – Newhall

Possible duplicate of as.Date produces unexpected result in a sequence of week-based dates – Amador

And this answer contains a comparison of the different week numbering functions from base R, lubridate, data.table, and ISOweekpackages. – Amador

@Amador I think you should move your nice answer over there to this more canonical Q&A – Adumbrate

@Adumbrate Thank you for your suggestion. I will do so as soon as time permits. – Amador

is it possible the other way arround? getting the start and finish date using the week number and the year? – Tameratamerlane

Answer 1

Base package

Using the function strftime passing the argument %V to obtain the week of the year as decimal number (01–53) as defined in ISO 8601. (More details in the documentarion: ?strftime)

strftime(c("2014-03-16", "2014-03-17","2014-03-18", "2014-01-01"), format = "%V")

Output:

[1] "11" "12" "12" "01"

Mccloskey

2014-01-01and 2014-12-29 will get both 01. – Colmar

@Colmar That is correct as defined in ISO 8601. If the week (starting on Monday) containing 1 January has four or more days in the new year, then it is considered week 1. You can double-check this in any of the iso 8601 week calculators online. – Mccloskey

It should be 2015-01-01, correct? How to fix this? – Signify

Answer 2

if you try with lubridate:

library(lubridate)
lubridate::week(ymd("2014-03-16", "2014-03-17","2014-03-18", '2014-01-01'))

[1] 11 11 12  1

The pattern is the same. Try isoweek

lubridate::isoweek(ymd("2014-03-16", "2014-03-17","2014-03-18", '2014-01-01'))
[1] 11 12 12  1

Queeniequeenly

?week (lubridate) states: Weeks is the number of complete seven day periods that have occured between the date and January 1st, plus one. – Newhall

@ChristianBorck isoweek is what you need? – Queeniequeenly

That looks good, but my lubridate (v 1.3.1) package seems to be missing the isoweek function? Which version do you use? – Newhall

@ChristianBorck I'm running lubridate_1.3.3 update it. – Queeniequeenly

Answer 3

I understand the need for packages in certain situations, but the base language is so elegant and so proven (and debugged and optimized).

Why not:

dt <- as.Date("2014-03-16")
dt2 <- as.POSIXlt(dt)
dt2$yday
[1] 74

And then your choice whether the first week of the year is zero (as in indexing in C) or 1 (as in indexing in R).

No packages to learn, update, worry about bugs in.

Judaea

I always try to solve problems with base R first. So, I am with you. But your answer misses to get the (calendar) week number I am looking for!? (dt2$yday-1)%/%7 +1 for example only works right, if January 1st was a monday. – Newhall

@ChristianBorck - Not to confuse things even further, but "right" depends on your definition of "week". The ISO-8601 standard defines a week to start on a Monday, although the week numbering depends on what day Jan 1 falls on. The week(...) function does not claim to use this standard. My point was that week(...) does not a appear to adhere to it's own definition. If you want ISO-8601 weeks (a good practice, by the way), use isoweek(...). – Crucifer

The above solution by user3229754 returns the day numbers starting with index=0 , I guess you can try : ( dt$yday ) %/%7 +1 – Eyler

Answer 4

Actually, I think you may have discovered a bug in the week(...) function, or at least an error in the documentation. Hopefully someone will jump in and explain why I am wrong.

Looking at the code:

library(lubridate)
> week
function (x) 
yday(x)%/%7 + 1
<environment: namespace:lubridate>

The documentation states:

Weeks is the number of complete seven day periods that have occured between the date and January 1st, plus one.

But since Jan 1 is the first day of the year (not the zeroth), the first "week" will be a six day period. The code should (??) be

(yday(x)-1)%/%7 + 1

NB: You are using week(...) in the data.table package, which is the same code as lubridate::week except it coerces everything to integer rather than numeric for efficiency. So this function has the same problem (??).

Crucifer

Answer 5

if you want to get the week number with the year use: "%Y-W%V":

e.g    yearAndweeks <- strftime(dates, format = "%Y-W%V")

so

> strftime(c("2014-03-16", "2014-03-17","2014-03-18", "2014-01-01"), format = "%Y-W%V")

becomes:

[1] "2014-W11" "2014-W12" "2014-W12" "2014-W01"

Darill

This is dangerous: strftime(c(as.Date("2014-01-01"),as.Date("2014-12-29")), format = "%Y-W%V") gives [1] "2014-W01" "2014-W01". – Colmar

This may be of some help: #49905070 – Darill

Answer 6

If you want to get the week number with the year, Grant Shannon's solution using strftime works, but you need to make some corrections for the dates around january 1st. For instance, 2016-01-03 (yyyy-mm-dd) is week 53 of year 2015, not 2016. And 2018-12-31 is week 1 of 2019, not of 2018. This codes provides some examples and a solution. In column "yearweek" the years are sometimes wrong, in "yearweek2" they are corrected (rows 2 and 5).

library(dplyr)
library(lubridate)

# create a testset
test <- data.frame(matrix(data = c("2015-12-31",
                                   "2016-01-03",
                                   "2016-01-04",
                                   "2018-12-30",
                                   "2018-12-31",
                                   "2019-01-01") , ncol=1, nrow = 6 ))
# add a colname
colnames(test) <- "date_txt"

# this codes provides correct year-week numbers
test <- test %>%
        mutate(date = as.Date(date_txt, format = "%Y-%m-%d")) %>%
        mutate(yearweek = as.integer(strftime(date, format = "%Y%V"))) %>%
        mutate(yearweek2 = ifelse(test = day(date) > 7 & substr(yearweek, 5, 6) == '01',
                                 yes  = yearweek + 100,
                                 no   = ifelse(test = month(date) == 1 & as.integer(substr(yearweek, 5, 6)) > 51,
                                               yes  = yearweek - 100,
                                               no   = yearweek)))
# print the result
print(test)

    date_txt       date yearweek yearweek2
1 2015-12-31 2015-12-31   201553    201553
2 2016-01-03 2016-01-03   201653    201553
3 2016-01-04 2016-01-04   201601    201601
4 2018-12-30 2018-12-30   201852    201852
5 2018-12-31 2018-12-31   201801    201901
6 2019-01-01 2019-01-01   201901    201901

Tahoe

Answer 7

I think the problem is that the week calculation somehow uses the first day of the year. I don't understand the internal mechanics, but you can see what I mean with this example:

library(data.table)

dd <- seq(as.IDate("2013-12-20"), as.IDate("2014-01-20"), 1)
# dd <- seq(as.IDate("2013-12-01"), as.IDate("2014-03-31"), 1)

dt <- data.table(i = 1:length(dd),
                 day = dd,
                 weekday = weekdays(dd),
                 day_rounded = round(dd, "weeks"))
## Now let's add the weekdays for the "rounded" date
dt[ , weekday_rounded := weekdays(day_rounded)]
## This seems to make internal sense with the "week" calculation
dt[ , weeknumber := week(day)]
dt 

    i        day   weekday day_rounded weekday_rounded weeknumber
1:  1 2013-12-20    Friday  2013-12-17         Tuesday         51
2:  2 2013-12-21  Saturday  2013-12-17         Tuesday         51
3:  3 2013-12-22    Sunday  2013-12-17         Tuesday         51
4:  4 2013-12-23    Monday  2013-12-24         Tuesday         52
5:  5 2013-12-24   Tuesday  2013-12-24         Tuesday         52
6:  6 2013-12-25 Wednesday  2013-12-24         Tuesday         52
7:  7 2013-12-26  Thursday  2013-12-24         Tuesday         52
8:  8 2013-12-27    Friday  2013-12-24         Tuesday         52
9:  9 2013-12-28  Saturday  2013-12-24         Tuesday         52
10: 10 2013-12-29    Sunday  2013-12-24         Tuesday         52
11: 11 2013-12-30    Monday  2013-12-31         Tuesday         53
12: 12 2013-12-31   Tuesday  2013-12-31         Tuesday         53
13: 13 2014-01-01 Wednesday  2014-01-01       Wednesday          1
14: 14 2014-01-02  Thursday  2014-01-01       Wednesday          1
15: 15 2014-01-03    Friday  2014-01-01       Wednesday          1
16: 16 2014-01-04  Saturday  2014-01-01       Wednesday          1
17: 17 2014-01-05    Sunday  2014-01-01       Wednesday          1
18: 18 2014-01-06    Monday  2014-01-01       Wednesday          1
19: 19 2014-01-07   Tuesday  2014-01-08       Wednesday          2
20: 20 2014-01-08 Wednesday  2014-01-08       Wednesday          2
21: 21 2014-01-09  Thursday  2014-01-08       Wednesday          2
22: 22 2014-01-10    Friday  2014-01-08       Wednesday          2
23: 23 2014-01-11  Saturday  2014-01-08       Wednesday          2
24: 24 2014-01-12    Sunday  2014-01-08       Wednesday          2
25: 25 2014-01-13    Monday  2014-01-08       Wednesday          2
26: 26 2014-01-14   Tuesday  2014-01-15       Wednesday          3
27: 27 2014-01-15 Wednesday  2014-01-15       Wednesday          3
28: 28 2014-01-16  Thursday  2014-01-15       Wednesday          3
29: 29 2014-01-17    Friday  2014-01-15       Wednesday          3
30: 30 2014-01-18  Saturday  2014-01-15       Wednesday          3
31: 31 2014-01-19    Sunday  2014-01-15       Wednesday          3
32: 32 2014-01-20    Monday  2014-01-15       Wednesday          3
     i        day   weekday day_rounded weekday_rounded weeknumber

My workaround is this function: https://github.com/geneorama/geneorama/blob/master/R/round_weeks.R

round_weeks <- function(x){
    require(data.table)
    dt <- data.table(i = 1:length(x),
                     day = x,
                     weekday = weekdays(x))
    offset <- data.table(weekday = c('Sunday', 'Monday', 'Tuesday', 'Wednesday', 
                                     'Thursday', 'Friday', 'Saturday'),
                         offset = -(0:6))
    dt <- merge(dt, offset, by="weekday")
    dt[ , day_adj := day + offset]
    setkey(dt, i)
    return(dt[ , day_adj])
}

Of course, you can easily change the offset to make Monday first or whatever. The best way to do this would be to add an offset to the offset... but I haven't done that yet.

I provided a link to my simple geneorama package, but please don't rely on it too much because it's likely to change and not very documented.

Synsepalous

Answer 8

Using only base, I wrote the following function.

Note:

1. Assumes Mon is day number 1 in the week
2. First week is week 1
3. Returns 0 if week is 52 from last year

Fine-tune to suit your needs.

findWeekNo <- function(myDate){
  # Find out the start day of week 1; that is the date of first Mon in the year
  weekday <- switch(weekdays(as.Date(paste(format(as.Date(myDate),"%Y"),"01-01", sep = "-"))),
                    "Monday"={1},
                    "Tuesday"={2},
                    "Wednesday"={3},
                    "Thursday"={4},
                    "Friday"={5},
                    "Saturday"={6},
                    "Sunday"={7}
  )

  firstMon <- ifelse(weekday==1,1, 9 - weekday )

  weekNo <- floor((as.POSIXlt(myDate)$yday - (firstMon-1))/7)+1
  return(weekNo)
}


findWeekNo("2017-01-15") # 2

Incrassate

Your code doesn't quite cut it. For example findWeekNo("2015-01-01") returns 0 and it should be week 1. – Halonna

@ekstroem: Depends on how you want to number your weeks. See the note above the code. This code assumes that week 1 starts with the first Monday in the year, similar to the standard used in timeanddate.com calendars. Days in the year before your first Mon belong to the last week of the previous year. I intentionally didn't code it to show 52 to avoid confusing it with week 52 of the year in question. – Incrassate

The ISO standard bases the first week on the first Thursday: "Weeks start with Monday. Each week's year is the Gregorian year in which the Thursday falls. The first week of the year, hence, always contains 4 January. ISO week year numbering therefore slightly deviates from the Gregorian for some days close to 1 January.". The page you reference, timeanddate.com also lists January 1st, 2015 as week 1. – Halonna