How to merge two tables

[Andrea84]

Hi all,
I am not an expert and I need some help to solve a problem. I have two tables where the rows are molecules measured in blood and the columns are the different patients. The patients are totally different between the two tables, whereas the rows are mostly commons. How can I create a single table that combines the common molecules, maintaining the different ones?

Here an example.

Thanks.

Andrea

[antonvsdata]

Hi,

There must be multiple solutions to this, but as we are in the RStudio forums, I think using the dplyr package is the way to go!

dplyr has many useful *_join functions that you can use to merge data frames by matching the values of one or more columns. In this case, full_join is the way to go, as it keeps all the molecules that appear in either of the two data frames.

Here is the code that reproduces your example:

df1 <- data.frame(Molecole = c("A", "B", "D", "E", "G"),
                  PZ1 = 1, PZ2 = 2, PZ3 = 3)

df2 <- data.frame(Molecole = c("A", "B", "C", "F", "G"),
                  PZ4 = 4, PZ5 = 5, PZ6 = 6)

merged <- dplyr::full_join(df1, df2) %>%
  arrange(Molecole)

full_join merges the tables together, and arrange orders the merged data frame by the column "Molecole" in alphabetical order. Hope this helps!

[Andrea84]

@antonvsdata

merged <- dplyr::full_join(df1, df2) %>% arrange(merged, Molecole)

Thank you for your reply. Unfortunately, I have these error messages:

Joining, by = "Molecole"
Errore: Argument 1 is of unsupported type data.frame
Inoltre: Warning message:
Column Molecole joining factors with different levels, coercing to character vector

[antonvsdata]

Hi again,

I'm so sorry about the error, I made a mistake when copying the code here. The last line should be arrange(Molecole), not arrange(merged, molecole). I edited my first reply to correct it!

[zx8754]

This should work:

result <- merge(df1, df2, by = "Molecole", all = TRUE)

See below post for more options:

How to join (merge) data frames (inner, outer, left, right)

[Andrea84]

@zx8754

result <- merge(df1, df2, by = "Molecole", all = TRUE)

thank you!! it seems to work!

[andresrcs]

If your question's been answered (even by you!), would you mind choosing a solution? It helps other people see which questions still need help, or find solutions if they have similar problems. Here’s how to do it:

FAQ: How do I mark a solution?

If your question has been answered, don't forget to mark the solution! How do I mark a solution? Find the reply you want to mark as the solution and look for the row of small gray icons at the bottom of that reply. Click the one that looks like a box with a checkmark in it: [image] Hovering over the mark solution button shows the label, "Select if this reply solves the problem". If you don't see the mark solution button, try clicking the three dots button ( ••• ) to expand the full set of options. When a solution is chosen, the icon turns green and the hover label changes to: "Unselect if this reply no longer solves the problem". Success! [solution_reply_author] …