R语言学习笔记之: 论如何正确把EXCEL文件喂给R处理

 

博客总目录:http://www.cnblogs.com/weibaar/p/4507801.html

 

 ----

前言: 应用背景兼吐槽

继续延续之前每个月至少一次更新博客,归纳总结学习心得好习惯。
这次的主题是论R与excel的结合,又称 论如何正确把EXCEL文件喂给R处理
分为:
1、 xlsx包安装及注意事项
2、用vba实现xlsx批量转化csv

以及,这个的对象,针对跟我一样那些从R开始接触编程的,一直以来都是用excel做数据分析的人……编程大牛请轻拍

之所以要研究这个,是因为最近工作上接了个活,要把原来在excel端的报表迁移到R端,自动输出可视化图形,并制作PDF或PPT。
这个活可以分为四个阶段:
1)源数据整理与搭建 & 需求分析
2)依据需求,R语言数据处理及输出处理后数据+图表
3)用markdown或者其他手段自动把图表复制到报告里
4)报告使用人自己编辑整合数据。

全程要求除了数据准备不是自动化,其他都要是自动化,能省就省。。而R本身与xlsx的融合并不好。

而R读取xlsx数据,就是我遇到的第一道槛。
这个活的数据都是人工从公司网页端数据库下载后储存在xlsx里的(用sql直连数据库的权限很难开)。尝试过直接从数据库端下载csv格式,但是一来格式时有错漏,二来直接下csv格式文件大小过大(单个文件从几兆变成几十兆),所以最终还是决定以xlsx格式储存,再另作打算。

相信对于那些从excel迁移到R工作的人,也会遇到同样的问题:

一、xlsx包

首先尝试用R包解决。即xlsx包。

xlsx包在加载时容易遇到问题。基本都是由于java环境未配置好,或者环境变量引用失败。因此要首先配置java环境,加载rJava包。

百度了一下,网上已有很多解决方案。我主要是参考这个帖子,操作步骤为:

1、 安装最新版本的java。如果你用的R是64位的,请下载64位java。
下载地址: http://www.java.com/en/download/manual.jsp
要安装在 C:\Program Files\Java 下面,win8的尤其小心不要安装为C:\Program Files(x86)。可能是R在读取路径时,对x86这样的文件夹不大好识别吧,我第一次装在x86里,读取是失败的。

2、在R中加载环境,即一行代码,路径要依据你的java版本做出更改。
R

Sys.setenv(JAVA_HOME='C:\\Program Files\\Java\\jre1.8.0_45\\') 

之后再加载rjava包或者xlsx包就成功了。

xlsx包加载成功后,用read.xlsx就可以直接读取xlsx文件,还可以指定读取的行和段,以及第几个表,以及可以保存为xlsx文件,这个包还是很强大的。

但是这个方法存在两个问题:

1、不是所有的公司电脑都能自由的配置java环境。很多人的权限是受限的。而且有些公司内部应用是在java环境下配置的。就算你找了IT去安装java,但是一些内部应用可能会因为版本号兼容问题而出错,得小失大。
2、用xlsx包读取数据,在数据量比较小的时候速度还是比较快的。但是如果xlsx本身比较大,包含数据多,read.xlsx效率会很低,不如data.table包的fread读取快捷以及省内存。但fread函数不支持xlsx的读入。。。
(参见这篇帖子,里面对千万行数据,fread也只用了10秒左右,比常规的read.table或者read.csv至少省时一倍)

综上,由于java环境的复杂性与兼容度,还有xlsx包本身读取速度的限制,用xlsx包读取xlsx包的方法,更适合于:
1、个人电脑,自己想怎么玩都无所谓,或者高大上的linux, mac环境
2、数据量不会特别大,而且excel文件很干净,需要细节的操作

不凑巧的是,这个方法不适合我,于是只好另找办法了。

二、用VBA把xlsx批量转化为csv格式

在上面的尝试已经发现,xlsx本身就是这个复杂问题的最根本原因。与之相反,R对csv等文本格式支持的很好,而且有fread这个神器,要处理一定量级的数据,还是得把xlsx转化为csv格式。

以此为思路,在参考了两个资料后,我成功改写了一段VBA,可以选中需要的xlsx,然后在其目录下新建csv文件夹,把xlsx批量转化为csv格式。

代码如下:

 1 Sub getCSV()
 2 '这是网上看到的xlsx批量转化,而改写的一个xlsx批量转化csv格式
 3 '1)批量转化csv参考:http://club.excelhome.net/thread-1036776-2-1.html
 4 '2)创建文件夹参考:http://jingyan.baidu.com/article/f54ae2fcdc79bc1e92b8491f.html
 5 '这里设置屏幕不动,警告忽略
 6 Application.DisplayAlerts = False
 7 Application.ScreenUpdating = False
 8 Dim data As Workbook
 9 '这里用GetOpenFilename弹出一个多选窗口,选中我们要转化成csv的xlsx文件,
10 file = Application.GetOpenFilename(MultiSelect:=True)
11 '用LBound和UBound
12 For i = LBound(file) To UBound(file)
13     Workbooks.Open Filename:=file(i)
14     Set data = ActiveWorkbook
15     Path = data.Path
16     '这里设置要保存在目录下面的csv文件夹里,之后可以自己调
17     '参考了里面的第一种方法
18     On Error Resume Next
19     VBA.MkDir (Path & "\csv")
20     With data
21         .SaveAs Path & "\csv\" & Replace(data.Name, ".xlsx", ".csv"), xlCSV
22         .Close True
23       End With
24 Next i
25 '弹出对话框表示转化已完成,这时去相应地方的csv里查看即可
26 MsgBox "已转换了" & (i-1) & "个文档"
27 Application.ScreenUpdating = True
28 Application.DisplayAlerts = True
29 End Sub

操作很简单:

把代码复制进excel的vba编辑器里,然后运行getcsv这个宏,会跳出一个窗口,要求选择你要转化的xlsx文件。(可多选)

选中以后,等一段时间,再回到xlsx文件下,会多一个csv文件夹,里面就是我们要导入R的文本文件了。

这个方法的好处是:

1、操作简单,直接依托于excel的VBA操作,不用配置java环境,之后沟通成本/换电脑成本小
2、特别适用于有一定数据量,但是数据格式整齐的文件,譬如从某数据端读入的数据。用fread还可以控制读取的行(skip=NNN),代码写入整洁方便。就算有一些异行数据,也可以事先用VBA进行操作,简单方便。

综上,我最后用的是第二种方法。
不过第一种方法,如果有java环境还是尽量配置比较好。因为有些有名的包都要依托于这个java环境。最典型的就是Rwordseg包(中文分词)

补充资料:

话说,当我最开始接触这个任务的时候,我真的没想到最后我是用VBA解决的问题。也有很多人会用python解决类似问题吧,可惜我现在暂时还抽不出空来学python。

不过黑猫白猫抓到老鼠就是好猫……继学会R以后,我又被迫学会了改VBA代码,心塞塞

关于VBA,有一些参考资料如:

1) VBA对文件夹的操作:

Excel VBA - 遍历某个文件夹中文件、文件夹及批量建立txt
这篇博文里介绍了很多用VBA实现建文件夹、改名等的应用。虽然个人觉得用R做dir()或者list.files更加直观一些,不过可以参考。
如何在Excel中用VBA创建文件夹?
两种创建文件夹的方法,我直接用了第一种。。。

2)VBA合并工作表,生成目录:
excelhome介绍的教程
#一宏微教程#第008~010期——Excel文件里工作表太多怎么办?

另外在文件夹层面,用R进行文件操作,可查看以下教程:
用R进行文件系统管理

 
-------------------------
坚持每个月写一篇学习总结,主要跟R相关,欢迎吐槽交流。我的博客: http://www.cnblogs.com/weibaar
posted @ 2015-05-16 10:47  尾巴AR  阅读(7553)  评论(2编辑  收藏