tidyxl将excel文件转换为机器可读的csv文件
笔者邀请您,先思考:
1 如何做数据转换,例如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/
内容推荐
数据人网:数据人学习,交流和分享的平台,诚邀您创造和分享数据知识,共建和共享数据智库。
请关注“恒诺新知”微信公众号,感谢“R语言“,”数据那些事儿“,”老俊俊的生信笔记“,”冷🈚️思“,“珞珈R”,“生信星球”的支持!