1

I'm not sure exactly what this would be called, so I'm struggling to search for it. Sorry if this is a repeated question, that would be why.

I have a data 43x17 data matrix,lets call it A, with four levels in the "site" column. I also have another 4x3 matrix containing coordinates for each site, lets call it B. I'd like to create a column in A where each site is assigned the coordinate listed in B.

Using the data below as an example, I'd like to make a new column in A and populate it with values from Coordinate1 (in B) with the associated "Site" value. 

A <- matrix(c(1:4), nrow = 12, ncol = 3, byrow = TRUE,
               dimnames = list(c(1:12),
                               c("Site", "D.2", "D.3")))
A
B<- matrix(c(1:4), nrow = 4, ncol = 3, byrow = TRUE,
           dimnames = list(c(1:4),
                           c("Site", "Coordinate1", "Coordinate2")))
B

I'm sure this is simple, but I'm blanking on what to call this and have apparently forgotten how to do it!

Uwe
  • 41,420
  • 11
  • 90
  • 134
Jesse001
  • 924
  • 1
  • 13
  • 37
  • 2
    You should also give the desired output. Looks like you just want a simple merge? Possible duplicate of: https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right (you probably want to be using data.frames rather than matrices). – MrFlick Jul 17 '17 at 21:24
  • I'm open to any data structure, matrix just seemed to be the easiest to throw a reproducible code up with. I tried the cbind code @coffeinjunky suggested, but am getting all NAs in the new columns. There shouldn't be any row without matching site values between the two bits of data, so I'm not sure where that's coming from – Jesse001 Jul 17 '17 at 21:35
  • 2
    If you are open to any datastruture, use a `data.frame` and go with `merge`. – coffeinjunky Jul 17 '17 at 21:46
  • You may try a right join using `library(data.table); as.matrix(data.table(B)[data.table(A), on = "Site"])`. – Uwe Jul 18 '17 at 11:42

1 Answers1

2

There are several options.

Without conversion to data.frame and using merge() as suggested in most of the comments, you may try to use match() and cbind():

cbind(A, B[match(A[, "Site"], B[, "Site"]), "Coordinate1", drop = FALSE])

   Site D.2 D.3 Coordinate1
1     1   2   3           2
2     4   1   2           1
3     3   4   1           4
4     2   3   4           3
5     1   2   3           2
6     4   1   2           1
7     3   4   1           4
8     2   3   4           3
9     1   2   3           2
10    4   1   2           1
11    3   4   1           4
12    2   3   4           3

or, to cbind all columns of B

cbind(A, B[match(A[, "Site"], B[, "Site"]), ])

   Site D.2 D.3 Site Coordinate1 Coordinate2
1     1   2   3    1           2           3
2     4   1   2    4           1           2
3     3   4   1    3           4           1
4     2   3   4    2           3           4
5     1   2   3    1           2           3
6     4   1   2    4           1           2
7     3   4   1    3           4           1
8     2   3   4    2           3           4
9     1   2   3    1           2           3
10    4   1   2    4           1           2
11    3   4   1    3           4           1
12    2   3   4    2           3           4

Conversion to data.frame and merge()

 as.matrix(merge(data.frame(A), data.frame(B), by = "Site", sort = FALSE))

distorts the original order of rows:

      Site D.2 D.3 Coordinate1 Coordinate2
 [1,]    1   2   3           2           3
 [2,]    1   2   3           2           3
 [3,]    1   2   3           2           3
 [4,]    4   1   2           1           2
 [5,]    4   1   2           1           2
 [6,]    4   1   2           1           2
 [7,]    3   4   1           4           1
 [8,]    3   4   1           4           1
 [9,]    3   4   1           4           1
[10,]    2   3   4           3           4
[11,]    2   3   4           3           4
[12,]    2   3   4           3           4

Using right join from the data.table package maintains the order of rows

library(data.table)
as.matrix(data.table(B)[data.table(A), on = "Site"])

      Site Coordinate1 Coordinate2 D.2 D.3
 [1,]    1           2           3   2   3
 [2,]    4           1           2   1   2
 [3,]    3           4           1   4   1
 [4,]    2           3           4   3   4
 [5,]    1           2           3   2   3
 [6,]    4           1           2   1   2
 [7,]    3           4           1   4   1
 [8,]    2           3           4   3   4
 [9,]    1           2           3   2   3
[10,]    4           1           2   1   2
[11,]    3           4           1   4   1
[12,]    2           3           4   3   4
Uwe
  • 41,420
  • 11
  • 90
  • 134