R 数据处理（二十一）— readxl
2021-02-01 本文已影响0人
名本无名
前言
我们进行数据处理时，有时还会遇到一种常见的文件：Microsoft Office Excel 工作表的格式。
在 R 中有许多用于读取 Excel 文件的包，如 gdata, xlsx, xlsReadWrite。
而我们要介绍的是 tidyverse 中的又一个包 readxl
与它们相比，readxl 没有任何外部依赖性，因此很容易在所有操作系统上安装和使用。它的设计是为了处理表格数据
readxl 支持旧版的 .xls 格式和现代的基于 xml 的 .xlsx 格式
在它的底层使用 libxls C 库来支持 .xls 格式，使用 RapidXML C++ 库来解析 .xlsx。
安装
最简单的就是直接安装 tidyverse
install.packages("tidyverse")
如果你只想安装 readxl
install.packages("readxl")
或者从 GitHub 上安装开发者版本
# install.packages("devtools")
devtools::install_github("tidyverse/readxl")
使用
1. 导入
readxl 不是 tidyverse 的核心包，需要显式导入
library(readxl)
2. 读取表
readxl 中包含一些示例文件，我们可以使用不带参数的 readxl_example() 来列出它们，或者传入示例文件名来获取文件的路径
> readxl_example()
[1] "clippy.xls" "clippy.xlsx" "datasets.xls" "datasets.xlsx" "deaths.xls" "deaths.xlsx"
[7] "geometry.xls" "geometry.xlsx" "type-me.xls" "type-me.xlsx"
> readxl_example("clippy.xls")
[1] "/Library/Frameworks/R.framework/Versions/3.6/Resources/library/readxl/extdata/clippy.xls"
然后使用 read_excel() 读取 xls 和 xlsx 文件，会自动从扩展名中检测文件格式
# 读取 xlsx 文件
> readxl_example("datasets.xlsx") %>% read_excel()
# A tibble: 150 x 5
Sepal.Length Sepal.Width Petal.Length Petal.Width Species
<dbl> <dbl> <dbl> <dbl> <chr>
1 5.1 3.5 1.4 0.2 setosa
2 4.9 3 1.4 0.2 setosa
3 4.7 3.2 1.3 0.2 setosa
4 4.6 3.1 1.5 0.2 setosa
5 5 3.6 1.4 0.2 setosa
6 5.4 3.9 1.7 0.4 setosa
7 4.6 3.4 1.4 0.3 setosa
8 5 3.4 1.5 0.2 setosa
9 4.4 2.9 1.4 0.2 setosa
10 4.9 3.1 1.5 0.1 setosa
# … with 140 more rows
# 读取 xls 文件
> readxl_example("datasets.xls") %>% read_excel()
# A tibble: 150 x 5
Sepal.Length Sepal.Width Petal.Length Petal.Width Species
<dbl> <dbl> <dbl> <dbl> <chr>
1 5.1 3.5 1.4 0.2 setosa
2 4.9 3 1.4 0.2 setosa
3 4.7 3.2 1.3 0.2 setosa
4 4.6 3.1 1.5 0.2 setosa
5 5 3.6 1.4 0.2 setosa
6 5.4 3.9 1.7 0.4 setosa
7 4.6 3.4 1.4 0.3 setosa
8 5 3.4 1.5 0.2 setosa
9 4.4 2.9 1.4 0.2 setosa
10 4.9 3.1 1.5 0.1 setosa
# … with 140 more rows
read_excel 默认会把第一个表读取进来，但是一个 Excel 文件可以包含多个表名，那么该如何读取其他表呢？
我们可以通过设置 sheet 参数来指定需要从 Excel 文件中读取的表名。例如 datasets.xlsx 文件中有一个名为 mtcars 的表
> readxl_example("datasets.xlsx") %>% read_excel(sheet = 'mtcars')
# A tibble: 32 x 11
mpg cyl disp hp drat wt qsec vs am gear carb
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 21 6 160 110 3.9 2.62 16.5 0 1 4 4
2 21 6 160 110 3.9 2.88 17.0 0 1 4 4
3 22.8 4 108 93 3.85 2.32 18.6 1 1 4 1
4 21.4 6 258 110 3.08 3.22 19.4 1 0 3 1
5 18.7 8 360 175 3.15 3.44 17.0 0 0 3 2
6 18.1 6 225 105 2.76 3.46 20.2 1 0 3 1
7 14.3 8 360 245 3.21 3.57 15.8 0 0 3 4
8 24.4 4 147. 62 3.69 3.19 20 1 0 4 2
9 22.8 4 141. 95 3.92 3.15 22.9 1 0 4 2
10 19.2 6 168. 123 3.92 3.44 18.3 1 0 4 4
# … with 22 more rows
那我是不是只能通过打开 Excel 文件来获知所有表名呢？
readxl 已经帮我们做到了，它提供了 excel_sheets() 函数，能够获取所有的表名
> readxl_example("datasets.xlsx") %>% excel_sheets()
[1] "iris" "mtcars" "chickwts" "quakes"
既然获取到了所有的表名，那我能不能通过传入表名的索引来获取对应表的数据呢？
> readxl_example("datasets.xlsx") %>% read_excel(sheet = 2)
# A tibble: 32 x 11
mpg cyl disp hp drat wt qsec vs am gear carb
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 21 6 160 110 3.9 2.62 16.5 0 1 4 4
2 21 6 160 110 3.9 2.88 17.0 0 1 4 4
3 22.8 4 108 93 3.85 2.32 18.6 1 1 4 1
4 21.4 6 258 110 3.08 3.22 19.4 1 0 3 1
5 18.7 8 360 175 3.15 3.44 17.0 0 0 3 2
6 18.1 6 225 105 2.76 3.46 20.2 1 0 3 1
7 14.3 8 360 245 3.21 3.57 15.8 0 0 3 4
8 24.4 4 147. 62 3.69 3.19 20 1 0 4 2
9 22.8 4 141. 95 3.92 3.15 22.9 1 0 4 2
10 19.2 6 168. 123 3.92 3.44 18.3 1 0 4 4
# … with 22 more rows
完全没问题。
3. 设置读取范围
有时候我们的 Excel 表格并不是完完全全矩阵格式，或者同一个表内包含了许多的表。
那么通过设置读取范围，能够准确快速的获取到我们想要的信息
- 用
n_max参数设置读取的行数
> readxl_example("datasets.xls") %>% read_excel(n_max = 3)
# A tibble: 3 x 5
Sepal.Length Sepal.Width Petal.Length Petal.Width Species
<dbl> <dbl> <dbl> <dbl> <chr>
1 5.1 3.5 1.4 0.2 setosa
2 4.9 3 1.4 0.2 setosa
3 4.7 3.2 1.3 0.2 setosa
- 用
range参数设置矩形的范围
> readxl_example("datasets.xls") %>% read_excel(range = "C1:E4")
# A tibble: 3 x 3
Petal.Length Petal.Width Species
<dbl> <dbl> <chr>
1 1.4 0.2 setosa
2 1.4 0.2 setosa
3 1.3 0.2 setosa
注意：我们指定了 range 为 C1-E4 的矩形范围，其中 E4 并不包含在范围之内
也可以为 range 指定行或列的读取范围
> readxl_example("datasets.xls") %>% read_excel(range = cell_rows(1:4))
# A tibble: 3 x 5
Sepal.Length Sepal.Width Petal.Length Petal.Width Species
<dbl> <dbl> <dbl> <dbl> <chr>
1 5.1 3.5 1.4 0.2 setosa
2 4.9 3 1.4 0.2 setosa
3 4.7 3.2 1.3 0.2 setosa
> readxl_example("datasets.xls") %>% read_excel(range = cell_cols('B:D'))
# A tibble: 150 x 3
Sepal.Width Petal.Length Petal.Width
<dbl> <dbl> <dbl>
1 3.5 1.4 0.2
2 3 1.4 0.2
3 3.2 1.3 0.2
4 3.1 1.5 0.2
5 3.6 1.4 0.2
6 3.9 1.7 0.4
7 3.4 1.4 0.3
8 3.4 1.5 0.2
9 2.9 1.4 0.2
10 3.1 1.5 0.1
# … with 140 more rows
> readxl_example("datasets.xls") %>% read_excel(range = cell_cols(2:4))
# A tibble: 150 x 3
Sepal.Width Petal.Length Petal.Width
<dbl> <dbl> <dbl>
1 3.5 1.4 0.2
2 3 1.4 0.2
3 3.2 1.3 0.2
4 3.1 1.5 0.2
5 3.6 1.4 0.2
6 3.9 1.7 0.4
7 3.4 1.4 0.3
8 3.4 1.5 0.2
9 2.9 1.4 0.2
10 3.1 1.5 0.1
# … with 140 more rows
为 range 指定表格以及范围
> readxl_example("datasets.xls") %>% read_excel(range = "mtcars!B1:D5")
# A tibble: 4 x 3
cyl disp hp
<dbl> <dbl> <dbl>
1 6 160 110
2 6 160 110
3 4 108 93
4 6 258 110
注意：请注意范围选取的开区间闭区间
- 用
na参数将空白之外的字符设置为NA
> readxl_example("datasets.xls") %>% read_excel(na = "setosa")
# A tibble: 150 x 5
Sepal.Length Sepal.Width Petal.Length Petal.Width Species
<dbl> <dbl> <dbl> <dbl> <chr>
1 5.1 3.5 1.4 0.2 NA
2 4.9 3 1.4 0.2 NA
3 4.7 3.2 1.3 0.2 NA
4 4.6 3.1 1.5 0.2 NA
5 5 3.6 1.4 0.2 NA
6 5.4 3.9 1.7 0.4 NA
7 4.6 3.4 1.4 0.3 NA
8 5 3.4 1.5 0.2 NA
9 4.4 2.9 1.4 0.2 NA
10 4.9 3.1 1.5 0.1 NA
# … with 140 more rows
- 用
skip参数设置在读取之前需要跳过的行数，如果设置了range，则不会发挥作用
> readxl_example("datasets.xls") %>% read_excel(skip = 1, sheet = "chickwts") %>% head(3)
# A tibble: 3 x 2
`179` horsebean
<dbl> <chr>
1 160 horsebean
2 136 horsebean
3 227 horsebean
4. 设置表名
read_excel 默认将第一行设置为表头，即 col_names=TRUE。如果 col_names=FALSE 则不会将第一行设置为表头。
> readxl_example("datasets.xls") %>% read_excel(skip = 1, sheet = "chickwts", col_names = FALSE)
New names:
* `` -> ...1
* `` -> ...2
# A tibble: 71 x 2
...1 ...2
<dbl> <chr>
1 179 horsebean
2 160 horsebean
3 136 horsebean
4 227 horsebean
5 217 horsebean
6 168 horsebean
7 108 horsebean
8 124 horsebean
9 143 horsebean
10 140 horsebean
# … with 61 more rows
同时 col_names 参数也可以接受一个字符串向量，代表我们需要设置的列名
> readxl_example("datasets.xls") %>% read_excel(skip = 1,
sheet = "chickwts",
col_names = c("chick_weight", "chick_ate_this"))
# A tibble: 71 x 2
chick_weight chick_ate_this
<dbl> <chr>
1 179 horsebean
2 160 horsebean
3 136 horsebean
4 227 horsebean
5 217 horsebean
6 168 horsebean
7 108 horsebean
8 124 horsebean
9 143 horsebean
10 140 horsebean
# … with 61 more rows
但是总是这样手动设置列名是很麻烦的，特别是一些不符合 R 变量语法的列名
readxl 提供 .name_repair 参数，该参数可以控制如何检查或修复列名
该参数在 read_excel(), read_xls() 和 read_xlsx() 中的作用与 tibble::tibble() 和 tibble::as_tibble() 中一样
默认情况下 .name_repair = "unique"，即只要保证每列的列名唯一即可，而不做其他检查
如果设置 .name_repair = "universal"，会将列名设置为符合语法规则的名称，确保它们不包含任何禁止使用的字符或保留字
设置为 unique 时，列名可以包含空格，如果为 universal 会用 . 替换空格
> readxl_example("deaths.xlsx") %>%
+ read_excel(range = "arts!A5:F8")
# A tibble: 3 x 6
Name Profession Age `Has kids` `Date of birth` `Date of death`
<chr> <chr> <dbl> <lgl> <dttm> <dttm>
1 David Bowie musician 69 TRUE 1947-01-08 00:00:00 2016-01-10 00:00:00
2 Carrie Fisher actor 60 TRUE 1956-10-21 00:00:00 2016-12-27 00:00:00
3 Chuck Berry musician 90 TRUE 1926-10-18 00:00:00 2017-03-18 00:00:00
# 用 . 替换空格
> readxl_example("deaths.xlsx") %>%
+ read_excel(range = "arts!A5:F8", .name_repair = "universal")
New names:
* `Has kids` -> Has.kids
* `Date of birth` -> Date.of.birth
* `Date of death` -> Date.of.death
# A tibble: 3 x 6
Name Profession Age Has.kids Date.of.birth Date.of.death
<chr> <chr> <dbl> <lgl> <dttm> <dttm>
1 David Bowie musician 69 TRUE 1947-01-08 00:00:00 2016-01-10 00:00:00
2 Carrie Fisher actor 60 TRUE 1956-10-21 00:00:00 2016-12-27 00:00:00
3 Chuck Berry musician 90 TRUE 1926-10-18 00:00:00 2017-03-18 00:00:00
除此之外 .name_repair 参数还可以设置为函数
# 传递函数
> readxl_example("clippy.xlsx") %>%
+ read_excel(.name_repair=toupper)
# A tibble: 4 x 2
NAME VALUE
<chr> <chr>
1 Name Clippy
2 Species paperclip
3 Approx date of death 39083
4 Weight in grams 0.9
# 自定义函数
> readxl_example("datasets.xlsx") %>%
+ read_excel(n_max = 3, .name_repair = function(x) tolower(gsub("[.]", "_", x)))
# A tibble: 3 x 5
sepal_length sepal_width petal_length petal_width species
<dbl> <dbl> <dbl> <dbl> <chr>
1 5.1 3.5 1.4 0.2 setosa
2 4.9 3 1.4 0.2 setosa
3 4.7 3.2 1.3 0.2 setosa
# purrr 风格的匿名函数，只能在 purrr 环境下使用
> readxl_example("datasets.xlsx") %>%
+ read_excel(n_max = 3, sheet = 'chickwts', .name_repair = ~ substr(.x, start = 1, stop = 3))
# A tibble: 3 x 2
wei fee
<dbl> <chr>
1 179 horsebean
2 160 horsebean
3 136 horsebean
对于 purrr 风格的匿名函数，我们可以看看下面的例子会更好理解
> f <- as_function(~ .x + 1)
> f(10)
[1] 11
>
> g <- as_function(~ -1 * .)
> g(4)
[1] -4
>
> h <- as_function(~ .x - .y)
> h(6, 3)
[1] 3
