convert year-month string into daily dates

Question

recently I asked how to convert calendar weeks into a list of dates and received a great and most helpful answer: convert calendar weeks into daily dates

I tried to apply the above method to create a list of dates based on a column with "year - month". Alas i cannot make out how to account for the different number of days in different months. And I wonder whether the package lubridate 'automatically' takes leap years into account?

Sample data:

df <-  data.frame(YearMonth = c("2016 - M02", "2016 - M06"), values = c(28,60))

M02 = February, M06 = June (M11 would mean November, etc.)

Desired result:

DateList  Values
2016-02-01  1
2016-02-02  1
ect
2016-02-28  1
2016-06-01  2
etc
2016-06-30  2

Values would something like

df$values / days_in_month()

Thanks a million in advance - it is honestly very much appreciated!

score 0 · Answer 1 · answered Feb 03 '16 at 20:14

0

I'll leave the parsing of the line to you.

To find the last day of a month, assuming you have GNU date, you can do this:

year=2016 
month=02
last_day=$(date -d "$year-$month-01 + 1 month - 1 day" +%d)
echo $last_day    # => 29 -- oho, a leap year!

Then you can use a for loop to print out each day.

answered Feb 03 '16 at 20:14

glenn jackman

238,783
38
220
352

Thanks for the answer. Alas, I get error messages when I run > last_day=$(date -d "$year-$month-01 + 1 month - 1 day" +%d) Error: unexpected '$' in "last_day=$". I removed the $ (just to check) but that gave another Error message: : unexpected string constant in "last_day=(date -d "$year-$month-01 + 1 month - 1 day"". What do I miss? – user2006697 Feb 04 '16 at 07:15
You missed sharing what language you're using. I see now from the link to your other question that it's R. I gave you a shell answer. – glenn jackman Feb 04 '16 at 11:14
Ah - sorry! Still interesting - I'll try it in Shell. Thank you so much for sending an answer! – user2006697 Feb 05 '16 at 07:21

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

thanks to answer 6 at Add a month to a Date and answer for (how to extract number with leading 0) i got an idea to solve my own question using lubridate. It might not be the most elegant way, but it works.

sample data

data <- data_frame(mon=c("M11","M02"), year=c("2013","2014"), costs=c(200,300))

step 1: create column with number of month

temp2 <- gregexpr("[0-9]+", data$mon)  
data$monN <- as.numeric(unlist(regmatches(data$mon, temp2)))

step 2: from year and number of month create a column with the start date

data$StartDate <- as.Date(paste(as.numeric(data$year), formatC(data$monN, width=2, flag="0") ,"01", sep = "-"))

step 3: create a column EndDate as last day of the month based on startdate

data$EndDate <- data$StartDate 
day(data$EndDate) <- days_in_month(data$EndDate)

step 4: apply answer from Apply seq.Date using two dataframe columns to create daily list for respective month

data$id <- c(1:nrow(data))
dataL <- setDT(data)[,list(datelist=seq(StartDate, EndDate, by='1 day'), costs= costs/days_in_month(EndDate)) , by = id]

convert year-month string into daily dates

2 Answers2

sample data