R语言：将Tidy层次化数据框转为层次化列表的高效方法问询

Hey there! Great question—ditching manual for loops for hierarchical data formatting is a smart move for both efficiency and cleaner code, especially when prepping data for styled Excel outputs with openxlsx.

Below are two streamlined approaches using popular R packages that avoid manual looping, scale better for larger datasets, and produce the nested structure you need:

1. Tidyverse (dplyr + purrr) Approach #

This method uses grouping and nesting to build the hierarchy in a declarative, easy-to-read way—perfect for most standard use cases.

First, let's set up your sample data:

library(dplyr)
library(purrr)
library(tibble)

# Sample input data
df <- tibble(
  g1 = c("A", "A", "A"),
  g2 = c("a", "a", "b"),
  desc = c(1, 2, 3),
  val = c("v1", "v2", "v3")
)

Now build the nested list structure:

# Create hierarchical nested list
hierarchical_list <- df %>%
  # Nest desc/val pairs under each g1/g2 combination
  group_by(g1, g2) %>%
  nest(items = c(desc, val)) %>%
  # Nest g2 groups under each g1
  group_by(g1) %>%
  nest(groups = c(g2, items)) %>%
  # Convert nested groups to named lists for easy access
  mutate(groups = map(groups, ~ set_names(.$items, .$g2))) %>%
  # Final conversion to a top-level named list
  select(g1, groups) %>%
  deframe()

The result is a clean nested structure:

• Top-level keys are your g1 values (e.g., "A")
• Each g1 contains named lists for g2 values (e.g., "a", "b")
• Each g2 holds the paired desc/val data ready for formatting.

2. Data.table Approach (For Large Datasets) #

If you're working with big data, data.table offers faster grouping and nesting operations optimized for speed. Here's how to implement it:

library(data.table)

# Convert to data.table format
setDT(df)

# First, combine desc and val into formatted strings for Excel
df[, item_text := paste(desc, val, sep = " ")]

# Build hierarchical structure
hierarchical_dt <- df[, .(
  sub_groups = .(df[.SD, .(items = list(item_text)), by = g2])
), by = g1] %>%
  # Convert to named lists
  .[, setNames(sub_groups[[1]], g1), by = g1] %>%
  as.list()

This method shines when dealing with tens of thousands of rows or more, as it avoids the overhead of manual loops entirely.

Generating Formatted Excel Output #

Once you have your nested list, use openxlsx to write it with proper indentation and styling—minimal manual row tracking required:

library(openxlsx)

# Create workbook and worksheet
wb <- createWorkbook()
addWorksheet(wb, "Hierarchical Data")

# Define styles for each hierarchy level
style_g1 <- createStyle(textDecoration = "bold", fontSize = 12)
style_g2 <- createStyle(textDecoration = "italic", fontSize = 11)
style_item <- createStyle(fontSize = 10)

row_counter <- 1

# Traverse the nested list and write to Excel
walk(names(hierarchical_list), function(g1_val) {
  # Write g1 level with bold style
  writeData(wb, sheet = 1, x = g1_val, startRow = row_counter, startCol = 1)
  addStyle(wb, sheet = 1, style = style_g1, rows = row_counter, cols = 1)
  row_counter <<- row_counter + 1
  
  # Traverse g2 groups
  g2_groups <- hierarchical_list[[g1_val]]
  walk(names(g2_groups), function(g2_val) {
    # Write g2 level with indent and italic style
    writeData(wb, sheet = 1, x = g2_val, startRow = row_counter, startCol = 1, indent = 1)
    addStyle(wb, sheet = 1, style = style_g2, rows = row_counter, cols = 1)
    row_counter <<- row_counter + 1
    
    # Write items with double indent
    items <- g2_groups[[g2_val]] %>% mutate(item = paste(desc, val)) %>% pull(item)
    walk(items, function(item) {
      writeData(wb, sheet = 1, x = item, startRow = row_counter, startCol = 1, indent = 2)
      addStyle(wb, sheet = 1, style = style_item, rows = row_counter, cols = 1)
      row_counter <<- row_counter + 1
    })
  })
})

# Save the finished workbook
saveWorkbook(wb, "formatted_hierarchy.xlsx", overwrite = TRUE)

Why These Methods Beat For Loops #

• Readability: Declarative code makes each step clear, unlike messy manual loops that can be hard to follow.
• Scalability: Both dplyr and data.table use optimized internal operations that outperform manual loops on large datasets.
• Maintainability: Adjusting grouping levels or formatting logic is straightforward, no need to rewrite entire loops.

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