CODEWITHSUNDEEP
rdataframemerge
bioinformatics_student
bioinformatics_student

Reputation: 488

Add same column to dataframe based on previous columns r

I am merging two dataframes together by a common key column (first column), however I want to add the same column once again based on the second column from the same previous column:

Code Snippet

clusering_matrix_example <- data.frame(BGC = c("BGC1", "BGC2", "BGC3", "BGC4"), Family = c("10","20","30","40"))
network_matrix_example <- data.frame(BGC1 = c("BGC1", "BGC1", "BGC1", "BGC2", "BGC2", "BGC2", "BGC3", "BGC3", "BGC3", "BGC4", "BGC4", "BGC4"),
                                     BGC2 = c("BGC2", "BGC3", "BGC4", "BGC1", "BGC3", "BGC4", "BGC1", "BGC2", "BGC4", "BGC1", "BGC2", "BGC3"),
                                     score = c(1,2,3,1,4,5,2,4,6,3,5,6))
network_output_example <- merge(network_matrix_example, clusering_matrix_example, by.x= "BGC1", by.y = "BGC")

network_output_example <- merge(network_matrix_example, clusering_matrix_example, by.x= "BGC2", by.y = "BGC")

Current Output

BGC1  | BGC2 | score |Family
BGC1    BGC2    1     10
BGC1    BGC3    2     10
BGC1    BGC4    3     10
BGC2    BGC1    1     20
BGC2    BGC3    4     20
BGC2    BGC4    5     20
BGC3    BGC1    2     30
BGC3    BGC2    4     30
BGC3    BGC4    6     30
BGC4    BGC1    3     40
BGC4    BGC2    5     40
BGC4    BGC3    6     40

Desired Output

BGC1  | BGC2 | score |Family1 | Family2
BGC1    BGC2    1     10        20
BGC1    BGC3    2     10        30
BGC1    BGC4    3     10        40
BGC2    BGC1    1     20        10
BGC2    BGC3    4     20        30
BGC2    BGC4    5     20        40
BGC3    BGC1    2     30        10
BGC3    BGC2    4     30        20
BGC3    BGC4    6     30        40
BGC4    BGC1    3     40        10
BGC4    BGC2    5     40        20
BGC4    BGC3    6     40        40

Upvotes: 1

Views: 55

Answers (2)

Leia
Leia

Reputation: 46

The reason why the last column is missing is because the second time you merge with the old frame 'network_matrix_example' instead of the newly merged one 'network_output_example'.

The code should be like this:

clusering_matrix_example <- data.frame(BGC = c("BGC1", "BGC2", "BGC3", "BGC4"), Family = c("10","20","30","40"))
network_matrix_example <- data.frame(BGC1 = c("BGC1", "BGC1", "BGC1", "BGC2", "BGC2", "BGC2", "BGC3", "BGC3", "BGC3", "BGC4", "BGC4", "BGC4"),
                                         BGC2 = c("BGC2", "BGC3", "BGC4", "BGC1", "BGC3", "BGC4", "BGC1", "BGC2", "BGC4", "BGC1", "BGC2", "BGC3"),
                                         score = c(1,2,3,1,4,5,2,4,6,3,5,6))
network_output_example <- merge(network_matrix_example, clusering_matrix_example, by.x= "BGC1", by.y = "BGC")

network_output_example <- merge(network_output_example, clusering_matrix_example, by.x= "BGC2", by.y = "BGC")

Upvotes: 3

user12256545
user12256545

Reputation: 3022

Hi i dont know if this is the smartest way but it gives the desired Result:

library(dplyr)
#your line:
network_output_example <- merge(network_matrix_example, clusering_matrix_example, by.x= "BGC1", by.y = "BGC")

# add left_join:
network_output_example %>% left_join(clusering_matrix_example, by= c("BGC2"= "BGC"))

Upvotes: 1

Related Questions