Прочитать все рабочие листы (как кадры данных) из нескольких рабочих книг Excel различной структуры.
Я это понимаю readxl может использоваться для чтения нескольких листов из рабочей книги. Однако я изо всех сил пытаюсь расширить это и векторизовать это во многих книгах с различными именами листов и количеством листов и данных в них.
Я демонстрирую, используя данные электронной таблицы Enron, которые представляют собой набор файлов.xlsx, которые я скачал.
head(list.files("../data/enron_spreadsheets/"), 3)
[1] "albert_meyers__1__1-25act.xlsx"
[2] "albert_meyers__2__1-29act.xlsx"
[3] "andrea_ring__10__ENRONGAS(1200).xlsx"
Чтобы сделать это управляемым, мы пробуем.
# Set the path to your directory of Enron spreadsheets here
enron_path <- "../data/enron_spreadsheets/"
# Set the sample size for testing here
sample_size <- 100
all_paths <- list.files(enron_path,
full.names = TRUE)
# For testing, look at n (sample_size) random workbooks.
set.seed(1337)
sample_paths <- sample(all_paths, sample_size)
paths <- sample_paths
Изучение этих рабочих тетрадей и подсчет количества рабочих таблиц показывает, что они имеют разное количество листов и содержат разные данные.
# purr package
# https://jennybc.github.io/purrr-tutorial/index.html
sheet_count <- purrr::map(paths, readxl::excel_sheets) %>%
purrr::map(length) %>%
unlist()
hist(sheet_count, main = "")
Однако для загрузки всех листов рабочей книги в список фреймов данных нам необходимо:
- Получите имена рабочих листов в виде одноименного символьного вектора (эти имена хорошо распространяются).
использование
purrr::map()повторять чтение листа.books <- dplyr::data_frame(filename = basename(paths), path = paths, sheet_name = purrr::map(paths, readxl::excel_sheets) ) %>% dplyr::mutate(id = as.character(row_number())) books # A tibble: 100 x 4 filename <chr> 1 kenneth_lay__19485__Mlp_1109.xlsx 2 kate_symes__18980__SP 15 pages.xls 3 chris_germany__1821__newpower-purc 4 john_griffith__15991__Forwards Det 5 jane_tholt__13278__bid2001A.xlsx 6 gerald_nemec__11481__EOLfieldnames 7 stacey_white__39009__Power RT Serv 8 eric_saibi__9766__012302.xlsx 9 david_delainey__8083__ENA Status o 10 daren_farmer__5035__HPLN0405.xlsx # ... with 90 more rows, and 3 # more variables: path <chr>, # sheet_name <list>, id <chr>
Здесь у нас есть один ряд на книгу в books с именами листов рабочей книги, хранящимися в столбце списка. Мы хотим, чтобы по одной строке на листе содержалось содержимое данных листа в столбце списка, чтобы мы могли добавлять дополнительные функции на основе данных листа (лист является экспериментальной единицей). Проблема в том, что это не векторизация, как ожидалось, я что-то упустил?
Это ошибки...
sheets <-
tibble::tibble("sheet_name" = unlist(books$sheet_name),
"path" = rep(paths,
times = unlist(
purrr::map_int(books$sheet_name, length))
),
"filename" = basename(path),
"sheet_data" = tibble::lst(
readxl::read_excel(path = path[],
sheet = sheet_name[])
)
) %>%
dplyr::mutate(id = as.character(row_number()))
Error in switch(ext, xls = "xls", xlsx = "xlsx", xlsm = "xlsx", if (nzchar(ext)) { :
EXPR must be a length 1 vector
Код работает, когда не передан вектор для пути к книге и имени листа, но, очевидно, данные не из правильной таблицы в этом примере ниже:
sheets <-
tibble::tibble("sheet_name" = unlist(books$sheet_name),
"path" = rep(paths,
times = unlist(
purrr::map_int(books$sheet_name, length))
),
"filename" = basename(path),
"sheet_data" = tibble::lst(
readxl::read_excel(path = path[1],
sheet = sheet_name[1])
)
) %>%
dplyr::mutate(id = as.character(row_number()))
dplyr::glimpse(sheets)
Observations: 313
Variables: 5
$ sheet_name <chr> "MLP's", "DJ SP15", "newpower-p...
$ path <chr> "../data/enron_spreadsheets//ke...
$ filename <chr> "kenneth_lay__19485__Mlp_1109.x...
$ sheet_data <list> [<# A tibble: 57 x 46, ...
$ id <chr> "1", "2", "3", "4", "5", "6", "...
Как мне прочитать данные из многих рабочих листов во многих рабочих книгах в столбец списка в таблице?
Я новичок в чтении в грязных таблицах и использовании purrr любая помощь или указатели будут оценены.
2 ответа
Поскольку вы упоминаете purrr пакет, некоторые другие пакеты Tidyverse стоит рассмотреть.
dplyrзаmutate()при примененииpurrr::map()в столбец фрейма данных и сохранение результата в виде списка-столбца.tidyrзаunnest(), который расширяет список-столбец, так что каждая строка в списке-столбце становится строкой в общем фрейме данных.tibbleдля красиво напечатанных вложенных фреймов данных
Образцы файлов необходимы для демонстрации. Этот код использует openxlsx пакет для создания одного файла, содержащего два листа (встроенный iris а также mtcars наборы данных) и еще один файл, содержащий три листа (с добавлением встроенного attitude Набор данных).
library(openxlsx)
# Create two spreadsheet files, with different numbers of worksheets
write.xlsx(list(iris, mtcars, attitude), "three_sheets.xlsx")
write.xlsx(list(iris, mtcars), "two_sheets.xlsx")
Теперь решение.
Сначала перечислите имена файлов, которые будут переданы readxl::excel_sheets() для имен листов в каждом файле, и readxl::read_excel() импортировать сами данные.
(paths <- list.files(pattern = "*.xlsx"))
#> [1] "three_sheets.xlsx" "two_sheets.xlsx"
(x <- tibble::data_frame(path = paths))
#> # A tibble: 2 x 1
#> path
#> <chr>
#> 1 three_sheets.xlsx
#> 2 two_sheets.xlsx
"Карта" readxl::excel_sheets() работать над каждым из путей к файлам и сохранять результаты в новом столбце списка. Каждый ряд sheet_name Столбец - это вектор имен листов. Как и ожидалось, у первого есть три имени листа, а у второго два.
(x <- dplyr::mutate(x, sheet_name = purrr::map(path, readxl::excel_sheets)))
#> # A tibble: 2 x 2
#> path sheet_name
#> <chr> <list>
#> 1 three_sheets.xlsx <chr [3]>
#> 2 two_sheets.xlsx <chr [2]>
Нам нужно передать каждое имя файла и имя каждого листа в readxl::read_excel(path=, sheet=) поэтому следующим шагом будет создание фрейма данных, в котором каждая строка дает путь и одно имя листа. Это сделано с помощью tidyr::unnest(),
(x <- tidyr::unnest(x))
#> # A tibble: 5 x 2
#> path sheet_name
#> <chr> <chr>
#> 1 three_sheets.xlsx Sheet 1
#> 2 three_sheets.xlsx Sheet 2
#> 3 three_sheets.xlsx Sheet 3
#> 4 two_sheets.xlsx Sheet 1
#> 5 two_sheets.xlsx Sheet 2
Теперь каждый путь и имя листа могут быть переданы в readxl::read_excel(), с помощью purrr::map2() скорее, чем purrr::map() потому что мы передаем два аргумента, а не один.
(x <- dplyr::mutate(x, data = purrr::map2(path, sheet_name,
~ readxl::read_excel(.x, .y))))
#> # A tibble: 5 x 3
#> path sheet_name data
#> <chr> <chr> <list>
#> 1 three_sheets.xlsx Sheet 1 <tibble [150 × 5]>
#> 2 three_sheets.xlsx Sheet 2 <tibble [32 × 11]>
#> 3 three_sheets.xlsx Sheet 3 <tibble [30 × 7]>
#> 4 two_sheets.xlsx Sheet 1 <tibble [150 × 5]>
#> 5 two_sheets.xlsx Sheet 2 <tibble [32 × 11]>
Теперь каждый набор данных находится в отдельном ряду data колонка. Мы можем посмотреть только на один из наборов данных, поместив этот столбец в подмножество.
x$data[3]
#> [[1]]
#> # A tibble: 30 x 7
#> rating complaints privileges learning raises critical advance
#> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 43.0 51.0 30.0 39.0 61.0 92.0 45.0
#> 2 63.0 64.0 51.0 54.0 63.0 73.0 47.0
#> 3 71.0 70.0 68.0 69.0 76.0 86.0 48.0
#> 4 61.0 63.0 45.0 47.0 54.0 84.0 35.0
#> 5 81.0 78.0 56.0 66.0 71.0 83.0 47.0
#> 6 43.0 55.0 49.0 44.0 54.0 49.0 34.0
#> 7 58.0 67.0 42.0 56.0 66.0 68.0 35.0
#> 8 71.0 75.0 50.0 55.0 70.0 66.0 41.0
#> 9 72.0 82.0 72.0 67.0 71.0 83.0 31.0
#> 10 67.0 61.0 45.0 47.0 62.0 80.0 41.0
#> # ... with 20 more rows
Я только что проверил это, и он работал нормально для одной книги.
library(readxl)
read_excel_allsheets <- function(filename) {
sheets <- readxl::excel_sheets(filename)
x <- lapply(sheets, function(X) readxl::read_excel(filename, sheet = X))
names(x) <- sheets
x
}
Это можно вызвать с помощью:
mysheets <- read_excel_allsheets("foo.xls")
Обратите внимание, это для xls и xlsx; это не будет работать для файлов xlsb.