0

Hello my question is: I have a column that has monthly, quarters and years e.g.

Time

2017 Q1
2017 Q2
2017
2016
Mar-07
Apr-07

I want to create another column such that: if it is a month then it should became the first day of the month 2015-03-01, if it is a year it shoul became the first day of the year 2017-01-01, and if it is a quarter1 then the 2017-01-01, if it is quarter 2 then 2017-04-01, if quarter 3 then 2017-07-01,if quarter 4 then 2017-10-2017. Thanks

Jean
  • 1,480
  • 15
  • 27
dede
  • 15
  • 5
  • What software, presumably, are you using to do this? – hd1 Aug 01 '17 at 09:43
  • The package `zoo` will help. See https://stackoverflow.com/questions/31071733/convert-quarter-year-format-to-a-date – Jean Aug 01 '17 at 09:52

1 Answers1

0

Hope this helps!

library(zoo)
df <- data.frame(time=c("2017 Q1", "2017 Q2", "2017", "2016", "Mar-07", "Apr-07"))

df$time <- sapply(df$time, function(x)gsub("^[a-zA-Z]{3}-\\d{2}$",as.Date(paste0('01-', x), format='%d-%b-%y'),x))
df$time <- sapply(df$time, function(x)gsub("^\\d{4}$",as.Date(paste0('01-01-', x), format='%d-%m-%Y'),x))
df$time <- sapply(df$time, function(x)gsub("^\\d{4}\\s\\w{2}$",as.Date(as.yearqtr(x, format = "%Y Q%q")),x))
df

Output is:

      time
2017-01-01
2017-04-01
2017-01-01
2016-01-01
2007-03-01
2007-04-01


Don't forget to let us know if it solved your problem :)

Prem
  • 11,775
  • 1
  • 19
  • 33
  • Prem it helpt me. Thank you very much ! – dede Aug 01 '17 at 13:09
  • I have another question can I make then the colum "time" date so that when I write it to excel I would recognize it as date ? thanks – dede Aug 01 '17 at 13:10
  • Glad that it helped. For date type conversion you can use `?strptime`. Something like `strptime(df$time, format="%Y-%m-%d", tz="GMT")` – Prem Aug 01 '17 at 13:16
  • Thanks Prem it works, but when I write it ro excel I get an extra 00:00:00 E.g. 1/1/2007 12:00:00 AM. How can I exclude it ? – dede Aug 01 '17 at 13:28
  • try simply `as.Date(df1$time)` – Prem Aug 01 '17 at 13:37
  • thanks, but if I cbind "time" with another column in excel I get just number of days e.g. 13665 instead of 2007-06-01. how can I fix this ? – dede Aug 01 '17 at 13:53
  • The issue which you have mentioned is about the compatibility between two softwares R & Excel. You can format the column (having either '13665' or '1/1/2007 12:00:00 AM') in Excel by right clicking and selecting 'Format' option to have the desired date format. Since you wanted to convert '2017 Q1' to 01-01-2017 and if its coming as '1/1/2007 12:00:00 AM' then I believe it's perfectly alright. Hope you got my point. Thanks! – Prem Aug 01 '17 at 17:44