r - 计算两个时间戳之间的时间间隔

Question

有这样的数据框:

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"))

如何为每一行添加具有月时差的新列，并且当前插入“2022 年 9 月”。

示例输出:

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"), time_stamp_1_duration = c(41,43,24,53), time_stamp_2_duration = c(32,12,45,32))

持续时间只是一个例子，它不是真实的，只是举例。

Answer 1

这应该可以解决问题。首先将所有“Current”和“Sept”替换为 R 认可的缩写“Sep”，然后使用 tidy::separate 和 zoo::as.yearmon()转换为年月格式，然后计算间隔(每个 OP 以月 (x12) 为单位):

library(tidyr)
library(zoo)

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"))

# convert current and Sept to "Sep 2022"
df[2:3] <- lapply(df[2:3], function(x) gsub("-Current|-Sept 2022", "-Sep 2022", x))

df %>%
  separate(time_stamp_1, into = c("my1a", "my1b"), sep = "-") %>%
  separate(time_stamp_2, into = c("my2a", "my2b"), sep = "-") %>%
  mutate(across(my1a:my2b, ~ as.yearmon(.x, format = "%b %Y"))) %>%
  mutate(interval_1 = (my1b - my1a) * 12,
         interval_2 = (my2b - my2a) * 12) %>%
  left_join(df) %>% select(names(df), "interval_1", "interval_2")

输出:

  id      time_stamp_1      time_stamp_2 interval_1 interval_2
1  1 Nov 2016-Sep 2022 Mar 2015-Nov 2016         70         20
2  2 May 2013-Sep 2022 May 2008-May 2013        112         60
3  3 Oct 2015-Sep 2022 Aug 2005-Sep 2022         83        205
4  4 May 2014-Sep 2022 Oct 2014-Jan 2015        100          3

正如 G. Grothendieck 在评论中提到的，我们可以将其包装在一个函数中:

# thanks to G. Grothendieck

ts2mos <- function(x) {
    x <- gsub("-Current|-Sept 2022", "-Sep 2022", x)
    12 * (as.yearmon(sub(".*-", "", x)) - as.yearmon(x, "%b %Y"))
}

df %>%  mutate(interval_1 = ts2mos(time_stamp_1),
                interval_2 = ts2mos(time_stamp_2))