如何在R语言中读取多文件夹下指定结构Excel文件并提取列？

Hey there! Let's work through how to read those Excel files from multiple folders and extract the specific column you need. This is a common task, and with a few R packages, we can make it nice and automated. Here's a step-by-step solution tailored to your scenario:

First, we'll use a handful of packages to handle file paths, batch processing, and Excel reading. If you haven't installed them yet, run the install command first:

# Install packages if you haven't already
install.packages(c("readxl", "purrr", "fs", "dplyr"))

# Load the packages into your R session
library(readxl)
library(purrr)
library(fs)
library(dplyr)

Let's start by aligning with a typical folder structure (adjust this to match your actual setup!):

• Main parent folder: ./my_data_directory
  • Subfolder 1: region_north
    • Contains 3 target Excel files (e.g., jan_data.xlsx, feb_data.xlsx, mar_data.xlsx)
  • Subfolder 2: region_south
    • Contains the same 3 named Excel files
  • ... and so on for all your subfolders

We'll be extracting a single column (let's say it's named Monthly_Revenue—swap this with your actual column name or position later).

First, we'll grab all the subfolder paths inside your main directory:

# Set the path to your main parent folder
main_folder <- "./my_data_directory"

# Get a list of all subfolders within the main folder
subfolders <- dir_ls(main_folder, type = "directory")

Next, create a reusable function that processes one subfolder at a time: it finds the 3 Excel files, reads your target column, and adds metadata (like which folder/file the data came from) so you can track origins:

# Function to process a single subfolder
process_subfolder <- function(folder_path) {
  # Grab the subfolder name for metadata
  folder_name <- path_file(folder_path)
  
  # List the exact names of your 3 target Excel files (UPDATE THESE!)
  target_files <- c("jan_data.xlsx", "feb_data.xlsx", "mar_data.xlsx")
  
  # Create full paths to each file
  file_paths <- path(folder_path, target_files)
  
  # Skip the folder if any of the 3 files are missing
  if (!all(file_exists(file_paths))) {
    warning(paste("Oops, missing one or more files in", folder_name, "- skipping this folder"))
    return(NULL)
  }
  
  # Read each file, extract the target column, and add metadata
  map_dfr(file_paths, function(file) {
    file_name <- path_file(file)
    # Read only the column you need - adjust "Monthly_Revenue" to your column name/position
    data <- read_excel(file, col_select = "Monthly_Revenue") %>%
      mutate(
        source_folder = folder_name,
        source_file = file_name
      )
    return(data)
  })
}

Now apply this function to all your subfolders and combine everything into one tidy data frame:

# Process all subfolders and combine results into a single data frame
final_combined_data <- map_dfr(subfolders, process_subfolder)

# Take a look at the first few rows to verify
head(final_combined_data)

Make sure to tweak these parts to match your actual files:

• Folder path: Update main_folder to the real path of your parent directory (use absolute paths like C:/data/main_folder on Windows or /home/user/data/main_folder on macOS/Linux if relative paths aren't working).
• Target file names: Replace target_files with the exact names of your 3 Excel files per folder. If your files follow a pattern (e.g., all end with _summary.xlsx), you can auto-detect them with dir_ls(folder_path, regexp = "_summary\\.xlsx") instead of listing names manually.
• Target column: Swap "Monthly_Revenue" with your column name, or use a column number (e.g., col_select = 4 for the 4th column) if your Excel files don't have headers.
• Inconsistent column names: If your column has different names across files (e.g., Revenue vs Monthly_Revenue), standardize it after reading with rename(final_combined_data, Monthly_Revenue = any_of(c("Revenue", "Monthly_Revenue"))).

• Missing package errors: Double-check you installed all packages with install.packages().
• File not found warnings: The function will warn you which folders are missing files—go back and verify those folders have all 3 Excel files.
• Reading issues: If read_excel struggles with your files, try openxlsx::read.xlsx() instead (just install the openxlsx package first).

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