I want to match a column from one table with another table and replace those matching values

Question

i have a table say,Table A :

uid pid code
1     1  aaa
2     1  ccc
3     4  ddd 
4     2  eee

i have another table, Table B:

pid msg
1   good
2   inspiring
3   thing to wtch
4   terrible

now, i want to replace pid in table A with msg in Table B.

I used merge(tableA, tableb, by =c("pid"))

I got the result as

uid pid code msg
1    1  aaa  good
2    1  ccc  good
3    4  ddd  terrible
4    2  eee  inspiring

where in i want the result as

uid msg code
1   good aaa
2   good ccc
3   terrible ddd
4   inspiring eee

You want to eliminate `pid` from the output and reorder the columns? — Henry, Oct 03 '17 at 07:08
I agree with @Henry . Can't you just delete the column `pid` ? `df$pid <- NULL` ? — Ronak Shah, Oct 03 '17 at 07:21

score 2 · Accepted Answer · answered Oct 03 '17 at 07:13

Your approach seems absolutely correct, just needs further steps:

With tidyverse functions, you can do something like:

TableA %>%
  left_join(TableB) %>%
  select(uid, msg, code)

which gives:

  uid       msg code
1   1      good  aaa
2   2      good  ccc
3   3  terrible  ddd
4   4 inspiring  eee

score 0 · Answer 2 · answered Oct 03 '17 at 07:27

0

Base R solution:

newtable = merge(tableA,tableB,by = "pid")
newtable$pid = NULL
newtable = newtable[order(newtable$uid,decreasing=FALSE),c(1,3,2)]

answered Oct 03 '17 at 07:27

tushaR

2 Answers2