将几个列合并成一个具有特定条件的列 [英] Merge several columns into one with specific conditions in R

问题描述

我有以下形式的数据：

Id1      Id21    c1      Id22    c2      Id23     c3      Id24       c4    
1         20      5        11     9        9      20       32        10
1         40      4        14     9        13      5       36         9
1         43      3        15     3        23      1       39         8     
2         47      5        17     8        11      9       10         5
2         5       4        12     8        14      8       28         4      
2         6       0        10     2        24      4       23         2
3         .       .         .     .         .      .       .          .
3         .       .         .     .         .      .       .          .
3          
4
.
.
100
100
100

Id1每个都有三个条目Id具有相应的Id2i和ci，i - > [1,4]
，使得id2i总是以递增的顺序，ci对于每个id1总是递减的。
我需要输出：

Id1 with three entries for each Id has corresponding Id2i and ci, i -> [1,4] such that id2i is always in increasing order and ci is always in decreasing order for each id1. I need the output to be :

Id1    Id2     c
1       9      20
1       32     10  
1       11     9
1       14     9
1       36     9
2       11     9
2       17     8
2       12     8
2       14     8
2       47     5
.
.
.
100
100
100
100
100     .      .

因此，在id1中的每个id的5个条目中，从所有ci中选择前5个c，使得c（输出）是所有ci的最大组。
如何在R中实现？

so that for five entries of each id in id1, top 5 c's are chosen from all the ci, such that c (output) is max group of all the ci. How this can be achieved in R ?

推荐答案

使用 dev版本的 data.table ：

# using first six rows from your post
require(data.table) # v1.9.5+
ans <- melt(setDT(df), measure = patterns(c("^Id2", "^c[0-9]$"))
         value.name = c("Id2", "c"))
ans[order(-c), head(.SD, 5L), by=Id1, .SDcols = -(variable)]
#     Id1 Id2  c
#  1:   1   9 20
#  2:   1  32 10
#  3:   1  11  9
#  4:   1  14  9
#  5:   1  36  9
#  6:   2  11  9
#  7:   2  17  8
#  8:   2  12  8
#  9:   2  14  8
# 10:   2  47  5

基本上， melt 可以接受列名列表，将列的每个元素的列分组成单独的列。看看 lapply（...）的结果，看看哪些列组合在一起。

Basically, melt can accept a list of column names to group columns from each element of the list into separate columns. Have a look at the result of lapply(...) to see which columns are combined together.

然后我们按照 c 列排序后按 Id1 分组，并从属于...的数据子集中选出前5行每组。

Then we group by Id1 after ordering by c column in decreasing order and pick the first 5 rows from subset of data belonging to each group.

这篇关于将几个列合并成一个具有特定条件的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！