R语言tibble行序调整：同Starttime_ms时长更长行前置

我们有如下结构的tibble数据：

df <- structure(list(id = c(4, 5, 6, 7, 8, 9, 10, 11, 12), Utterance = c("yeah", 
                                                                         "Come Home ", ">last time ", "two weeks ago?  ", 
                                                                         "and X", 
                                                                         "and then last night", "are you sure?", 
                                                                         "Come Home climax ", "=she said  Y"
), Story = c(NA, "Come Home ", NA, NA, NA, NA, NA, NA, NA), Climax = c(NA, 
                                                                       NA, NA, NA, NA, NA, NA, "Come Home climax ", NA), Starttime_ms = c(20405, 
                                                                                                                                          20405, 23818, 25470, 26623, 32688, 34099, 34099, 38075), Endtime_ms = c(23532, 
                                                                                                                                                                                                                  47677, 25110, 26259, 32103, 33797, 37542, 39895, 39895)), row.names = c(NA, 
                                                                                                                                                                                                                                                                                          -9L), class = c("tbl_df", "tbl", "data.frame"))

原始数据展示：

df
# A tibble: 9 × 6
     id Utterance             Story        Climax              Starttime_ms Endtime_ms
  <dbl> <chr>                 <chr>        <chr>                      <dbl>      <dbl>
1     4 "yeah"                 NA           NA                        20405      23532
2     5 "Come Home "          "Come Home "  NA                        20405      47677
3     6 ">last time "          NA           NA                        23818      25110
4     7 "two weeks ago?  "     NA           NA                        25470      26259
5     8 "and X"                NA           NA                        26623      32103
6     9 "and then last night"  NA           NA                        32688      33797
7    10 "are you sure?"        NA           NA                        34099      37542
8    11 "Come Home climax "    NA          "Come Home climax "        34099      39895
9    12 "=she said  Y"         NA           NA                        38075      39895

需求说明 #

当某行与其他行Starttime_ms相同，且该行的时间间隔（Endtime_ms - Starttime_ms）大于另一行时，将时长更长的行排在前面，最终得到目标排序结果。

解决方案 #

使用dplyr包的分组排序功能，按Starttime_ms分组后，在每组内按时间间隔降序排列即可实现需求：

library(dplyr)

df_updated <- df %>%
  group_by(Starttime_ms) %>%
  arrange(desc(Endtime_ms - Starttime_ms), .by_group = TRUE) %>%
  ungroup()

# 查看调整后的结果
df_updated

逻辑解释 #

• group_by(Starttime_ms)：将数据按起始时间分组，确保仅在相同起始时间的条目内调整顺序
• arrange(desc(Endtime_ms - Starttime_ms), .by_group = TRUE)：在每组内，按时间间隔从长到短排序（时长越长越靠前），.by_group = TRUE保证分组内的排序逻辑生效
• ungroup()：取消分组，恢复普通tibble结构

内容的提问来源于stack exchange，提问作者Chris Ruehlemann