take subset of dataset based on another dataset

suppose I have one dataset, which is dat1

ID  block   plot    SPID    TotHeight
1   1   1   4   44.5
2   1   1   4   51
3   1   1   4   28.7
4   1   1   4   24.5
5   1   1   4   27.3
6   1   1   4   20
17  1   10  1   44.5
19  1   10  1   51
1   1   11  21  28.7
2   1   11  21  24.5
3   1   11  21  27.3
4   1   11  21  20
5   1   11  21  12.88666667
6   1   11  21  7.235238095
7   1   11  21  1.583809524

Then I have another big dataset, which is dat2:

ID  block   plot    SPID    Species TotHeight
1   1   1   4   BENI    72
2   1   1   4   BENI    55
3   1   1   4   BENI    51
4   1   1   4   BENI    47
5   1   1   4   BENI    49
6   1   1   4   BENI    34
7   1   1   4   BENI    .
8   1   1   4   BENI    51
9   1   1   4   BENI    66
10  1   1   4   BENI    40
11  1   1   4   BENI    24
12  1   1   4   BENI    62
13  1   1   4   BENI    34
14  1   1   4   BENI    49
15  1   1   4   BENI    57
16  1   1   4   BENI    22
17  1   1   4   BENI    76
18  1   1   4   BENI    56
19  1   1   4   BENI    55
20  1   1   4   BENI    29
21  1   1   4   BENI    24
22  1   1   4   BENI    18
23  1   1   4   BENI    65
24  1   1   4   BENI    55
25  1   1   4   BENI    63
26  1   1   4   BENI    57
27  1   1   4   BENI    57
28  1   1   4   BENI    57
29  1   1   4   BENI    45
30  1   1   4   BENI    83
31  1   1   4   BENI    37
32  1   1   4   BENI    56
33  1   1   4   BENI    65
34  1   1   4   BENI    75
35  1   1   4   BENI    51
36  1   1   4   BENI    .
1   1   2   16  PRSE    141
2   1   2   16  PRSE    192
3   1   2   16  PRSE    .
4   1   2   16  PRSE    197
5   1   2   16  PRSE    172
6   1   2   16  PRSE    143
7   1   2   16  PRSE    141
8   1   2   16  PRSE    155
9   1   2   16  PRSE    167
10  1   2   16  PRSE    155
11  1   2   16  PRSE    175
12  1   2   16  PRSE    190
13  1   2   16  PRSE    148
14  1   2   16  PRSE    180
1开发者_如何学运维5  1   2   16  PRSE    .

My question is how can I take a subset of data from dat2 in which ID, block and plot match those in dat1? And how can I get a subset of data from dat2 in which ID, block and plot do not match those in dat1?

It sounds like you want a merge on the columns ID, block, and plot.

Assuming your data are read in named dat1 and dat2, this should be what you want:

> merge(dat1, dat2, by = c("ID", "block", "plot"))
  ID block plot SPID.x TotHeight.x SPID.y Species TotHeight.y
1  1     1    1      4        44.5      4    BENI          72
2  2     1    1      4        51.0      4    BENI          55
3  3     1    1      4        28.7      4    BENI          51
4  4     1    1      4        24.5      4    BENI          47
5  5     1    1      4        27.3      4    BENI          49
6  6     1    1      4        20.0      4    BENI          34

This essentially performs an inner join in SQL terms on the three columns of interest. Read the help page for merge for left, right, and outer join possibilities if that is also of interest.

Fully reproducible gist here

To get the rows that didn't merge from dat2, this hack works. There is probably a more efficient way to do this, but here it is. First, add the parameter all.y = TRUE. This specifies a right join which will return rows from dat2 which did not merge. Then we can subset on that knowing that the rows that didn't merge will return NA's:

subset(merge(dat1, dat2, by = c("ID", "block", "plot"), all.y = TRUE), is.na(SPID.x) == TRUE)