Generate a single column from two different columns in the same format

Question

I have multiple data frames in R in the following format:

Dataframe1:

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

Dataframe2:

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 that I want to bind through rbind(). The problem is that the exact correlation is represented in two ways in those two dataframes.

A bacteria is found in Var1 of Dataframe:1 while its pair is found in Var2 of Dataframe:2 (see first correlation).

I want to make a column so that each pair of correlations can be represented in the 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 someone help me achieve this, please?

Answer 1

I think this should be fairly straightforward:

# working on Dataframe2
Dataframe2$tmp <- Dataframe2$Var2   # Create a temporary column of Var2
Dataframe2$Var2 <- Dataframe2$Var1  # Replace Var2 values by Var1
Dataframe2$Var1 <- Dataframe2$tmp   # Re-attribute tmp values to Var1
Dataframe2$tmp <- NULL              # delete tmp column

# Binding the two dataframes
df <- rbind(Dataframe1, Dataframe2)

# Concatenating the two columns
df$Var1 <- paste(df$Var1, df$Var2, sep = ":")
df$Var2 <- NULL                     # delete Var2 column

PS: I haven't tested this code so let me know if it works for you.

EDIT1:

InvertedRows <- Dataframe1[!(Dataframe1$Var1 %in% Dataframe2$Var2),]
InvertedRows$tmp <- InvertedRows$Var2   # Create a temporary column of Var2
InvertedRows$Var2 <- InvertedRows$Var1  # Replace Var2 values by Var1
InvertedRows$Var1 <- InvertedRows$tmp   # Re-attribute tmp values to Var1
InvertedRows$tmp <- NULL              # delete tmp column
Dataframe2<- rbind(InvertedRows, Dataframe1[Dataframe1$Var1 %in% Dataframe2$Var2,])
df <- rbind(Dataframe1, Dataframe2)