tidyxl将excel文件转换为机器可读的csv文件

我不会写很长的介绍，我们都知道Excel在商业界广泛应用，它拥有大量非常好的特性，特别是对于那些不懂得编写程序的商业从业者。但是，如果Excel提供的功能不能满足个人意愿，那就会比较麻烦。通常，人们把Excel当做一个汇报的工具，虽然它实际上不是这样的工具。他们制作非常精致复杂的电子表格，以便人们阅读，却不能被其它工具导入。
在这片博客帖子中（可能是一系列的一部分），我会向你展示如何从这样：

转换为这样：

你可以在这里找到我使用的数据。点击“Time use”并下载工作本。
Excel工作本包含了几个表格（法语版以及英语版），表格的内容是卢森堡居民从周一到周日使用时间的情况。比如，职场人士在工作日平均花费八小时的睡眠时间，而在周六为八小时四十五分钟。
在你看到的电子表格中，每个sheet包含了多个表格，而每个表格又包含很多小标题，表格一个紧挨一个。尝试采用readxl::read_excel()只能产生一个奇怪的东西。
这就是{tidyxl}出现的原因。现在采用{tidyxl}导入工作本。

1library(tidyverse)
2library(tidyxl)
3
4time_use_xl <- xlsx_cells("time-use.xlsx")

看一下发生了什么：

1head(time_use_xl)

 1## # A tibble: 6 x 21
 2##   sheet address   row   col is_blank data_type error logical numeric
 3##   <chr> <chr>   <int> <int> <lgl>    <chr>     <chr> <lgl>     <dbl>
 4## 1 Index A1          1     1 FALSE    character <NA>  NA           NA
 5## 2 Index B1          1     2 TRUE     blank     <NA>  NA           NA
 6## 3 Index C1          1     3 TRUE     blank     <NA>  NA           NA
 7## 4 Index D1          1     4 TRUE     blank     <NA>  NA           NA
 8## 5 Index E1          1     5 TRUE     blank     <NA>  NA           NA
 9## 6 Index F1          1     6 TRUE     blank     <NA>  NA           NA
10## # … with 12 more variables: date <dttm>, character <chr>,
11## #   character_formatted <list>, formula <chr>, is_array <lgl>,
12## #   formula_ref <chr>, formula_group <int>, comment <chr>, height <dbl>,
13## #   width <dbl>, style_format <chr>, local_format_id <int>

正如你看到的那样，sheet被导入了，但结果并不是希望的那样。实际上，time_use_xl是一个tibble的对象，其中的每一行都是Excel表中的一个小单元。这看起来可能非常难以处理，但是你将看到它确实可以把事情简单化。
我只想使用英文版的sheets，所以我采用下面的编码忽略法语版的sheets。

1sheets <- xlsx_sheet_names("time-use.xlsx") %>%
2    keep(grepl(pattern = ".*day$", .))

同样，有一个sheet整合了工作日以及周末的结果，我也忽略了。
现在，为了提取每个sheet中的表格，我编写以下函数：

 1extract_data <- function(sheet){
 2    activities <- sheet %>%
 3        filter(col == 2) %>%
 4        select(row, character) %>%
 5        filter(row %in% seq(6,58)) %>%
 6        rename(activities = character) %>%
 7        select(-row)
 8
 9    cols_to_extract <- sheet %>% 
10        filter(grepl("Population who completed.*", character)) %>% 
11        pull(col)
12
13    headers_pos <- cols_to_extract - 1
14
15    headers <- sheet %>%
16        filter(col %in% headers_pos, row == 3) %>%
17        pull(character)
18
19    cols_to_extract %>% 
20        map(~filter(sheet, col %in% .)) %>%
21        map(~select(., sheet, address, row, col, character)) %>%
22        map(~filter(., row %in% seq(6,58))) %>%
23        map(~select(., character)) %>%
24        map2(.x = ., .y = headers, ~mutate(.x, "population" = .y)) %>%
25        map(., ~bind_cols(activities, .)) %>%
26        bind_rows()
27}

让我们一步步学习并看看它到底如何工作的。首先，有一个关键点sheet。这个函数将会与工作本中每一个sheet配对。接着，我写的第一个区间用于提取活动：

1  activities <- sheet %>%
2        filter(col == 2) %>%
3        select(row, character) %>%
4        filter(row %in% seq(6,58)) %>%
5        rename(activities = character) %>%
6        select(-row)

我只保留了第二列（filter(col == 2)）,col是tibble中的colunmn，如果你翻开工作本，你将发现活动位于第二列，或者B列。接着，我选择两列，row和character列，row不言自明，character实际上包含了任何出现在单元里面的东西。然后，我保留6到58行，因为我对它们感兴趣，其余部分要么是空的，要么不需要。最后，我把character列重新命名为activities并且删除了row的列。
第二个区域：

1    cols_to_extract <- sheet %>% 
2        filter(grepl("Population who completed.*", character)) %>% 
3        pull(col)

返回了我想要提取列的序号。我只关心完成活动的人群，因此采用filter()里面的grepl()。我放置这些列，并且使用pull()……把它们拉出数据框！cols_to_extract是我想要保留的一个完美的原子矢量列。
在第三个区间，我提取了头标签

1headers_pos <- cols_to_extract - 1

为什么-1?这是因为当你翻看Excel，你会发现头标签是在列标签“People who completed the activity”的前一列。比如G列，有“People who completed the activity”以及在F列有头标签“Male”。
现在我要提取头标签：

1    headers <- sheet %>%
2        filter(col %in% headers_pos, row == 3) %>%
3        pull(character)

头标签总是在第三行，虽然在不同列，因此采用col %in% headers_pos。接着通过pull(character)从单元里抓取数值。因此我的headers对象将会是一个原子矢量，包含“All”,“Male”，“Female”，“10 – 19 years”等，都在第三行。
最后的一个区域，提取了数据：

1    cols_to_extract %>% 
2        map(~filter(sheet, col %in% .)) %>%
3        map(~select(., sheet, address, row, col, character)) %>%
4        map(~filter(., row %in% seq(6,58))) %>%
5        map(~select(., character)) %>%
6        map2(.x = ., .y = headers, ~mutate(.x, "population" = .y)) %>%
7        map(., ~bind_cols(activities, .)) %>%
8        bind_rows()

cols_to_extract是我感兴趣的列所在位置的一个矢量。比如“4”，“7”，“10”等。我把这个矢量与sheet配对，返回了一系列数据框。接着我使用select()（什么在map()里面……为什么？因为输入的参数是一系列的数据框）。因此对于目录里面的每个数据框，我选择了sheet,address,row,col以及character列。接着采用filter()保留6到58行。然后，选择character列，它包含单元中的文本信息。随后，使用map2()，在headers对象中添加数值作为新列，命名为population。接着，将activities列添加进数据框，并把所有的行放在一起。
是时候运行该函数了！看一下结果：

1clean_data <- sheets %>%
2    map(~filter(time_use_xl, sheet %in% .)) %>%
3    set_names(sheets) %>%
4    map(extract_data) %>%
5    map2(.x = ., .y = sheets, ~mutate(.x, "day" = .y)) %>%
6    bind_rows() %>%
7    select(day, population, activities, time = character)
8
9glimpse(clean_data)

1## Observations: 2,968
2## Variables: 4
3## $ day        <chr> "Year 2014_Monday til Friday", "Year 2014_Monday til …
4## $ population <chr> "All", "All", "All", "All", "All", "All", "All", "All…
5## $ activities <chr> "Personal care", "Sleep", "Eating", "Other personal c…
6## $ time       <chr> "11:07", "08:26", "01:47", "00:56", "07:37", "07:47",…

我用tibble操作目录中的sheets，tibble是用readxl导入的，使用set_names命名我的目录（其实没必要，只是我想表示一下，可能你对此有兴趣），然后把我的函数应用到这个结果上。我可以在这里停下，但我接着增加了新的一列到每个数据框中，包含了数据被测量的日期信息，将每行组装起来，重新排序每列。完成！
现在，我怎么制作出这个函数？我没有借用一个函数开始。开始时，我写一些代码只针对sheet中每一个表格我需要的部分。只有当我写出了一些可以操作的代码，我才开始把它推广应用到多个表格，最后到多个sheets。大部分的时间都用在了尝试寻找Excel表中的模式，以便于可以写进我的函数里面（比如发现了我需要的头标签总是在感兴趣所在列的前一列）。我建议，在编写程序时，先从处理一个小问题开始，把代码包装进函数，然后把函数应用到一系列的元素中！

作者：Bruno Rodrigues
原文链接：

https://www.brodrigues.co/blog/2018-09-11-human_to_machine/

内容推荐

数据人网：数据人学习，交流和分享的平台，诚邀您创造和分享数据知识，共建和共享数据智库。