0

I want to extract weeknumber in R using the same system 1 as used by WEEKNUM function in Excel.

Please refer to the image below

enter image description here

I have explored all the available option to extract weeknumber from date in R but nothing is fulfilling my objective

library(parsedate)
dates =c("12/25/2015", "12/26/2015", "12/27/2015", "12/28/2015", "12/29/2015", "12/30/2015", "12/31/2015", "1/1/2016", "1/2/2016", "1/3/2016", "12/24/2016", "12/26/2016", "12/27/2016", "12/28/2016", "12/29/2016", "12/30/2016", "1/1/2017", "1/2/2017")
#Converting dates into date format in R 
dates = as.Date(parse_date(dates), format = "%Y-%m-%d")

desired_output

52 52 53 53 53 53 53 1 1 2 2 52 53 53 53 53 53 53 1 1

Can anyone please help me to figure out a way to do this.

Caution :- Please verify things before marking this as possible duplicate. This might ends up deviating folks who can provide solution to the question.

learner
  • 828
  • 2
  • 19
  • 36
  • @RonakShah Its not duplicate. Please verify your stance. If I apply this to "2016-01-03" then this will give me 53 whereas in excel it is 2. – learner Feb 28 '18 at 05:36
  • @chinsoon12 If I apply this to "2017-12-31" then it will produce 54 as the output instead of 53 – learner Feb 28 '18 at 05:41
  • Almost a duplicate of https://stackoverflow.com/questions/22439540/how-to-get-week-numbers-from-dates – Ronak Shah Feb 28 '18 at 05:42
  • @chinsoon12 appears to be working fine. Let me check and verify this on my data – learner Feb 28 '18 at 05:46
  • @chinsoon12 my bad. This is not working if I consider 1/1/2017. It gives 2 where the correct excel output is 1 – learner Feb 28 '18 at 05:56
  • `as.numeric(format(dates,"%U"))` or `as.numeric(format(dates,"%V"))` or `as.numeric(format(dates,"%W"))` depending on how you want to define your weeks. take a look at `?strptime` to see the difference between these three – Onyambu Feb 28 '18 at 06:39

2 Answers2

0

Woah there seems to be more than meet the eye. Seems like when Jan 1st falls on a Sunday (e.g. 2006-01-01, 2012-01-01), "%U" in R for these Jan 1st dates give "01" instead of "00". Below is a hack. 

df <- data.frame(dates=as.Date(c(sapply(seq(as.Date("2000-01-01"), by="1 year", length.out=20), function(x) {
    x + -7:7  
})), origin="1970-01-01"))

df$rweeknum <- ifelse(format(as.Date(format(df$dates, "%Y-01-01")), "%a")=="Sun",
    as.integer(strftime(as.Date(df$dates, "%m/%d/%Y"), format = "%U")),
    as.integer(strftime(as.Date(df$dates, "%m/%d/%Y"), format = "%U")) + 1)

#open and check in Excel
write.csv(df, "dates.csv", row.names=FALSE)
shell("dates.csv")

Need to investigate further and double check before raising an issue with R Core. Can someone else also verify if you are getting the same behaviour? all(format(seq(as.Date("1970-01-01"), by="1 year", length.out=100), "%U")=="00")

chinsoon12
  • 25,005
  • 4
  • 25
  • 35
0

This gives the desired output for me. (OP: if there are edge cases that aren't handled properly, please edit the question to include them ...)

Setup:

dates =c("12/25/2015", "12/26/2015", "12/27/2015", 
        "12/28/2015", "12/29/2015", "12/30/2015", "12/31/2015", 
        "1/1/2016", "1/2/2016", "1/3/2016", "1/4/2016", "1/5/2016", 
        "1/6/2016")
dates = as.Date(dates,format="%m/%d/%Y")

Convert:

as.numeric(format(dates,"%U"))+1
## [1] 52 52 53 53 53 53 53  1  1  2  2  2  2

System info:

R version 3.4.3 (2017-11-30)
Platform: x86_64-apple-darwin15.6.0 (64-bit)
Running under: OS X El Capitan 10.11.6

locale:
[1] en_CA.UTF-8/en_CA.UTF-8/en_CA.UTF-8/C/en_CA.UTF-8/en_CA.UTF-8
Ben Bolker
  • 211,554
  • 25
  • 370
  • 453
  • thank you for your effort. If I use x = as.Date("2017-01-01") as.numeric(format(x, "%U")) + 1 output is 2 but the required output is 1 – learner Mar 01 '18 at 02:08
  • I have updated the question. Thanks a lot for notifying that edge cases should be included – learner Mar 01 '18 at 02:20