Help me in representing correlations between two variables in the same combination format.

[DC7]

I have multiple data frames in the following format:

Dataframe:1

Var1    Var2    cor p   fdr
Bacteroides_galacturonicus  Lactobacillus_rogosae   0.967374392060265   0   0
Bacteroides_galacturonicus  Lachnospira_pectinoschiza   0.526779994497013   0   0
Lactobacillus_rogosae   Lachnospira_pectinoschiza   0.29419393866076    0   0
Bacteroides_galacturonicus  Bacteroides_salyersiae  0.374581964109831   0.548   0.829267117726658
Lactobacillus_rogosae   Bacteroides_salyersiae  0.45736458243079    0.002   0.03622024291498
Lachnospira_pectinoschiza   Bacteroides_salyersiae  0.566636776534  0.21    0.56910941475827
Bacteroides_galacturonicus  Holdemanella_biformis   0.342443352 0.094   0.394447279549719
Lactobacillus_rogosae   Holdemanella_biformis   0.15592768883205    0.174   0.527328455284553

Dataframe:2

Var1    Var2    cor p   fdr
Lactobacillus_rogosae   Bacteroides_galacturonicus  0.467374392060265   0   0
Lachnospira_pectinoschiza   Bacteroides_galacturonicus  0.426779994497013   0   0
Lachnospira_pectinoschiza   Lactobacillus_rogosae   0.69419393866076    0   0
Bacteroides_galacturonicus  Bacteroides_salyersiae  0.074581964109831   0.548   0.829267117726658
Lactobacillus_rogosae   Bacteroides_salyersiae  0.320636458243079   0.002   0.03622024291498
Lachnospira_pectinoschiza   Bacteroides_salyersiae  0.132857736776534   0.21    0.56910941475827
Bacteroides_galacturonicus  Holdemanella_biformis   0.19490130543352    0.094   0.394447279549719
Lactobacillus_rogosae   Holdemanella_biformis   0.14472768883205    0.174   0.527328455284553

These are two correlation matrices which I want to bind through rowbind() . But the problem is same correlation is represented in two different ways in two dataframes. For a pair of correlation, a bacteria is found in Var1 in Dataframe:1 while the for the same pair of correlation, the same bacteria is represented in Var2 in Dataframe:2 ( see first correlation ). I want to make a column so that each pair of correlation can be represented in same format. e.g.:

Var1:Var2   cor p   fdr
Lactobacillus_rogosae:Bacteroides_galacturonicus    0.967374392060265   0   0 
Lactobacillus_rogosae:Bacteroides_galacturonicus    0.467374392060265   0   0 

can anyone please help me achieveing this?

many many thanks

[nirgrahamuk]

I'm assuming you are asking for help how to swap / rename columns and order the results ?

library(tidyverse)

(dfa <- tibble(
  v1 = letters[1:3],
  v2 = letters[26:24],
  stat = 1:3
))


(dfb <- tibble(
  v1 = letters[26:24],
  v2 = letters[1:3],
  stat = sqrt(1+1:3)
))


bind_rows(
  dfa,
  dfb %>% select(
    v1 = v2,
    v2 = v1,
    stat
  )
) %>% arrange(v1, v2,stat)

[DC7]

I am not just asking about changing the column names. Say in Dataframe:1 a correlation is like A-B. the same may or may not be as B-A in the Dataframe:2. It means not all the rows are just inverted.

[nirgrahamuk]

it seems you need to apply a definitive ordering to both tables, then its trivial to bind them together.

library(tidyverse)

(dfa <- tibble(
  v1 = letters[1:3],
  v2 = letters[26:24],
  stat = 1:3
))
#middle agrees with dfa others differ
(dfb <- tibble(
  v1 = c("z","b","x"),
  v2 = c("a","y","c"),
  stat = sqrt(1+1:3)
))


definite_arrange <- function(x){
  mutate(rowwise(x),
                vx =list(c(v1,v2)),
                v1=ifelse(vx[1]<vx[2],vx[1],vx[2]),
                v2=ifelse(vx[1]<vx[2],vx[2],vx[1])) |> 
    select(-vx) |> 
    ungroup()
}


bind_rows(
  definite_arrange(dfa),
  definite_arrange(dfb)
) %>% arrange(v1, v2,stat)

[DC7]

Thanks @nirgrahamuk - If I have multiple dataframes like above, can I just use the function for all of them and then do row binding like following:

bind_rows(
  definite_arrange(dfa),
  definite_arrange(dfb),
definite_arrange(dfc),
definite_arrange(dfz)
) %>% arrange(v1, v2,stat)

[nirgrahamuk]

theres no reason you can't but you wouldnt extend this approach to dozens or hundreds of frames, at some point you would use a purrr::map or equivalent

library(purrr)
map_dfr(list(dfa,dfb,dfc,dfz), 
         definite_arrange)

[DC7]

Thanks a lot sir @nirgrahamuk .