R中字符转含NA数值类及数值格式化报错解决问询

错误根源 #

• prettyNum相关报错：dplyr处理后的数据为tibble格式，使用data2[2]取列时返回的是单列tibble（二维结构），而非向量。prettyNum传入二维结构会输出长度不符合预期的结果，触发vapply长度校验报错。
• 强制转换引入NA警告：直接对tibble的列子集（二维结构）执行as.numeric转换，相当于对列表结构做数值转换，自然生成无效NA。
• 附加隐藏问题：原代码library(dplyr, tidyr)写法错误，tidyr实际未加载，会导致pivot_wider函数找不到。

修复后完整代码 #

# 修正加载多个包的写法
library(dplyr)
library(tidyr)

df <- data.frame(
  Date = c("Sep 2005","May 2005","Sep 2004","Sep 2005","May 2005","Sep 2004"),
  Group = c("A.A","B.A","A.A","B.B","A.B","B.C"),
  Category = c("Total","Total","Total","Total","Total","Total"),
  Cost = c(100025,51580.75,0,357450.25,73040,1754400)
)

x <- c("A.A","A.B","B.A","B.B","B.C","Total")

Tables <- function(data, x){
  data2 <- bind_rows(
    # summarise加.groups = "drop" 避免隐式分组
    data %>% group_by(Group, Date) %>% dplyr::summarise(Amt = sum(Cost), .groups = "drop"),
    data %>% group_by(Category, Date) %>% dplyr::summarise(Amt = sum(Cost), .groups = "drop") %>% dplyr::rename(Group = Category)
  ) %>%
    arrange(Date, Group) %>%
    pivot_wider(names_from = Date, values_from = Amt) %>%
    slice(match(x, Group))
  
  # 计算QoQ，也可以替换为按列名取更稳妥，比如`May 2005` `Sep 2005`
  data2 <- data2 %>% mutate(QoQ = ((as.numeric(.[[4]]) - as.numeric(.[[3]])) / as.numeric(.[[3]])))
  
  # 批量格式化2-4列，用across遍历列，.x代表每一列的向量
  data2 <- data2 %>% mutate(across(2:4, ~prettyNum(.x, digits = 0, big.mark = ",", scientific = FALSE)))
  
  return(data2)
}

df2 <- as.data.frame(Tables(df, x))

关键修改说明 #

• 用across()批量遍历2-4列，每列直接传入向量给prettyNum处理，符合函数输入要求
• 加载包修正为分别加载dplyr和tidyr，避免函数找不到的问题
• summarise添加.groups = "drop"参数，消除dplyr新版的分组提示，避免后续操作出现隐式分组问题

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