add a column from one data (three columns) to another dataset(two columns) if similar (in two columns) in R

Question

I have the following problem:

I have two data frames

The first one looks like this:

    A      P     S
   <dbl>  <dbl> <dbl>
1 450001      2     2
2 450006      1     1
3 450006      2     2 
4 450006      3     2
5 450007      1     1
6 450008      1     2
7 400008      2     1

P is categorial and can only take the values (0,1,2) and S is categorial too and can only take the values (1,2). The first data frame has about 2000 rows

My second data frame looks like this:

     A      P
   <dbl>  <dbl>
1 450001      2
2 450001      2
3 450001      2
4 450006      3
5 450006      2
6 450008      2
7 400008      1
8 400008      1
9 400008      2

it has about 50.000 rows. Basically I want to add column S from data frame 1 to data frame 2, but obviously they don't have the same length. So basically I want something like this: go into data frame 2 first row and compare the first row to data frame 1, they are the same so add the right value S = 2 to the first row in data frame 1. Next step, go to row 2 in data frame 2 and again compare the values where you can see that it has the same values as in row 1, so add S = 2 to the second row.

I have tried many things, if-else loop but nothing works

My new data frame should look like this:

     A      P     S
   <dbl>  <dbl>
1 450001      2   2
2 450001      2   2
3 450001      2   2
4 450006      2   2
5 450006      2   2
6 450007      1   1
7 400008      1   2
8 400008      2   1

`merge` or `dplyr::left_join` on the basis of two common columns — AnilGoyal, Jun 22 '21 at 13:38

mikey · Answer 1 · 2021-06-22T12:37:33.800

Welcome to Stack Overflow. You can achieve this with the merge function. Try something like:

merge(df1, df2, by="A", all=FALSE, all.y=TRUE)

By specifying all.y=TRUE, you are telling it to keep all the rows in df2 and apply a relevant row from df1. (This assumes your first dataframe is called df1 and your second is called df2 and that you want to keep all rows in df2.)

If you only want to keep rows in df1 and not all rows in df2, you would use:

merge(df1, df2, by="A", all=FALSE, all.x=TRUE)

See ?merge for more details

add a column from one data (three columns) to another dataset(two columns) if similar (in two columns) in R

1 Answers1