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

前言:

应用背景兼吐槽

继续延续之前每个月至少一次更新博客，归纳总结学习心得好习惯。 
这次的主题是论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格式。

代码如下：

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

操作很简单：

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

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

这个方法的好处是：

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

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

欢迎各位在评论里补充你们看完本章后，想到的相关问题，定期补充上去。
也欢迎扫码关注我的微信公众号，谢谢。 

微信回复关键字即可学习

回复 R              R语言快速入门免费视频 
回复 统计          统计方法及其在R中的实现
回复 用户画像   民生银行客户画像搭建与应用 
回复 大数据      大数据系列免费视频教程
回复 可视化      利用R语言做数据可视化
回复 数据挖掘   数据挖掘算法原理解释与应用
回复 机器学习   R&Python机器学习入门