基于聚合数据按站点生成多Excel文件的R语言实现求助

Perfect, you're already halfway there with the global file—let's tweak your code to loop through each unique site and create individual Excel files with the three module sheets. Here's a step-by-step breakdown and full working code:

Step 1: Load Libraries & Define Modules (Reuse Your Setup) #

First, keep your initial setup for libraries and module column definitions—they work great as-is:

# Load required libraries
library(dplyr)
library(xlsx)

# Define the module columns (cleaner to use column names directly instead of indices)
BlockOverall <- c("Seating", "Decor", "Reception", "Toilets")
BlockComfortSpeed <- c("Comfort", "Speed")
BlockOps <- c("Efficiency", "Courtesy", "Responsiveness")

Step 2: Loop Through Each Unique Site #

The core fix is to iterate over every unique site, filter the data to that site only, process each module's comments, and save to a dedicated Excel file. We'll use a for loop here—it's straightforward for beginners to follow and debug.

Full Working Code

# Get all unique site names from your data frame
unique_sites <- unique(df$Site)

# Loop through each site one by one
for (site in unique_sites) {
  # 1. Isolate data for the current site
  site_data <- df %>% filter(Site == site)
  
  # 2. Clean and format comments for each module
  # Module 1: Overall
  overall_comments <- site_data %>%
    select(all_of(BlockOverall)) %>%  # Grab columns for this module
    unlist(use.names = FALSE) %>%     # Flatten all comments into a single list
    na.omit() %>%                     # Remove empty/NA comments
    data.frame(Comments_Overall = .)  # Convert to a data frame for Excel compatibility
  
  # Module 2: Comfort & Speed
  comfort_speed_comments <- site_data %>%
    select(all_of(BlockComfortSpeed)) %>%
    unlist(use.names = FALSE) %>%
    na.omit() %>%
    data.frame(Comments_Comfort_Speed = .)
  
  # Module 3: Operations
  operations_comments <- site_data %>%
    select(all_of(BlockOps)) %>%
    unlist(use.names = FALSE) %>%
    na.omit() %>%
    data.frame(Comments_Operations = .)
  
  # 3. Create the output file and add sheets
  # Define the file name to match your desired format
  output_file <- paste0("COMMENTS_", site, "_2017.xlsx")
  
  # Write the first sheet (Overall)
  write.xlsx(overall_comments, output_file, sheetName = "Overall", row.names = FALSE)
  
  # Append the other two sheets to the same workbook
  write.xlsx(comfort_speed_comments, output_file, sheetName = "Comfort & Speed", row.names = FALSE, append = TRUE)
  write.xlsx(operations_comments, output_file, sheetName = "Operations", row.names = FALSE, append = TRUE)
  
  # Optional: Print a progress update
  cat("Successfully created:", output_file, "\n")
}

Key Changes Explained #

• Site-specific filtering: site_data <- df %>% filter(Site == site) makes sure we only process comments from the current site, not the entire dataset.
• all_of() in select(): This safely references the column names in your module lists (more readable and robust than using column indices).
• Loop structure: The for loop automatically handles every unique site, so you don't have to manually repeat code for each location.
• Sheet appending: Using append = TRUE adds the second and third sheets to the same workbook instead of overwriting the file each time.

Optional: Modern Alternative with purrr #

If you want to try a more concise R approach, replace the for loop with purrr::walk (no extra variables needed):

library(purrr)

walk(unique_sites, function(site) {
  site_data <- df %>% filter(Site == site)
  
  # Process modules (same logic as above)
  overall_comments <- site_data %>% select(all_of(BlockOverall)) %>% unlist(use.names=F) %>% na.omit() %>% data.frame(Comments_Overall=.)
  comfort_speed_comments <- site_data %>% select(all_of(BlockComfortSpeed)) %>% unlist(use.names=F) %>% na.omit() %>% data.frame(Comments_Comfort_Speed=.)
  operations_comments <- site_data %>% select(all_of(BlockOps)) %>% unlist(use.names=F) %>% na.omit() %>% data.frame(Comments_Operations=.)
  
  output_file <- paste0("COMMENTS_", site, "_2017.xlsx")
  write.xlsx(overall_comments, output_file, sheetName="Overall", row.names=F)
  write.xlsx(comfort_speed_comments, output_file, sheetName="Comfort & Speed", row.names=F, append=T)
  write.xlsx(operations_comments, output_file, sheetName="Operations", row.names=F, append=T)
  cat("Created file:", output_file, "\n")
})

Either approach will generate the three Excel files you expected: COMMENTS_Tokyo Harbor_2017.xlsx, COMMENTS_Arlington_2017.xlsx, and COMMENTS_Cairo Skyline_2017.xlsx—each with three sheets for your modules!

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