Я читаю данные из отдельных файлов xlsx, причем данные хранятся в 10-20 тысячах отдельных вкладок в каждом файле рабочей книги. Первый лист содержит таблицу основных данных, включая ссылки на отдельные вкладки с дополнительными данными. Данные на основе столбцов с вкладками суммируются и транспонируются перед добавлением к основным данным.
Таблица основных данных большая (10 тысяч строк x сотни столбцов) сама по себе, вкладки дополнительных данных малы по размеру. собственные права (от нескольких столбцов на 10 до нескольких строк '00).
Использование пакета XLConnect
привело к сбою из-за нехватки памяти уже при вызове loadWorkbook()
(R 3.4.0, RStudio 1.1.383, 64-битная машина, 8G), в противном случае я мог бы работать по схеме это.
Поскольку мне нужно загружать данные с отдельных вкладок, в настоящее время я использую вложенный цикл for() для загрузки данных каждой отдельной вкладки. Однако с моим количеством вкладок это занимает почти минуту на цикл, в результате чего общее время выполнения составляет почти неделю! Использование вложенного цикла for() также явно неаккуратно, поэтому я подозреваю, что есть более аккуратный и (намного) более быстрый способ добиться этого, но я не вижу его.
Я прочитал ссылки на выделенный df (linkReferences
) в R. Источник данных не мой, поэтому я застрял с вводом, как указано.
Проблема связана исключительно со скоростью чтения листов, которая увеличивается по мере увеличения количества листов в файле (и, следовательно, размера файла).
Я ищу любое решение, чтобы ускорить это, обновленное автономным минимальным примером. На моем ПК: n = 10
дает время/лист 0,16 сек, n = 100
~0,56 сек/лист и n = 1000
~3 сек/лист, что похоже на то, что я вижу в моих реальных данных (‹10 сек/лист для 16 тыс. листов)
library(tidyverse)
number_of_sheets= 100
# =========================================================================
# CREATE SAMPLE FILE . Layout similar to actual data
library(openxlsx)
my.sheets.file <- "sampleXLSX.xlsx"
linkReferences <- data_frame( sheet = str_c("Data ",seq(1:number_of_sheets)) )
wb <- write.xlsx(linkReferences, file=my.sheets.file)
sample_header <-data.frame( head_name = c("head1", "head2","head3","head4","head5") ,
head_text = c("text1", "text2","text3","text4","text5") )
set.seed(31415)
for (i in 1:number_of_sheets) {
cat(i,"..")
sheet_name_i <- paste0("Data ",i)
addWorksheet(wb, sheetName = sheet_name_i)
writeData(wb, sheet=sheet_name_i, sample_header, startCol = "B", startRow=2)
n = ceiling( runif(1)*200 )
sample_data <- data_frame(A=seq(1:n),
B= runif(n),
C= sample(seq(1:5),n,replace=TRUE))
writeData(wb, sheet=sheet_name_i, sample_data, startCol = "B", startRow=10)
}
saveWorkbook(wb, file=my.sheets.file, overwrite=TRUE)
#===========================================================================
# THIS IS THE ACTUAL QUESTION
# Read from file with many tabs
library(readxl)
library(stringr)
linkReferences <- linkReferences %>%
mutate( Head1 = NA, Head2 = NA, Head3 = NA, Head4 = NA, Head5 = NA,
A.1 = NA, B.1 = NA, C.1 = NA,
A.2 = NA, B.2 = NA, C.2 = NA,
A.3 = NA, B.3 = NA, C.3 = NA,
A.4 = NA, B.4 = NA, C.4 = NA,
A.5 = NA, B.5 = NA, C.5 = NA
)
linkReferences.nrows = nrow(linkReferences)
lRnames <- names(linkReferences)
start.row=1
start_time <- Sys.time()
for (i in start.row:linkReferences.nrows){
cat("i=",i, " / ",linkReferences.nrows,"\n")
start_time_i=Sys.time()
linked_data <- read_xlsx(my.sheets.file,
sheet=as.character(linkReferences[i,"sheet"]),
skip=2,
col_types = c("text","text","text"),
col_names=FALSE)
print(Sys.time()-start_time_i) # This takes 99% of the loop time
linkReferences[i,2:6] <- unlist( linked_data[1:5,2])
data_head_row <- which( linked_data[,1]=="A")
names(linked_data) <- c("A","B","C")
linked_data <- linked_data[ (data_head_row+1):(nrow(linked_data)),]
# create a (rather random) sample summary
summary_linked_data <- linked_data%>%
group_by(C) %>%
summarise(B=last(B), A=last(A)) %>%
arrange(desc(C))
# not all data has the full range of options, so use actual number
summary_linked_data_nrows <- nrow(summary_linked_data)
#start_time_i2 <- Sys.time()
for( ii in 1:summary_linked_data_nrows) {
linkReferences[i, match(str_c("A.",ii),lRnames):match(str_c("C.",ii),lRnames)] <-
summary_linked_data[ii,]
}
#print(Sys.time()-start_time_i2)
print(linkReferences[i,2:20])
# ________________________________________________________
# BELOW IS ONLY FOR TEST LOOP TIMING STATS IN THIS EXAMPLE
delta_time <- Sys.time() - start_time
delta_time_attr <- attr(delta_time, "units")
row_time <- delta_time/(i-start.row+1)
if (delta_time_attr =="mins") {
row_time <- row_time*60
} else if( delta_time_attr == "hours") {
row_time <- row_time*3600
}
total_time <- row_time*(linkReferences.nrows-start.row-1)/3600
cat( "Passed time: ", delta_time, attr(delta_time, "units"),
" | time/row: ", round(row_time,2), "secs.",
" | Est total time:",
round(total_time*60,2), "mins = )",
round(total_time,2), "hours )",
"\n---------------\n")
}
# Conversion of data loaded as character to numeric can all happen outside loop once all data is loaded.
xlsx
, и использование сборки мусора в вашем цикле может помочь: stackoverflow. com/q/7963393/2060081. - person sempervent   schedule 11.01.2018linkReferences
, чтобы сделать это полный проверяемый пример. - person dww   schedule 11.01.2018