arrange correlation table into rows

Question

I want to arrange the correlation table into rows.

Table

 var1 var2 var3 var4
var1 1 x x x
var2 x 1 x x
var3 x x 1 x
var4 x x x 1

Desired output

var1 var2 x
var1 var3 x
var1 var4 x
var2 var3 x
var2 var4 x
var3 var4 x

Answer 1

You can get the rownames as separate columns, get the data in long format and then remove rows which has 1 in it.

library(magrittr)
df1 <- df %>%
        tibble::rownames_to_column('rows') %>%
        tidyr::pivot_longer(cols = -rows, names_to = 'cols') %>%
        dplyr::filter(value != 1)
df1

# A tibble: 12 x 3
#   rows  cols  value
#   <chr> <chr> <chr>
# 1 var1  var2  x    
# 2 var1  var3  x    
# 3 var1  var4  x    
# 4 var2  var1  x    
# 5 var2  var3  x    
# 6 var2  var4  x    
# 7 var3  var1  x    
# 8 var3  var2  x    
# 9 var3  var4  x    
#10 var4  var1  x    
#11 var4  var2  x    
#12 var4  var3  x  

To write it as csv we can do :

write.csv(df1, 'data.csv', row.names = FALSE)  

data

df <- structure(list(var1 = c("1", "x", "x", "x"), var2 = c("x", "1", 
"x", "x"), var3 = c("x", "x", "1", "x"), var4 = c("x", "x", "x", 
"1")), class = "data.frame", row.names = c("var1", "var2", "var3","var4"))