Leggere file Excel in R (readxl)

Per leggere file Excel R ha bisogno di un pacchetto

R base legge i formati di testo semplice senza nulla in più, ma .xlsx non è testo: è un pacchetto compresso di XML, e prima di lui .xls era un formato binario. Non c'è nessuna read.xlsx() integrata ad aspettarti. Per aprire file Excel installi un pacchetto, e la scelta standard è readxl: legge sia .xlsx sia .xls, non ha dipendenze esterne (niente Java, non serve Excel installato) e fa un solo lavoro, bene.

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

Gli esempi di questa pagina richiedono readxl installato in locale, quindi sono mostrati come codice statico e non come blocchi eseguibili: provali nella tua sessione di R. Se i pacchetti sono una novità per te, in breve: install.packages() lo scarica una volta, library() lo carica ogni volta che avvii R.

read_excel(): le basi

Una sola chiamata legge in R il primo foglio di una cartella di lavoro:

library(readxl)

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

Le abitudini sono le stesse di ogni importazione: str() per controllare il tipo di ogni colonna, head() per dare un'occhiata alle prime righe. Se il file non viene trovato, la causa è quasi sempre la directory di lavoro: file.exists("sales.xlsx") te lo dice in un secondo, e la soluzione è la stessa dei file CSV: usa un percorso completo o imposta la directory di lavoro sulla cartella del file.

Quello che ottieni è un tibble, la versione del data frame proposta dal tidyverse. Per tutto quello che farai da principiante si comporta esattamente come un data frame (lo è, con qualcosa in più): $ estrae le colonne, nrow() conta le righe e funziona con i verbi di dplyr. Le differenze visibili sono estetiche e piacevoli: stampa solo le prime dieci righe con i tipi delle colonne sotto i nomi, invece di riversare tutto. Se qualche funzione più vecchia pretende un data frame normale, as.data.frame(sales) lo converte.

Scegliere fogli e intervalli, e saltare la spazzatura

Le cartelle di lavoro reali raramente sono una tabella pulita che parte dalla cella A1. Gli argomenti di readxl gestiscono il solito disordine.

Quali fogli ci sono? Chiedilo prima di leggere:

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

sheet = ne sceglie uno, per nome o per posizione:

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

Preferisci il nome: sheet = 5 legge in silenzio il foglio sbagliato il giorno in cui qualcuno riordina le schede.

range = legge un rettangolo esatto, nella notazione di Excel. È il modo più pulito per saltare righe con il logo, righe di titolo e note sparse attorno alla tabella vera e propria:

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

skip = e col_names = sono l'alternativa meno rigida quando sai quante righe inutili ci sono sopra i dati ma non dove finiscono:

# 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 (il default) usa la prima riga come nomi; FALSE genera ...1, ...2 e tratta la prima riga come dati.

Per scrivere in Excel serve un altro pacchetto

readxl è di sola lettura per scelta. Quando un collega vuole i tuoi risultati in un foglio di calcolo, la via più rapida è writexl, una sola riga e nessuna dipendenza:

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

Se ti serve più dei soli valori, come intestazioni in grassetto, riquadri bloccati, colori delle celle o più fogli formattati nella stessa cartella di lavoro, entri nel territorio di openxlsx:

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

Una lista con nomi diventa un foglio per ogni elemento. openxlsx sa anche leggere i file Excel, quindi in giro lo vedrai usato in entrambe le direzioni; per la semplice lettura, readxl resta lo strumento più semplice.

L'alternativa pragmatica: esportare un CSV

A volte vince la soluzione meno furba. Se la cartella di lavoro è un caso isolato, per esempio qualcuno ti ha mandato un foglio di calcolo e ti servono i numeri subito, aprila in Excel, usa "Salva con nome" per esportare il foglio come CSV e leggilo con lo strumento che già conosci:

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

Nessun pacchetto, nessun nome di foglio, nessuna cella unita. Il procedimento completo è in leggere CSV. I compromessi: perdi gli altri fogli, appiattisci le informazioni legate alla formattazione (celle colorate che "significano" qualcosa, comunque un cattivo segnale nella progettazione dei dati) e l'esportazione è un passaggio manuale che qualcuno dimenticherà quando il file sorgente si aggiorna. Per una pipeline che si ripete, leggi direttamente il .xlsx; per un'importazione una tantum, il CSV va benissimo.

I classici tranelli dell'importazione da Excel

I file Excel portano problemi che i CSV non hanno, perché i fogli di calcolo permettono alle persone di fare cose che le tabelle non dovrebbero.

Le date arrivano come numeri. Excel salva le date come un conteggio progressivo di giorni, e se una colonna mescola tipi o è stata formattata in modo strano, puoi ritrovarti con 44688 dove ti aspettavi una data. Di solito readxl converte correttamente le vere celle data, ma quando ottieni il numero grezzo, convertilo con l'origine di Excel, che è 1899-12-30, non il 1970:

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

Le celle unite si separano in celle vuote. Un'intestazione unita su tre colonne torna come un valore e due celle vuote; un'etichetta di categoria unita su dieci righe diventa un valore e nove mancanti. readxl non può recuperare un'intenzione che esisteva solo visivamente: aspettati di riempire tu quei vuoti dopo l'importazione.

Una sola cella fuori posto trasforma una colonna in testo. I tipi delle colonne vengono dedotti dai dati, quindi un solo "n/a", una nota scritta in mezzo ai numeri o uno spazio in una cella "vuota" fanno tornare tutta la colonna come carattere. str() subito dopo la lettura lo scopre; l'argomento col_types (per esempio col_types = c("text", "numeric", "date")) impone i tipi quando la deduzione continua a sbagliare.

Il filo conduttore: un foglio di calcolo è una tela su cui le persone disegnano, non una tabella. Leggilo con spirito scettico, esegui str() e verifica qualche valore rispetto all'originale prima di fidarti dell'importazione.

Cosa ti porti a casa

• R base non legge i .xlsx: installa readxl, poi read_excel("file.xlsx").
• excel_sheets() elenca il contenuto di una cartella di lavoro; sheet = (meglio con i nomi) ne sceglie uno; range = "B4:E20" ritaglia esattamente la tabella che ti serve.
• Ottieni un tibble, cioè un data frame con una stampa più gradevole.
• La scrittura passa da un altro pacchetto: writexl::write_xlsx() per l'output semplice, openxlsx per le cartelle di lavoro formattate.
• Per i casi isolati, esportare un CSV da Excel e usare read.csv() è una scorciatoia rispettabilissima.
• Occhio ai tre classici: date come numeri progressivi (origine 1899-12-30), celle unite che diventano vuote e una cella sbagliata che trascina una colonna a testo.

Prossimo passo: la direzione opposta, cioè scrivere i tuoi data frame in file CSV e RDS.