Transform year/week to date object
Before converting year-week to a date you have to specify a day of the week but more importantly you have to ensure which of the different conventions is being used.
Base R's strptime()
function knows 3 definitions of week of the year (but supports only 2 of them on input) and 2 definitions of weekday number,
see ?strptime
:
Week of the year
US convention: Week of the year as decimal number (00–53) using Sunday as the first day 1 of the week (and typically with the first Sunday of the year as day 1 of week 1):
%U
UK convention: Week of the year as decimal number (00–53) using Monday as the first day of week (and typically with the first Monday of the year as day 1 of week 1):
%W
ISO 8601 definition: Week of the year as decimal number (01–53) 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. Otherwise, it is the last week of the previous year, and the next week is week 1:
%V
which is accepted but ignored on input.
Note that there is also a week-based year (%G
and%g
) which is to be used with%V
as it may differ from the calendar year (%Y
and%y
).
Numeric weekday
- Weekday as a decimal number (1–7, Monday is 1):
%u
- Weekday as decimal number (0–6, Sunday is 0):
%w
- Interestingly, there is no format for the case Sunday is counted as day 1 of the week.
Converting year-week-day with the different conventions
If we append day 1 to the string and use the different formats we do get
as.Date("2015101", "%Y%U%u")
# [1] "2015-03-09"
as.Date("2015101", "%Y%U%w")
# [1] "2015-03-09"
as.Date("2015101", "%Y%W%u")
# [1] "2015-03-09"
as.Date("2015101", "%Y%W%w")
# [1] "2015-03-09"
as.Date("2015101", "%G%V%u")
# [1] NA
For weekday formats %u
and %w
we do get the same result because day 1 is Monday in both conventions (but watch out when dealing with Sundays).
For 2015, the US and the UK definition for week of the year coincide but this is not true for all years, e.g., not for 2001, 2007, and 2018:
as.Date("2018101", "%Y%U%u")
#[1] "2018-03-12"
as.Date("2018101", "%Y%W%u")
#[1] "2018-03-05"
The ISO 8601 format specifiers aren't supported on input. Therefore, I had created the ISOweek
package some years ago:
ISOweek::ISOweek2date("2015-W10-1")
#[1] "2015-03-02"
Edit: Using Thursday to associate a week with a month
As mentioned above you need to specify a day of the week to get a full calendar date. This is also required if the dates need to be aggregated by month later on.
If no weekday is specified and if the dates are supposed to be aggregated by month later on, you may take the Thursday of each week as reference day (following a suggestion by djhurio). This ensures that the whole week is assigned to the month to which the majority of the days of the week belong to.
For example, taking Sunday as reference day would return
ISOweek::ISOweek2date("2015-W09-7")
[1] "2015-03-01"
which consequently would associate the whole week to the month of March although only one day of the week belongs to March while the other 6 days belong to February. Taking Thursday as reference day will return a date in February:
ISOweek::ISOweek2date("2015-W09-4")
[1] "2015-02-26"
How do I transform week (and year) to date of the first date (i.e. Monday) of the week in R?
Your code is good. But you have only one problem: NA if week == 0. If week number is zero then first day of the year is not monday. So you can check if week number is zero (and find monday in previous year) or do as in you example. For example as.Date("2018-1-1", "%Y-%W-%w") == 2018-01-01
(week == 1, not 0).
foo <- function(year, week) {
if (week == 0) {
year <- year - 1
week <- data.table::week(as.Date(paste0(year, "-12-31"))) - 1
}
return(as.Date(paste0(c(year, week, "1"), collapse = "-"), "%Y-%W-%w"))
}
Convert week number to date
as.Date
is calling the 1 to 9 as NA as it is expects two digits for the week number and can't properly parse it.
To fix it, add in some - to split things up:
as.Date(paste(2014, df$Week, 1, sep="-"), "%Y-%U-%u")
How to convert week numbers into date format using R
Maybe there is a more automated way, but try something like this. I think this gets the right days, I looked at a 2020 calendar and counted. But if something is off, its a matter of playing with the (week - 1) * 7 - 1
component to return what you want.
This just grabs the first day of the year, adds x weeks worth of days, and then uses ceiling_date()
to find the next Sunday.
library(dplyr)
library(tidyr)
library(lubridate)
df %>%
separate(week, c("year", "week"), sep = 4, convert = TRUE) %>%
mutate(date = ceiling_date(ymd(paste(year, "01", "01", sep = "-")) +
(week - 1) * 7 - 1, "week", week_start = 7))
# # A tibble: 6 x 4
# year week Revenue date
# <int> <int> <dbl> <date>
# 1 2020 9 4543 2020-03-01
# 2 2020 10 6764 2020-03-08
# 3 2020 11 2324 2020-03-15
# 4 2020 12 5674 2020-03-22
# 5 2020 13 2232 2020-03-29
# 6 2020 14 2323 2020-04-05
How can I convert year and week to Java Date object?
Solution using standard java classes. This just gets the current time and sets the fields of year and week of year to known values.
Calendar cld = Calendar.getInstance();
cld.set(Calendar.YEAR, year);
cld.set(Calendar.WEEK_OF_YEAR, week);
Date result = cld.getTime();
Convert year.week to date
If you append a weekday then as.Date
will work:
x <- c("2016.49", "2016.50", "2016.51")
as.Date(paste0(x, ".1"), "%Y.%U.%u")
[1] "2016-12-05" "2016-12-12" "2016-12-19"
Convert Week number to date in data frame
You can do this in base R using as.Date
itself.
Based on your attempt it seems your locale is English, so you can try :
as.Date(paste(df$Week, df$Year, 'Sun'), '%U %Y %a')
#[1] "2019-01-06" "2019-01-13" "2020-01-05" "2020-01-05"
Related Topics
Aggregating by Unique Identifier and Concatenating Related Values into a String
Subset Rows Corresponding to Max Value by Group Using Data.Table
What Are the Differences Between "=" and "≪-" Assignment Operators
How to Debug "Contrasts Can Be Applied Only to Factors With 2 or More Levels" Error
How to Use R'S Ellipsis Feature When Writing Your Own Function
How to Create a Lag Variable Within Each Group
Data.Table VS Dplyr: Can One Do Something Well the Other Can't or Does Poorly
Convert Data.Frame Columns from Factors to Characters
Complete Dataframe With Missing Combinations of Values
Convert a List to a Data Frame
Left Align Two Graph Edges (Ggplot)
Gather Multiple Sets of Columns
Linear Regression and Group by in R
Does Ifelse Really Calculate Both of Its Vectors Every Time? Is It Slow
How to Plot Two Histograms Together in R
Combing a Categorical Variable to Create a New Categorical Variable in R