如何用R计算前后均为失业状态的就业spell持续时长

我持有跨度1年的人群月度就业状态数据，dataframe包含4个变量：个人ID、国家（Country）、月份（Month）、对应月份的主要活动（Main Activity，取值为Employed、Unemployed、Inactive、Other），示例数据构造代码如下：

ID <- c(1:10, 1:10, 1:10)
country <- c("AT", "BE", "CH", "CZ", "HR", "SO", "SV", "RU", "GR", "GE", "AT", "BE", "CH", "CZ", "HR", 
             "SO", "SV", "RU", "GR", "GE", "AT", "BE", "CH", "CZ", "HR", 
             "SO", "SV", "RU", "GR", "GE")
month <- c("Jan", "Feb", "Mar", "Apr", "May", "Aug", "Dec", "Nov", "Sep", "Jan", "Jun", "Jul", "Oct",
           "Jan", "Feb", "Mar", "Apr", "May", "Aug", "Dec", "Nov", "Sep", "Jan", "Jun", "Jul", "Oct",
           "Nov", "Jan", "Jun", "Aug")
act <- c("Unemployed", "Employed", "Other", "Other", "Inactive", "Unemployed", "Employed", 
         "Employed", "Employed", "Unemployed", "Other", "Unemployed", "Unemployed", "Unemployed", 
         "Other", "Other", "Employed", "Other", "Other", "NA", "Unemployed", "Unemployed", NA, "Unemployed", "Other", "Employed", "Employed", NA, "Inactive", "Unemployed")
df <- data.frame(ID, country, month, act)
df[order(ID),]

输出的示例数据结构如下：

ID country month        act
1   1      AT   Jan Unemployed
11  1      AT   Jun      Other
21  1      AT   Nov Unemployed
2   2      BE   Feb   Employed
12  2      BE   Jul Unemployed
22  2      BE   Sep Unemployed
3   3      CH   Mar      Other
13  3      CH   Oct Unemployed
23  3      CH   Jan       <NA>
4   4      CZ   Apr      Other
14  4      CZ   Jan Unemployed
24  4      CZ   Jun Unemployed
5   5      HR   May   Inactive
15  5      HR   Feb      Other
25  5      HR   Jul      Other
6   6      SO   Aug Unemployed
16  6      SO   Mar      Other
26  6      SO   Oct   Employed
7   7      SV   Dec   Employed
17  7      SV   Apr   Employed
27  7      SV   Nov   Employed
8   8      RU   Nov   Employed
18  8      RU   May      Other
28  8      RU   Jan       <NA>
9   9      GR   Sep   Employed
19  9      GR   Aug      Other
29  9      GR   Jun   Inactive
10 10      GE   Jan Unemployed
20 10      GE   Dec         NA
30 10      GE   Aug Unemployed

需求说明 #

需要生成新的dataframe，每一行对应一段符合条件的就业spell：仅统计前后均为失业spell的就业周期，即从失业转为就业、最终又转回失业的完整就业周期，最终输出包含5个变量：个人ID、国家、spell持续时长、起始月份、结束月份，预期输出示例如下：

ID country spell_duration starting ending
1   1      AT              5      Jan    May
11  1      AT              5      Jun    Oct
2   2      BE              7      Feb    Aug
12  2      BE              6      Jul    Dec
3   3      CH             10      Mar    Dec
13  3      CH              1      Oct    Oct
4   4      CZ              8      Apr    Nov
14  4      CZ              5      Jan    May
5   5      HR              5      May    Sep
15  5      HR              4      Feb    May
6   6      SO              2      Aug    Sep
16  6      SO              6      Mar    Aug
7   7      SV              1      Dec    Dec
17  7      SV              9      Apr    Dec
8   8      RU              8      Nov    Dec
18  8      RU              7      May    Nov
9   9      GR              3      Sep    Nov
19  9      GR              2      Aug    Sep
10 10      GE              8      Jan    Aug
20 10      GE              1      Dec    Dec

实现代码 #

使用dplyr即可完成需求，先做月份的数值映射方便计算时长，再按ID分组识别符合条件的spell：

library(dplyr)

# 建立月份到数值的映射，方便时间排序和时长计算
month_map <- setNames(1:12, month.abb)

result <- df %>%
  # 过滤空值，新增月份数值列
  filter(!is.na(act), act != "NA") %>%
  mutate(month_n = month_map[month]) %>%
  # 按个人ID分组，按时间顺序排序
  group_by(ID) %>%
  arrange(month_n, .by_group = TRUE) %>%
  # 标记失业状态，用失业状态作为spell的分割边界
  mutate(is_unemployed = act == "Unemployed",
         spell_id = cumsum(is_unemployed)) %>%
  # 过滤掉失业行，剩下的就是夹在两次失业中间的就业相关周期
  filter(!is_unemployed) %>%
  # 按个人、国家、spell分组统计周期信息
  group_by(ID, country, spell_id) %>%
  summarise(
    spell_duration = max(month_n) - min(month_n) + 1,
    starting = month[which.min(month_n)],
    ending = month[which.max(month_n)],
    .groups = "drop"
  ) %>%
  select(-spell_id)

如果需要处理跨年的12月到1月的连续周期，可以给小于前序月份的数值加12后再计算时长即可。 #

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