r - 如何在R中一次清理多个excel文件？

Question

我有一百多个excel文件需要清理，所有文件都在同一个数据结构中。下面列出的代码是我用来清理单个 excel 文件的代码。文件的名称都在结构中，例如“abcdefg.xlsx”

library('readxl')

df <- read_excel('abc.xlsx', sheet = 'EQuote')

# get the project name 
project_name <- df[1,2]
project_name <- gsub(".*:","",project_name)
project_name <- gsub(".* ","",project_name)

# select then needed columns 
df <- df[,c(3,4,5,8,16,17,18,19)]

# remane column
colnames(df)[colnames(df) == 'X__2'] <- 'Product_Models'
colnames(df)[colnames(df) == 'X__3'] <- 'Qty'
colnames(df)[colnames(df) == 'X__4'] <- 'List_Price'
colnames(df)[colnames(df) == 'X__7'] <- 'Net_Price'
colnames(df)[colnames(df) == 'X__15'] <- 'Product_Code'
colnames(df)[colnames(df) == 'X__16'] <- 'Product_Series'
colnames(df)[colnames(df) == 'X__17'] <- 'Product_Group'
colnames(df)[colnames(df) == 'X__18'] <- 'Cat'

# add new column named 'Project_Name', and set value to it 
df$project_name <- project_name

# extract rows between two specific characters
begin <- which(df$Product_Models == 'SKU')
end <- which(df$Product_Models == 'Sub Total:')

## set the loop
in_between <- function(df, start, end){
  return(df[start:end,])
}

dividers = which(df$Product_Models %in% 'SKU' == TRUE)
df <- lapply(1:(length(dividers)-1), function(x) in_between(df, start =         
dividers[x], end = dividers[x+1]))
df <-do.call(rbind, df)

# remove the rows 
df <- df[!(df$Product_Models %in% c("SKU","Sub Total:")), ]

# remove rows with NA
df <- df[complete.cases(df),]

# remove part of string after '.'
NeededString <- df$Product_Models
NeededString <- gsub("\\..*", "", NeededString)
df$Product_Models <- NeededString

然后我可以得到一个结构良好的数据框。 Well Structured Dataframe Example

你们能帮我写一个代码，它可以帮助我一次清理所有的excel文件。所以，我不需要运行这个代码一百次。然后，将所有文件聚合成一个大的 csv 文件。

Answer 1

您可以使用 lapply (基础 R)或 map (purrr 包)使用一组命令读取和处理所有文件。 lapply和 map遍历一个向量或列表(在这种情况下是一个列表或文件名向量)，将相同的代码应用于向量或列表的每个元素。

例如，在下面的代码中，它使用 map (实际上是 map_df，它返回单个数据帧，而不是单独数据帧的列表)，file_names是文件名的向量(或文件路径 + 名称，如果文件不在工作目录中)。 ...all processing steps...是您问题中要处理的所有代码df变成你想要的形式:

library(tidyverse) # Loads several tidyverse packages, including purrr and dplyr
library(readxl)

single_data_frame = map_df(file_names, function(file) {

  df = read_excel(file, sheet="EQUOTE")

  ... all processing steps ...

  df
}

现在您有了一个从所有 Excel 文件生成的大型数据框。您现在可以将其另存为 csv文件，例如 write_csv(single_data_frame, "One_large_data_frame.csv") .

您可能还可以做其他事情来简化代码。例如，重命名 df 的列，您可以使用 recode函数(来自 dplyr)。我们通过首先更改内置 mtcars 的名称来演示这一点。数据框与数据中的名称相似。然后我们使用 recode更改一些名称:

# Rename mtcars data frame
set.seed(2)  
names(mtcars) = paste0("X__", sample(1:11))

# Look at data frame
head(mtcars)

# Recode three of the column names
names(mtcars) = recode(names(mtcars),
                       X__1="New.1",
                       X__5="New.5",
                       X__9="New.9")

或者，如果名称的顺序始终相同，您可以这样做(使用您的数据结构):

names(df) = c('Product_Models','Qty','List_Price','Net_Price','Product_Code','Product_Series','Product_Group','Cat')

或者，如果您的 Excel 文件有列名，您可以使用 skip read_excel 的论点在读入数据之前跳到标题行。这样，您将直接从 Excel 文件中获得正确的列名。由于看起来您还需要从前几行中获取项目名称，因此您可以先通过单独调用 read_excel 来读取这些行。并使用 range论点，和/或 n_max参数以仅获取项目名称的相关行或单元格。