Reputation: 486
I have got two dataframes s1 and s2. I want to generate s3 based on matching ids.
> s1
Type id status
1 B 3 0
2 B 4 0
3 S 2 1
4 B 1 2
> s2
id status
1 3 88
2 4 99
> s3
Type id status
1 B 3 88
2 B 4 99
3 S 2 1
4 B 1 2
s1=data.frame('Type'=c('B', 'B', 'S', 'B'), 'id'=c(3,4,2,1), 'status'=c(0,0,1,2))
s2=data.frame('id'=c(3,4), 'status'=c(88,99))
I have tried something like this but its going to work only if the ids are ordered
s1[s1$id %in% match(s2$id,s1$id),]$status <- s2$status
Upvotes: 1
Views: 528
Reputation:
If your status value to replace is always 0, you could use merge
and rowSums
:
s3 <- data.frame(s1[,1:2],status=rowSums(merge(s1,s2,by='id',all=T,sort=F)[,3:4], na.rm = T))
Which creates the following:
Type id status
1 B 3 88
2 B 4 99
3 S 2 1
4 B 1 2
Upvotes: 0
Reputation: 816
Or using dplyr
:
library(dplyr)
s1 <- data.frame('Type'=c('B', 'B', 'S', 'B'), 'id'=c(3,4,2,1), 'status'=c(0,0,1,2))
s2 <- data.frame('id'=c(3,4), 'status'=c(88,99))
s3 <- left_join(s1, s2, by = "id", suffix = c('_s1', '_s2')) %>%
mutate(status = coalesce(status_s2, status_s1)) %>%
select(-status_s1, -status_s2)
s3
Upvotes: 0
Reputation: 13591
Try
s1$status <- ifelse(is.na(match(s1$id, s2$id)), s1$status, s2$status[match(s1$id, s2$id)])
Type id status
1 B 3 88
2 B 4 99
3 S 2 1
4 B 1 2
Upvotes: 1