注册    登录
今天看啥  ›  专栏  ›  名本无名

R 数据处理（二十一）— readxl

名本无名  · 简书  ·  · 2021-02-01 19:59

前言

我们进行数据处理时，有时还会遇到一种常见的文件： 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



原文地址：访问原文地址
快照地址： 访问文章快照
推荐文章
室内设计精品PPT模版  ·  【独家精品】大师设计一现代摩登·高雅艺术精致品质175户型样板间设计方案汇报文本PPT模板(可编辑)
11 小时前
康石石  ·  澳洲院校发起奖学金，是真舍得啊
12 小时前
TGB湖南人  ·  【6.11复盘】芯片成A股孤勇者，小盘股联合科技股能否战胜权重股杀跌呢。。。
昨天
康石石  ·  众多留学国家，性价比最高的居然是
昨天
康石石  ·  能拿下港理皇艺，是因为我“小心思”比较多：
3 天前
猫头鹰教室  ·  [12月18日 直播]SCI科研绘图实操班开课啦
2 年前
吴玫  ·  什么也没看到的善琏古镇和什么都便宜的新市古镇
3 年前
育学园  ·  该如何科学记录孩子生长？
4 年前
统计微讯  ·  【数据发布】2017年12月中国非制造业商务活动指数为55.0%
6 年前
银幕穿越者  ·  《寻梦环游记》：穿越生死的爱与亡灵乌托邦
6 年前
关于   移动版   ·   Py中国   ·   RSS之家   ·   codingpro   ·   Code   ·   link之家   ·   卧龙AI搜索   ·   藏经阁   ·   小百科
今天看啥 - 微信公众号rss订阅, 微信rss, 稳定的RSS源
© 2024 ~ 沪ICP备11025650号