时间戳

首页 > 解决方案 > 如何在R中一次清理多个excel文件？

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

问题描述

我有一百多个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

然后我可以得到一个结构良好的数据框。结构良好的数据框示例

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

标签： rexcel

解决方案

您可以使用lapply(base R) 或map( purrrpackage) 通过一组命令读取和处理所有文件。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函数 (from 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参数仅获取相关行或单元格项目名。

推荐阅读