formattable::currency()格式化资产负债表报错的解决求助

错误的核心原因是：你的financial_data数据框的列是list类型——generate_financials函数返回的向量混合了空字符串（""）和数值，赋值给data.frame列时，R会自动将这类混合类型的列转为list，而formattable::currency无法处理list对象。之前尝试的as.numeric转换无效，因为list中的元素是字符与数值的混合，直接转换会报错，且未解决列类型的本质问题。

我们需要先确保数据框的列是numeric类型，再处理空值和格式化：

步骤1：修改generate_financials函数，用NA替代空字符串 #

把返回向量中的""换成NA，这样返回的是纯numeric向量（NA是numeric的缺失值，不会触发类型混合）：

generate_financials <- function(year) {
  set.seed(123)  # 设置随机种子保证可复现
  
  # 生成随机财务数据（逻辑不变）
  cash <- sample(89000:217000, 1)
  AR <- 0.25 * cash
  pexp <- sample(4800:6800, 1)
  inv <- sample(7800:11900, 1)
  tca <- sum(cash, AR, pexp, inv)
  PPE <- 2.7 * cash
  gw <- round(pi / 2.7 * PPE, 2)
  TAss <- tca + PPE + gw
  AP <- 0.03 * TAss
  acexp <- 0.5 * AP
  UR <- 1/3 * AP
  Tliab <- sum(AP, acexp, UR)
  LTD <- TAss * 0.5
  OLTD <- 0.23 * LTD
  TL <- sum(Tliab, LTD, OLTD)
  RE <- TAss - TL - sample(35000:76000, 1)
  EQ <- TAss - TL - RE
  SE <- EQ + RE
  TLSE <- SE + TL
  
  # 用NA替代空字符串，返回纯numeric向量
  return(c(NA, NA, NA, cash, AR, pexp, inv, tca, NA, PPE, gw, TAss, NA, AP, acexp, UR, Tliab, LTD, OLTD, TL, NA, EQ, RE, SE, TLSE))
}

步骤2：初始化数据框时指定列类型为numeric #

创建financial_data时，明确设置列类型为numeric，避免自动转为list：

# 创建存储财务数据的数据框，指定列类型为numeric
financial_data <- data.frame(matrix(numeric(), ncol = 5, nrow = length(Accounts)))
colnames(financial_data) <- c(lubridate::year(Sys.Date()), lubridate::year(Sys.Date()) - 1, lubridate::year(Sys.Date()) - 2, lubridate::year(Sys.Date()) - 3, lubridate::year(Sys.Date()) - 4)

步骤3：格式化数值并替换NA为空字符串 #

先对numeric列应用currency格式化，再把NA替换回空字符串（保持表格的排版结构）：

# 合并账户名和财务数据
bs <- cbind(Accounts, financial_data)
colnames(bs)[1] <- "Accounts"
rownames(bs) <- bs$Accounts

# 仅对数值列应用货币格式化
bs[,-1] <- formattable::currency(bs[,-1], symbol = "$", digits = 2L, big.mark = ",")

# 将NA替换为空字符串，恢复原表格的空行结构
bs[is.na(bs)] <- ""

完整修正后的代码 #

# 定义账户名称
Accounts <- data.frame(c("", "(Millions of USD)", "Current assets:", "Cash", "Accounts Receivable", "Prepaid expenses", "Inventory", "Total current assets", "", "Property & Equipment", "Goodwill", "Total Assets", "Liabilities", "Accounts Payable", "Accrued expenses", "Unearned revenue", "Total Current Liabilities", "Longterm Debt", "Other Long Term Liabilities", "Total Liabilities", "Shareholder's Equity", "Equity Capital", "Retained Earnings", "Shareholder's Equity", "Total Liabilities & Shareholder's Equity"))

# 生成指定年份财务数据的函数
generate_financials <- function(year) {
  set.seed(123)  # 设置随机种子保证可复现
  
  # 生成随机财务数据
  cash <- sample(89000:217000, 1)
  AR <- 0.25 * cash
  pexp <- sample(4800:6800, 1)
  inv <- sample(7800:11900, 1)
  tca <- sum(cash, AR, pexp, inv)
  PPE <- 2.7 * cash
  gw <- round(pi / 2.7 * PPE, 2)
  TAss <- tca + PPE + gw
  AP <- 0.03 * TAss
  acexp <- 0.5 * AP
  UR <- 1/3 * AP
  Tliab <- sum(AP, acexp, UR)
  LTD <- TAss * 0.5
  OLTD <- 0.23 * LTD
  TL <- sum(Tliab, LTD, OLTD)
  RE <- TAss - TL - sample(35000:76000, 1)
  EQ <- TAss - TL - RE
  SE <- EQ + RE
  TLSE <- SE + TL
  
  # 返回纯numeric向量，用NA替代空字符串
  return(c(NA, NA, NA, cash, AR, pexp, inv, tca, NA, PPE, gw, TAss, NA, AP, acexp, UR, Tliab, LTD, OLTD, TL, NA, EQ, RE, SE, TLSE))
}

# 创建存储财务数据的数据框，指定列类型为numeric
financial_data <- data.frame(matrix(numeric(), ncol = 5, nrow = length(Accounts)))
colnames(financial_data) <- c(lubridate::year(Sys.Date()), lubridate::year(Sys.Date()) - 1, lubridate::year(Sys.Date()) - 2, lubridate::year(Sys.Date()) - 3, lubridate::year(Sys.Date()) - 4)

# 填充各年份财务数据
for (i in 1:ncol(financial_data)) {
  financial_data[, i] <- generate_financials(colnames(financial_data)[i])
}

# 合并账户名和财务数据
bs <- cbind(Accounts, financial_data)
colnames(bs)[1] <- "Accounts"
rownames(bs) <- bs$Accounts

# 格式化数值为货币格式
bs[,-1] <- formattable::currency(bs[,-1], symbol = "$", digits = 2L, big.mark = ",")

# 将NA替换为空字符串，保持表格排版
bs[is.na(bs)] <- ""

# 查看结果
bs

• 用NA替代空字符串是为了让R识别列类型为numeric，避免转为list；
• 格式化完成后再把NA换回空字符串，保证表格的空行结构和原需求一致；
• 若不需要保留空行，也可以直接过滤掉NA行后再格式化，但会破坏资产负债表的层级结构。

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