Mercurial > repos > tomnl > create_sqlite_db

comparison create_sqlite_db.R @ 12:225009d1f603 draft

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
planemo upload for repository https://github.com/computational-metabolomics/mspurity-galaxy commit 97c9122c9af9ddca3a2498185ce8c7919c0df03a
author tomnl
date Tue, 08 May 2018 05:37:10 -0400
parents c59965822dc8
children 4acad02faf32
comparison
equal deleted inserted replaced
11:c59965822dc8 12:225009d1f603
57 make_option("--galaxy_names", type="character"), 57 make_option("--galaxy_names", type="character"),
58 make_option("--grp_peaklist", type="character"), 58 make_option("--grp_peaklist", type="character"),
59 make_option("--db_name", type="character", default='lcms_data.sqlite'), 59 make_option("--db_name", type="character", default='lcms_data.sqlite'),
60 make_option("--raw_rt_columns", action="store_true"), 60 make_option("--raw_rt_columns", action="store_true"),
61 make_option("--metfrag_result", type="character"), 61 make_option("--metfrag_result", type="character"),
62 make_option("--sirius_csifingerid_result", type="character") 62 make_option("--sirius_csifingerid_result", type="character"),
63 make_option("--probmetab_result", type="character")
63 ) 64 )
64 65
65 66
66 # store options 67 # store options
67 opt<- parse_args(OptionParser(option_list=option_list)) 68 opt<- parse_args(OptionParser(option_list=option_list))
131 132
132 con <- DBI::dbConnect(RSQLite::SQLite(), db_pth) 133 con <- DBI::dbConnect(RSQLite::SQLite(), db_pth)
133 134
134 add_extra_table_elucidation <- function(name, pth){ 135 add_extra_table_elucidation <- function(name, pth){
135 136
136 print('CHECK1')
137 print(name)
138 print(pth)
139 if (!is.null(pth)){ 137 if (!is.null(pth)){
140 print('CHECK2') 138
141 print(pth) 139 print(pth)
142 df <- read.table(pth, header = TRUE, sep='\t', stringsAsFactors = FALSE, comment.char = "") 140 df <- read.table(pth, header = TRUE, sep='\t', stringsAsFactors = FALSE, comment.char = "")
143 # bug for repeating headers 141 # bug for repeating headers
144 df <- df[!df$UID=='UID',] 142 df <- df[!df$UID=='UID',]
145 143
154 152
155 } 153 }
156 154
157 } 155 }
158 156
157
158 add_probmetab <- function(pth){
159 if (!is.null(pth)){
160
161 df <- read.table(pth, header = TRUE, sep='\t', stringsAsFactors = FALSE, comment.char = "")
162 df$grp_id <- 1:nrow(df)
163 start <- T
164 for (i in 1:nrow(df)){
165
166 x <- df[i,]
167
168
169 if(is.na(x$proba) | x$proba =='NA'){
170
171 next
172 }
173
174 mpc <- stringr::str_split(x$mpc, ';')
175 proba <- stringr::str_split(x$proba, ';')
176
177 for (j in 1:length(mpc[[1]])){
178
179 row <- c(x$grp_id, x$propmz, mpc[[1]][j], proba[[1]][j])
180
181 if (start){
182 df_out <- data.frame(t(row), stringsAsFactors=F)
183 start <- F
184 }else{
185 df_out <- data.frame(rbind(df_out, row), stringsAsFactors=F)
186 }
187 print(df_out)
188
189 }
190
191 }
192
193 colnames(df_out) <- c('grp_id', 'propmz', 'mpc', 'proba')
194 DBI::dbWriteTable(con, name='probmetab_results', value=df_out, row.names=FALSE)
195
196 }
197
198
199 }
200
159 add_extra_table_elucidation('metfrag_results', opt$metfrag_result) 201 add_extra_table_elucidation('metfrag_results', opt$metfrag_result)
160 add_extra_table_elucidation('sirius_csifingerid_results', opt$sirius_csifingerid_result) 202 add_extra_table_elucidation('sirius_csifingerid_results', opt$sirius_csifingerid_result)
203 add_probmetab(opt$probmetab_result)
204
161 205
162 206
163 207
164 cmd <- paste('SELECT cpg.grpid, cpg.mz, cpg.mzmin, cpg.mzmax, cpg.rt, cpg.rtmin, cpg.rtmax, c_peaks.cid, ', 208 cmd <- paste('SELECT cpg.grpid, cpg.mz, cpg.mzmin, cpg.mzmax, cpg.rt, cpg.rtmin, cpg.rtmax, c_peaks.cid, ',
165 'c_peaks.mzmin AS c_peak_mzmin, c_peaks.mzmax AS c_peak_mzmax, ', 209 'c_peaks.mzmin AS c_peak_mzmin, c_peaks.mzmax AS c_peak_mzmax, ',