如何在R中为DataFrame的周期波动率组计算中间百分比均值

我们有一个记录波动率数据的DataFrame，包含以下维度：

• 周期：3MTH、6MTH、9MTH、12MTH
• 百分比档位：90%、100%、110%
• 核心需求：对每个周期组，计算相邻档位的均值生成中间档位（95%、105%），同时保留原始档位的数据。

比如9MTH组的计算逻辑：

• 9MTH_IMPVOL_95%MNY_DF = (9MTH_IMPVOL_90%MNY_DF + 9MTH_IMPVOL_100%MNY_DF) / 2
• 9MTH_IMPVOL_105%MNY_DF = (9MTH_IMPVOL_100%MNY_DF + 9MTH_IMPVOL_110%MNY_DF) / 2

下面用tidyverse工具链（dplyr + tidyr）实现，步骤清晰易维护：

1. 加载依赖包并构造示例数据 #

先安装并加载tidyverse，再把你提供的数据转换成可操作的DataFrame：

# 安装包（首次运行需执行）
install.packages("tidyverse")

library(tidyverse)

# 构造示例数据
df <- tribble(
  ~datestamp, ~entity, ~short_name, ~item, ~value, ~source,
  "2006-01-03", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "3MTH_IMPVOL_90%MNY_DF", 23.9, "PSEC",
  "2006-01-03", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "6MTH_IMPVOL_100.0%MNY_DF", 18.0, "PSEC",
  "2006-01-03", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "9MTH_IMPVOL_110%MNY_DF", 16.2, "PSEC",
  "2006-01-03", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "12MTH_IMPVOL_90%MNY_DF", 21.0, "PSEC",
  "2006-01-03", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "3MTH_IMPVOL_100%MNY_DF", 18, "PSEC",
  "2006-01-03", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "6MTH_IMPVOL_110.0%MNY_DF", 15.8, "PSEC",
  "2006-01-03", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "9MTH_IMPVOL_90%MNY_DF", 21.5, "PSEC",
  "2006-01-03", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "12MTH_IMPVOL_100%MNY_DF", 18.5, "PSEC",
  "2006-01-03", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "3MTH_IMPVOL_110%MNY_DF", 15.0, "PSEC",
  "2006-01-03", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "6MTH_IMPVOL_90.0%MNY_DF", 22.0, "PSEC",
  "2006-01-03", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "9MTH_IMPVOL_100%MNY_DF", 18.3, "PSEC",
  "2006-01-03", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "12MTH_IMPVOL_110%MNY_DF", 16.7, "PSEC",
  "2006-01-04", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "3MTH_IMPVOL_90%MNY_DF", 23.9, "PSEC",
  "2006-01-04", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "6MTH_IMPVOL_100.0%MNY_DF", 18.0, "PSEC",
  "2006-01-04", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "9MTH_IMPVOL_110%MNY_DF", 16.2, "PSEC",
  "2006-01-04", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "12MTH_IMPVOL_90%MNY_DF", 21.0, "PSEC",
  "2006-01-04", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "3MTH_IMPVOL_100%MNY_DF", 18, "PSEC",
  "2006-01-04", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "6MTH_IMPVOL_110.0%MNY_DF", 15.8, "PSEC",
  "2006-01-04", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "9MTH_IMPVOL_90%MNY_DF", 21.5, "PSEC",
  "2006-01-04", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "12MTH_IMPVOL_100%MNY_DF", 18.5, "PSEC",
  "2006-01-04", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "3MTH_IMPVOL_110%MNY_DF", 15.0, "PSEC",
  "2006-01-04", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "6MTH_IMPVOL_90.0%MNY_DF", 22.0, "PSEC",
  "2006-01-04", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "9MTH_IMPVOL_100%MNY_DF", 18.3, "PSEC",
  "2006-01-04", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "12MTH_IMPVOL_110%MNY_DF", 16.7, "PSEC",
  "2006-01-05", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "3MTH_IMPVOL_90%MNY_DF", 23.9, "PSEC",
  "2006-01-05", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "6MTH_IMPVOL_100.0%MNY_DF", 18.0, "PSEC",
  "2006-01-05", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "9MTH_IMPVOL_110%MNY_DF", 16.2, "PSEC",
  "2006-01-05", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "12MTH_IMPVOL_90%MNY_DF", 21.0, "PSEC",
  "2006-01-05", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "3MTH_IMPVOL_100%MNY_DF", 18, "PSEC",
  "2006-01-05", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "6MTH_IMPVOL_110.0%MNY_DF", 15.8, "PSEC",
  "2006-01-05", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "9MTH_IMPVOL_90%MNY_DF", 21.5, "PSEC",
  "2006-01-05", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "12MTH_IMPVOL_100%MNY_DF", 18.5, "PSEC",
  "2006-01-05", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "3MTH_IMPVOL_110%MNY_DF", 15.0, "PSEC",
  "2006-01-05", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "6MTH_IMPVOL_90.0%MNY_DF", 22.0, "PSEC",
  "2006-01-05", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "9MTH_IMPVOL_100%MNY_DF", 18.3, "PSEC",
  "2006-01-05", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "12MTH_IMPVOL_110%MNY_DF", 16.7, "PSEC",
  "2006-01-06", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "3MTH_IMPVOL_90%MNY_DF", 23.8, "PSEC",
  "2006-01-06", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "6MTH_IMPVOL_100.0%MNY_DF", 18.1, "PSEC",
  "2006-01-06", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "9MTH_IMPVOL_110%MNY_DF", 16.2, "PSEC",
  "2006-01-06", "JALSHTR Index", "FTSE/JSE Africa All Shares Index", "12MTH_IMPVOL_90%MNY_DF", 21.0, "PSEC"
) %>%
  mutate(datestamp = as.Date(datestamp))

2. 拆分item列，提取周期和百分比 #

用正则表达式从item字段中拆分出周期和百分比数值，方便后续分组计算：

df_processed <- df %>%
  mutate(
    # 提取周期部分（如3MTH、6MTH）
    period = str_extract(item, "^[0-9]+MTH"),
    # 提取百分比数值，去掉%和可能的.0后缀
    pct = str_extract(item, "[0-9]+(?:\\.[0-9]+)?(?=%)") %>% as.numeric()
  )

3. 宽格式转换与中间值计算 #

将数据转换为宽格式，让同一周期、同一日期的不同百分比数值在同一行，然后计算95%和105%的均值：

df_wide <- df_processed %>%
  select(datestamp, entity, short_name, source, period, pct, value) %>%
  pivot_wider(
    id_cols = c(datestamp, entity, short_name, source, period),
    names_from = pct,
    values_from = value
  ) %>%
  # 计算95%和105%的数值
  mutate(
    `95` = (`90` + `100`) / 2,
    `105` = (`100` + `110`) / 2
  )

4. 转换回长格式并合并原始数据 #

把宽格式数据转回长格式，生成新的item字段，然后和原始数据合并，得到包含原始和新增中间值的完整数据集：

final_df <- df_wide %>%
  pivot_longer(
    cols = c(`90`, `95`, `100`, `105`, `110`),
    names_to = "pct",
    values_to = "value"
  ) %>%
  mutate(
    # 重新构造item字段，匹配原始格式
    item = str_glue("{period}_IMPVOL_{pct}%MNY_DF")
  ) %>%
  # 保留和原始数据一致的列顺序
  select(datestamp, entity, short_name, item, value, source) %>%
  # 按日期、周期、百分比排序
  arrange(datestamp, period, as.numeric(pct))

验证结果 #

查看9MTH组2006-01-03的数据：

final_df %>%
  filter(datestamp == "2006-01-03", period == "9MTH")

输出结果：

# A tibble: 5 × 6
  datestamp entity        short_name                       item                     value source
  <date>    <chr>         <chr>                            <chr>                    <dbl> <chr> 
1 2006-01-03 JALSHTR Index FTSE/JSE Africa All Shares Index 9MTH_IMPVOL_90%MNY_DF     21.5 PSEC  
2 2006-01-03 JALSHTR Index FTSE/JSE Africa All Shares Index 9MTH_IMPVOL_95%MNY_DF     19.9 PSEC  
3 2006-01-03 JALSHTR Index FTSE/JSE Africa All Shares Index 9MTH_IMPVOL_100%MNY_DF    18.3 PSEC  
4 2006-01-03 JALSHTR Index FTSE/JSE Africa All Shares Index 9MTH_IMPVOL_105%MNY_DF    17.2 PSEC  
5 2006-01-03 JALSHTR Index FTSE/JSE Africa All Shares Index 9MTH_IMPVOL_110%MNY_DF    16.2 PSEC  

内容的提问来源于stack exchange，提问作者The Prescient Robot