Calculating Percent Change with Missing Dates (time series)

292 views Asked by At

I'm attempting to calculate percent change over a period of time where there are certain days that do not have any data. The problem I am running into is that the Delt function produces NA's (I'm guessing because I have missing dates?)

The code looks something like this:

Date <- c("6/1/2015", "6/3/2015", "6/4/2015", "6/5/2015", "6/8/2015")
variable <- c(4,7,10,22,3)
df <- data.frame(Date, variable)
df$Date <- as.Date(df$Date, "%m/%d/%Y")
df

library(plyr)
library(quantmod)

perct.Change <- ddply(df, "Date", transform,
    Daily.Change = round(Delt(variable)*100,1))

and the data looks like this:

        Date variable Delt.1.arithmetic
1 2015-06-01        4                NA
2 2015-06-03        7                NA
3 2015-06-04       10                NA
4 2015-06-05       22                NA
5 2015-06-08        3                NA
2

There are 2 answers

0
jeremycg On

I think the ddply function isn't working like you think it is - it is applying Delt to a dataframe at each time point and returning NA, as that is always the first output of Delt in a time series.

You probably want something like this:

df$change <- Delt(df$variable) / c(0, as.numeric(diff(df$Date)))

Where we have calculated the Delt, then divided it by the difference in days between measures.

1
RHertel On

You could try the following:

Date <- c("6/1/2015", "6/3/2015", "6/4/2015", "6/5/2015", "6/8/2015")
variable <- c(4,7,10,22,3)
Date <-as.Date(Date, "%m/%d/%Y")
library(xts)
df <- as.xts(variable,Date)
changes <-diff(df)

With this I obtain:

> changes
            [,1]
2015-06-01    NA
2015-06-03     3
2015-06-04     3
2015-06-05    12
2015-06-08   -19

Those are the absolute changes per day. To convert these daily absolute changes into daily percent changes, one can shift the entries of the original time series using the lag() function:

 perc_change <- round(changes/lag(df,1)*100,2)

This yields:

> perc_change
             [,1]
2015-06-01     NA
2015-06-03  75.00
2015-06-04  42.86
2015-06-05 120.00
2015-06-08 -86.36

Hope this helps.