Wczytywanie plików Excel w R (readxl)

R potrzebuje pakietu do czytania plików Excel

Bazowy R od razu czyta formaty tekstowe, ale .xlsx nie jest tekstem: to spakowany zestaw plików XML, a wcześniejszy .xls był formatem binarnym. Nie czeka na ciebie żadna wbudowana read.xlsx(). Żeby otwierać pliki Excel, instalujesz pakiet, a standardowym wyborem jest readxl: czyta zarówno .xlsx, jak i .xls, nie ma zewnętrznych zależności (nie potrzeba Javy ani zainstalowanego Excela) i dobrze robi jedną rzecz.

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

Fragmenty kodu na tej stronie wymagają lokalnie zainstalowanego readxl, więc są pokazane jako statyczny kod, a nie bloki do uruchomienia. Uruchom je we własnej sesji R. Jeśli pakiety są dla ciebie nowością, w skrócie: install.packages() pobiera pakiet raz, a library() ładuje go przy każdym uruchomieniu R.

read_excel(): podstawy

Jedno wywołanie wczytuje do R pierwszy arkusz skoroszytu:

library(readxl)

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

Nawyki są takie same jak przy każdym imporcie: str() do sprawdzenia typu każdej kolumny, head() do rzucenia okiem na pierwsze wiersze. Jeśli plik nie zostanie znaleziony, przyczyną jest prawie zawsze katalog roboczy: file.exists("sales.xlsx") powie ci to w sekundę, a rozwiązanie jest takie samo jak dla plików CSV: użyj pełnej ścieżki albo ustaw katalog roboczy na folder z plikiem.

Wynikiem jest tibble, czyli wersja ramki danych z tidyverse. We wszystkim, co robisz jako początkujący, zachowuje się dokładnie jak ramka danych (jest nią, tylko z dodatkami): $ wyciąga kolumny, nrow() liczy wiersze, a całość płynnie trafia do czasowników dplyr. Widoczne różnice są kosmetyczne i przyjemne: wypisuje tylko pierwsze dziesięć wierszy z typami kolumn pod nazwami, zamiast wyrzucać wszystko. Jeśli jakaś starsza funkcja upiera się przy zwykłej ramce danych, as.data.frame(sales) ją przekształci.

Wybór arkuszy i zakresów, pomijanie śmieci

Prawdziwe skoroszyty rzadko są jedną czystą tabelą zaczynającą się w komórce A1. Argumenty readxl radzą sobie z typowym bałaganem.

Jakie arkusze istnieją? Zapytaj, zanim zaczniesz czytać:

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

sheet = wybiera jeden, według nazwy albo pozycji:

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

Wybieraj nazwę: sheet = 5 po cichu wczyta zły arkusz tego dnia, gdy ktoś zmieni kolejność kart.

range = wczytuje dokładny prostokąt w notacji samego Excela. To najczystszy sposób na pominięcie wierszy z logo, wierszy tytułowych i zabłąkanych notatek wokół właściwej tabeli:

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

skip = i col_names = to luźniejsza alternatywa, gdy wiesz, ile śmieciowych linii jest nad danymi, ale nie wiesz, gdzie dane się kończą:

# 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 (domyślnie) używa pierwszego wiersza jako nazw; FALSE generuje ...1, ...2 i traktuje pierwszy wiersz jako dane.

Zapis do Excela wymaga innego pakietu

readxl z założenia tylko czyta. Gdy ktoś z zespołu chce twoich wyników jako arkusza kalkulacyjnego, najszybsza droga to writexl: jedna linia i zero zależności:

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

Jeśli potrzebujesz czegoś więcej niż surowe wartości (pogrubionych nagłówków, zablokowanych okienek, kolorów komórek, kilku sformatowanych arkuszy w jednym skoroszycie), to terytorium openxlsx:

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

Nazwana lista zamienia się w jeden arkusz na każdy element. openxlsx potrafi też czytać pliki Excel, więc w praktyce zobaczysz go w obu kierunkach; do zwykłego czytania readxl pozostaje prostszym narzędziem.

Pragmatyczna alternatywa: eksport do CSV

Czasem wygrywa najmniej wyrafinowane rozwiązanie. Jeśli skoroszyt to jednorazowa sprawa (ktoś przysłał ci arkusz, a liczby potrzebujesz teraz), otwórz go w Excelu, użyj „Zapisz jako”, żeby wyeksportować arkusz do CSV, i wczytaj go narzędziem, które już znasz:

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

Bez pakietu, bez nazw arkuszy, bez scalonych komórek. Cały proces opisuje artykuł o wczytywaniu CSV. Koszty: tracisz pozostałe arkusze, spłaszczasz informacje zapisane w formatowaniu (kolory komórek, które coś „znaczą”, i tak są złym projektem danych), a eksport to ręczny krok, o którym ktoś zapomni, gdy plik źródłowy się zmieni. W powtarzalnym procesie czytaj .xlsx bezpośrednio; przy jednorazowym imporcie CSV jest naprawdę w porządku.

Klasyczne pułapki importu z Excela

Pliki Excel niosą problemy, których nie mają pliki CSV, bo arkusze kalkulacyjne pozwalają ludziom robić rzeczy, których tabele nie powinny dopuszczać.

Daty przychodzą jako liczby. Excel przechowuje daty jako kolejny numer dnia i jeśli kolumna miesza typy albo została dziwnie sformatowana, możesz dostać 44688 tam, gdzie miała być data. readxl zwykle poprawnie konwertuje prawdziwe komórki z datami, ale gdy dostaniesz surową liczbę, przelicz ją względem punktu początkowego Excela, którym jest 1899-12-30, a nie 1970:

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

Scalone komórki rozpadają się na puste pola. Nagłówek scalony na trzy kolumny wraca jako jedna wartość i dwie puste komórki; etykieta kategorii scalona w dół na dziesięć wierszy staje się jedną wartością i dziewięcioma brakującymi. readxl nie odtworzy intencji, która istniała tylko wizualnie, więc licz się z tym, że te luki trzeba uzupełnić ręcznie po imporcie.

Jedna zabłąkana komórka zamienia kolumnę w tekst. Typy kolumn są zgadywane na podstawie danych, więc pojedyncze "n/a", notatka wpisana między liczby albo spacja w „pustej” komórce sprawiają, że cała kolumna wraca jako tekst. str() zaraz po wczytaniu to wyłapie; argument col_types (np. col_types = c("text", "numeric", "date")) wymusza typy, gdy zgadywanie wciąż się myli.

Motyw przewodni: arkusz kalkulacyjny to płótno, po którym ludzie rysują, a nie tabela. Czytaj go sceptycznie, uruchom str() i porównaj kilka wartości z oryginałem, zanim zaufasz importowi.

Najważniejsze informacje

• Bazowy R nie czyta .xlsx: zainstaluj readxl, a potem read_excel("file.xlsx").
• excel_sheets() wypisuje zawartość skoroszytu; sheet = (lepiej z nazwą) wybiera arkusz; range = "B4:E20" wycina dokładnie tę tabelę, której potrzebujesz.
• Dostajesz tibble, czyli ramkę danych z ładniejszym wypisywaniem.
• Zapis odbywa się przez inny pakiet: writexl::write_xlsx() dla zwykłego wyniku, openxlsx dla sformatowanych skoroszytów.
• Przy jednorazowych zadaniach eksport CSV z Excela i read.csv() to całkowicie przyzwoity skrót.
• Uważaj na trzy klasyki: daty jako liczby seryjne (punkt początkowy 1899-12-30), scalone komórki zamieniające się w puste pola i jedną złą komórkę, która zamienia kolumnę w tekst.

Dalej: kierunek odwrotny, czyli zapisywanie ramek danych do plików CSV i RDS.