如何用R语言批量处理同格式Excel文件夹？（偏好Tidyverse）

Hey there! Using the tidyverse tools you prefer, here's a simple, efficient workflow to batch process your Excel files, summarize the data as you need, and combine everything into one final dataframe:

Step 1: Load Required Packages #

First, make sure you have these packages installed (if not, run install.packages(c("tidyverse", "readxl"))):

library(tidyverse)
library(readxl)

Step 2: Get All Excel File Paths #

Point this to your target folder containing all the Excel files. The pattern will match both .xlsx and .xls formats:

# Replace this with your actual folder path
folder_path <- "path/to/your/excel/files"
file_paths <- list.files(path = folder_path,
                         pattern = "\\.xlsx$|\\.xls$",
                         full.names = TRUE)

Step 3: Define a File Processing Function #

This function handles a single Excel file: reads it in, groups by country and year, sums the count values, and optionally adds a column to track which file the data came from (super helpful for debugging later!):

process_single_file <- function(file_path) {
  read_excel(file_path) %>%
    # Group and summarize exactly as you specified
    group_by(country, year) %>%
    summarize(total_count = sum(count, na.rm = TRUE), .groups = "drop") %>%
    # Add source filename for traceability (optional but recommended)
    mutate(source_file = basename(file_path))
}

• na.rm = TRUE ensures we ignore any missing values in the count column
• .groups = "drop" cleans up the grouping structure after summarizing, preventing unexpected behavior in later steps

Step 4: Batch Process and Combine All Files #

Use map_dfr() from the purrr package (included in tidyverse) to apply our function to every file and automatically row-bind the results into one dataframe:

final_combined_df <- map_dfr(file_paths, process_single_file)

If you're working with a lot of files and want to track progress, add .progress = TRUE (requires purrr version 1.0.0 or newer):

final_combined_df <- map_dfr(file_paths, process_single_file, .progress = TRUE)

Verify the Result #

You can check the final dataframe with:

glimpse(final_combined_df)
head(final_combined_df)

This will give you a single dataframe with the summed counts per country and year across all your files, plus the source file info if you kept that optional column.

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