R: converting xts or zoo object to a data frame

What is an easy way of coercing time series data to a data frame, in a format where the resulting data is a summary of the original?

This could be some example data, stored in 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 transform it to a data frame like:

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

The reason I'm asking this, is because I want to plot monthly calculated summaries of data in the same plot. I can do this qui开发者_运维知识库te easily for data in the latter format, but haven't found a plotting method for the time series format.

For example, I could have temperature data from several years measured in a daily interval and I would like to plot the curves for the monthly mean temperatures for each year in the same plot. I didn't figure out how to do this using the xts-formatted data, or if this even suits the purpose of the xts/zoo formatting of the data, which seems to always carry the year information along it.

Please provide a sample of data to work with and I will try to provide a less general answer. Basically you can use apply.monthly to calculate summary statistics on your xts object. Then you can convert the index to yearmon and convert the xts object to a data.frame.

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

Here's a solution using the tidyquant package, which includes functions as_xts() for coercing data frames to xts objects and as_tibble() for coercing xts objects to tibbles ("tidy" data frames).

Recreating 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 a tibble. The preserve_row_names = TRUE adds a column called "row.names" with the xts index as character class. A rename and mutate are used to clean up dates. The output is a tibble 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 a step 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