Reorder one data.frame using two columns from another data.frame in R

Question

I have two data.frames in R, one of which has two columns and of the other of each has three columns, and where two columns are common between the two frames. The frame have the same number of rows. An example of the frames, a and b, is provided below. What I need to do is reorder the rows of b using the order of rows in a. Note that in frame b, any unique combination of the first two columns, id and lob, will be associated with a unique value in the val column. The id and lob columns given here are a factor and a character, but I would want a solution to work for any datatype.

Note that if we were to consider a case where frame a just had the id column and frame b just had the id and val columns, I would accomplish this with something like

b[match(a$id,b$id),]

Unfortunately, I'm not sure how to accomplish the same thing when I need to order by two columns.

a:

  id lob
1 1+   X
2  3   X
3  2   X
4  1   X
5  1   Y
6 1+   Y
7 1+   X
8  3   X
9  3   X

b:

  id lob val
1 1+   X   1
2 1+   Y   9
3 1+   X   1
4  3   X   5
5  3   X   5
6  3   X   5
7  2   X   4
8  1   X   3
9  1   Y   2

I want to get this:

  id lob val
1 1+   X   1
2  3   X   5
3  2   X   4
4  1   X   3
5  1   Y   2
6 1+   Y   9
7 1+   X   1
8  3   X   5
9  3   X   5

Answer 1

Try using paste to combine your id and lob within your merge function call.

b[match(paste(a$id,a$lob), paste(b$id,b$lob)),]


    id lob val
1   1+   X   1
4    3   X   5
7    2   X   4
8    1   X   3
9    1   Y   2
2   1+   Y   9
1.1 1+   X   1
4.1  3   X   5
4.2  3   X   5

Answer 2

Here is another way as long as the pairings in a and b match perfectly:

b[order(b$id,b$lob), ][ order(order(a$id,a$lob)), ]

The first use of order sorts the b data frame by id and lob colmuns, then second set (2 orders) says reorder the rows of b, in the way that would unsort a back to its original order after being sorted.