R: List of Time Series Dates - Need to Break Out in order to perform ARIMA styled forecasting

Question

I've done some research and I am stuck in finding the solution. I have a time series data on insurance claims and disability dates, a very basic data frame, let's call it data:

See list of dates Claims

I've run the following code (after loading the XTS library) to identify and sort the dates in the correct order:

data = read.csv('Claims1.csv')
data$DISABILITYDATE <- as.Date(data$DISABILITYDATE, "%m/%d/%Y")
data
str(data)
as.Date(data[,1])
xts(x=data[,-1], order.by = data[,1])

However I'm need to count/break out the number of claims per month so I can begin to perform and ARIMA(auto.arima) forecast.

Ideally I would like something like this data frame Ideal Data Frame so I can perform some AR/MA/ARMA/ARIMA styled forecasting on yearly/12 month projections

Answer 1

The xts library has functions for applying functions over a given time period. You will need to setup your data in an xts format, but this can be done in one line. Then just apply a function to count the number of instances within each month. For example,

library(xts)
data = read.csv('Claims1.csv')
data$DISABILITYDATE <- as.Date  (data$DISABILITYDATE, "%m/%d/%Y")
df <- xts(rep(1,length(data$DISABILITYDATE)),order.by=data$DISABILITYDATE)
apply.monthly(df,function(x) length(x))

It can also be done by the aggregate function, as mentioned in the comments.