CODEWITHSUNDEEP
rdataframesubset
SHRram
SHRram

Reputation: 4227

subset dataset by matching in r

I have bigger dataset like follows:

l = 9
loc <- c(paste('Loc', 1:l, sep = ''))
 vloc <- c(rep(loc, each=2))
 qi <- c(  13, 12, 27, 20, 16, 18,  14, 17, 15, 22, 21, 26,12, 14, 11,
   18,  8, 24  )
    afreq <- c( 0.308, 0.4, 0.041, 0.5, 0.125, 0.5,0.139, 0.2, 0.219, 0.2,0.176, 
    0.33,0.358, 0.4, 0.274, 0.5, 0.173, 0.15)   
 loctab <- data.frame(vloc = vloc, qi = qi, afreq = afreq)
loctab 

   vloc     qi afreq
1  Loc1     13 0.308
2  Loc1     12 0.400
3  Loc2     27 0.041
4  Loc2     20 0.500
5  Loc3     16 0.125
6  Loc3     18 0.500
7  Loc4     14 0.139
8  Loc4     17 0.200
9  Loc5     15 0.219
10 Loc5     22 0.200
11 Loc6     21 0.176
12 Loc6     26 0.330
13 Loc7     12 0.358
14 Loc7     14 0.400
15 Loc8     11 0.274
16 Loc8     18 0.500
17 Loc9      8 0.173
18 Loc9     24 0.150

And smaller dataset is as follows: 

ex1 <-data.frame (loc, qi = c(13, 27, 16, 14, 15, 21, 12, 11,  8)
ex1

   loc qi
1 Loc1 13
2 Loc2 27
3 Loc3 16
4 Loc4 14
5 Loc5 15
6 Loc6 21
7 Loc7 12
8 Loc8 11
9 Loc9  8

For for each loc, I need to match qi value between ex1 (small) and loctab (big) and create a newtable.

I tried the following but is not not providing correct answer. 

 nloct <- loctab[loctab$qi %in%  ex1$qit, ]

The expected output is 

nloct <- data.frame (loc, qi = c(13, 27, 16, 14, 15, 21, 12, 11,  8), 
afreq = c( 0.308,  0.041,  0.125, 0.139, 0.219,0.176, 0.358,  0.274,  0.173))
  loc qi afreq
1 Loc1 13 0.308
2 Loc2 27 0.041
3 Loc3 16 0.125
4 Loc4 14 0.139
5 Loc5 15 0.219
6 Loc6 21 0.176
7 Loc7 12 0.358
8 Loc8 11 0.274
9 Loc9  8 0.173

Upvotes: 1

Views: 1432

Answers (2)

nograpes
nograpes

Reputation: 18323

merge(loctab,ex1,by.x=c('vloc','qi'),by.y=c('loc','qi'))

But if you named the columns 'vloc' and 'loc' the same thing, you could just say:

merge(loctab,ex1)

Upvotes: 0

Josh O&#39;Brien
Josh O&#39;Brien

Reputation: 162321

You are looking for some sort of call to merge():

## e.g. :
## merge(ex1, loctab)[c(2,1,4)]
## OR
merge(ex1, loctab, by.x=c("loc", "qi"), by.y=c("vloc", "qi"))
   loc qi afreq
1 Loc1 13 0.308
2 Loc2 27 0.041
3 Loc3 16 0.125
4 Loc4 14 0.139
5 Loc5 15 0.219
6 Loc6 21 0.176
7 Loc7 12 0.358
8 Loc8 11 0.274
9 Loc9  8 0.173

Upvotes: 3

Related Questions