Data.frame合并和选择2 Data.frames中常见的值 [英] Data.frame Merge and Selection of values which are common in 2 Data.frames
问题描述
我有3个data.frames
I have 3 data.frames
> head(ON1)
Entrez.ID Nearest.Refseq Gene.Name Tag.Count
1 11302 NM_007377 Aatk 137.48
2 11303 NM_013454 Abca1 118.09
3 11305 NM_007379 Abca2 93.56
4 11306 NM_009592 Abcb7 92.42
5 11308 NM_007380 Abi1 410.73
6 11356 NM_009598 Abl5 149.46
> head(ON2)
Entrez.ID Nearest.Refseq Gene.Name Tag.Count
1 11303 NM_013454 Abca1 86.02
2 11305 NM_007379 Abca2 103.45
3 11306 NM_009592 Abcb7 95.32
4 11308 NM_007380 Abi1 313.85
5 11350 NM_009594 Abl1 116.24
6 11352 NM_009595 Abl2 155.76
> head(ON3)
Entrez.ID Nearest.Refseq Gene.Name Tag.Count
1 11303 NM_013454 Abca1 69.49
2 11305 NM_007379 Abca2 82.02
3 11306 NM_009592 Abcb7 83.16
4 11308 NM_007380 Abi1 306.44
5 11350 NM_009594 Abl1 150.37
6 11355 NM_009599 Abl4 154.93
某些行对于只有1个data.frame（例如 row1 ON1 ）而言是唯一的，有些行是常见的2 data.frames（例如 row5 ON2 和 ON3 ，这行不存在于 ON1 ）中，有些在所有data.frames（例如 row2 code> ON1 这是 row1 在 ON2 和 ON3 ）。 唯一的区别是最后一列中的值 Tag.Count
Some rows are unique to only 1 data.frame (e.g. row1 of ON1), some are common in 2 data.frames (e.g. row5 of ON2 and ON3, this row doesn't exist in ON1) and some are common in all data.frames (e.g. row2 of ON1 which is row1 in ON2 and ON3). The only difference is the values in last column Tag.Count
我想合并所有3个数据框架，以便我只获取最终数据框架中最少的两个数据框架中常见的那些行，以及 Tag.Count 其中较高者将被分配到该行。
I want to merge all 3 data.frames in such a way that I obtain only those rows in my final data.frame which are common among at-least 2 data.frames and the value of Tag.Count which is higher among them will be assigned to that row.
> head(F)
Entrez.ID Nearest.Refseq Gene.Name Tag.Count
1 11303 NM_013454 Abca1 118.09
2 11305 NM_007379 Abca2 103.45
3 11306 NM_009592 Abcb7 95.32
4 11308 NM_007380 Abi1 410.73
5 11350 NM_009594 Abl1 150.37
Entrez.ID = 11302 被删除，因为它只在所有的数据框架和那些常见的行中发出一次在这里至少显示2个data.frames，但是所有data.frames中最大的 Tag.Count 分数分配给该行。
Here you see that rows with Entrez.ID = 11302 is removed as it only appeard once among all the data.frames and those rows which were common in at least 2 data.frames are appearing here but the Tag.Count score which was maximum among all the data.frames is assigned to that row.
更新
如何平均排列。在三个数据集上合并之后，而不是仅保留一行最大 Tag.count 值，我想将 Tag.count value并除以具有相同的总数的 Entrez.ID 。实际上前3列的值是相同的，差异仅在最后一列出现。
示例：
How to take average of rows. After merging above three datasets, instead of just keeping one row which has maximum Tag.count value, I want to sum the Tag.count value and divide by total number of rows having the same Entrez.ID. In fact the values in the first 3 columns are the same, difference only arises in last column.
Example:
> head(d)
Entrez.ID Nearest.Refseq Gene.Name Tag.Count
1 11302 NM_007377 Aatk 137.48
2 11303 NM_013454 Abca1 118.09
7886 11303 NM_013454 Abca1 86.02
15407 11303 NM_013454 Abca1 69.49
3 11305 NM_007379 Abca2 93.56
7887 11305 NM_007379 Abca2 103.45
所以，在这种情况下，由于3行具有 Entrez.ID = 11303， Tag.count 值将总计（118.09 + 86.02 + 69.49），除以3，最终输出将只包含1行， Entrez.ID 11303和 Tag.Count值 =行的Sum / no.of
So, in this case as 3 rows have Entrez.ID = 11303, the Tag.count values will be summed up (118.09 + 86.02 + 69.49) and divided by 3 and final output will contain only 1 row having Entrez.ID 11303 and Tag.Count value = Sum/no.of Rows
推荐答案
这是一种组合三个数据帧。结合三者之后，我们发现不止一次出现的值。使用该索引，我们可以使用函数 max 汇总数据框：
Here is a way with combining the three data frames. After combining all three, we find the values that appear more than once. With that index we can aggregate the data frame with the function max:
d <- do.call(rbind, list(ON1, ON2, ON3))
d1 <- do.call(paste, d[1:3])
tbl <- table(d1) > 1L
indx <- d1 %in% names(tbl[tbl])
aggregate(Tag.Count ~., d[indx,], FUN=max)
# Entrez.ID Nearest.Refseq Gene.Name Tag.Count
# 1 11303 NM_013454 Abca1 118.09
# 2 11305 NM_007379 Abca2 103.45
# 3 11306 NM_009592 Abcb7 95.32
# 4 11308 NM_007380 Abi1 410.73
# 5 11350 NM_009594 Abl1 150.37
这篇关于Data.frame合并和选择2 Data.frames中常见的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
