为序列中正负值分配位置：求更高效的R语言解决方案

原始数据框 #

df = tibble(
        quarter = c(seq.Date(as.Date("2022-03-01"), as.Date("2023-12-01"), "quarter")),
        value = c(rnorm(3), 0, rnorm(4))
     )

数据预览：

# A tibble: 8 × 2
  quarter       value
  <date>        <dbl>
1 2022-03-01 -0.670  
2 2022-06-01 -0.00760
3 2022-09-01  1.78   
4 2022-12-01  0      
5 2023-03-01 -1.14   
6 2023-06-01  1.37   
7 2023-09-01  1.33   
8 2023-12-01  0.336 

需求说明 #

为连续同符号的正负值分配序列位置（例如1st negative），值为0时标记为zero，期望输出如下：

# A tibble: 8 × 3
  quarter       value position    
  <date>        <dbl> <chr>       
1 2022-03-01 -0.670   1st negative
2 2022-06-01 -0.00760 2nd negative
3 2022-09-01  1.78    1st positive
4 2022-12-01  0       zero        
5 2023-03-01 -1.14    1st negative
6 2023-06-01  1.37    1st positive
7 2023-09-01  1.33    2nd positive
8 2023-12-01  0.336   3rd positive

现有解决方案（不够优雅） #

df %>% 
   mutate(position = coalesce(accumulate(ifelse(sign(value) - lag(sign(value)) != 0 | row_number() == 1, 1, NA),
                                         ~ ifelse(is.na(.y), .x + 1, .y))),
          position = ifelse(sign(value) > 0, paste(position, "pos"), 
                            ifelse(sign(value) < 0, paste(position, "neg"), 'zero')))

输出结果：

# A tibble: 8 × 3
  quarter       value position
  <date>        <dbl> <chr>   
1 2022-03-01 -0.670   1 neg   
2 2022-06-01 -0.00760 2 neg   
3 2022-09-01  1.78    1 pos   
4 2022-12-01  0       zero    
5 2023-03-01 -1.14    1 neg   
6 2023-06-01  1.37    1 pos   
7 2023-09-01  1.33    2 pos   
8 2023-12-01  0.336   3 pos

更高效的实现方法 #

可以利用dplyr的分组能力结合连续区间识别函数，逻辑更清晰且易维护：

方法1：使用data.table的rleid #

library(dplyr)
library(data.table)

df %>%
  mutate(sign_group = case_when(
    value > 0 ~ "positive",
    value < 0 ~ "negative",
    TRUE ~ "zero"
  )) %>%
  # 按连续的符号组分组
  group_by(grp = rleid(sign_group)) %>%
  mutate(
    pos_num = ifelse(sign_group != "zero", row_number(), NA),
    # 转换为序数词
    ordinal = case_when(
      pos_num == 1 ~ "1st",
      pos_num == 2 ~ "2nd",
      pos_num == 3 ~ "3rd",
      pos_num >=4 ~ paste0(pos_num, "th"),
      TRUE ~ NA_character_
    ),
    position = coalesce(paste(ordinal, sign_group), "zero")
  ) %>%
  ungroup() %>%
  select(-sign_group, -grp, -pos_num, -ordinal)

方法2：仅用dplyr（需dplyr 1.1.0+） #

用consecutive_id替代rleid，无需额外加载data.table：

library(dplyr)

df %>%
  mutate(sign_group = case_when(
    value > 0 ~ "positive",
    value < 0 ~ "negative",
    TRUE ~ "zero"
  )) %>%
  group_by(grp = consecutive_id(sign_group)) %>%
  mutate(
    pos_num = ifelse(sign_group != "zero", row_number(), NA),
    ordinal = case_when(
      pos_num == 1 ~ "1st",
      pos_num == 2 ~ "2nd",
      pos_num == 3 ~ "3rd",
      pos_num >=4 ~ paste0(pos_num, "th"),
      TRUE ~ NA_character_
    ),
    position = coalesce(paste(ordinal, sign_group), "zero")
  ) %>%
  ungroup() %>%
  select(-sign_group, -grp, -pos_num, -ordinal)

两种方法最终输出均与期望一致，核心逻辑：

1. 先给每个值标记符号分组（positive/negative/zero）
2. 识别连续同符号的区间并分组
3. 每组内对非零值生成行号，转换为对应序数词
4. 拼接序数词与符号描述，零值直接标记为zero

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