0

I'm working to transform some data to a quarterly time xts series object. First my data wasn't an appropriate time-based object and now as.yearqtr is behaving in I cannot make sense of.

I want the object df to be transformed so that I can plot it with plot.xts, my final goal, but I am stuck as shown below. 

df <- structure(list(yrQ = structure(1:7, .Label = c("2016-1", "2016-2", 
"2016-3", "2016-4", "2016-5", "2016-6", "2016-7"), class = "factor"), 
    a = c(4.14, 2.83, 3.71, 4.15, 4.63, 4.91, 5.31), b = c(4.25, 
    3.5, 3.5, 3.5, 3.5, 3.5, 5)), .Names = c("yrQ", "a", "b"
), row.names = c(NA, 7L), class = "data.frame")
df
#      yrQ    a    b
# 1 2016-1 4.14 4.25
# 2 2016-2 2.83 3.21
# 3 2016-3 3.71 3.21
# 4 2016-4 4.15 3.21
# 5 2016-5 4.63 3.21
# 6 2016-6 4.91 3.21
# 7 2016-7 5.31 5.00

# install.packages(c("xts"), dependencies = TRUE)
library(xts)
xts(df, order.by = df[,1])
# Error in xts(df, order.by = df[, 1]) : 
#   order.by requires an appropriate time-based object

df$yrQ <- as.yearqtr(df$yrQ)
df    
#       yrQ    a    b
# 1 2016 Q1 4.14 4.25
# 2 2016 Q2 2.83 3.21
# 3 2016 Q3 3.71 3.21
# 4 2016 Q4 4.15 3.21
# 5  NA QNA 4.63 3.21
# 6  NA QNA 4.91 3.21
# 7  NA QNA 5.31 5.00
Eric Fail
  • 8,191
  • 8
  • 72
  • 128
  • 1
    The column `yrQ` obviously doesn't denote quarters because there aren't 7 quarters in 2016. Is your original data monthly? How do you want to aggregate it to quarterly (sum, average, OHLC...)? – Chris Haug Nov 27 '16 at 17:03
  • @ChrisHaug, I am truly sorry. I was doing this way after I should have gone to bed (@R.S. also politely identify this mental lapse) I will update my question ASAP. Thanks! – Eric Fail Nov 28 '16 at 08:20

1 Answers1

1

The data you have in df seems to be monthly data as it runs way past 4.

In that case , I guess the way to go would be using as.yearmon and from there to to.quarterly. It does not really plot so well without an OHLC chart though, so I looked up another option as well. See what you think of it.

Here's my attempt: 

require(xts)
df <- structure(list(yrQ = structure(1:7, .Label = c("2016-1", "2016-2", 
"2016-3", "2016-4", "2016-5", "2016-6", "2016-7"), class = "factor"), 
    a = c(4.14, 2.83, 3.71, 4.15, 4.63, 4.91, 5.31), b = c(4.25, 
    3.5, 3.5, 3.5, 3.5, 3.5, 5)), .Names = c("yrQ", "a", "b"
), row.names = c(NA, 7L), class = "data.frame")
df


# using yearmon to create xts
myxts<- xts(df[,-1], order.by = as.yearmon(as.character(df[,1])  ))

myxts

#using to.quarterly .. cannot be used simultaneously with both columns
#guess that would need apply and Reduce

myxtsQ<- to.quarterly(myxts$a)
myxtsQ
plot(myxtsQ) 
# require(quantmod)
# quantmod::chartSeries(myxtsQ)



# One option seems to be indexing 
# though it seems XTS does not support recycling of boolean indices
#so we need to create a list of indices

indx<- rep( c(T,F,F), ceiling(nrow(myxts)/3 ))
indx
myxtsQ<- myxts[indx,]
plot.xts(myxtsQ)
R.S.
  • 2,093
  • 14
  • 29
  • 1
    Thank you for a great answer! I think I stayed up too late last night. As both you and @ChrisHaug pointed out `yrQ` obviously does not denote quarters, but months. Your answer was, despite my mental lapse, very clear and well written. Thanks! – Eric Fail Nov 28 '16 at 15:37
  • 1
    Pleased it helped. I personally liked that quantmod OHLC style chart for the purpose, btw. – R.S. Nov 28 '16 at 16:34