如何在R中将指定数据框生成为要求格式的多变量表格并导出至Excel

我有一个名为hhinc_all的tibble数据框，头部数据如下：

> head(hhinc_all)
# A tibble: 6 × 6
# Groups:   county, incgrp, hhsize, hhworker [6]
  county        incgrp       hhsize hhworker na.rm     n
  <chr>         <fct>        <fct>  <fct>    <lgl> <int>
1 Hardin_Orange $0 - $25,170 1      0        TRUE   5205
2 Hardin_Orange $0 - $25,170 1      1        TRUE    984
3 Hardin_Orange $0 - $25,170 2      0        TRUE   1254
4 Hardin_Orange $0 - $25,170 2      1        TRUE    664
5 Hardin_Orange $0 - $25,170 2      2+       TRUE    102
6 Hardin_Orange $0 - $25,170 3      0        TRUE    211

变量说明：

• county：县
• incgrp：收入类别（作为表格Y轴）
• hhsize：家庭规模（1人、2人、3人、4人、5人及以上）
• hhworker：家庭工作者数量（0、1、2及以上）
• n：表格单元格的加权填充值

我需要生成一个多层表头的表格：X轴按家庭规模分层，每个家庭规模下再展示不同的家庭工作者数量，Y轴是收入类别，单元格填充对应的n值。不想导出CSV用Excel做透视表，请问怎么在R里直接实现？

可以用tidyverse完成宽表转换，再配合openxlsx生成带多层表头的Excel文件，全程在R中实现需求：

1. 生成宽表结构（R内查看用） #

先加载tidyverse，用pivot_wider把长表转成符合结构的宽表：

library(tidyverse)

# 转宽表：收入类别为行，家庭规模+工作者数量为列
wide_table <- hhinc_all %>%
  # 若需按县单独生成表格，保留group_by(county)；否则去掉
  group_by(county) %>%
  pivot_wider(
    id_cols = incgrp,
    names_from = c(hhsize, hhworker),  # 列层级：先家庭规模，再工作者数量
    values_from = n,
    values_fill = 0  # 空值填充0，可按需调整
  ) %>%
  ungroup()

此时wide_table的列名格式为家庭规模_工作者数量（比如1_0、2_2+），已符合核心数据结构。

2. 生成带多层表头的Excel文件 #

如果需要导出和目标格式一致的Excel（带合并表头），用openxlsx包实现：

library(openxlsx)

# 创建工作簿和工作表
wb <- createWorkbook()
addWorksheet(wb, "Income_Table")

# 拆分列名得到表头层级
col_names <- colnames(wide_table)[-1]  # 排除第一列incgrp
level1 <- str_extract(col_names, "^[^_]+")  # 第一层：家庭规模
level2 <- str_remove(str_extract(col_names, "_([^_]+)$"), "_")  # 第二层：工作者数量

# 写入并合并第一层表头（家庭规模）
writeData(wb, "Income_Table", unique(level1), startCol = 2, startRow = 1)
for (size in unique(level1)) {
  target_cols <- which(level1 == size) + 1  # 偏移1是因为第一列是收入类别
  mergeCells(wb, "Income_Table", cols = target_cols, rows = 1)
}

# 写入第二层表头（工作者数量）
writeData(wb, "Income_Table", level2, startCol = 2, startRow = 2)

# 写入数据内容
writeData(wb, "Income_Table", wide_table, startRow = 3, colNames = FALSE)

# 设置Y轴表头（收入类别）并合并单元格
writeData(wb, "Income_Table", "收入类别", startCol = 1, startRow = 2)
mergeCells(wb, "Income_Table", cols = 1, rows = 1:2)

# 保存文件
saveWorkbook(wb, "household_income_table.xlsx", overwrite = TRUE)

运行后会生成一个Excel文件，结构完全匹配你需要的多层表头格式，无需手动调整。

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