Android Beginner
Android Beginner

Reputation: 486

copy subset of column from one dataframe to another

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

Answers (3)

user8571351
user8571351

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

67342343
67342343

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

CPak
CPak

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

Related Questions