Subset a matrix using a column from another matrix in R

Question

I have a matrix X1 with 6 columns. Column 3 in this X1 matrix contains RouteNo. I also have a vector V1 which is extracted from another matrix. Few values from this vector matches with RouteNo in X1. The task is to take a subset from matrix X1 where RouteNo from X1 matches with RouteNo from V1. V1 contains extra RouteNo than in matrix X1.

> X1
    V1 V2       V3 V4   V5 V6
1    1  2 84072082  1 2000  0
2    2  2 84046006  1 2000  0
3    3  2 84046006  1 2001  0
4    4  2 84046006  1 2002  0
5    5  2 84021002  1 2002  0
6    6  2 84021002  1 2003  0
7    7  2 84021002  1 2003  0
8    8  2 84021002  1 2004  0
9    9  2 84021002  1 2005  0
10  10  2 84021002  1 2005  0
11  11  2 12468015  1 2006  0
12  12  2 12468015  1 2007  0
13  96  2 12468015  2 2000  0
> V1
 [1] 84021001 84021002 84021105 84046006 84046007 84046008 84046009 84046011 84046013 84046014
> n2 = subset(X1, subset = X1[,3] %in% V1)
> dim(n2)
[1] 0 6

I tried using subset function but I am not getting the desired result. I expect to get the matrix as below

2    2  2 84046006  1 2000  0
3    3  2 84046006  1 2001  0
4    4  2 84046006  1 2002  0
5    5  2 84021002  1 2002  0
6    6  2 84021002  1 2003  0
7    7  2 84021002  1 2003  0
8    8  2 84021002  1 2004  0
9    9  2 84021002  1 2005  0

Is there any other way to get the result? Any help is appreciated. Thank in advance.

Answer 1

You are running into problems with scoping. You have a column named V1 in your data.frame x1. Change your look up vector to a name that isn't a column name and everything should be fine, i.e.:

subset(x1, V3 %in% v1)

or use [ to index directly

x1[x1$V3 %in% V1,]

The proof is in the pudding:

txt1 <- "    V1 V2       V3 V4   V5 V6
1    1  2 84072082  1 2000  0
2    2  2 84046006  1 2000  0
3    3  2 84046006  1 2001  0
4    4  2 84046006  1 2002  0
5    5  2 84021002  1 2002  0
6    6  2 84021002  1 2003  0
7    7  2 84021002  1 2003  0
8    8  2 84021002  1 2004  0
9    9  2 84021002  1 2005  0
10  10  2 84021002  1 2005  0
11  11  2 12468015  1 2006  0
12  12  2 12468015  1 2007  0
13  96  2 12468015  2 2000  0"
txt2 <- "84021001 84021002 84021105 84046006 84046007 84046008 84046009 84046011 84046013 84046014"

x1 <- read.table(textConnection(txt1))
#Note the lowercase
v1 <- read.table(textConnection(txt2))
#Make "V1" as you have it
V1 <- v1 

> #Bad
> dim(subset(x1, V3 %in% V1))
[1] 0 6
> #Good
> dim(subset(x1, V3 %in% v1))
[1] 9 6
#Does subset method equal the direct indexing method
> all.equal(subset(x1, V3 %in% v1),x1[x1$V3 %in% V1,])
[1] TRUE