如何用R的pivot_longer()函数将宽格式数据转为指定长格式？

现有如下宽格式数据：

Country     MDA     BI  MDA Last Year   BI Last Year    Month
Netherland  230     280     234     789     June
Scotland    100     340     456     234     June
UK          100     100     768     567     June
USA         200     780     765     123     June
Ireland     100     890     675     987     June
Nigeria     500     560     234     876     June

尝试使用pivot_longer()函数将其转换为长格式未得到正确结果，期望输出格式如下：

Country     Cur_Year Prod_Cat_new   Prev_Year   Prod_cat_old    Month
Netherland  230 MDA         234 MDA Last Year           June
Scotland    100 MDA         456 MDA Last Year           June
UK          100 MDA         768 MDA Last Year           June
USA         200 MDA         765 MDA Last Year           June
Ireland     100 MDA         675 MDA Last Year           June
Nigeria     500 MDA         234 MDA Last Year           June
Netherland  280 BI          789 BI Last Year            June
Scotland    340 BI          234 BI Last Year            June
UK          100 BI          567 BI Last Year            June
USA         780 BI          123 BI Last Year            June
Ireland     890 BI          987 BI Last Year            June
Nigeria     560 BI          876 BI Last Year            June

可以通过tidyr::pivot_longer()配合正则匹配列名，再整理字段来实现需求，完整R代码如下：

library(tidyr)
library(dplyr)

# 构造示例数据集（如果已有数据可替换为你的数据框）
df <- tibble(
  Country = c("Netherland", "Scotland", "UK", "USA", "Ireland", "Nigeria"),
  MDA = c(230, 100, 100, 200, 100, 500),
  BI = c(280, 340, 100, 780, 890, 560),
  `MDA Last Year` = c(234, 456, 768, 765, 675, 234),
  `BI Last Year` = c(789, 234, 567, 123, 987, 876),
  Month = rep("June", 6)
)

# 执行格式转换
result <- df %>%
  # 按正则拆分列名，捕获产品类别和年份类型
  pivot_longer(
    cols = -c(Country, Month),
    names_pattern = "(.*)( Last Year)?",
    names_to = c("Prod_Cat_new", ".value")
  ) %>%
  # 重命名列以匹配期望输出
  rename(
    Cur_Year = ``,
    Prev_Year = `Last Year`,
    Prod_cat_old = Prod_Cat_new
  ) %>%
  # 将数值与类别文本拼接
  mutate(
    Cur_Year = paste(Cur_Year, Prod_Cat_new),
    Prev_Year = paste(Prev_Year, paste(Prod_Cat_new, "Last Year"))
  ) %>%
  # 调整列顺序至期望格式
  select(Country, Cur_Year, Prod_Cat_new, Prev_Year, Prod_cat_old, Month)

# 查看结果
print(result)

代码说明： #

1. pivot_longer()的names_pattern用正则(.*)( Last Year)?拆分列名，第一组捕获产品类别（MDA/BI），第二组捕获年份标识（空代表当前年，Last Year代表去年）；.value参数指定第二组作为值的列名。
2. 通过rename()调整列名匹配需求。
3. mutate()将数值与类别文本拼接成期望的格式。
4. select()调整列顺序与目标输出一致。

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