R: convert xts or zoo object to data frame

Question

What is a simple way to force time series data to a data frame in a format in which the resulting data is a summary of the original?

These may be some examples of data stored in an xts or zoo object:

t, V1 "2010-12-03 12:00", 10.0 "2010-11-04 12:00", 10.0 "2010-10-05 12:00", 10.0 "2010-09-06 12:00", 10.0 ...and so on, monthly data for many years. 

and I would like to convert it to a data frame, for example:

 year, month, V1 2010, 12, a descriptive statistic calculated of that month data 2010, 11, ... 2010, 10, ... 2010, 9, ... 

The reason I ask about this is because I want to calculate the monthly calculation summaries of the data in the same plot. I can do this quite easily for data in the latter format, but I have not found a method for plotting the time series format.

For example, I can have temperature data for several years, measured in the daily interval, and I would like to build curves for the average monthly temperatures for each year on the same site. I did not understand how to do this using data formatted in xts format, or even if it is even suitable for formatting data in xts / zoo format, which seems to always contain year information on it.

Answer 1

Please provide sample work data, and I will try to give a less general answer. Basically you can use apply.monthly to compute summary statistics for your xts object. You can then convert the index to yearmon and convert the xts object to a data.frame file.

 x <- xts(rnorm(50), Sys.Date()+1:50) mthlySumm <- apply.monthly(x, mean) index(mthlySumm) <- as.yearmon(index(mthlySumm)) Data <- as.data.frame(mthlySumm) 

Answer 2

Here's a solution that uses the tidyquant package, which includes as_xts() functions to force the use of data frames for xts and as_tibble() objects to force xts to be tibbles ("neat" data frames).

Re-creating your data:

 > data_xts V1 2010-09-06 10 2010-10-05 10 2010-11-04 10 2010-12-03 10 

Use as_tibble() to convert to slice. preserve_row_names = TRUE adds a column named "row.names" with index xts as the character class. A rename and mutate are used to clear dates. The conclusion is halftones with dates and values.

 > data_df <- data_xts %>% as_tibble(preserve_row_names = TRUE) %>% rename(date = row.names) %>% mutate(date = as_date(date)) > data_df # A tibble: 4 × 2 date V1 <date> <dbl> 1 2010-09-06 10 2 2010-10-05 10 3 2010-11-04 10 4 2010-12-03 10 

You can go further and add other fields, such as day, month and year, using the mutate function.

 > data_df %>% mutate(day = day(date), month = month(date), year = year(date)) # A tibble: 4 × 5 date V1 day month year <date> <dbl> <int> <dbl> <dbl> 1 2010-09-06 10 6 9 2010 2 2010-10-05 10 5 10 2010 3 2010-11-04 10 4 11 2010 4 2010-12-03 10 3 12 2010