in R: reorder the rows of a dataframe based on those in another table

Question

I have a table as follows:

tab1 <- as.table(matrix(c(8,6,9,0,8,4,0,12,7,10), ncol = 2, byrow = FALSE, 
   dimnames = list(site = c("beta", "alpha", "gamma", "theta", "delta"),
  count = c("low", "high"))))
> tab1
        count
site    low high
beta    8    4
alpha   6    0
gamma   9   12
theta   0    7
delta   8   10

and a data.frame that maps the site names to siteID's:

data.frame(site = c("alpha", "beta", "gamma", "delta", "theta"), siteId = c(1102, 3154, 9000, 1101, 1103))
     site siteId
1   alpha   1102
2    beta   3154
3   gamma   9000
4   delta   1101
5   theta   1103

Finally, I have a data.frame that contains these siteID's and some other variables:

data.frame(siteId = c(1101, 1102, 1103, 3154, 9000), treatment = c("A", "B", "C", "E", "F"))
  siteId treatment
1   1101         A
2   1102         B
3   1103         C
4   3154         E
5   9000         F

What I need to be able to do is to order the columns in the last dataframe in the same way that the rows in tab1 were ordered, so it should yield:

  siteId treatment
1   3154         E
2   1102         B
3   9000         F
4   1003         C
5   1001         A

How can I do that, without engaging in elaborate looping? The actual dataset is quite large, so looping would take much more time than I would want to.

thanks for finding that related question, it is slightly simpler than mine, but indeed very similar. — Steve G. Jones, Sep 03 '14 at 19:23

score 4 · Accepted Answer · answered Sep 03 '14 at 13:07

You can do this by matching the IDs from the different data frames. By the way I changed epsilon in data frame a to theta, as there's no epsilon in tab1.

tab1 <- as.table(matrix(c(8,6,9,0,8,4,0,12,7,10), ncol = 2, byrow = FALSE, 
                                                dimnames = list(site = c("beta", "alpha", "gamma", "theta", "delta"),
                                                                                count = c("low", "high"))))


a = data.frame(site = c("alpha", "beta", "gamma", "delta", "theta"), siteId = c(1102, 3154, 9000, 1101, 1103))
b = data.frame(siteId = c(1101, 1102, 1103, 3154, 9000), treatment = c("A", "B", "C", "E", "F"))

# put a in the order of tab1
a = a[match(a$site,rownames(tab1)),]

# put b in order of a
b = b[match(a$siteId, b$siteId),]

> b
#  siteId treatment
#4   3154         E
#2   1102         B
#5   9000         F
#3   1103         C
#1   1101         A

in R: reorder the rows of a dataframe based on those in another table

1 Answers1