Чтение файлов Excel в R (readxl)

Чтобы читать Excel, R нужен пакет

Базовый R читает текстовые форматы из коробки, но .xlsx — не текст, это упакованный набор XML, а .xls до него был двоичным форматом. Никакой встроенной read.xlsx() вас не ждёт. Чтобы открывать файлы Excel, вы устанавливаете пакет, и стандартный выбор — readxl: он читает и .xlsx, и .xls, не имеет внешних зависимостей (не нужны ни Java, ни установленный Excel) и хорошо делает одну работу.

install.packages("readxl")   # once per machine
library(readxl)              # once per session

Сниппетам на этой странице нужен локально установленный readxl, поэтому они показаны статичным кодом, а не запускаемыми блоками, — выполняйте их в собственной сессии R. Если пакеты для вас в новинку, коротко: install.packages() скачивает пакет один раз, library() загружает его при каждом запуске R.

read_excel(): основы

Один вызов читает первый лист книги в R:

library(readxl)

sales <- read_excel("sales.xlsx")
head(sales)
str(sales)

Привычки те же, что и при любом импорте: str() — проверить тип каждого столбца, head() — окинуть взглядом первые строки. Если файл не найден, причина почти всегда в рабочем каталоге — file.exists("sales.xlsx") скажет об этом за секунду, а решение то же, что и для файлов CSV: использовать полный путь или установить рабочий каталог в папку с файлом.

Возвращается тиббл — версия датафрейма от tidyverse. Для всего, что вы будете делать как новичок, он ведёт себя ровно как датафрейм (он им является, с дополнениями): $ извлекает столбцы, nrow() считает строки, он течёт в глаголы dplyr. Видимые отличия косметические и приятные — он печатает только первые десять строк с типами столбцов под именами, вместо того чтобы вываливать всё. Если какая-то старая функция настаивает на обычном датафрейме, as.data.frame(sales) его преобразует.

Выбор листов, диапазонов и пропуск мусора

Реальные книги редко представляют собой одну чистую таблицу, начинающуюся с ячейки A1. Аргументы readxl справляются с обычным беспорядком.

Какие листы существуют? Спросите до чтения:

excel_sheets("report.xlsx")
# [1] "Summary"  "Q1"  "Q2"  "Q3"  "Raw data"

sheet = выбирает один по имени или по позиции:

q3 <- read_excel("report.xlsx", sheet = "Q3")
raw <- read_excel("report.xlsx", sheet = 5)

Предпочитайте имя — sheet = 5 молча прочитает не тот лист в день, когда кто-то переставит вкладки.

range = читает точный прямоугольник в собственной нотации Excel. Это самый чистый способ пропустить строки с логотипом, заголовки и разрозненные заметки вокруг настоящей таблицы:

budget <- read_excel("budget.xlsx", range = "B4:E20")
budget <- read_excel("budget.xlsx", sheet = "Plan", range = "B4:E20")

skip = и col_names = — более свободная альтернатива, когда вы знаете, сколько мусорных строк сидит над данными, но не знаете, где данные заканчиваются:

# Data starts after 3 title rows, first real row is the header:
df <- read_excel("export.xlsx", skip = 3)

# No header row at all - supply names yourself:
df <- read_excel("export.xlsx", col_names = c("id", "region", "amount"))

col_names = TRUE (по умолчанию) использует первую строку как имена; FALSE генерирует ...1, ...2 и трактует первую строку как данные.

Для записи в Excel нужен другой пакет

readxl только для чтения по замыслу. Когда коллега хочет ваши результаты в виде таблицы, самый быстрый путь — writexl, однострочник без зависимостей:

install.packages("writexl")
writexl::write_xlsx(results, "results.xlsx")

Если нужно больше, чем сырые значения, — жирные заголовки, закреплённые области, цвета ячеек, несколько оформленных листов в одной книге, — это территория openxlsx:

library(openxlsx)
write.xlsx(list(Summary = summary_df, Detail = detail_df), "report.xlsx")

Именованный список превращается в один лист на элемент. openxlsx умеет также читать файлы Excel, поэтому в дикой природе вы встретите его в обоих направлениях; для простого чтения readxl остаётся более простым инструментом.

Прагматичная альтернатива: экспортировать CSV

Иногда побеждает наименее хитроумное решение. Если книга разовая — кто-то прислал вам таблицу, и числа нужны сейчас, — откройте её в Excel, через «Сохранить как» экспортируйте лист в CSV и прочитайте инструментом, который вы уже знаете:

df <- read.csv("sales.csv")

Ни пакета, ни имён листов, ни объединённых ячеек. Полный рабочий процесс — в статье про чтение CSV. Компромиссы: вы теряете остальные листы, вы уплощаете информацию, закодированную форматированием (цвета ячеек, которые что-то «означают», — впрочем, это и так признак плохого дизайна данных), а экспорт становится ручным шагом, о котором кто-нибудь забудет при обновлении исходного файла. Для повторяющегося конвейера читайте .xlsx напрямую; для разового импорта CSV — честно говоря, нормально.

Классические ловушки импорта из Excel

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

Даты приходят числами. Excel хранит даты как порядковый счёт дней, и если столбец смешивает типы или был странно отформатирован, вы можете получить 44688 там, где ждали дату. readxl обычно корректно преобразует настоящие ячейки с датами, но когда вы всё же получили сырое число, преобразуйте его с эпохой Excel — это 1899-12-30, а не 1970:

as.Date(44688, origin = "1899-12-30")
# [1] "2022-05-07"

Объединённые ячейки разъединяются в пустоты. Заголовок, объединённый по трём столбцам, вернётся как одно значение и две пустые ячейки; подпись категории, объединённая вниз на десять строк, станет одним значением и девятью пропусками. readxl не может восстановить намерение, существовавшее только визуально, — рассчитывайте заполнять эти пробелы самостоятельно после импорта.

Одна затесавшаяся ячейка превращает столбец в текст. Типы столбцов угадываются по данным, поэтому единственное "n/a", заметка, впечатанная среди чисел, или пробел в «пустой» ячейке делают весь столбец символьным. str() сразу после чтения это ловит; аргумент col_types (например, col_types = c("text", "numeric", "date")) ставит точку в вопросе, когда угадывание постоянно ошибается.

Общая мысль: таблица — это холст, на котором люди рисуют, а не таблица данных. Читайте её в шляпе скептика, запускайте str() и сверяйте несколько значений с оригиналом, прежде чем доверять импорту.

Что вы уносите с собой

• Базовый R не умеет читать .xlsx — установите readxl, затем read_excel("file.xlsx").
• excel_sheets() перечисляет содержимое книги; sheet = (предпочитайте имена) выбирает лист; range = "B4:E20" вырезает ровно нужную таблицу.
• Возвращается тиббл — датафрейм с более приятной печатью.
• Запись идёт через другой пакет: writexl::write_xlsx() для простого вывода, openxlsx для оформленных книг.
• Для разовых задач экспорт CSV из Excel и read.csv() — вполне уважаемый обходной путь.
• Следите за тремя классиками: даты как порядковые числа (origin 1899-12-30), объединённые ячейки, превращающиеся в пустоты, и одна плохая ячейка, утаскивающая столбец в текст.

Дальше: обратное направление — запись ваших датафреймов в файлы CSV и RDS.