This is a test version of Biostars. For the public version, visit https://www.biostars.org.
R - Merging and aligning two CSVs using common values in multiple columns

(Repost so tables aren't images.) I currently have two .csv files that look like this:

File 1:

ATTEMPT  RESULTS
Int1     B
Int2     H

File 2:

NAME  OUTCOME1  OUTCOME2  OUTCOME3
Sam1  A         B         C
Sam2  D         E         F
Sam3  G         H         I

I would like to merge and align the two .csvs such that the result each row of File 1 is aligned by its "result" cell, against any of the three "outcome" columns in File 2, leaving blanks or "NA"s if there are no similarities.

Ideally, would look like this:

ATTEMPT  RESULTS  NAME  OUTCOME1  OUTCOME2  OUTCOME3
Int1     B        Sam1  A         B         C
                  Sam2  D         E         F
Int2     H        Sam3  G         H         I

Any help would be much appreciated.

Edit: for my real data, I have several thousand rows in each file.

csv r
• 2,585 views • link
updated by cpad0112 cpad0112 • written by Michael • Michael 
> library(sqldf)
> df1=read.csv("test1.txt", header = T, sep="\t")
> df2=read.csv("test2.txt", header = T, sep="\t")
> sqldf('select df1.*, df2.* from df2 left join df1 on df2.OUTCOME1=df1.RESULTS OR
+       df2.OUTCOME2=df1.RESULTS OR
+       df2.OUTCOME3=df1.RESULTS')

  ATTEMPT RESULTS NAME OUTCOME1 OUTCOME2 OUTCOME3
1    Int1       B Sam1        A        B        C
2    <NA>    <NA> Sam2        D        E        F
3    Int2       H Sam3        G        H        I
• 0 views • link
written by cpad0112 cpad0112

1 answer

You should left join file 2 with file 1, using the OUTCOME2/RESULT column. Here's an untested example using R/tidyverse:

# In order to left-join, there must be a shared column name
file1 <- rename(file1, OUTCOME2=RESULTS)
# Left-join returns all the rows from the first argument,
# adding columns from the second argument where the key column (OUTCOME2) value matches,
# or leaving NA otherwise
combined <- left_join(file1, file2, by="OUTCOME2")

# OUTCOME2 and RESULT are redundant columns, 
# but if it is important to you that both columns exist, 
# you can add it back using mutate.
combined <- mutate(combined, RESULTS=OUTCOME2)
# Finally, if ordering of the columns matter to you:
combined <- select(combined, ATTEMPT, RESULTS, NAME, OUTCOME1, OUTCOME2, OUTCOME3)
• 0 views • link
written by ning • ning

This doesn't address OP requirement of "any of the three "outcome" columns in File 2"

• 0 views • link
written by cpad0112 cpad0112

My mistake, you're right! Your solution is much better :)

• 0 views • link
written by ning • ning

well, there is no better or best solution, it's only working solution. Your solution works given OP data. Solution I proposed works for 3 columns only.

• 0 views • link
written by cpad0112 cpad0112

Log in to answer this question.

More posts like this