2.3 数据导入
数据分析师可能经常会遇到来自不同数据源和数据格式的数据。例如,csv/txt的文本文件数据、存储在数据库中的销售数据,或者需要从网络上爬取数据来丰富你的数据源、从Hive中直接读取数据等。下面我们来学习如何将不同数据源的数据导入R工具中。
2.3.1 利用RStudio导入
R暂时没有很好用的可视化数据导入工具,所以需要使用命令来导入/导出数据。但可以使用Rstudio编辑器的简单数据导入功能,如图2-11所示。
图2-11 利用RStudio导入csv/txt及网络数据
假如在C:\Users\Think\Documents文件夹下有一个文件:iris.csv。在RStudio右上角窗口的Import Dataset下拉列表中选择From Local Files,选中iris.csv文件后单击打开,得到如图2-12所示的窗口。
图2-12 利用RStudio导入csv/txt及网络数据
窗口左侧的Name表示导入数据时要保存的数据对象名称,RStuido会默认获取与导入文件名称相同的对象名称,当然,也可以手动修改成自己需要的名称。其他选项包括编码类型、是否需要标题、分隔符、缺失值的处理、字符串是否转换成因子等参数设置。窗口右上角是导入的数据源预览,右下角是数据导入R中的预览。参数调整完成后,单击Import按钮将数据导入R中。
Justin Rao的网站上有份从2002年到2008年间的NBA工资数据:http://www.justinmrao.com/salary_data.csv。可以在RStudio右上角窗口的Import Dataset下拉列表中选择From Web URL,在打开的窗口中输入以上的网址后单击OK按钮,即可完成网络数据的下载,如图2-13所示。
图2-13 输入网络地址
数据下载完成会出现与从本地导入csv文件相同的窗口,单击Import按钮,即可把数据导入R工具中,如图2-14所示。
图2-14 数据设置窗口
2.3.2 文本文件的导入
有众多的格式和文本文件标准可用于存储数据。用于存储数据的通用格式为分隔符值(即CSV或制表符分隔文件)、可扩展标记语言(XML)、JavaScript对象表示法(JSON),其中,最常用于存储数据的通用格式为分隔符值(即CSV或制表符分隔文件)。
假如当前目录下有两个文件:iris.txt和iris.csv。可以利用read.table函数将这两份数据读入R工具中。
> import.txt <- read.table("iris.txt",header = TRUE) # 读入iris.txt文件 > head(import.txt) Sepal.Length Sepal.Width Petal.Length Petal.Width Species 1 5.1 3.5 1.4 0.2 setosa 2 4.9 3.0 1.4 0.2 setosa 3 4.7 3.2 1.3 0.2 setosa 4 4.6 3.1 1.5 0.2 setosa 5 5.0 3.6 1.4 0.2 setosa 6 5.4 3.9 1.7 0.4 setosa
read.table函数的第一个参数file是导入目录中的数据,如果数据不在当前目录中,则需要增加完整路径;参数header用来设置导入的数据是否有变量名称,默认是FALSE;参数sep默认以一个或多个空格、制表符、换行或回车为字段分隔符,因为csv文件以逗号作为字段分隔符,故如果导入csv文件,需要将参数sep设置为”,”。
> import.csv <- read.table("iris.csv", sep = ",") #读入iris.csv文件 > head(import.csv) Sepal.Length Sepal.Width Petal.Length Petal.Width Species 1 5.1 3.5 1.4 0.2 setosa 2 4.9 3.0 1.4 0.2 setosa 3 4.7 3.2 1.3 0.2 setosa 4 4.6 3.1 1.5 0.2 setosa 5 5.0 3.6 1.4 0.2 setosa 6 5.4 3.9 1.7 0.4 setosa
有几个read.table的包装函数使用起来比较方便。read.csv函数默认将分隔符设置为逗号,并假设数据有标题行。
> import.csv1 <- read.csv("iris.csv") # 利用read.csv将iris.csv文件读入 > head(import.csv1) Sepal.Length Sepal.Width Petal.Length Petal.Width Species 1 5.1 3.5 1.4 0.2 setosa 2 4.9 3.0 1.4 0.2 setosa 3 4.7 3.2 1.3 0.2 setosa 4 4.6 3.1 1.5 0.2 setosa 5 5.0 3.6 1.4 0.2 setosa 6 5.4 3.9 1.7 0.4 setosa
不是所有的文本文件都像定界符文件那样有一个定义良好的结构。如果文件的结构松散,更简单的做法是:先读入文件中的所有文本行,再对其内容进行文本分词及挖掘。readLines(注意两个单词间没有点连接,且第二个单词的首字母是大写字母L)就提供了这种方法。它接受一个文件路径(或文件连接)和一个可选的最大行数作为参数来读取文件。
> unstructuredText <- readLines("unstructuredText.txt") > unstructuredText [1] "R语言是一套开源的数据分析解决方案,几乎可以独立完成数据处理、数据可视化、数据建模及模型评估等工作,而且可以完美配合其他工具进行数据交互。具体来说,R语言具有以下优势:" [2] "1)高效的数据处理能力" [3] "2)数据分析" [4] "3)数据可视化" [5] "4)通过庞大的R程序包库文件进行扩展"
2.3.3 Excel文件的导入
读取一个Excel文件的最好方式,就是在Excel中将其导出为一个逗号分隔值文件(csv),并使用read.csv()的方式将其导入R中。R中也有好几个包可以直接将Excel文件导入R中,如RODBC包中的odbcConnectExcel2007函数、xlsx包中的read.xlsx函数、XLConnect包中的loadworkbook和readWorksheet函数、readxl包中的read_excel函数。
假如有一个sample.xlsx文件,利用4种方式将其读入R中。
> # 利用RODBC包读入 > library(RODBC) > channel <- odbcConnectExcel2007("sample.xlsx") # 建立连接 > odbcdf <- sqlFetch(channel,'data') # 读取工作表data的数据 > odbcClose(channel) # 关闭连接 > odbcdf总序号性别年龄职业 1 1 1 5 4 2 2 2 2 1 3 3 2 1 1 4 4 1 2 1 5 5 1 3 5 > # 利用xlsx包读取EXcel数据 > library(xlsx)载入需要的程辑包:rJava载入需要的程辑包:xlsxjars > res <- read.xlsx('sample.xlsx',1 , encoding="UTF-8") # 利用read.xlsx函数读取Excel文件 > res总序号性别年龄职业 1 1 1 5 4 2 2 2 2 1 3 3 2 1 1 4 4 1 2 1 5 5 1 3 5 > detach(package:xlsx) > # 利用XLConnect包读取Excel数据 > library(XLConnect) > wb <- loadWorkbook("sample.xlsx") # 将工作簿加载到R中 > xldf<-readWorksheet(wb,sheet=getSheets(wb)[1]) # 读取第一个工作表的数据 > xldf总序号性别年龄职业 1 1 1 5 4 2 2 2 2 1 3 3 2 1 1 4 4 1 2 1 5 5 1 3 5 > # 利用readxl包读取Excel数据 > library(readxl) > readexcel <- read_excel("sample.xlsx",1,col_names = T) > readexcel # A tibble: 5 × 4总序号性别年龄职业 <dbl><dbl><dbl><dbl> 1 1 1 5 4 2 2 2 2 1 3 3 2 1 1 4 4 1 2 1 5 5 1 3 5
2.3.4 数据库文件的导入
在R中通过RODBC包访问一个数据库也许是最流行的方式。这种方式允许R连接到任意一种拥有ODBC驱动的数据库,其实几乎就是市面上的所有数据库。
现在,尝试用RODBC连接生产环境中的MySQL数据库。由于服务器上的MySQL是32位,计算机系统是64位,所以需要在C:\Windows\SysWOW64文件夹下找到odbcad32.exe,双击打开ODBC数据源管理器界面,如图2-15所示。
图2-15 ODBC数据源管理器界面
单击“添加”按钮,选择MySQL ODBC驱动,完成之后会弹出一个数据库配置的对话框,如图2-16所示。
图2-16 填写ODBC信息
填写完数据库信息,单击Test按钮测试连接成功,在64位的Windows下配置好32位的MySQL ODBC,如图2-17所示。
图2-17 ODBC测试成功
在32位的R中利用install.packages("RODBC")命令安装RODBC包。包下载安装好后,可以利用包中的odbcConnect(dsn,uid="",pwd="",...)函数连接数据库,并继续数据的传输及分析工作。
> library(RODBC) > channel <- odbcConnect("daniel","root","123456") # 建立连接 > odbcGetInfo(channel) # 显示数据库信息 DBMS_Name DBMS_Ver Driver_ODBC_Ver "MySQL" "5.5.28" "03.80" Data_Source_Name Driver_Name Driver_Ver "daniel" "myodbc5a.dll" "05.03.0006" ODBC_Ver Server_Name "03.80.0000" "localhost via TCP/IP"
可以使用sqlSave(channel,dat,tablename=NULL,append=FALSE)命令将R中的数据框写入或更新(append=TRUE)到MySQL数据库的某个表中。比如想把R自带的mtcars数据写入MySQL中,在数据库中生成新表mydata。
# 将mtcars数据集写入MySQL中 > sqlSave(channel,mtcars,"mydata",append = FALSE)
在MySQL中查询刚生成的新表mydata,结果如图2-18所示。
图2-18 将R中的数据框写入MySQL中
可以利用sqlFetch(channel,sqtable,...,colnames=FALSE,rownames=TRUE)命令将MySQL数据库中的mydata表读取到一个数据框中。
> mydata <- sqlFetch(channel,"mydata") > str(mydata) 'data.frame': 32 obs. of 11 variables: $ mpg : num 21 21 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 ... $ cyl : num 6 6 4 6 8 6 8 4 4 6 ... $ disp: num 160 160 108 258 360 ... $ hp : num 110 110 93 110 175 105 245 62 95 123 ... $ drat: num 3.9 3.9 3.85 3.08 3.15 2.76 3.21 3.69 3.92 3.92 ... $ wt : num 2.62 2.88 2.32 3.21 3.44 ... $ qsec: num 16.5 17 18.6 19.4 17 ... $ vs : num 0 0 1 1 0 1 0 1 1 1 ... $ am : num 1 1 1 0 0 0 0 0 0 0 ... $ gear: num 4 4 4 3 3 3 3 4 4 4 ... $ carb: num 4 4 1 1 2 1 4 2 2 4 ...
可以使用sqlQuery(channel,query,errors=TRUE,...,rows_at_time)命令向MySQL数据库提交一个查询并返回结果。比如想对mydata表按照vs和am统计分组,并统计mpg的平均组,执行以下代码可以完成该操作。
> rm(list=ls()) > ls() character(0) > result <- sqlQuery(channel,"select vs,am,avg(mpg) from mydata group by vs,am") > result vs am avg(mpg) 1 0 0 15.05000 2 0 1 19.75000 3 1 0 20.74286 4 1 1 28.37143
利用sqlDrop(channel,sqtable,errors=TRUE)命令可以直接删除数据库中的某个表。比如删除mydata表,在R中执行以下命令后得到的结果如图2-19所示。
> sqlDrop(channel,"mydata") # 删除数据库中的mydata表 > odbcClose(channel) # 关闭连接
图2-19 MySQL中已不存在mydata表
2.3.5 网络数据的爬取
在网络数据爬取的过程中,用户从互联网上提取嵌入在网页中的信息,并将其保存为R中的数据结构进一步分析。因为R有内置的Web服务器,所以某些读取数据的函数默认带有网络访问功能。例如,read.table(或read.csv)可以接受一个URL作为参数。
以Justin Rao的网站上从2002年到2008年间的NBA工资数据为例(http://www.justin-mrao.com/salary_data.csv)进行演示。
> salary_data <- read.csv("http://www.justinmrao.com/salary_data.csv") > head(salary_data) team year player contract_years_remaining 1 Boston Celtics2002-03Bremer, J.R. 1 2 Cleveland Cavaliers 2003-04 Bremer, J.R. 1 3 Charlotte Hornets 2001-02 Brown, P.J. 7 4 New Orleans Hornets2002-03 Brown, P.J. 7 5 New Orleans Hornets 2003-04 Brown, P.J. 4 6 New Orleans Hornets 2004-05 Brown, P.J. 4 contract_thru position full_name salary_year salary_total year_counter obs 1 2002-03 G Bremer 349458 349458 1 2 2 2003-04 G Bremer 563679 563679 2 2 3 2002-03 F Brown 6404800 36000000 1 6 4 2002-03 F Brown 7044800 36000000 2 6 5 2006-07 F Brown 8000000 34000000 3 6 6 2006-07 F Brown 8000000 34000000 4 6 mean_salary mean_remaining 1 456568.5 1 2 456568.5 1 3 7668267.0 5 4 7668267.0 5 5 7668267.0 5 6 7668267.0 5
完成这个任务的另一种途径是使用函数readLines下载网页,然后使用正则表达式对有用的数据进行提取及分析。例如,想爬取一个在线教育网站的所有在线课程(共8页)的课程名称、课时数、学生人数、授课老师、课程价格等信息。网页及源代码如图2-20所示。
图2-20 网站页面及网页源代码
解析网页源代码,利用正则表达式提取相关数据。
> # 方法一利用readLines函数和正则表达式提取网页数据 > # 爬取全部网页 > web <- NULL > for(i in 1:8){ + url <- paste0("https://edu.hellobi.com/course/explore?page=",i) + web1 <- readLines(url,encoding = 'UTF-8') + web <- c(web1,web) + } > # 提取课程名称所在的行 > class <- web[grep("class=\"caption\"",web)+3] > # 删除多余的空格 > class <- gsub(" ","",class) > # 提取课时所在的行 > length <- web[grep("class=\"length\"",web)] > # 利用正则表达式提取课时数 > length <- substr(length,regexpr("i>",length)+2,regexpr("课",length)-1) > # 提取学生人数 > people <- web[grep("class=\"pull-right people\"",web)] > people <- substr(people,regexpr(">",people)+1,regexpr("人",people)-1) > # 提取授课老师 > teacher <- web[grep("class=\"teacher\"",web)] > for(i in 1:length(teacher)){ + teacher[i] <- +substr(teacher[i],gregexpr(">",teacher[i])[[1]][2]+1,gregexpr("<",teacher[i])[[1]][3]-1) + } > # 提取课程价格 > price <- web[grep("class=\"teacher\"",web)+1] > price <- substr(price,regexpr(">",price)+1,regexpr("/",price)-2) > # 将结果整理成data.frame形式 > result <- data.frame(课程=class,课时数=length,学生人数=people, + 授课老师=teacher,课程价格=price) > head(result) 课程 课时数 学生人数 授课老师 课程价格 1 SSRS2012WIN8Metro高端报表教程 13 1015 IWORK 免费 2 OBIEE深入浅出精品视频教程 61 150 冰咖啡 1500元 3 问答社区微软BI问题及性能优化工具合集 10 465 梁勇 免费 4 SSRS2012MetroUI高端报表视频教程 57 159 BIWORK 1800元 5 天善问答OracleBIEE常见问题视频教程 3 654 冰咖啡 免费 6 天善内部精品Cognos教程 8 1041 曾力 免费
也可以用rvest包快速实现以上的数据爬取工作。代码如下。
> #### 利用rvest包爬取网页数据 > library(rvest) > library(magrittr) > result <- data.frame(课程=1,课时数=1,学生人数=1,授课老师=1,课程价格=1) > result <- result[-1,] > for(i in 1:7){ + url <- paste0("https://edu.hellobi.com/course/explore?page=",i) + web <- read_html(url,encoding = 'UTF-8') + class <- web %>% html_nodes("div.course-box") %>% + html_nodes("img") # 提取课程名称 + class <- substr(class,regexpr("alt=",class)+5,regexpr(">",class)-3) + length <- web %>% html_nodes("div.meta") %>% html_nodes("span.length") %>% + html_text() # 提取课时数 + people <- web %>% html_nodes("div.meta") %>% html_nodes("span.people") %>% + html_text() # 提取学习人数 + teacher <- web %>% html_nodes("div.meta") %>% html_nodes("span.teacher") %>% + html_text() # 提取老师 + price <- web %>% html_nodes("div.meta") %>% html_nodes("span.price") %>% + html_text() # 提取价格 + result1 <- data.frame(课程=class,课时数=length,学生人数=people, + 授课老师=teacher,课程价格=price) + result <- data.frame(rbind(result,result1)) + } > head(result) 课程 课时数 学生人数 授课老师 课程价格 1 机器学习技术在Python语言的商业应用录播 4课时 94人学习 丘祐玮 免费 2 Python机器学习kaggle案例 6课时 166人学习 唐宇迪 免费 3 对话大数据系列技术从破冰到精进 41课时10人学习 MarsJ 499元 4 需求链驱动数据化的零售生意录播 2课时 57人学习 dxcking 免费 5 基本统计方法及其在R中的实现 5课时 129人学习 黄小明 免费 6 用数据说话-Excel BI商业智能分析零基础精讲课程5课时 9人学习 李奇 499元
R中也有若干用于爬取网络数据的包,如quantmod、XML、RCurl等,可以爬取各种复杂的网络数据。其中quantmod包是R平台用于金融建模的扩展包。主要功能有:从多个数据源获取历史数据、绘制金融数据图表、在金融数据图表中添加技术指标、计算不同时间尺度的收益率、金融时间序列分析、金融模型拟合与计算,等等。
例如,从雅虎爬取创梦天地(股票代码:DSKY)上市至今的股价数据。利用get-Symbols函数实现。
> library(quantmod) > getSymbols("DSKY",scr="yahoo") > # 查看最后六天的股票记录 > tail(DSKY) DSKY.Open DSKY.High DSKY.Low DSKY.Close DSKY.Volume DSKY.Adjusted 2016-05-17 13.53 13.71 13.48 13.55 94000 13.55 2016-05-18 13.44 13.66 13.44 13.66 86400 13.66 2016-05-19 13.61 13.67 13.55 13.58 59400 13.58 2016-05-20 13.58 13.70 13.58 13.69 62400 13.69 2016-05-23 13.67 13.75 13.63 13.70 71200 13.70 2016-05-24 13.67 13.74 13.63 13.67 29900 13.67
getSymbols函数把股票每天的开盘价格、最高价格、最低价格、收盘价格、成交量和调整价格都爬取到R中。可以利用candleChart函数绘制蜡烛图,如图2-21所示。
candleChart(DSKY,theme="white") #蜡烛图
图2-21 绘制蜡烛图