1

I am trying to create a new column denoting quarter in my data.frame using an ifelse statement on a date column of class as.POSIXct .

this is a short verion of the date

 a            

            spot.id local.date
q12014local.1  11824267 2013-12-30
q12014local.2  11825708 2013-12-30
q12014local.3  11823669 2013-12-30
q12014local.4  11825407 2013-12-30
q12014local.5  11824268 2013-12-30
q12014local.6  11825709 2013-12-30
q12014local.7  11823670 2013-12-30
q12014local.8  11825408 2013-12-30
q12014local.9  11824266 2013-12-31
q12014local.10 11825707 2013-12-31

This is the ifelse statement i wrote: > a$quarter <- ifelse(a$local.date >= 2013-07-01 & a$local.date <= 2013-09-30,"q32013", ifelse(a$local.date >= 2013-10-01 & a$local.date <= 2013-12-31 , "q42013", ifelse(a$local.date >= 2014-01-01 & a$local.date <= 2014-03-31, "q12014", ifelse(a$local.date >= 2014-04-01 & a$local.date <= 2014-06-30, "q22014", ifelse(a$local.date >= 2014-07-01 & a$local.date <= 2014-09-30, "q32014", NA)))))

For some reason, all I get is NA values in my new column ! I can use the row.names in my data.frame with a gsub like a$quarter <- gsub("[\\.][0-9]+", "", row.names(a)) but that's not ideal. If ifelse is unclassing date objects, I tried using the function from this post : How to prevent ifelse() from turning Date objects into numeric objects but that didn't work out. I still get NA values. What's the right way to achieve my objective ?

EDIT: Was unaware of the numerous functions like quarters , format.yearqtr available in zoo and lubridate. But related to the same, what if my quarters are not the same as calendar year quarters - For e.g. My quarter 1 begins in October, quarter 2 in Jan etc.

Community
  • 1
  • 1
vagabond
  • 3,526
  • 5
  • 43
  • 76
  • It may be better if you create some example data and the expected result so it becomes easier for others to test the code. – akrun Nov 08 '14 at 19:05

3 Answers3

2

Try

library(zoo)
a$quarter <- format.yearqtr(a$local.date, 'q%q%Y')
a
#                spot.id local.date quarter
#q12014local.1  11824267 2013-12-30  q42013
#q12014local.2  11825708 2013-12-30  q42013
#q12014local.3  11823669 2013-12-30  q42013
#q12014local.4  11825407 2013-12-30  q42013
#q12014local.5  11824268 2013-12-30  q42013
#q12014local.6  11825709 2013-12-30  q42013
#q12014local.7  11823670 2013-12-30  q42013
#q12014local.8  11825408 2013-12-30  q42013
#q12014local.9  11824266 2013-12-31  q42013
#q12014local.10 11825707 2013-12-31  q42013

Update

Based on the new info, about starting the quarter from October, may be this helps.

#creating the data
DATE <- seq(as.Date('1926-05-04'), length.out=1200, by='1 day')
month <- as.numeric(format(DATE,'%m'))
year <- as.numeric(format(DATE,'%Y'))
set.seed(25)
Val <- sample(0:120, 1200, replace=TRUE)
df <- data.frame(DATE, month, year, Val, stringsAsFactors=FALSE)
df$qtr <- ifelse(month %in% 10:12, 'q1', ifelse(month %in% 1:3,
                            'q2', ifelse(month %in% 4:6, 'q3', 'q4')))


indx <- df$year-min(df$year) + !df$month %in% 10:12
indx1 <- cumsum(c(TRUE,diff(indx) <0))
df$year2 <- indx1+ (min(df$year)-1)   
df$Quarter <- with(df, paste0(qtr,year2))
head(df)

Update2

Or based on @G. Grothendieck's comments

 format(as.yearqtr(df$DATE)-0.75, 'q%q%Y')
akrun
  • 874,273
  • 37
  • 540
  • 662
  • question : What if my quarters start from another year - meaning Q1 = Oct-Nov-Dec, Q2 = Jan-Feb-Mar etc. – vagabond Nov 08 '14 at 18:35
  • @vagabond Please show it on your post and the expected result because others may not read all the comments. – akrun Nov 08 '14 at 18:37
  • 1
    `yearqtr` class stores its data as year plus 0 for Q1, .25 for Q2, .5 for Q3 and .75 for Q4 thus if `x` is a Date class variable then `as.yearqtr(x) - .75` gives the year/quarter with quarters starting in October. For example, if `x <- as.Date("2014-11-08")` then it gives `2014 Q1` – G. Grothendieck Nov 08 '14 at 20:13
  • @G. Grothendieck Thanks for the comments. It is very useful. – akrun Nov 09 '14 at 06:46
2

Without using any extra package :

transform(dat,qu=paste0(quarters(local.date),format(local.date,'%Y')))
agstudy
  • 119,832
  • 17
  • 199
  • 261
1

How about using the lubridate package's quarter() function:

df <- read.table(text = "
            spot.id local.date
q12014local.1  11824267 2013-12-30
q12014local.2  11825708 2013-12-30
q12014local.3  11823669 2013-12-30
q12014local.4  11825407 2013-12-30
q12014local.5  11824268 2013-12-30
q12014local.6  11825709 2013-12-30
q12014local.7  11823670 2013-12-30
q12014local.8  11825408 2013-12-30
q12014local.9  11824266 2013-12-31
q12014local.10 11825707 2013-12-31", stringsAsFactors = FALSE)
df$local.date <- as.POSIXct(df$local.date)

library('lubridate')
library('dplyr')

df %>%
  mutate(quarter = paste0("q", quarter(local.date), year(local.date)))
#     spot.id local.date quarter
# 1  11824267 2013-12-30  q42013
# 2  11825708 2013-12-30  q42013
# 3  11823669 2013-12-30  q42013
# 4  11825407 2013-12-30  q42013
# 5  11824268 2013-12-30  q42013
# 6  11825709 2013-12-30  q42013
# 7  11823670 2013-12-30  q42013
# 8  11825408 2013-12-30  q42013
# 9  11824266 2013-12-31  q42013
# 10 11825707 2013-12-31  q42013
Kara Woo
  • 3,595
  • 19
  • 31
  • question : What if my quarters start from another year - meaning Q1 = Oct-Nov-Dec, Q2 = Jan-Feb-Mar etc. – vagabond Nov 08 '14 at 18:34