CODEWITHSUNDEEP
rdate
Christopher Humbert
Christopher Humbert

Reputation: 13

Formatting Date to a new Year / quarter column

I have a dateframe in this format..

Timestamp  variation_id  
 5-Aug-16       A
 6-Aug-16       A
11-Aug-16       A

I want to create a new column determining which quarter of year it is based on the Timestamp column and have it look something like this..

Timestamp  variation_id  Quarter
 5-Aug-16       A        2016 Q3
 6-Aug-16       A        2016 Q3
11-Aug-16       A        2016 Q3

This is the code i've written so far, it's created the new column but nothing but NAs are in it...

df$Timestamp <- as.yearqtr(df$Timestamp, format = "%dd-%mmm-YY%"

How can i get this to work? Is there an easier way? Appreciate any help!

Upvotes: 0

Views: 43

Answers (2)

joran
joran

Reputation: 173527

This should be all that is necessary:

df <- data.frame(date = c("5-Aug-16", "6-Aug-16", "2-Jan-16"), 
                                 variation_id = "A",
                                 stringsAsFactors = FALSE)

as.yearqtr(as.Date(df$date,"%d-%b-%y"))

Or it looks like you can use as.yearqtr directly on the characters:

as.yearqtr(df$date,"%d-%b-%y")

Note how I specified the format in as.Date and read ?strptime for a description of how the formatting symbols work.

Upvotes: 1

Felix T.
Felix T.

Reputation: 530

I would consider doing this in parts, i.e.: 

require(tidyverse)

df <- data.frame(date = c("5-Aug-16", "6-Aug-16", "2-Jan-16"), 
                 variation_id = "A")

df %>% 
  mutate(fmtdate = lubridate::month(lubridate::dmy(date)),
         q = cut(fmtdate, breaks = c(-1, 3, 6, 9, 12), labels = c("Q1", "Q2", "Q3", "Q4")),
         Quarter = str_c(lubridate::year(lubridate::dmy(date)), " ", q)) %>% 
  select(date, variation_id, Quarter)

#       date     variation_id     Quarter
# 1 5-Aug-16                A     2016 Q3
# 2 6-Aug-16                A     2016 Q3
# 3 2-Jan-16                A     2016 Q1

Maybe someone can help you find a more elegant solution than this, but it's the best I can do for now!

Upvotes: 0

Related Questions