correlation time series in r

Question

I currently have a data set that looks as such (3 variables and a time series):

date       variable    value    
01/01/16      A         1000
01/01/16      B         800
01/01/16      C         10
01/02/16      A         2000
01/02/16      B         1800
01/02/16      C         100
01/03/16      A         100
01/03/16      B         80
01/03/16      C         10

What I'm trying to do is calculate the correlation between all three variables (A-B, A-C, B-C) per day so that I can graph the result as a plot with the date being on the x axis and on the y axis the correlation coefficient (w/ the legend being A-B, A-C, B-C).

Any help would be greatly appreciated!

You could only calculate a correlation per-day per-pair (A-B, B-C, B-C) if you had multiple values for each A, B, and C, on that day. But your example data only has one point per day. Is that an oversight, or is that how your data actually looks? — Mike Wise
only one point per variable per day...what would be the best way to represent this data so as to show that for instance, variables A and B are closely related, even on a daily basis — user3330107

Mike Wise Mike Wise · Accepted Answer · 2016-02-18T07:52:36

I think this is what you want:

df <- read.csv(sep=" ",stringsAsFactors=F,text="date variable value
01/01/16 A 1000
01/01/16 B 800
01/01/16 C 10
01/02/16 A 2000
01/02/16 B 1800
01/02/16 C 100
01/03/16 A 100
01/03/16 B 80
01/03/16 C 10 ")
df$date <- strptime(df$date, format = "%m/%d/%y")
df$value <- as.numeric(df$value)

vars <- unique(df$variable)
nv <- length(vars)
npairs <- nv*(nv-1)/2
labs <- rep("", npairs)
cors <- rep(0, npairs)
k <- 1
for (i in 1:(nv-1)) {
    vi <- vars[i]
    si <- df[df$variable == vi,]$value
    for (j in (i+1):nv) {
        vj <- vars[j]
        sj <- df[df$variable == vj,]$value
        labs[k] <- sprintf("%s-%s",vi,vj)
        cors[k] <- cor(si, sj)
        k <- k+1
    }
}
pdf <- data.frame(labs=labs,cors=cors)
ggplot(pdf) + geom_bar(aes(x=labs,y=cors),stat="identity")

yielding:

correlation time series in r

1 Answers