Используйте R и Openxlsx для вывода списка фреймов данных в виде листов в одном файле Excel.

У меня есть набор файлов CSV. Я хочу упаковать их и экспортировать данные в один файл Excel, содержащий несколько листов. Я читаю файлы CSV как набор фреймов данных.

Моя проблема в том, как создать команду в openxlsx, я могу сделать это вручную, но у меня проблема с построением списка. В частности, как добавить фрейм данных в качестве подкомпонента именованного списка, а затем передать в качестве параметра write.xlsx()

Пример

Хорошо, поэтому я сначала перечисляю файлы CSV на диске и создаю набор фреймов данных в памяти ...

# Generate a list of csv files on disk and shorten names... 
filePath <- "../02benchmark/results/results_20170330/"
filePattern <- "*.csv"
fileListwithPath = list.files(path = filePath, pattern = filePattern, full.names = TRUE)
fileList = list.files(path = filePath, pattern = filePattern, full.names = FALSE)

datasets <- gsub("*.csv$", "", fileList)
datasets <- gsub("sample_", "S", datasets)
datasets

# Now generate the dataframes for each csv file...
list2env(
  lapply(setNames(fileListwithPath, make.names(datasets)),
         read.csv), envir = .GlobalEnv)

Пример вывода:

dput(datasets)
c("S10000_R3.3.2_201703301839", "S10000_T4.3.0_201703301843", 
"S20000_R3.3.2_201703301826", "S20000_T4.3.0_201703301832", "S280000_R3.3.2_201704020847", 
"S280000_T4.3.0_201704021100", "S290000_R3.3.2_201704020447", 
"S290000_T4.3.0_201704020702", "S30000_R3.3.2_201703301803", 
"S30000_T4.3.0_201703301817", "S310000_R3.3.2_201704012331", 
"S310000_T4.3.0_201704020242", "S320000_R3.3.2_201704011827", 
"S320000_T4.3.0_201704012128", "S330000_R3.3.2_201704011304", 
"S330000_T4.3.0_201704011546", "S340000_R3.3.2_201704010652", 
"S340000_T4.3.0_201704011010", "S350000_R3.3.2_201704010020", 
"S350000_T4.3.0_201704010404", "S360000_R3.3.2_201703311819", 
"S360000_T4.3.0_201703312134", "S370000_R3.3.2_201703310914", 
"S370000_T4.3.0_201703311301", "S380000_R3.3.2_201703310134", 
"S380000_T4.3.0_201703310509", "S390000_R3.3.2_201703301846", 
"S390000_T4.3.0_201703302252", "S40000_R3.3.2_201703301738", 
"S40000_T4.3.0_201703301752", "S50000_R3.3.2_201703301707", "S50000_T4.3.0_201703301724", 
"S60000_R3.3.2_201703301624", "S60000_T4.3.0_201703301647", "S70000_R3.3.2_201703301535", 
"S70000_T4.3.0_201703301602", "S80000_R3.3.2_201703301430", "S80000_T4.3.0_201703301508", 
"S90000_R3.3.2_201703301324", "S90000_T4.3.0_201703301400")

Теперь у нас есть набор фреймов данных, и мы хотим создать один файл Excel с несколькими листами ...

wb <- createWorkbook()
saveWorkbook(wb, 'output.xlsx')

lapply(names(myList), function(x) write.xlsx(myList[[x]], 'output.xlsx', sheetName=x, append=TRUE))

Проблема:

Проблема в том, что я могу создать структуру списка вручную и могу подтвердить, что она работает, НО я не могу создать список автоматически.

myList <- sapply(datasets,function(x) NULL)
names(myList)
str(myList)
myList$S10000_R3.3.2_201703301839 <- eval(S10000_R3.3.2_201703301839)

таким образом:

> str(myList)
List of 40
 $ S10000_R3.3.2_201703301839 :'data.frame':    43 obs. of  4 variables:
  ..$ function.: Factor w/ 42 levels "DF add random number vector",..: 30 25 38 42 36 39 40 29 26 22 ...
  ..$ user     : num [1:43] 2.144 0.263 0.024 0.068 0.008 ...
  ..$ system   : num [1:43] 0.63 0.065 0.001 0.004 0 ...
  ..$ elapsed  : num [1:43] 12.274 1.104 0.047 0.115 0.009 ...
 $ S10000_T4.3.0_201703301843 : NULL
 $ S20000_R3.3.2_201703301826 : NULL
 ...

Конкретная проблема: как добавить каждый фрейм данных в список ...

myList <- lapply( myList, function(x) eval(x) )

что я делаю не так с lapply здесь? Вышеупомянутый lapply () не выполняет итерацию по списку и не добавляет фрейм данных к записи списка имен.

i.e. myList$S10000_R3.3.2_201703301839 <- eval(S10000_R3.3.2_201703301839)
> str(myList)
    List of 40
     $ S10000_R3.3.2_201703301839 :'data.frame':    43 obs. of  4 variables:
      ..$ function.: Factor w/ 42 levels "DF add random number vector",..: 30 25 38 42 36 39 40 29 26 22 ...
      ..$ user     : num [1:43] 2.144 0.263 0.024 0.068 0.008 ...
      ..$ system   : num [1:43] 0.63 0.065 0.001 0.004 0 ...
      ..$ elapsed  : num [1:43] 12.274 1.104 0.047 0.115 0.009 ...
     $ S10000_T4.3.0_201703301843 : NULL
     $ S20000_R3.3.2_201703301826 : NULL
     ...

Что мне не хватает? Всем помощь с благодарностью. Да, я почти уверен, что упускаю что-то очевидное ... но ... я в тупике.


r excel xlsx lapply openxlsx
person Technophobe01    schedule 03.04.2017    source источник

Ответы (3)


arrow_upward
5
arrow_downward

У меня нет ваших фреймов данных, поэтому я не могу это проверить, но приведенный ниже код аналогичен подходу, который я использую, когда мне нужно читать и записывать файлы Excel. В приведенном ниже коде используется пакет xlsx, с которым я знаком, но, надеюсь, вы сможете адаптировать его, если вам нужно использовать openxlsx.

library(xlsx)

Сначала прочтите файлы в список. Что-то вроде этого:

filePath <- "../02benchmark/results/results_20170330/"
filePattern <- "*.csv"
fileListwithPath = list.files(path = filePath, 
                              pattern = filePattern, 
                              full.names = TRUE)
fileList = list.files(path = filePath, pattern = filePattern, full.names = FALSE)
fileListwithPath = setNames( fileListwithPath, 
                             list.files(path = filePath, pattern = filePattern))
df.list = lapply(fileListwithPath, read.csv)

# Now we rename the List Names for use in worksheets...
# Remove .csv and sample_ prefix used in filenames...
# Reult in workbook S<size>_<R version>_<date>
names(df.list) <- gsub("\\.csv$","", names(df.list))
names(df.list) <- gsub("sample_","S", names(df.list))

Теперь у вас есть список, в котором каждый элемент является фреймом данных, а имя каждого элемента - именем файла. Теперь давайте запишем каждый фрейм данных на другой лист в той же книге Excel, а затем сохраним файл как файл xlsx:

wb = createWorkbook()

lapply( names(df.list), 
        function(df) {
          sheet = createSheet(wb, df)
          addDataFrame(df.list[[df]], sheet = sheet, row.names = FALSE)
          } )

saveWorkbook(wb, "My_workbook.xlsx")

Я разделил чтение и запись файлов CSV для иллюстрации, но вы можете объединить их в одну функцию, которая читает каждый отдельный файл CSV и записывает его на новый лист в одной книге Excel.

person eipi10    schedule 03.04.2017
comment
Спасибо. Код, который вы предоставили, работал - очень признателен. Мне нужно потратить немного времени, чтобы познакомиться с манипуляциями со списками. Если я исправлю исходный код, я опубликую объяснение. Вы позволили мне двигаться вперед - очень ценю вашу помощь. Хорошая карма и благодарность летят к вам. - person Technophobe01; 03.04.2017
comment
Мне интересно посмотреть, как я могу изменить имя целевого рабочего листа с df.list$sample_10000_R3.3.2_201703301839.csv на df.list$S10000_R3.3.2_201703301839 - просто я знаю, но ... - person Technophobe01; 03.04.2017
comment
Смотрите обновление. Я добавил строку, чтобы удалить .csv в конце каждого имени файла. - person eipi10; 03.04.2017
comment
eipi10 - Спасибо, я обновил ваш пример, чтобы отразить окончательный код. Я должен изменить имена df.list после прочтения файлов с lapply(). Ваша помощь очень ценится :-) - person Technophobe01; 03.04.2017

arrow_upward
3
arrow_downward

Вот решение с openxlsx:

## create data;
dataframes <- split(iris, iris$Species)

# create workbook
wb <- createWorkbook()
 
#Iterate the same way as PavoDive, slightly different (creating an anonymous function inside Map())
Map(function(data, nameofsheet){     

    addWorksheet(wb, nameofsheet)
    writeData(wb, nameofsheet, data)
 
}, dataframes, names(dataframes))
         
## Save workbook to excel file 
saveWorkbook(wb, file = "file.xlsx", overwrite = TRUE)

.. однако openxlsx также может использовать для этого свою функцию openxlsx::write.xlsx, поэтому вы можете просто предоставить объекту свой список фреймов данных и путь к файлу, и openxlsx достаточно умен, чтобы создать список в виде листов в xlsx-файле. Код, который я публикую здесь с Map(), предназначен для тех случаев, когда вы хотите отформатировать листы определенным образом.

person emilBeBri    schedule 04.09.2018

arrow_upward
2
arrow_downward

Я думаю, что, возможно, стоит добавить решение, используя функцию imap из функции _ 2_, поскольку он предлагает удобный механизм для доступа к имени и индекс элемента списка за один вызов:

imap_xxx(x, ...), проиндексированная карта, является сокращением для map2(x, names(x), ...), если у x есть имена, или map2(x, seq_along(x), ...), если нет. Это полезно, если вам нужно вычислить как значение, так и позицию элемента.

imap решение

На фиктивных данных для воспроизводимости.

lst_data <- list(cars = mtcars, air = airmiles)
wb <- openxlsx::createWorkbook()
purrr::imap(
    .x = lst_data,
    .f = function(df, object_name) {
        openxlsx::addWorksheet(wb = wb, sheetName = object_name)
        openxlsx::writeData(wb = wb, sheet = object_name, x = df)
    }
)
t_file <- tempfile(pattern = "test_df_export", fileext = ".xlsx")
saveWorkbook(wb = wb, file = t_file)
person Konrad    schedule 09.01.2020
comment
вы можете использовать iwalk, чтобы избежать сообщений на консоли - person Captain Tyler; 13.04.2021