0

I'm trying to match data across two datatables and import the values from one table into the other.

Both tables have a column that contains an alphanumeric code (e.g., "AB001") and on one table (in this example, df2) there are associated columns with values that I would like to import to the other table. I have created corresponding columns in df1 that need to be filled (df1$col2 and df1$col3) which currently contain 0 values. So I need to match the "ABXXX" value on df1 to the "ABXXX" value in df2 and then import all the values from df2$col2 and df2$col3 directly onto the corresponding columns in df1.


df1 <- data.frame(col1 = c("AB001", "AB004", "AB003", "AB005", "AB002"), 
                  col2 = c(0, 0, 0, 0, 0),
                  col3 = c(0, 0, 0, 0, 0)) 

df2 <- data.frame(col1 = c("AB001", "AB002", "AB003", "AB004", "AB005"),  
                  col2 = c(2, 5, 6, 1, 23),
                  col3 = c(4, 7, 9, 10, 4))

I've tried a few things but can't seem to get the scripting right to import the numbers. I feel like I'm on the right lines with a str_replace and match, but maybe with the sequencing wrong. Can anyone have a look and help? This is definitely wrong:

match_data <- df1 %>% 
    mutate(df1col2 = str_replace(match(df1$col1, df2$col1), df1$col1, df2$col2)
Splod
  • 13
  • 4
  • Does this answer your question? [How to join (merge) data frames (inner, outer, left, right)](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) – Martin Gal Sep 06 '21 at 18:44

1 Answers1

1

We could do a join

library(data.table)
setDT(df1)[df2, c('col2', 'col3') := .(i.col2, i.col3), on = .(col1)]

or with dplyr

library(dplyr)
left_join(df1 %>% 
    select(col1), df2)
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Sorry, I should have said that in the real data there are many rows of data with the ABXXX reference but in the second table, just a single row of values for each ABXXX. Therefore I need the script to match to the ABXXX code on the second dataframe and extrapolate the associated values. – Splod Sep 06 '21 at 19:05
  • @Splod `left_join` or join does that – akrun Sep 06 '21 at 19:06