Converting time series to data frame, matrix, or table

Question

With reference to this question:

Transforming a time-series into a data frame and back

I have a list of monthly averages that start in May 2012 and go through May 2015. It looks like this initially:

head (AVG_LOSCAT2)
    month AVG_LOSCAT YEAR MONTH
1 2012-05   5.342066 2012    05
2 2012-06   6.544096 2012    06
3 2012-07   6.448767 2012    07
4 2012-08   7.897919 2012    08
5 2012-09   8.908504 2012    09
6 2012-10   8.088083 2012    10

I do this to get it to convert into a ts object:

AVG_LOSCATSET<- AVG_LOSCAT2[, c(2)] 
AVG_LOSCATSET<-round(AVG_LOSCATSET,digits= 1)

Now it looks like this:

AVG_LOS_CATSET
[1]  5.3  6.5  6.4  7.9  8.9  8.1 10.1 12.0 14.7 10.6  8.4  6.3  6.7
[14]  7.4  9.8  9.3 15.1 11.7 11.9 20.7 19.0  9.2 18.1  6.4  8.2  7.9
[27] 11.7 11.8  9.8 10.4  9.8 21.3 12.9 14.0  8.2  4.8 19.7   NA

I convert to a time series to get this lovely output:

    AVG_LOSCATSET2<-ts(AVG_LOSCATSET,frequency = 12, start=c(2012,5))
    AVG_LOSCATSET2

      Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct  Nov  Dec
2012                      5.3  6.5  6.4  7.9  8.9  8.1 10.1 12.0
2013 14.7 10.6  8.4  6.3  6.7  7.4  9.8  9.3 15.1 11.7 11.9 20.7
2014 19.0  9.2 18.1  6.4  8.2  7.9 11.7 11.8  9.8 10.4  9.8 21.3
2015 12.9 14.0  8.2  4.8 19.7   NA 

At this point doing anything with this is incredibly difficult. I cannot drop it into a report using the ReporteRs package because it is a ts object.

This converts it to a list (I think?):

tapply(AVG_LOSCATSET2, list(year = floor(time(AVG_LOSCATSET2)), month = month.abb[cycle(AVG_LOSCATSET2)]), c)

The months are now in alphabetical order and there is a wonky output for Jan 2013, Jan 2014 appears to have the original value of Jan 2015, and Jan 2015 is strangely NULL.

        month
year   Apr  Aug  Dec  Feb  Jan       Jul  Jun Mar  May  Nov  Oct  Sep 
  2012 NULL 7.9  12   NULL NULL      6.4  6.5 NULL 5.3  10.1 8.1  8.9 
  2013 6.3  9.3  20.7 10.6 Numeric,2 9.8  7.4 8.4  6.7  11.9 11.7 15.1
  2014 6.4  11.8 21.3 9.2  12.9      11.7 7.9 18.1 8.2  9.8  10.4 9.8 
  2015 4.8  NULL NULL 14   NULL      NULL NA  8.2  19.7 NULL NULL NULL

If I use numbers in stead of abbreviations for the month, I still have the same problems.

 tapply(AVG_LOSCATSET2, list(year = floor(time(AVG_LOSCATSET2)), month = cycle(AVG_LOSCATSET2)), c)

      month
year   1         2    3    4    5    6   7    8    9    10   11   12  
  2012 NULL      NULL NULL NULL 5.3  6.5 6.4  7.9  8.9  8.1  10.1 12  
  2013 Numeric,2 10.6 8.4  6.3  6.7  7.4 9.8  9.3  15.1 11.7 11.9 20.7
  2014 12.9      9.2  18.1 6.4  8.2  7.9 11.7 11.8 9.8  10.4 9.8  21.3
  2015 NULL      14   8.2  4.8  19.7 NA  NULL NULL NULL NULL NULL NULL

Any thoughts on how I can fix these glitches affecting my Jan values and/or smoothly convert my ts object into a data frame, matrix, or table?

Thank you.

Answer 1

You do not need time series, just tapply:

res=tapply(AVG_LOSCAT2$AVG_LOSCAT, list(year = AVG_LOSCAT2$YEAR, month = AVG_LOSCAT2$MONTH), round,2)
res 

      month
year      1    2    3    4    5    6    7    8    9   10   11   12
  2012   NA   NA   NA   NA   NA 7.51 7.31 8.33 7.66 5.36 6.46 8.30
  2013 5.74 7.89 6.49 7.09 5.91 6.31 8.24 6.73 8.56 8.19 6.54 6.49
  2014 8.03 6.80 6.25 7.10 5.38 6.21 7.78 8.87 6.62 6.09 8.40 8.37
  2015 8.00 5.73 6.32 6.71 6.32 6.75   NA   NA   NA   NA   NA   NA