如何将宽格式DataFrame转为长格式并新增Event列？

可以使用tidyr包的pivot_longer()函数（替代旧版的gather()）来完成宽转长的操作，这个函数能更灵活地处理成对的列名。

步骤示例 #

1. 加载所需工具包：

library(dplyr)
library(tidyr)

2. 构造匹配你提供格式的示例数据：

df <- tibble(
  Date = c("01/01/2020", "02/01/2020", "03/01/2020", "04/01/2020", "05/01/2020", "06/01/2020",
           "07/01/2020", "08/01/2020", "09/01/2020", "10/01/2020", "11/01/2020", "12/01/2020"),
  `Event 1 start` = c("06:35", "06:36", "06:36", "06:36", "06:36", "06:37", "06:37", "06:37", "06:38", "07:38", "08:38", "09:38"),
  `Event 1 end` = c("18:02", "18:02", "18:01", "18:01", "18:01", "18:01", "18:01", "18:01", "18:01", "19:01", "20:01", "21:01"),
  `Event 2 start` = c("06:13", "06:13", "06:13", "06:14", "06:14", "06:14", "06:14", "06:15", "06:15", "07:15", "08:15", "09:15"),
  `Event 2 end` = c("18:24", "18:24", "18:24", "18:24", "18:23", "18:23", "18:23", "18:23", "18:23", "19:23", "20:23", "21:23"),
  `Event 3 start` = c("03:15", "04:02", "04:50", "05:39", "06:28", "07:18", "08:07", "08:56", "09:43", "10:43", "11:43", "12:43"),
  `Event 3 end` = c("15:18", "15:57", "16:37", "17:21", "18:06", "18:54", "19:43", "20:34", "21:26", "22:26", "23:26", "00:26")
)

3. 执行宽转长操作：

long_df <- df %>%
  pivot_longer(
    cols = -Date,  # 保留Date列，重塑其余所有列
    names_to = c("Event", ".value"),  # 将列名拆分：第一部分作为Event名称，第二部分作为值列名
    names_pattern = "(Event \\d+) (start|end)"  # 用正则匹配列名的结构，捕获需要的部分
  ) %>%
  select(Date, Event, Start = start, End = end)  # 调整列顺序和列名大小写（可选）

结果说明 #

执行后得到的long_df即为目标长格式：每一行对应一个日期+事件的组合，包含Date、Event、Start、End四列，完整保留原数据中的时间值。

注：你提供的目标格式中的时间值与原数据不一致，推测是输入错误，上述代码会保留原数据的真实时间数据。

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