R语言长表转宽表：按周期合并行列并保留A的timestamp

原始数据 #

df <- data.frame(
  timestamp = c(1675930826.3839524, 1675930826.3839593, 1675930826.3839765, 1675930826.388385, 1675930826.3884094, 1675930826.3884153),
  label = c("A", "B", "C", "A", "B", "C"),
  value = c(1.996, 0.404, 4.941, 1.996, 0.404, 4.941)
)

需求说明 #

数据按A、B、C的周期循环，需将每个周期的A、B、C值合并到一行，保留该周期A对应的timestamp，输出格式如下：

timestamp          A      B      C
1675930826.3839524 1.996  0.404  4.941
1675930826.388385  1.996  0.404  4.941 

错误代码分析 #

你尝试的代码先转宽表再转长表，逻辑绕回了原始结构，无法实现合并需求。

正确解决方法 #

方法一：分组标识法 #

通过cumsum(label == "A")生成每个周期的分组ID，统一分组内的timestamp后转宽表去重：

library(dplyr)
library(tidyr)

df %>%
  mutate(group = cumsum(label == "A")) %>%
  pivot_wider(names_from = label, values_from = value) %>%
  group_by(group) %>%
  mutate(timestamp = first(timestamp)) %>%
  ungroup() %>%
  distinct(timestamp, A, B, C)

方法二：填充timestamp法 #

先将非A行的timestamp设为NA，向下填充A的timestamp后转宽表去重：

library(dplyr)
library(tidyr)

df %>%
  mutate(timestamp = ifelse(label == "A", timestamp, NA)) %>%
  fill(timestamp, .direction = "down") %>%
  pivot_wider(names_from = label, values_from = value) %>%
  distinct()

两种方法都能得到符合要求的输出，方法二更简洁直接。

内容的提问来源于stack exchange，提问作者Tricker Macedonia