Simpler R code for adding value column to dataframe based on ID column

Question

I have a dataset A and a summary dataset B.

A<-data.frame(ID=rep(c(1,2),times=c(3,2)))
A

ID
1
1
1 
2
2

B<-data.frame(ID=c(1,2),value=c(14,15))
B

ID value
 1 14
 2 15

I want to add a column in dataset A that has the values in B repeated for each ID, like this:

So far, I've been achieving this using this code:

library(dplyr)
count<-A %>% group_by(ID)%>% tally()
n<-A$n
val<-rep(B$value,times=n)
A$value<-val

But I'm sure there's an easier way. Any thoughts?

score 2 · Answer 1 · answered Oct 18 '20 at 21:33

One simply way to do what you want is using base R match which behaves similarly to known look up functions:

#Data
A<-data.frame(ID=rep(c(1,2),times=c(3,2)))
B<-data.frame(ID=c(1,2),value=c(14,15))
#Add
A$value <- B[match(A$ID,B$ID),"value"]

Output:

akrun · Accepted Answer · 2020-10-18T22:36:26.577

We can use the simple left_join from dplyr instead of replicating by 'ID'

library(dplyr)
left_join(A, B)

-output

#   ID value
#1  1    14
#2  1    14
#3  1    14
#4  2    15
#5  2    15

Or with match

A %>% 
     mutate(value = B$value[match(ID, B$ID)])

-output

#   ID value
#1  1    14
#2  1    14
#3  1    14
#4  2    15
#5  2    15

Or using a join on with data.table

library(data.table)
setDT(A)[B, value := value, on = .(ID)]

Or using base R

A$value <- with(A, B$value[match(ID, B$ID)])

Or another simple option in base R is merge

merge(A, B, by = 'ID', all.x = TRUE)

-output

#   ID value
#1  1    14
#2  1    14
#3  1    14
#4  2    15
#5  2    15

2 Answers2