15  电子表格数据读取

15.1 读取excel表格

使用read_excel()函数，常用参数包括：

  • col_names：自定义列名。
  • skip：跳过数据第一行。通常在使用col_names改完列名后，表头行（即原列名行）成了数据的第 1 行，可使用 skip 参数跳过。
  • col_types：自定义列的数据类型。可用选项包括：“skip”、“guess”、“logical”、“numeric”、“date”、“text”、“list”。注意，在转换时注意原始数据类型，例如下面例子中，age 被读取为字符型，其应为数值型，但原始数据中有非数值条目”five”。此时的转换就需格外谨慎。。
  • na：当表中有缺失值但未被识别为NA(如果被识别则应显示<NA>)，通过此参数指定哪些字符为缺失值。
或使用janitor::clean_names()函数将列名统一整洁化。
students <- read_excel(
  "D:/Document/0.Study R/0.R4DS/data/students.xlsx",
  col_names = c(
    "student_id",
    "full_name",
    "favourite_food",
    "meal_plan",
    "age"
  ),
  skip = 1,
  na = c("", "N/A")
  # col_types = c("numeric", "text", "text", "text", "numeric")
)

# 将five改为5，再转化列类型
students <- students |>
  mutate(
    age = if_else(age == "five", "5", age),
    age = parse_number(age)
  )
students
# A tibble: 6 × 5
  student_id full_name        favourite_food     meal_plan             age
       <dbl> <chr>            <chr>              <chr>               <dbl>
1          1 Sunil Huffmann   Strawberry yoghurt Lunch only              4
2          2 Barclay Lynn     French fries       Lunch only              5
3          3 Jayendra Lyne    <NA>               Breakfast and lunch     7
4          4 Leon Rossini     Anchovies          Lunch only             NA
5          5 Chidiegwu Dunkel Pizza              Breakfast and lunch     5
6          6 Güvenç Attila    Ice cream          Lunch only              6

15.2 读取excel中的sheet表

read_excel()中的参数sheet用来指定要读取的sheet表，可以使用名称或sheet表的位置数字(如第3个就设定sheet = 3)。

read_excel(
  "D:/Document/0.Study R/0.R4DS/data/penguins.xlsx",
  sheet = "Torgersen Island",
  na = "NA") # 将字符串"NA"转换为缺失值NA
# A tibble: 52 × 8
   species island    bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
   <chr>   <chr>              <dbl>         <dbl>             <dbl>       <dbl>
 1 Adelie  Torgersen           39.1          18.7               181        3750
 2 Adelie  Torgersen           39.5          17.4               186        3800
 3 Adelie  Torgersen           40.3          18                 195        3250
 4 Adelie  Torgersen           NA            NA                  NA          NA
 5 Adelie  Torgersen           36.7          19.3               193        3450
 6 Adelie  Torgersen           39.3          20.6               190        3650
 7 Adelie  Torgersen           38.9          17.8               181        3625
 8 Adelie  Torgersen           39.2          19.6               195        4675
 9 Adelie  Torgersen           34.1          18.1               193        3475
10 Adelie  Torgersen           42            20.2               190        4250
# ℹ 42 more rows
# ℹ 2 more variables: sex <chr>, year <dbl>

如何批量读取多个excel表格或一个excel中的多个sheet表，可参看 小节 21.4.2。

15.3 读取部分区域

使用range参数指定读取范围。

# label: read_excel()-range
deaths_path <- readxl_example("deaths.xlsx")
deaths <- read_excel(deaths_path)
read_excel(deaths_path, range = "A5:F15") |> 
  janitor::clean_names()
# A tibble: 10 × 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 Fis… 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
 4 Bill Paxton actor         61 TRUE     1955-05-17 00:00:00 2017-02-25 00:00:00
 5 Prince      musician      57 TRUE     1958-06-07 00:00:00 2016-04-21 00:00:00
 6 Alan Rickm… actor         69 FALSE    1946-02-21 00:00:00 2016-01-14 00:00:00
 7 Florence H… actor         82 TRUE     1934-02-14 00:00:00 2016-11-24 00:00:00
 8 Harper Lee  author        89 FALSE    1926-04-28 00:00:00 2016-02-19 00:00:00
 9 Zsa Zsa Gá… actor         99 TRUE     1917-02-06 00:00:00 2016-12-18 00:00:00
10 George Mic… musician      53 FALSE    1963-06-25 00:00:00 2016-12-25 00:00:00

15.4 表格读取时的数据类型

CSV 中所有值为字符串，而 Excel 则较为复杂：

  • 布尔值（TRUE、FALSE、NA）
  • 数字（如 10或 10.5）
  • 日期时间（如 2021/11/1 15:00）
  • 文本（如 "ten"）

readxl包会自动猜测读取数据的类型，但面对相对复杂的情况时，猜的不对。因此在读取excel表格时推荐如下工作流：

  1. 初步读取并让 readxl 猜测；
  2. 检查类型；
  3. 若有瑕疵，重新指定 col_types。如某列类型混合（数值、文本、日期），可设为 "list"，每个单元格为一个单值向量。
提示
  • 有时数据会以更奇特的方式存储，比如单元格背景的颜色，或者文本是否加粗。在这种情况下，tidyxl 包就派上了用场。
  • 有关处理来自 Excel 的非表格数据的策略，请参阅 https://nacnudus.github.io/spreadsheet-munging-strategies/ 获取更多信息。