tidyr::pivot_wider报错：无法子集不存在的Group列，求排查方案

报错信息 #

Error in `select_wider_id_cols()`:
! Can't subset columns that don't exist.
x Column `Group` doesn't exist. 

完整代码 #

library(tidyr)
library(dplyr)
library(stringr)

impacts = impact %>%
    tidyr::pivot_longer(PI_Direct_Impact:E_Total,
                        names_to = "names",
                        values_to = "values") %>%
    dplyr::mutate(
      Impact_Type = dplyr::case_when(
        stringr::str_detect(names, "PI_") ~ "Income Impacts",
        stringr::str_detect(names, "TV_") ~ "Total Value Added",
        stringr::str_detect(names, "O_") ~ "Output Impacts",
        stringr::str_detect(names, "E_") ~ "Employment Impacts"
      )
    ) %>%
    dplyr::mutate(
      Group = dplyr::case_when(
        stringr::str_detect(names, "Direct_Impact") ~ "Direct",
        stringr::str_detect(names, "Indirect_Impact") ~ "Indirect",
        stringr::str_detect(names, "Induced_Impact") ~ "Induced",
        stringr::str_detect(names, "Total") ~ "Total"
      )
    ) %>%
    dplyr::select(-names) %>%
    tidyr::pivot_wider(
      id_cols = c(
        fips,
        `Economic Category`,
        `Species Category`,
        spec_no,
        Impact_Type,
        Group,
        Imports
      ),
      names_from = Group,
      values_from = values
    )

报错前数据集列头 #

# A tibble: 6 x 8
   fips `Economic Category` Species~1 spec_no Imports values Impac~2 Group
  <dbl> <chr>               <chr>       <dbl> <chr>    <dbl> <chr>   <chr>

问题原因 #

你在pivot_wider中存在逻辑冲突：

• names_from = Group 指定用Group列的取值生成宽表的新列名，执行时该列会被从数据中移除
• 同时你又把Group放在了id_cols参数里，要求将其作为标识列保留

这种矛盾导致函数执行时，先处理names_from移除了Group列，再处理id_cols时找不到该列，触发报错。

解决方案 #

将Group从id_cols参数中移除即可，修改后的pivot_wider代码如下：

tidyr::pivot_wider(
  id_cols = c(
    fips,
    `Economic Category`,
    `Species Category`,
    spec_no,
    Impact_Type,
    Imports
  ),
  names_from = Group,
  values_from = values
)

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