2
$\begingroup$

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?

$\endgroup$
1
  • $\begingroup$ Could please kindly consider @user438383's answer for upvoting/accepting. It is a good answer. $\endgroup$
    – M__
    Sep 14, 2023 at 12:12

1 Answer 1

1
$\begingroup$

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)
$\endgroup$
5
  • $\begingroup$ Thanks, @user324810. The problem is not all the combination is just the opposite.Some are the same in both dataframe. In this case, all of them will just flip. $\endgroup$
    – DEEP
    Jun 16, 2022 at 9:14
  • $\begingroup$ I am not sure I understand you. Do you mean that not all the rows are inverted? $\endgroup$
    – user324810
    Jun 16, 2022 at 9:24
  • $\begingroup$ Yes, that's correct. Not all rows are inverted. We can just frame the question in another way: like if we first bind the rows, then format the inverted combinations in identical way. Is it possible? $\endgroup$
    – DEEP
    Jun 16, 2022 at 9:42
  • $\begingroup$ I understand better. I am on the move right now. If nobody answers in an hour or so, I will come back to resolve this issue once I am back on my PC. $\endgroup$
    – user324810
    Jun 16, 2022 at 9:56
  • $\begingroup$ Meanwhile, you can see my comment, I wrote it but didn't test. $\endgroup$
    – user324810
    Jun 16, 2022 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.