如何为R数据框添加时间戳月间隔列？将Current替换为Sept 2022

首先定义原始数据框：

df <- data.frame(
  id = c(1,2,3,4),
  time_stamp_1 = c("Nov 2016-Current", "May 2013-Current", "Oct 2015-Current", "May 2014-Current"),
  time_stamp_2 = c("Mar 2015-Nov 2016", "May 2008-May 2013", "Aug 2005-Current", "Oct 2014-Jan 2015"),
  stringsAsFactors = FALSE
)

步骤1：替换"Current"为指定日期 #

用gsub批量替换所有时间戳里的"Current"为"Sept 2022"，同时创建临时列用于后续计算：

df$ts1_temp <- gsub("Current", "Sept 2022", df$time_stamp_1)
df$ts2_temp <- gsub("Current", "Sept 2022", df$time_stamp_2)

步骤2：拆分时间范围并转换日期格式 #

使用tidyr::separate拆分临时列为起始和结束时间，再用lubridate::myd转换为日期格式（用trimws去除拆分后可能的空格，避免转换出错）：

library(tidyr)
library(lubridate)

df <- separate(df, ts1_temp, into = c("ts1_start", "ts1_end"), sep = "-", remove = FALSE)
df <- separate(df, ts2_temp, into = c("ts2_start", "ts2_end"), sep = "-", remove = FALSE)

df$ts1_start <- myd(trimws(df$ts1_start))
df$ts1_end <- myd(trimws(df$ts1_end))
df$ts2_start <- myd(trimws(df$ts2_start))
df$ts2_end <- myd(trimws(df$ts2_end))

步骤3：计算月份差并整理结果 #

用interval结合%/% months(1)计算两个日期的整月间隔，最后清理临时列保留目标字段：

df$time_stamp_1_duration <- interval(df$ts1_start, df$ts1_end) %/% months(1)
df$time_stamp_2_duration <- interval(df$ts2_start, df$ts2_end) %/% months(1)

# 保留需要的列
df <- df[, c("id", "time_stamp_1", "time_stamp_2", "time_stamp_1_duration", "time_stamp_2_duration")]

最终输出结果：

print(df)
#   id          time_stamp_1          time_stamp_2 time_stamp_1_duration time_stamp_2_duration
# 1  1 Nov 2016-Current Mar 2015-Nov 2016                    70                    20
# 2  2 May 2013-Current May 2008-May 2013                    112                    60
# 3  3 Oct 2015-Current Aug 2005-Current                    83                   205
# 4  4 May 2014-Current Oct 2014-Jan 2015                    100                     3

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