How to compare two variable and different length data frames to add values from one data frame to the other, repeating values where necessary

0
votes

I apologize as I'm not sure how to word this title exactly.

I have two data frames. df1 is a series of paths with columns "source" and "destination". df2 stores values associated with the destinations. Below is some sample data:

df1

row source destination
1 A B
2 C B
3 H F
4 G B

df2

row destination n
1 B 26
2 F 44
3 L 12

I would like to compare the two data frames and add the n column to df1 so that df1 has the correct n value for each destination. df1 should look like:

row source destination n
1 A B 26
2 C B 26
3 H F 44
4 G B 26

The data that I'm actually working with is much larger, and is never the same number of rows when I run the program. The furthest I've gotten with this is using the which command to get the right values, but only each value once.

df2[ which(df2$destination %in% df1$destination), ]$n

[1] 26 44

When what I would need is the list (26,26,44,26) so I can save it to df1$n

2
rdataframe
merge(df1, df2, by = "destination") - r2evans

2 Answers

2
votes

We can use a merge or left_join

library(data.table)
setDT(df1)[df2, n := i.n, on = .(destination)]
1
votes

A base R option using match

transform(
  df1,
  n = df2$n[match(destination, df2$destination)]
)

which gives

  row source destination  n
1   1      A           B 26
2   2      C           B 26
3   3      H           F 44
4   4      G           B 26

Data

df1 <- data.frame(row = 1:4, source = c("A", "C", "H", "G"), destination = c("B", "B", "F", "B"))
df2 <- data.frame(row = 1:3, destination = c("B", "F", "L"), n = c(26, 44, 12))