R - Number of observations per month in an xts object (weekday data)

Question

I have several xts-objects containing weekday data from 2001-01-01 to 2021-12-31. Now I need to know the number of observations within each month from January 2001 to December 2021 in order to further analyse them. How can I get these? I'm rather new to R (and programming), so I assume there is a simple formula for this I am unaware of.

    structure(c(6.5156, 6.5, 6.4531, 6, 5.8594, 5.8281, 5.8438, 5.8281, 
5.8438, 5.8438, 5.8438, 5.7969), class = c("xts", "zoo"), .CLASS = "double", index = structure(c(978307200, 
978393600, 978480000, 978566400, 978652800, 978912000, 978998400, 
979084800, 979171200, 979257600, 979516800, 979603200), tzone = "UTC", tclass = "Date"), .Dim = c(12L, 
1L))

Could you please share your data using `dput(df)`? – Quinten May 25 '22 at 11:07 — Quinten, May 25 '22 at 11:07

phiver · Accepted Answer · 2022-05-25T11:57:50.620

1

xts has all kinds of period.apply functions you can use. for monthly: apply.monthly

Based on your example if you want the sum / mean / n of observations:

# sum
apply.monthly(my_xts, sum)
              [,1]
2001-01-16 72.1564

# mean
apply.monthly(my_xts, mean)
               [,1]
2001-01-16 6.013033

# n of records 
# length works like sum or mean, 
# but this is an example of how to use an anonymous function.
apply.monthly(my_xts, function(x) length(x))
           [,1]
2001-01-16   12

xts always takes the last day of the period to show the information.

edited May 25 '22 at 11:57

answered May 25 '22 at 11:28

phiver

23,048
14
44
56

Thank you very much, I got the values by simply using "length" as additional formula :) – Lennart May 25 '22 at 11:42

score 0 · Answer 2 · answered May 25 '22 at 11:20

First I convert your xts object to a dataframe. After that you can create a month column using month from lubridate and group_by per month to summarise the number of observations per month like this:

library(dplyr)
library(lubridate)
data.frame(date = index(df), coredata(df)) %>%
  mutate(month = month(date)) %>%
  group_by(month) %>%
  summarise(n = n())

Output:

# A tibble: 1 × 2
  month     n
  <int> <int>
1     1    12

In this case your data has 12 observations in January.

R - Number of observations per month in an xts object (weekday data)

2 Answers2