你好,游客 登录 注册 搜索
背景:
阅读新闻

【译文】利用R语言处理数据库中的数据

[日期:2016-04-17] 来源:图灵社区  作者:钱亦欣翻译 [字体: ]

R中的dplyr包是我最喜欢的包之一(译者注:也是我的最爱),可以用来处理存储在内存和数据库中的数据。本文我会分享自己使用dplyr包来处理数据的经验,基本还敢数据处理的方方面面。关于dplyr包的基本函数 Teja 在DataScience+上传了另一篇 文章 进行了介绍。

如果需要处理的数据量很大,读入R中费时费力,这时使用dplyr包直接在数据库中处理数据会非常便利。我们可以把数据存储在数据库里,只把需要处理的子集读入R中进行处理。并且如果我们要处理很多数据文件,将数据存储在数据库中比起用CSV等格式存储会更安全,更易于处理。

plyr包是操作数据的利器。可以在不了解SQL的情况下,进行数据清洗,探索和特征工程等工作,它也提供了只在R中处理数据的方式。与其不同,dplyr包中的函数非常易读易写,它将数据表作为数据框处理,并使用惰性求值(延迟操作,只在真正需要的时候才从数据库中读入数据)原则。如果你对Spark很熟悉,你会发现二者在处理方式和很多函数上有不少相似之处。

dplyr包支持sqlite,mysql和pstgresql等数据库,本文中我将演示如何在sqlite库中进行操作。你可以从dplyr包的 小品文 中获取更多相关信息。

本文将继续使用FDA的不良事件数据(见“在R中使用SQL命令“译文),我们提取相关的病人、药物和服药指令等数据,把所有数据放在一个数据库里并使用dplyr包来处理这些数据。

你可以直接把下面的代码运行一遍,它会下载不良事件数据并合并不同类目下的数据,创建一个很大的数据集。为方便说明,我们使用2013-2015年的数据做演示。不良事件数据集是一个季度数据,每个类目的季度数据都被保存为一个数据文件。

加载R包

library(dplyr)
library(ggplot2)
library(data.table)

下载不良事件数据集

year_start=2013
year_last=2015
for (i in year_start:year_last){
    j=c(1:4)
    for (m in j){
        url1<-paste0("http://www.nber.org/fda/faers/",i,"/demo",i,"q",m,".csv.zip")
            download.file(url1,dest="data.zip") # 人口统计数据
            unzip ("data.zip")
        url2<-paste0("http://www.nber.org/fda/faers/",i,"/drug",i,"q",m,".csv.zip")
            download.file(url2,dest="data.zip")  # 药物数据
            unzip ("data.zip")
        url3<-paste0("http://www.nber.org/fda/faers/",i,"/reac",i,"q",m,".csv.zip")
            download.file(url3,dest="data.zip") # 反应数据
            unzip ("data.zip")
        url4<-paste0("http://www.nber.org/fda/faers/",i,"/outc",i,"q",m,".csv.zip")
            download.file(url4,dest="data.zip") # 结果数据
            unzip ("data.zip")
        url5<-paste0("http://www.nber.org/fda/faers/",i,"/indi",i,"q",m,".csv.zip")
            download.file(url5,dest="data.zip") # 应对措施数据
            unzip ("data.zip")
    }
}

连接季度数据文件并对每个类目创建单个数据集

人口统计数据

filenames <- list.files(pattern="^demo.*.csv", full.names=TRUE)
demography = rbindlist(lapply(filenames, fread,
        select=c("primaryid","caseid","age","age_cod","event_dt",
         "sex","wt","wt_cod","occr_country"),data.table=FALSE))

str(demography)
'data.frame':    3037542 obs. of  9 variables:
 $ primaryid   : int  30375293 30936912 32481334 35865322 37005182 37108102 37820163 38283002 38346784 40096383 ...
 $ caseid      : int  3037529 3093691 3248133 3586532 3700518 3710810 3782016 3828300 3834678 4009638 ...
 $ age         : chr  "44" "38" "28" "45" ...
 $ age_cod     : chr  "YR" "YR" "YR" "YR" ...
 $ event_dt    : int  199706 199610 1996 20000627 200101 20010810 20120409 NA 20020615 20030619 ...
 $ sex         : chr  "F" "F" "F" "M" ...
 $ wt          : num  56 56 54 NA NA 80 102 NA NA 87.3 ...
 $ wt_cod      : chr  "KG" "KG" "KG" "" ...
 $ occr_country: chr  "US" "US" "US" "AR" ...

可以看到人口统计数据有超过300万行观测,变量则包括年龄,年龄代码,事件发生日期,性别,体重,体重代码和事件发生国家。

药物数据

 filenames <- list.files(pattern="^drug.*.csv", full.names=TRUE)
 drug = rbindlist(lapply(filenames, fread,
        select=c("primaryid","drug_seq","drugname","route"
         ),data.table=FALSE))

str(drug)
'data.frame':    9989450 obs. of  4 variables:
 $ primaryid: chr  "" "" "" "" ...
 $ drug_seq : chr  "" "" "20140601" "U" ...
 $ drugname : chr  "" "" "" "" ...
 $ route    : chr  "" "21060" "" "76273" ... 

药物数据集有大概1000万的观测,变量包括药物名称和路径等。

诊断结果/反应特征

filenames <- list.files(pattern="^indi.*.csv", full.names=TRUE)
 indication = rbindlist(lapply(filenames, fread,
        select=c("primaryid","indi_drug_seq","indi_pt"
         ),data.table=FALSE))

str(indication)
'data.frame':    6383312 obs. of  3 variables:
 $ primaryid    : int  8480348 8480354 8480355 8480357 8480358 8480358 8480358 8480359 8480360 8480361 ...
 $ indi_drug_seq: int  1020135312 1020135329 1020135331 1020135333 1020135334 1020135337 1020135338 1020135339 1020135340 1020135341 ...
 $ indi_pt      : chr  "CONTRACEPTION" "SCHIZOPHRENIA" "ANXIETY" "SCHIZOPHRENIA" ...

该数据集有600多万个观测,变量有身份证ID,药物序列和反应特征。

事件结果

filenames <- list.files(pattern="^outc.*.csv", full.names=TRUE)
outcome = rbindlist(lapply(filenames, fread,
        select=c("primaryid","outc_cod"),data.table=FALSE))

str(outcome)
 'data.frame':    2453953 obs. of  2 variables:
 $ primaryid: int  8480347 8480348 8480350 8480351 8480352 8480353 8480353 8480354 8480355 8480356 ...
 $ outc_cod : chr  "OT" "HO" "HO" "HO" ...

该数据集有2000多万观测,变量有省份证ID和最终结果。

针对事件的措施

filenames <- list.files(pattern="^reac.*.csv", full.names=TRUE)
reaction = rbindlist(lapply(filenames, fread,
           select=c("primaryid","pt"),data.table=FALSE))

str(reaction)
'data.frame':    9288270 obs. of  2 variables:
 $ primaryid: int  8480347 8480348 8480349 8480350 8480350 8480350 8480350 8480350 8480350 8480351 ...
 $ pt       : chr  "ANAEMIA HAEMOLYTIC AUTOIMMUNE" "OPTIC NEUROPATHY" "DYSPNOEA" "DEPRESSED MOOD" ...

这是一个有约1000万观测,变量为身份证ID和事件应对措施的数据集。

创建数据库

要在R中创建一个SQLite数据库,我们只需要设定路径,使用src_sqlite()函数来连接R和现有的sqlite数据库,再用tbl()函数把数据表和该库连接在一起就大功告成了。我们也可以用src_sqlite()函数在特定路径下创建新的SQLite数据库,如果不额外指定路径,数据库将被创建于当前工作目录下。

my_database<- src_sqlite("adverse_events", create = TRUE)
 # create =TRUE 该参数设定为创建新的数据库

将数据写入数据库

我们使用dplyr包中的copy_to()函数把数据上传到数据库。根据文档,新写入的对象可能只是一个临时文件,我们需要把temporary参数设定为false来使得新对象是永久文件。

#上传各个类目的数据至SQLite数据库
copy_to(my_database,demography,temporary = FALSE)
copy_to(my_database,drug,temporary = FALSE)   
copy_to(my_database,indication,temporary = FALSE) 
copy_to(my_database,reaction,temporary = FALSE)  
copy_to(my_database,outcome,temporary = FALSE)     

我已经把所有数据上传到了“不良事件”数据库中了,我现在可以访问这个库并做一些数据分析了。

连接到数据库

我们可以直接使用dplyr中的函数来操作数据,dplyr包会将我们的R代码转化为SQL代码。利用tbl()函数可以连接到数据库中的表格。

demography = tbl(my_db,"demography" )

class(demography)
tbl_sqlite" "tbl_sql" "tbl" 

head(demography,3)

US = filter(demography, occr_country=='US')  # 过滤出发生在美国的不良事件数据

上述filter函数对应的SQL查询指令如下:

US$query
 SELECT "primaryid", "caseid", "age", "age_cod", "event_dt", "sex", "wt", "wt_cod", "occr_country"
FROM "demography"
WHERE "occr_country" = 'US'

我们也能看到数据库如何执行这个查询指令

explain(US)

SELECT "primaryid", "caseid", "age", "age_cod", "event_dt", "sex", "wt", "wt_cod", "occr_country"
FROM "demography"
WHERE "occr_country" = 'US'
  selectid order from                detail
1        0     0    0 SCAN TABLE demography

利用相似方法,连接到其他数据集

drug = tbl(my_db,"drug" )
indication = tbl(my_db,"indication" )
outcome = tbl(my_db,"outcome" )
reaction = tbl(my_db,"reaction" )

有意思的是dplyr包会延迟这些查询操作,只在我们需要数据的时候才把相应的对象加载到R中。即当我们使用诸如collect(), head(), count()等函数时,先前的查询指令才被执行。(译者注:也就是遵循惰性求值原则)

当我们对数据库中提取的数据进行tail()操作,程序会报错。因为只有当整个查询指令被执行完毕,我们才能找到数据表中的最后几行观测。

head(indication,3)

tail(indication,3)
Error: tail is not supported by sql sources

对数据库中的表使用dplyr中的指令 (select, arrange, filter, mutate, summarize, rename)

我们可以利用magrittr包中的管道操作符%>%将不同指令连接起来。%>%符号会把左边的输出传递到右边的函数,作为右侧函数的第一个参数。

寻找不良事件发生最多的10个国家

demography%>%group_by(Country= occr_country)%>% 
       summarize(Total=n())%>%      
       arrange(desc(Total))%>%       
       filter(Country!='')%>% head(10)

我们也可以在操作链中加入ggplot函数来对数据进行可视化

demography%>%group_by(Country= occr_country)%>% #按国家分组
       summarize(Total=n())%>%    # 找到每个国家的事件数
       arrange(desc(Total))%>%    # 按照降序对事件数排序
       filter(Country!='')%>%     # 把国家变量为空的观测删除
       head(10)%>%                # 找出前十名
       mutate(Country = factor(Country,levels = Country[order(Total,decreasing =F)]))%>%                      
    ggplot(aes(x=Country,y=Total))+geom_bar(stat='identity',color='skyblue',fill='#b35900')+
       xlab("")+ggtitle('Top ten countries with highest number of adverse event reports')+
       coord_flip()+ylab('Total number of reports')  

寻找最常见药物

drug%>%group_by(drug_name= drugname)%>% # 按照药物名分组
       summarize(Total=n())%>%    # 找到每组的事件发生数
       arrange(desc(Total))%>%    # 按照降序排序
       head(1)                   # 找到频率最高的药物

最常见的5大事件结果

head(outcome,3)  # 查看变量名

outcome%>%group_by(Outcome_code= outc_cod)%>% # 按结果代码分组
       summarize(Total=n())%>%    # 找到每组的事件发生数
       arrange(desc(Total))%>%    # 按照降序排序
       head(5)                    # 提出最前面的5个结果代码

前10大事件应对措施

head(reaction,3)  # to see the variable names

reaction%>%group_by(reactions= pt)%>% # 按应对措施分组
       summarize(Total=n())%>%    # 找到每组的事件发生数
       arrange(desc(Total))%>%    # 按照降序排序
       head(10)                   # 提出最前面的5个

Joins(连接)

让我们把人口统计数据,结果数据和应对数据利用身份证ID做主键连接起来:

inner_joined = demography%>%inner_join(outcome, by='primaryid',copy = TRUE)%>%
           inner_join(reaction, by='primaryid',copy = TRUE)

head(inner_joined)

我们也可以设定在连接时设定主键和第二主键。让我们把药物和反应特征数据利用两个键连接起来。

drug_indication= indication%>%rename(drug_seq=indi_drug_seq)%>%
   inner_join(drug, by=c("primaryid","drug_seq"))
head(drug_indication)

In this post, we saw how to use the dplyr package to create a database and upload data to the database. We also saw how to perform various analytics by querying data from the database. Working with databases in R has huge advantage when our data is big where loading it to R is impossible or slows down our analytics. If our data resides in a database, rather than loading the whole data to R, we can work with subsets or aggregates of the data that we are interested in. Further, if we have many data files, putting our data in a database, rather than in csv or other format, has better security and is easier to manage. This is enough for this post. See you in my next post. You can read about dplyr two-table verbs here. If you have any questions or feedback, feel free to leave a comment.

通过本文,我们演示了如何利用dplyr包来创建数据库并上传数据。我们还演示了如何查询数据库中的数据,并进行一系列分析操作。

在R中使用数据库有不少优势,尤其在数据量很大,直接读入R中进行分析效率很低时。如果把数据存储在数据库中,而不是直接加载到R中,我们可以只对我们感兴趣的部分数据进行操作。更进一步,假若我们有多个数据文件,把数据存在数据库中,而不是使用csv或其他格式保存,数据的存贮的安全性和可操作性会更高。

本文到这里就结束了,朋友们我们下篇文章见!你可以在 这里 查看dplyr中对两张表可以进行的操作。如果有任何建议和意见,请在下方留言。

注:原文刊载于datascience+网站

链接: http://datascienceplus.com/working-with-databases-in-r/





收藏 推荐 打印 | 录入:elainebo | 阅读:
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数
点评:
       
评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款