Simplify R code for correlation matrix using for-loop?

Question

I am trying to create a correlation matrix with a for loop or another more simple/efficient solution, but to be honest I am really struggling it with it.

This is the structure of the data frame and the correlation matrix:

# Create data frame
ID <- c("r1", "r1", "r1", "r1", "r1", "r2", "r2", "r2", "r2", "r2", "r3", "r3", "r3", "r3", "r3")
V1.1 <- c(3, 3, 3, 3, 3, 3, 2, 3, 3, 1, 2, 2, 1, 1, 2)
V2.1 <- c(2, 2, 3, 2, 3, 3, 3, 3, 3, 3, 2, 2, 3, 3, 3)
V3.1 <- c(4, 4, 4, 4, 3, 4, 1, 2, 5, 2, 2, 2, 4, 5, 1)
V4.1 <- c(3, 4, 3, 3, 3, 3, 3, 3, 4, 4, 5, 4, 4, 4, 2)
V5.1 <- c(3, 2, 3, 3, 2, 3, 2, 2, 2, 3, 2, 3, 3, 3, 3)
V1.2 <- c(4, 4, 3, 3, 4, 3, 3, 2, 3, 3, 3, 3, 3, 3, 3)
V2.2 <- c(3, 4, 4, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 5)
V3.2 <- c(2, 1, 2, 2, 2, 4, 3, 4, 4, 3, 4, 2, 1, 2, 1)
V4.2 <- c(2, 5, 2, 3, 4, 4, 3, 4, 2, 3, 4, 5, 2, 4, 3)
V5.2 <- c(5, 4, 3, 4, 3, 3, 4, 4, 2, 3, 2, 4, 4, 1, 3)

df <- data.frame(ID, V1.1, V2.1, V3.1, V4.1, V5.1, V1.2, V2.2, V3.2, V4.2, V5.2)

# Define variables
ID.vars <- c("r1", "r2", "r3")
vars <- c("V1", "V2", "V3", "V4", "V5")
vars.1 <- c("V1.1", "V2.1", "V3.1", "V4.1", "V5.1")
vars.2 <- c("V1.2", "V2.2", "V3.2", "V4.2", "V5.2")

# Empty data frame for correlation matrix
corrmat <- data.frame(ID = ID.vars)
corrmat <- cbind(corrmat, matrix(NA, nrow = length(ID.vars), ncol = length(vars)))
names(corrmat)[2:ncol(corrmat)] <- vars

This is what I want to do with a loop or better solution:

# Subset data per id
r1 <- subset(df, ID == "r1", select = c(vars.1, vars.2))
r2 <- subset(df, ID == "r2", select = c(vars.1, vars.2))
r3 <- subset(df, ID == "r3", select = c(vars.1, vars.2))

# Fill-in correlations matrix
corrmat[corrmat$ID == "r1", "V1"] <- cor(r1$V1.1, r1$V1.2)
corrmat[corrmat$ID == "r1", "V2"] <- cor(r1$V2.1, r1$V2.2)
corrmat[corrmat$ID == "r1", "V3"] <- cor(r1$V3.1, r1$V3.2)
corrmat[corrmat$ID == "r1", "V4"] <- cor(r1$V4.1, r1$V4.2)
corrmat[corrmat$ID == "r1", "V5"] <- cor(r1$V5.1, r1$V5.2)

corrmat[corrmat$ID == "r2", "V1"] <- cor(r2$V1.1, r2$V1.2)
corrmat[corrmat$ID == "r2", "V2"] <- cor(r2$V2.1, r2$V2.2)
corrmat[corrmat$ID == "r2", "V3"] <- cor(r2$V3.1, r2$V3.2)
corrmat[corrmat$ID == "r2", "V4"] <- cor(r2$V4.1, r2$V4.2)
corrmat[corrmat$ID == "r2", "V5"] <- cor(r2$V5.1, r2$V5.2)

corrmat[corrmat$ID == "r3", "V1"] <- cor(r3$V1.1, r3$V1.2)
corrmat[corrmat$ID == "r3", "V2"] <- cor(r3$V2.1, r3$V2.2)
corrmat[corrmat$ID == "r3", "V3"] <- cor(r3$V3.1, r3$V3.2)
corrmat[corrmat$ID == "r3", "V4"] <- cor(r3$V4.1, r3$V4.2)
corrmat[corrmat$ID == "r3", "V5"] <- cor(r3$V5.1, r3$V5.2)

This is the beginning of the attempt and now I am stuck :(

# Loop attempt
for(i in ID.vars) {
    results <- subset(df, ID == i, select = c(vars.1, vars.2)) # subset data
    # Loop across variables
    for(j in vars){
    }
}

Thanks!

As soon as you start creating variable names with numbers in the name, stop what you’re doing, delete the variables and replace them by a vector or list as appropriate. The simplification of the code will fall out of that. — Konrad Rudolph, Aug 10 '21 at 10:11

score 1 · Accepted Answer · answered Aug 10 '21 at 10:18

library(dplyr)
library(tidyr)
df %>% 
  pivot_longer(cols = -ID, names_to = c("set", ".value"), names_pattern = "(V\\d)\\.(\\d)") %>% 
  group_by(ID, set) %>% 
  summarise(c = cor(`1`, `2`), .groups = "drop") %>% 
  pivot_wider(id_cols = ID, names_from = set, values_from = c)
# A tibble: 3 × 6
  ID        V1     V2     V3     V4     V5
  <chr>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
1 r1    NA      0.167 -0.25   0.772  0.327
2 r2    -0.375 NA      0.722 -0.764 -0.218
3 r3    NA      0.408 -0.124  0.320  0.343

score 0 · Answer 2 · answered Aug 10 '21 at 10:26

Can see a solution exists now but it's already written so here you go. The other solution is more efficient but this one might be simpler to go through

library(purrr)
library(dplyr)

ids <- 1:5
# store as a list of the individual correlation results
list_output <- lapply(ids, function(i){
  # identify the two columns to do the correlation
  col1 <- paste("V", i, ".1", sep="")
  col2 <- paste("V", i, ".2", sep="")
  
  # dplyr 
  # - group by ID
  # - do the correlation of the col1 and col2. Use get() to extract data
  out <- df %>% 
    group_by(ID) %>% 
    summarise(cor(get(col1), get(col2)))
  # fix column name
  names(out)[2] <- paste("V", i, sep="")
  out
})
# merge them together - https://stackoverflow.com/questions/8091303/simultaneously-merge-multiple-data-frames-in-a-list
# and convert back to data.frame from a tibble
corrmat <- purrr::reduce(list_output, left_join, by="ID") %>% as.data.frame
corrmat

Simplify R code for correlation matrix using for-loop?

2 Answers2