Search code examples
rdplyrcorrelationxts

Calculate correlation on a monthly/weekly level

I am having trouble calculating the correlation coefficient between electricity prices of different countries on monthly/ weekly level. The dataset (https://github.com/Argiro1983/prices_df.git) looks like this:

prices_df<-structure(list(DATETIME = structure(c(1609459200, 1609462800, 
1609466400, 1609470000, 1609473600, 1609477200, 1609480800, 1609484400, 
1609488000, 1609491600), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
    GR = c(50.87, 48.19, 44.68, 42.92, 40.39, 20.96, 39.63, 40.1, 
    20, 40.74), IT = c(50.87, 48.19, 44.68, 42.92, 40.39, 40.2, 
    39.63, 40.09, 41.27, 41.67), BG = c(49.95, 48.05, 49.62, 
    46.73, 45.39, 44.25, 36.34, 19.97, 20, 20.43), HU = c(45.54, 
    41.59, 40.05, 36.9, 34.47, 32.82, 27.7, 15, 8.43, 20.77), 
    TR = c(26.31, 24.06, 24.21, 23.2, 23.2, 26.31, 24.98, 26.31, 
    24.04, 26.31), SR = c(38.89, 34.86, 33.62, 28.25, 29.03, 
    29.22, 29.71, 1.08, 1.1, 36.07)), row.names = c(NA, 10L), class = "data.frame")

I have tried converting it to xts and using apply.monthly (or apply.weekly) as follows, but it does not work.

library(xts)
SEE_prices <- xts(x = prices_df, order.by = DATETIME)
storage.mode(SEE_prices) <- "numeric"
SEE_prices <- na.locf(SEE_prices)


library(tidyverse)
library(tidyquant)
apply.monthly(SEE_prices, cor(SEE_prices$GR, SEE_prices$SR))

Another way I tried to get correlation on weekly level was to use the dplyr package, but it also did not work:

library(lubridate)
library(magrittr)
library(dplyr)

prices_df %<>% mutate( DATETIME = ymd_hms(DATETIME) )
table1<- prices_df %>% group_by( year( DATETIME ), isoweek( DATETIME )  ) %>%
  summarise( DateCount = n_distinct(date(DATETIME)), correlation = cor(prices_df$GR, prices_df$SR))

Does anybody have an idea on how to calculate weekly/monthly correlation on a dataset? Thank you in advance.

Solution

  • Don't use $ in dplyr pipes. To calculate correlation try -

    library(dplyr)
library(lubridate)

prices_df %>%
  mutate(DATETIME = ymd_hms(DATETIME),
         year = year(DATETIME), week = isoweek(DATETIME)) %>%
  group_by(year, week) %>%
  summarise(DateCount = n_distinct(date(DATETIME)), 
            correlation = cor(GR, SR), .groups = 'drop')