前言
我們進(jìn)行數(shù)據(jù)處理時，有時還會遇到一種常見的文件：Microsoft Office Excel 工作表的格式。
在 R 中有許多用于讀取 Excel 文件的包，如 gdata, xlsx, xlsReadWrite。
而我們要介紹的是 tidyverse 中的又一個包 readxl
與它們相比，readxl 沒有任何外部依賴性，因此很容易在所有操作系統(tǒng)上安裝和使用。它的設(shè)計是為了處理表格數(shù)據(jù)
readxl 支持舊版的 .xls 格式和現(xiàn)代的基于 xml 的 .xlsx 格式
在它的底層使用 libxls C 庫來支持 .xls 格式，使用 RapidXML C++ 庫來解析 .xlsx。
安裝
最簡單的就是直接安裝 tidyverse
install.packages("tidyverse")
如果你只想安裝 readxl
install.packages("readxl")
或者從 GitHub 上安裝開發(fā)者版本
# install.packages("devtools")
devtools::install_github("tidyverse/readxl")
使用
1. 導(dǎo)入
readxl 不是 tidyverse 的核心包，需要顯式導(dǎo)入
library(readxl)
2. 讀取表
readxl 中包含一些示例文件，我們可以使用不帶參數(shù)的 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 文件，會自動從擴(kuò)展名中檢測文件格式
# 讀取 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 默認(rèn)會把第一個表讀取進(jìn)來，但是一個 Excel 文件可以包含多個表名，那么該如何讀取其他表呢？
我們可以通過設(shè)置 sheet 參數(shù)來指定需要從 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 已經(jīng)幫我們做到了，它提供了 excel_sheets() 函數(shù)，能夠獲取所有的表名
> readxl_example("datasets.xlsx") %>% excel_sheets()
[1] "iris" "mtcars" "chickwts" "quakes"
既然獲取到了所有的表名，那我能不能通過傳入表名的索引來獲取對應(yīng)表的數(shù)據(jù)呢？
> 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. 設(shè)置讀取范圍
有時候我們的 Excel 表格并不是完完全全矩陣格式，或者同一個表內(nèi)包含了許多的表。
那么通過設(shè)置讀取范圍，能夠準(zhǔn)確快速的獲取到我們想要的信息
- 用
n_max參數(shù)設(shè)置讀取的行數(shù)
> 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參數(shù)設(shè)置矩形的范圍
> 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 并不包含在范圍之內(nèi)
也可以為 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
注意：請注意范圍選取的開區(qū)間閉區(qū)間
- 用
na參數(shù)將空白之外的字符設(shè)置為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參數(shù)設(shè)置在讀取之前需要跳過的行數(shù)，如果設(shè)置了range，則不會發(fā)揮作用
> 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. 設(shè)置表名
read_excel 默認(rèn)將第一行設(shè)置為表頭，即 col_names=TRUE。如果 col_names=FALSE 則不會將第一行設(shè)置為表頭。
> 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 參數(shù)也可以接受一個字符串向量，代表我們需要設(shè)置的列名
> 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
但是總是這樣手動設(shè)置列名是很麻煩的，特別是一些不符合 R 變量語法的列名
readxl 提供 .name_repair 參數(shù)，該參數(shù)可以控制如何檢查或修復(fù)列名
該參數(shù)在 read_excel(), read_xls() 和 read_xlsx() 中的作用與 tibble::tibble() 和 tibble::as_tibble() 中一樣
默認(rèn)情況下 .name_repair = "unique"，即只要保證每列的列名唯一即可，而不做其他檢查
如果設(shè)置 .name_repair = "universal"，會將列名設(shè)置為符合語法規(guī)則的名稱，確保它們不包含任何禁止使用的字符或保留字
設(shè)置為 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 參數(shù)還可以設(shè)置為函數(shù)
# 傳遞函數(shù)
> 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
# 自定義函數(shù)
> 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 風(fēng)格的匿名函數(shù)，只能在 purrr 環(huán)境下使用
> 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ēng)格的匿名函數(shù)，我們可以看看下面的例子會更好理解
> 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
