如何基于Timestamp优雅实现Utterance成对数据的宽表转换？

我的Utterance列中存在Timestamp完全相同的成对数据点，希望将这些不同的Utterance对转换为宽表格式，让相同Timestamp的数据对齐到同一行。目前已实现需求，但想找到更优雅、直接的方法——能否省略mutate(Index_N = lag(Index_N))和filter(!is.na(Index_N))这两步，改用与Timestamp相关的逻辑替代？

原实现代码 #

library(tidyverse)
df %>%
  pivot_wider(names_from = Speaker, values_from = Utterance) %>%
  mutate(Index_N = lag(Index_N)) %>%      # 能否省略此步骤？
  filter(!is.na(Index_N))                 # 能否省略此步骤？

输出结果 #

# A tibble: 3 × 10
  Timestamp                   gest_dur stroke  hold  prep relax prehold nucleus Index_N                       Gesture_N                              
  <chr>                          <int>  <int> <int> <int> <int> <lgl>     <int> <chr>                         <chr>                                  
1 00:04:57.973 - 00:05:00.011     2038   1297    NA    NA    NA NA         1297 ((i: CV=0, SZ=0, FO=0, SL=0)) ((1_m: b h open palms in fingers exten…
2 00:05:00.011 - 00:05:00.924      913    252   169   492    NA NA          421 ((i: CV=0, SZ=0, FO=1, SL=0)) ((2_m: l h rotates palm to the left an…
3 00:05:00.924 - 00:05:01.847      923    247   209   467    NA NA          456 ((i: CV=0, SZ=1, FO=1, SL=0)) ((3_m: b h open palms facing in hands …

原始数据 #

df <- structure(list(Timestamp = c("00:04:57.973 - 00:05:00.011", "00:04:57.973 - 00:05:00.011", 
                                   "00:05:00.011 - 00:05:00.924", "00:05:00.011 - 00:05:00.924", 
                                   "00:05:00.924 - 00:05:01.847", "00:05:00.924 - 00:05:01.847"), 
                     Speaker = c("Index_N", "Gesture_N", "Index_N", "Gesture_N", 
                                 "Index_N", "Gesture_N"), Utterance = c("((i: CV=0, SZ=0, FO=0, SL=0))", 
                                                                        "((1_m: b h open palms in fingers extended @ct))", "((i: CV=0, SZ=0, FO=1, SL=0))", 
                                                                        "((2_m: l h rotates palm to the left and pushes away from lct to lperi))", 
                                                                        "((i: CV=0, SZ=1, FO=1, SL=0))", "((3_m: b h open palms facing in hands projected forwards @ctct))"
                                 ), gest_dur = c(NA, 2038L, NA, 913L, NA, 923L), stroke = c(NA, 
                                                                                            1297L, NA, 252L, NA, 247L), hold = c(NA, NA, NA, 169L, NA, 
                                                                                                                                 209L), prep = c(NA, NA, NA, 492L, NA, 467L), relax = c(NA_integer_, 
                                                                                                                                                                                        NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
                                                                                                                                 ), prehold = c(NA, NA, NA, NA, NA, NA), nucleus = c(NA, 1297L, 
                                                                                                                                                                                     NA, 421L, NA, 456L)), row.names = c(NA, -6L), class = c("tbl_df", 
                                                                                                                                                                                                                                             "tbl", "data.frame"))

优化方案 #

可以直接通过按Timestamp分组汇总的方式省略那两步，利用dplyr的group_by和summarise提取每列的非NA值，代码更直观且通用：

library(tidyverse)

df %>%
  pivot_wider(names_from = Speaker, values_from = Utterance) %>%
  group_by(Timestamp) %>%
  summarise(across(everything(), ~first(na.omit(.x))))

原理说明 #

原pivot_wider后，每个Timestamp会生成两行数据：一行仅Index_N有值，其余列多为NA；另一行仅Gesture_N有值，其余列包含有效数据。通过按Timestamp分组，再对每列提取第一个非NA值，就能直接将两行合并为一行，完全替代lag和filter的作用。

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