如何基于部署日期列填充音频记录仪的回收日期列

我有一份音频记录仪的部署数据集，技术人员记录了记录仪部署的日期时间，以及后续更换电池和存储卡时的日期（即新录音开始的日期）。现在需要按recorder_id分组，将每个记录仪下一条记录的date_of_deployment值填充到当前记录的date_of_pickup列中。

数据集如下：

data <- structure(list(recorder_id = c("smm03978", "smm03895", "smm03922", 
"smm03886", "smm03918", "smm04004", "smm03988", "smm03890", "smm03753", 
"smm03976", "smm03988", "smm04004", "smm03918", "smm03922", "smm03886", 
"smm03976", "smm03895", "smm03753", "smm04004", "smm03753", "smm03890", 
"smm03978", "smm03918", "smm03895", "smm03978", "smm03976", "smm04004", 
"smm03988", "smm03922", "smm03886", "smm03753", "smm03890"), 
    card_id = c("msd-1285\n", "msd-1326", "msd- 0198", "msd-1463", 
    "msd-1478", "msd-0722", "msd-1365", "msd-0807", "msd-0247", 
    "msd-1430", "msd-2683", NA, "msd-2687", "msd-0530", "msd-2682", 
    "msd-2688", "msd-2684", NA, "msd-2826", "msd-2830", "msd-2829", 
    "msd-2828", "msd-2885", "msd-2881", "msd-2878", "msd-2882", 
    "msd-2879", "msd-2877", "msd-2884", "msd-2880", "msd-2886", 
    "msd-2883"), date_of_deployment = c("2022-04-02 13:50:00", 
    "2022-04-02 14:03:00", "2022-04-02 14:41:00", "2022-04-02 14:58:00", 
    "2022-04-02 17:34:00", "2022-04-02 18:27:00", "2022-04-02 18:49:00", 
    "2022-04-03 15:19:00", "2022-04-03 15:32:00", "2022-04-03 16:06:00", 
    "2022-06-24 01:42:00", "2022-06-23 15:44:00", "2022-06-23 16:15:00", 
    "2022-06-23 17:17:00", "2022-06-23 17:32:00", "2022-06-23 18:00:00", 
    "2022-06-23 18:26:00", "2022-06-29 01:25:00", "2022-07-14 13:46:00", 
    "2022-07-15 17:30:00", "2022-07-15 17:48:00", "2022-07-15 18:27:00", 
    "2022-09-10 15:00:00", "2022-09-10 15:27:00", "2022-09-10 15:37:00", 
    "2022-09-10 16:03:00", "2022-09-10 16:38:00", "2022-09-10 16:45:00", 
    "2022-09-10 18:29:00", "2022-09-10 18:38:00", "2022-09-10 19:04:00", 
    "2022-09-10 19:14:00"), date_of_pickup = c("", "", "", "", 
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 
"", "", "", "", "", "", "", "", "", "", "", "", "")), row.names = c(NA, 
-32L), class = c("tbl_df", "tbl", "data.frame"))

使用dplyr包即可实现需求，步骤如下：

• 转换部署时间为时间类型，确保排序准确
• 按recorder_id分组，组内按部署时间升序排序
• 用lead()函数将下一条记录的部署时间赋值给当前记录的回收时间
• 可选：保留原数据的行顺序

代码示例：

library(dplyr)

# 处理数据集
data_processed <- data %>%
  # 转换部署时间为POSIXct类型
  mutate(date_of_deployment = as.POSIXct(date_of_deployment)) %>%
  # 按记录仪ID分组，组内按部署时间排序
  group_by(recorder_id) %>%
  arrange(date_of_deployment, .by_group = TRUE) %>%
  # 填充回收日期为下一条部署日期
  mutate(date_of_pickup = lead(date_of_deployment)) %>%
  # 取消分组
  ungroup() %>%
  # 恢复原数据的行顺序（可根据需求删除此行）
  arrange(row.names(.))

# 查看处理后的结果
head(data_processed)

关键说明 #

• lead()函数会提取同一分组内下一行的部署时间，每组最后一条记录的date_of_pickup会是NA，符合业务逻辑（最后一次部署尚未进行回收/更换）
• 转换时间类型是为了避免字符串排序可能出现的错误
• 如果不需要保留原行顺序，可删除最后一行arrange(row.names(.))，结果会按recorder_id和部署时间排序

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