R语言技术问询：导入Excel文件为DataFrame并添加文件名列，及批量为环境中DataFrame添加名称列

Hi there! Let's tackle your two R questions one by one—they're super common when working with multiple datasets, so you're in the right place.

For this task, the readxl package is perfect for importing Excel files, and dplyr makes adding columns a breeze. We'll also use base R's tools package to handle filename extraction cleanly.

Step 1: Install and load required packages #

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

Step 2: Single file import #

If you're working with one Excel file at a time, here's a straightforward way to add the filename column:

# Define your file path
file_path <- "A_2011.xlsx"

# Extract the filename without the .xlsx extension
file_name <- tools::file_path_sans_ext(basename(file_path))

# Import the data and add the filename column
A_2011 <- read_excel(file_path) %>%
  mutate(filename = file_name)

Step 3: Batch import multiple files #

If you have multiple Excel files to import, you can automate this with purrr (part of the tidyverse) to avoid repeating code:

library(purrr)

# Get a list of all .xlsx files in your target folder
file_list <- list.files(path = "./your_excel_folder", pattern = "\\.xlsx$", full.names = TRUE)

# Import each file and add the filename column
imported_data <- map(file_list, function(file) {
  file_name <- tools::file_path_sans_ext(basename(file))
  read_excel(file) %>%
    mutate(filename = file_name)
})

# Optional: Name each data frame in the list with the filename, then load them into your global environment
names(imported_data) <- tools::file_path_sans_ext(basename(file_list))
list2env(imported_data, envir = .GlobalEnv)

To add a FILE column where the value matches the data frame's name, we'll use base R's get() and assign() functions to access and modify objects in your global environment.

Option 1: Target specific data frames #

If you only want to update specific data frames (like A_1 to A_4), list their names explicitly:

# List the names of the data frames you want to modify
target_dfs <- c("A_1", "A_2", "A_3", "A_4")

# Loop through each data frame to add the FILE column
for(df_name in target_dfs) {
  # Grab the current data frame
  current_df <- get(df_name)
  # Add the FILE column with the data frame's name
  current_df$FILE <- df_name
  # Save the modified data frame back to your environment
  assign(df_name, current_df, envir = .GlobalEnv)
}

Option 2: Update all data frames in your environment #

If you want to add the FILE column to every data frame in your global environment (excluding other objects like functions or vectors), use this:

# Get the names of all objects that are data frames
all_dfs <- ls(envir = .GlobalEnv)[sapply(ls(envir = .GlobalEnv), function(x) is.data.frame(get(x)))]

# Loop through each data frame
for(df_name in all_dfs) {
  current_df <- get(df_name)
  current_df$FILE <- df_name
  assign(df_name, current_df, envir = .GlobalEnv)
}

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