Đọc file Excel trong R (readxl)

R cần một package để đọc tệp Excel

Base R đọc được các định dạng văn bản thuần ngay từ đầu, nhưng .xlsx không phải văn bản: đó là một gói XML được nén, còn .xls trước đó là định dạng nhị phân. Không có hàm read.xlsx() dựng sẵn nào chờ bạn cả. Để mở tệp Excel, bạn cài một package, và lựa chọn chuẩn là readxl: nó đọc cả .xlsx lẫn .xls, không phụ thuộc gì bên ngoài (không cần Java, không cần cài Excel) và làm tốt đúng một việc.

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

Các đoạn code trong trang này cần cài readxl trên máy, nên chúng được hiển thị dưới dạng code tĩnh thay vì khối chạy được: hãy chạy chúng trong phiên R của bạn. Nếu bạn mới làm quen với package, tóm tắt ngắn gọn là: install.packages() tải package về một lần, library() nạp nó mỗi lần bạn khởi động R.

read_excel(): những điều cơ bản

Một lệnh gọi đọc sheet đầu tiên của workbook vào R:

library(readxl)

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

Thói quen giống như mọi lần nhập dữ liệu: str() để kiểm tra kiểu của từng cột, head() để xem qua các hàng đầu tiên. Nếu không tìm thấy tệp, nguyên nhân gần như luôn là thư mục làm việc (working directory): file.exists("sales.xlsx") cho bạn biết trong một giây, và cách sửa giống hệt với tệp CSV: dùng đường dẫn đầy đủ hoặc đặt thư mục làm việc về thư mục chứa tệp.

Kết quả trả về là một tibble, phiên bản data frame của tidyverse. Với mọi việc bạn làm khi mới bắt đầu, nó hoạt động y như data frame (nó chính là data frame, có thêm vài tiện ích): $ lấy cột, nrow() đếm hàng, và nó dùng được với các động từ của dplyr. Những khác biệt nhìn thấy được chỉ là hình thức và dễ chịu: nó chỉ in mười hàng đầu kèm kiểu cột dưới tên cột, thay vì đổ ra toàn bộ. Nếu một hàm cũ nhất định đòi data frame thông thường, as.data.frame(sales) sẽ chuyển đổi.

Chọn sheet, vùng ô và bỏ qua phần rác

Workbook thực tế hiếm khi là một bảng sạch bắt đầu ở ô A1. Các đối số của readxl xử lý được những mớ lộn xộn thường gặp.

Có những sheet nào? Hỏi trước khi đọc:

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

sheet = chọn một sheet, theo tên hoặc theo vị trí:

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

Nên dùng tên: sheet = 5 sẽ âm thầm đọc nhầm sheet vào cái ngày có người sắp xếp lại các tab.

range = đọc chính xác một vùng chữ nhật, theo cách ghi của Excel. Đây là cách gọn nhất để bỏ qua các hàng logo, hàng tiêu đề và ghi chú lạc quanh bảng dữ liệu thật:

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

skip = và col_names = là lựa chọn lỏng hơn khi bạn biết có bao nhiêu dòng rác phía trên dữ liệu nhưng không biết dữ liệu kết thúc ở đâu:

# 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 (mặc định) dùng hàng đầu tiên làm tên cột; FALSE tự tạo tên ...1, ...2 và coi hàng đầu là dữ liệu.

Ghi ra Excel cần một package khác

readxl được thiết kế chỉ để đọc. Khi đồng nghiệp muốn nhận kết quả dưới dạng bảng tính, cách nhanh nhất là writexl, chỉ một dòng và không phụ thuộc gì:

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

Nếu bạn cần nhiều hơn giá trị thô, như tiêu đề in đậm, cố định hàng (freeze panes), màu ô, nhiều sheet có định dạng trong cùng một workbook, thì đó là việc của openxlsx:

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

Một list có tên sẽ thành mỗi phần tử một sheet. openxlsx cũng đọc được tệp Excel, nên bạn sẽ thấy nó được dùng cho cả hai chiều trong thực tế; còn để chỉ đọc, readxl vẫn là công cụ đơn giản hơn.

Giải pháp thực dụng: xuất ra CSV

Đôi khi giải pháp ít khéo léo nhất lại thắng. Nếu workbook chỉ dùng một lần (ai đó gửi bạn một bảng tính và bạn cần số liệu ngay), hãy mở nó trong Excel, dùng "Save As" để xuất sheet ra CSV, rồi đọc bằng công cụ bạn đã biết:

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

Không package, không tên sheet, không ô gộp. Toàn bộ quy trình có trong bài đọc CSV. Cái giá phải trả: bạn mất các sheet khác, mất thông tin dựa vào định dạng (màu ô mang "ý nghĩa", vốn đã là dấu hiệu thiết kế dữ liệu kém) và bước xuất là thao tác thủ công mà ai đó sẽ quên khi tệp gốc được cập nhật. Với pipeline chạy lặp lại, hãy đọc thẳng tệp .xlsx; với một lần nhập duy nhất, CSV hoàn toàn ổn.

Những cái bẫy kinh điển khi nhập Excel

Tệp Excel mang theo những vấn đề mà CSV không có, vì bảng tính cho phép con người làm những điều mà một bảng dữ liệu không nên có.

Ngày tháng bị đọc thành số. Excel lưu ngày dưới dạng số ngày tuần tự, và nếu một cột trộn nhiều kiểu hoặc được định dạng lạ, bạn có thể nhận 44688 trong khi đang chờ một ngày. readxl thường chuyển đúng các ô ngày thật, nhưng khi bạn gặp con số thô, hãy chuyển đổi theo mốc của Excel, tức 1899-12-30, không phải 1970:

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

Ô gộp bị tách thành ô trống. Một tiêu đề gộp qua ba cột trả về một giá trị và hai ô trống; một nhãn danh mục gộp xuống mười hàng thành một giá trị và chín giá trị thiếu. readxl không thể khôi phục ý đồ chỉ tồn tại về mặt hình ảnh: hãy chuẩn bị tự lấp những chỗ trống đó sau khi nhập.

Một ô lạc biến cả cột thành văn bản. Kiểu cột được đoán từ dữ liệu, nên chỉ một "n/a", một ghi chú gõ lẫn vào giữa các con số, hay một dấu cách trong ô "trống" cũng khiến cả cột thành character. Chạy str() ngay sau khi đọc sẽ phát hiện ra; đối số col_types (ví dụ col_types = c("text", "numeric", "date")) ép kiểu khi việc đoán cứ sai mãi.

Điểm chung: bảng tính là tấm vải để người ta vẽ lên, không phải một bảng dữ liệu. Hãy đọc nó với con mắt hoài nghi, chạy str() và đối chiếu vài giá trị với bản gốc trước khi tin vào dữ liệu đã nhập.

Điều cần nhớ

• Base R không đọc được .xlsx: cài readxl, rồi gọi read_excel("file.xlsx").
• excel_sheets() liệt kê nội dung workbook; sheet = (nên dùng tên) chọn một sheet; range = "B4:E20" cắt đúng bảng bạn cần.
• Kết quả trả về là tibble, một data frame in ra đẹp hơn.
• Ghi ra dùng package khác: writexl::write_xlsx() cho đầu ra thuần, openxlsx cho workbook có định dạng.
• Với việc làm một lần, xuất CSV từ Excel rồi dùng read.csv() là một lối tắt hoàn toàn đàng hoàng.
• Cảnh giác ba lỗi kinh điển: ngày bị đọc thành số tuần tự (mốc 1899-12-30), ô gộp thành ô trống, và một ô hỏng kéo cả cột thành văn bản.

Tiếp theo: chiều ngược lại, ghi data frame ra tệp CSV và RDS.