使用 readxl 在 R 中读取并导入 Excel 文件

尽管许多组织将数据存储在数据库以及 AWS、Azure、GCP 等存储方案中，Microsoft Excel 电子表格仍然被广泛用于存放较小的数据集。

与 R 相比，Excel 在数据科学方面的功能更有限，因此能够将电子表格中的数据导入 R 十分有用。

在本教程中，我们将介绍如何使用 readxl 包将 Excel 工作表（以及特定的行和列）读取到 R 中。

要理解这些内容，您需要具备 R 的基本操作知识。 

如需更通用的指南，了解如何将多种文件类型导入 R，请阅读如何将数据导入 R：教程。

Excel 文件

我们将读取到 R 的数据集很小，只有两个工作表，用来演示如何指定要读取的工作表。您可以在此处找到。

第一个工作表是银行营销数据集，包含 45,211 行和 17 列。下面的截图来自 Excel 文件“sample.xlsx”，工作表名称为“bank-full”。

第二个工作表是 Iris 数据集，包含 150 行和 6 列，记录了鸢尾花种类的信息，例如花萼和花瓣的长度与宽度。下面的截图来自同一 Excel 文件“sample.xlsx”，工作表名称为“iris”。 

包概览

本教程使用 readxl 包。openxlsx 包也是一个不错的替代方案，它还支持将数据写入 XLSX 文件，但与 dplyr、tidyr 等 tidyverse 包的集成不如 readxl 强。

要使用 readxl 包读取 Excel 文件，我们需要先安装该包，然后通过“library”函数导入。

install.packages("readxl")技术支持

您将在控制台看到如下输出，表示安装成功。

trying URL 'https://cran.rstudio.com/bin/macosx/big-sur-arm64/contrib/4.2/readxl_1.4.2.tgz'
Content type 'application/x-gzip' length 1545782 bytes (1.5 MB)
==================================================
downloaded 1.5 MB


The downloaded binary packages are in
/var/folders/mq/46mc_8tj06n0wh2xjkk08r140000gn/T//RtmpHIGYqM/downloaded_packages技术支持

要使用“readxl”的方法，请在 R 控制台中运行以下命令。

library(readxl)技术支持

请注意，openxlsx 包是写入 XLSX 文件的另一种不错选择。

读取第一个工作表

让我们只使用路径参数，通过 read_xlsx() 读取第一个工作表“bank-full”的所有数据。

bank_df <- read_xlsx(path = "sample.xlsx")技术支持

得到的数据是一个 tibble。

您也可以像使用 read_xlsx() 一样使用 read_excel()，接下来各部分中看到的所有参数在该函数中同样适用。read_excel() 会尝试判断您使用的是 XLSX 电子表格，还是更早的 XLS 格式。

bank_df <- read_excel(path = "sample.xlsx")技术支持

读取其他工作表

现在让我们使用 read_xlsx() 函数，并通过 sheet 参数读取第二个工作表“iris”的全部数据。

iris <- read_xlsx("sample.xlsx", sheet = "iris")技术支持

您也可以在 sheet 参数中用工作表编号代替工作表名称。

iris2 <- read_xlsx("sample.xlsx", sheet = 1)技术支持

读取特定行

让我们通过设置 skip 和 n_max 参数来读取工作表中的特定行。要跳过前几行，可以将 skip 参数设置为要跳过的行数。

bank_df_s2 <- read_excel("sample.xlsx", sheet = "bank-full", skip = 2)技术支持

请注意，上述代码也会跳过表头。您将在后续部分学习如何在 read_xlsx() 函数中显式指定表头。

同样地，要读取前 n 行，请在 read_xlsx() 函数中指定 n_max 参数。下面的代码读取“bank-full”工作表的前 1000 行。

bank_df_n1k <- read_excel("sample.xlsx", sheet = "bank-full", n_max = 1000)技术支持

您也可以同时使用这两个参数，既跳过若干行，又从剩余数据集中读取特定数量的行。

读取特定单元格

虽然“skip”和“n_max”参数可以让您读取数据的行子集，但您也可以通过设置 range 参数，从工作表中读取特定单元格。

指定数据子集有两种标记法：

• A1（E4:G8）标记法——A 表示列，1 表示行号。
• R1C1（R4C5:R8C7）标记法——明确指定行号与列号

核心思路是指定要从数据集中截取的矩形区域的坐标。

标记法 1：

bank_df_range1 <- read_excel("sample.xlsx", sheet = "bank-full", range = "A3:E10")技术支持

标记法 2：

bank_df_range2 <- read_excel("sample.xlsx", sheet = "bank-full", 

                 range = "R3C1:R10C5")技术支持

range 也允许您在参数中包含工作表名称（示例：wbook!E4:G8）。

bank_df_range3 <- read_excel("sample.xlsx", range = "bank-full!R3C1:R10C5")技术支持

读取无表头的数据

让我们通过将 col_names 参数设置为一个字符向量，来读取没有表头行的数据。 

附注：我们先使用 skip 参数移除表头行。

columns <- c("ID", "Sepal Length", "Sepal Width", "Petal Length", "Petal Width", "Species Name")

iris3 <- read_excel("sample.xlsx", sheet = 2, skip = 1, col_names = columns)技术支持

修复表头名称

在上一部分中，我们指定的表头为用空格分隔。您可以通过 .name_repair = "universal" 参数，将表头名称转换为符合 R 语法的变量名。

iris4 <- read_excel("sample.xlsx", sheet = 2, skip = 1,
                  col_names = columns, .name_repair = "universal")技术支持

指定列类型

默认情况下，读取 Excel 文件时，R 会猜测每个变量的数据类型。让我们查看使用默认参数读取的 iris 数据集的列类型。

sapply(iris, class)技术支持

  Id SepalLengthCm  SepalWidthCm PetalLengthCm  PetalWidthCm       Species
    "numeric"     "numeric"     "numeric"     "numeric"     "numeric"   "character" 技术支持

要覆盖列类型的自动猜测，您可以使用 col_types 参数。

iris5 <- read_excel("sample.xlsx", sheet = 2,
      col_types = c("numeric", "numeric", "numeric", "numeric", "numeric", "text"))

sapply(iris5, class)技术支持

    Id SepalLengthCm  SepalWidthCm PetalLengthCm  PetalWidthCm       Species
    "numeric"     "numeric"     "numeric"     "numeric"     "numeric"   "character" 技术支持

您也可以通过为某一列将 col_types 的取值指定为“guess”，让 R 只对选定变量的列类型进行猜测。

iris6 <- read_excel("sample.xlsx", sheet = 2,
        col_types = c("guess", "numeric", "numeric", "numeric", "numeric", "text"))

sapply(iris6, class)技术支持

Id SepalLengthCm  SepalWidthCm PetalLengthCm  PetalWidthCm       Species
    "numeric"     "numeric"     "numeric"     "numeric"     "numeric"   "character" 技术支持

跳过列

人们常说，数据越多越好。但在许多用例中，您会发现某些变量/列并不包含有效信息，原因可能包括以下几种： 

• 高基数：几乎每个取值都不同
• 零基数：所有取值都相同
• 与业务用例无关
• 缺失值过多
• 噪声值过多

您可以通过将 col_types 设置为 "skip" 来跳过读取某些列，如下所示。

iris7 <- read_excel("sample.xlsx", sheet = 2,
            col_types = c("numeric", "skip", "skip", "numeric", "numeric", "text"))

sapply(iris7, class)技术支持

更进一步

在一个数据以惊人速度、以多种形式生成的世界中，您的编程语言必须支持读取这些数据类型。R 正是支持这一目标的强大语言之一。报名参加“Introduction to Importing Data in R”课程，学习 R 如何通过各类包导入不同的数据集。本课程提供教程与测验，帮助您加深对在 R 中导入数据的理解。