基于另一个数据集获取数据集的子集 [英] take subset of dataset based on another dataset
问题描述
假设我有一个数据集，即 dat1
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
然后我还有一个大数据集，就是dat2:
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
15 1 2 16 PRSE .
我的问题是如何从 dat2 中获取 ID、块和绘图与 dat1 中的数据匹配的数据子集?以及如何从 dat2 中获取 ID、block 和 plot 与 dat1 中的不匹配的数据子集?
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?
推荐答案
听起来你想要对列 ID、块和绘图进行 merge.
It sounds like you want a merge on the columns ID, block, and plot.
假设您的数据是在名为 dat1 和 dat2 中读取的，这应该是您想要的:
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
这实质上是对感兴趣的三列执行 SQL 术语内连接.如果您也感兴趣，请阅读有关左、右和外连接可能性的合并帮助页面.
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.
完全可重现的要点这里
要从 dat2 获取未合并的行，此 hack 有效.可能有一种更有效的方法可以做到这一点，但就是这样.首先，添加参数all.y = TRUE.这指定了一个右连接，它将从 dat2 返回未合并的行.然后我们可以知道没有合并的行将返回 NA's:
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)
这篇关于基于另一个数据集获取数据集的子集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
