• 主页
  • 课程

    关于课程

    • 课程归档
    • 成为一名讲师
    • 讲师信息
    同等学历教学

    同等学历教学

    免费
    阅读更多
  • 特色
    • 展示
    • 关于我们
    • 问答
  • 事件
  • 个性化
  • 博客
  • 联系
  • 站点资源
    有任何问题吗?
    (00) 123 456 789
    weinfoadmin@weinformatics.cn
    注册登录
    恒诺新知
    • 主页
    • 课程

      关于课程

      • 课程归档
      • 成为一名讲师
      • 讲师信息
      同等学历教学

      同等学历教学

      免费
      阅读更多
    • 特色
      • 展示
      • 关于我们
      • 问答
    • 事件
    • 个性化
    • 博客
    • 联系
    • 站点资源

      R语言

      • 首页
      • 博客
      • R语言
      • tidyxl将excel文件转换为机器可读的csv文件

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

      • 发布者 weinfoadmin
      • 分类 R语言
      • 日期 2019年1月29日
      测试开头

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


      笔者邀请您,先思考:

      1 如何做数据转换,例如Excel文件转换为csv文件?

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

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

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


      转换为这样:

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


      你可以在这里找到我使用的数据。点击“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/


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

      内容推荐


      • 如何阅读论文?

      • 论文管理工具,我用Zotero

      • 一个数据人的2018

      • R机器学习介绍第一部分

      • R机器学习介绍第二部分

      • R中你应该学习的7种可视化

      • R中用线性回归进行预测建模

      • 使用RMarkdown沟通结果

      • 使用LIME探索模型

      • RStudio1.2新功能介绍

      • 使用spotifyr聚类Springsteen专辑

      • 使用R和tidytext对Trustpilot 的评论进行主题建模

      • R语言做深度学习

      • 使用pdftools包获取pdfs的数据

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


      测试结尾

      请关注“恒诺新知”微信公众号,感谢“R语言“,”数据那些事儿“,”老俊俊的生信笔记“,”冷🈚️思“,“珞珈R”,“生信星球”的支持!

      • 分享:
      作者头像
      weinfoadmin

      上一篇文章

      R语言编程艺术 第2章 向量(下)
      2019年1月29日

      下一篇文章

      一篇零代码的富集分析流程文献(上)
      2019年1月29日

      你可能也喜欢

      3-1665801675
      R语言学习:重读《R数据科学(中文版)》书籍
      28 9月, 2022
      6-1652833487
      经典铁死亡,再出新思路
      16 5月, 2022
      1-1651501980
      R语言学习:阅读《R For Everyone 》(第二版)
      1 5月, 2022

      搜索

      分类

      • R语言
      • TCGA数据挖掘
      • 单细胞RNA-seq测序
      • 在线会议直播预告与回放
      • 数据分析那些事儿分类
      • 未分类
      • 生信星球
      • 老俊俊的生信笔记

      投稿培训

      免费

      alphafold2培训

      免费

      群晖配置培训

      免费

      最新博文

      Nature | 单细胞技术揭示衰老细胞与肌肉再生
      301月2023
      lncRNA和miRNA生信分析系列讲座免费视频课和课件资源包,干货满满
      301月2023
      如何快速批量修改 Git 提交记录中的用户信息
      261月2023
      logo-eduma-the-best-lms-wordpress-theme

      (00) 123 456 789

      weinfoadmin@weinformatics.cn

      恒诺新知

      • 关于我们
      • 博客
      • 联系
      • 成为一名讲师

      链接

      • 课程
      • 事件
      • 展示
      • 问答

      支持

      • 文档
      • 论坛
      • 语言包
      • 发行状态

      推荐

      • iHub汉语代码托管
      • iLAB耗材管理
      • WooCommerce
      • 丁香园论坛

      weinformatics 即 恒诺新知。ICP备案号:粤ICP备19129767号

      • 关于我们
      • 博客
      • 联系
      • 成为一名讲师

      要成为一名讲师吗?

      加入数以千计的演讲者获得100%课时费!

      现在开始

      用你的站点账户登录

      忘记密码?

      还不是会员? 现在注册

      注册新帐户

      已经拥有注册账户? 现在登录

      close
      会员购买 你还没有登录,请先登录
      • ¥99 VIP-1个月
      • ¥199 VIP-半年
      • ¥299 VIP-1年
      在线支付 激活码

      立即支付
      支付宝
      微信支付
      请使用 支付宝 或 微信 扫码支付
      登录
      注册|忘记密码?