Mercurial > repos > devteam > cummerbund_to_tabular

comparison cummerbund_to_tabular.py @ 0:648c27c78eed draft

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Initial commit with version 1.0.0 of the tool.
author devteam
date Tue, 23 Dec 2014 16:01:24 -0500
parents
children 36f917aa4b60
comparison
equal deleted inserted replaced
-1:000000000000 0:648c27c78eed
1 import os
2 import argparse
3 import sys
4 import string
5
6 from galaxy.model.orm import *
7 import logging
8 from galaxy import eggs
9 eggs.require('SQLAlchemy')
10 import sqlalchemy
11
12
13 class CummerbundParser(object):
14
15 def __init__(self, opts):
16 self.cummerbund_db = opts.filename
17 self.__connect_database()
18
19 def generate_file( self, table ):
20 if hasattr( self, table ):
21 with open( '%s.tabular' % table, 'w' ) as self.fh:
22 getattr( self, table )()
23 else:
24 print 'Table %s is not supported or does not exist.' % table
25
26 def __connect_database( self ):
27 database_connection = 'sqlite:///%s' % os.path.abspath( self.cummerbund_db )
28 # Initialize the database connection.
29 engine = create_engine( database_connection )
30 meta = MetaData( bind=engine )
31 sa_sesssion = Session = scoped_session( sessionmaker( bind=engine, autoflush=False, autocommit=True ) )
32 self.session = sa_sesssion
33
34 def __write_line(self, line):
35 columns = []
36 for col in line:
37 if isinstance( col, float ):
38 if str( col ) in [ '-inf', 'inf' ]:
39 columns.append( str( col ) )
40 elif col == int(col):
41 columns.append( str( int( col ) ) )
42 else:
43 columns.append( str( col ) )
44 elif col is None:
45 columns.append( '-' )
46 else:
47 columns.append( str( col ) )
48 print >>self.fh, '\t'.join( columns )
49
50 def __get_diff_from_table( self, table, identifier ):
51 columns = [ '${table}.${identifier}', '${table}.gene_id', 'genes.gene_short_name', 'genes.locus',
52 '${table}.sample_1', '${table}.sample_2', '${table}.status',
53 '${table}.value_1', '${table}.value_2', '${table}.JS_dist',
54 '${table}.test_stat', '${table}.p_value', '${table}.q_value',
55 '${table}.significant' ]
56 query = string.Template( 'SELECT %s FROM ${table} JOIN genes on ${table}.gene_id = genes.gene_id' % ', '.join(columns) )
57 result = self.session.execute( query.safe_substitute( table=table, identifier=identifier ) )
58 self.__write_line( [ 'test_id', 'gene_id', 'gene', 'locus', 'sample_1',
59 'sample_2', 'status', 'value_1', 'value_2', 'sqrt(JS)',
60 'test_stat', 'p_value', 'q_value', 'significant' ] )
61 for row in result:
62 self.__write_line( row )
63
64 def __get_read_group_data( self, table, identifier ):
65 header = [ 'tracking_id', 'condition', 'replicate', 'raw_frags',
66 'internal_scaled_frags', 'external_scaled_frags', 'FPKM',
67 'effective_length', 'status' ]
68 columns = [ identifier, 'sample_name', 'replicate', 'raw_frags',
69 'internal_scaled_frags', 'external_scaled_frags', 'fpkm',
70 'effective_length', 'status' ]
71 self.__write_line( header )
72 for row in self.session.execute( 'SELECT %s FROM %s' % ( ', '.join( columns ), table ) ):
73 self.__write_line( row )
74
75
76 def __get_exp_diff( self, table, data_table, data_table_as, column ):
77 header = [ 'test_id', 'gene_id', 'gene', 'locus', 'sample_1', 'sample_2',
78 'status', 'value_1', 'value_2', 'log2(fold_change)', 'test_stat',
79 'p_value', 'q_value', 'significant' ]
80 columns = [ '${dtas}.${column}', '${table}.gene_id', '${table}.gene_short_name', '${table}.locus',
81 '${dtas}.sample_1', '${dtas}.sample_2', '${dtas}.status',
82 '${dtas}.value_1', '${dtas}.value_2', '${dtas}.log2_fold_change',
83 '${dtas}.test_stat', '${dtas}.p_value', '${dtas}.q_value',
84 '${dtas}.significant' ]
85 query = string.Template( 'SELECT %s FROM ${dtab} as ${dtas} JOIN ${table} on ${dtas}.${column} = ${table}.${column}' % ', '.join( columns ) )
86 self.__write_line( header )
87 for row in self.session.execute( query.safe_substitute( dtas=data_table_as, dtab=data_table, table=table, column=column ) ):
88 self.__write_line( row )
89
90 def __get_per_sample_fpkm( self, identifiers, table, column ):
91 columns = []
92 for identifier in identifiers:
93 samples = self.session.execute( "SELECT sample_name FROM %s WHERE %s = '%s' ORDER BY sample_name ASC" % ( table, column, identifier[0] ) )
94 for sample in samples:
95 sample_name = sample[0]
96 columns.extend( [ '%s_FPKM' % sample_name,
97 '%s_conf_lo' % sample_name,
98 '%s_conf_hi' % sample_name,
99 '%s_status' % sample_name ] )
100 return columns
101
102 def __get_fpkms( self, table, data_table, column ):
103 tss_columns = [ column, 'class_code', 'nearest_ref_id', 'gene_id',
104 'gene_short_name', column, 'locus', 'length', 'coverage' ]
105 output_cols = [ 'tracking_id', 'class_code', 'nearest_ref_id', 'gene_id', 'gene_short_name',
106 'tss_id', 'locus', 'length', 'coverage' ]
107 tss_groups = self.session.execute( 'SELECT %s FROM %s LIMIT 1' % ( ', '.join( tss_columns ), table ) )
108 output_cols.extend( self.__get_per_sample_fpkm( identifiers=tss_groups, column=column, table=data_table ) )
109 self.__write_line( output_cols )
110 tss_groups = self.session.execute( 'SELECT %s FROM %s' % ( ', '.join( tss_columns ), table ) )
111 for tss_group in tss_groups:
112 out_data = list( tss_group )
113 samples = self.session.execute( "SELECT fpkm, conf_hi, conf_lo, quant_status FROM %s WHERE %s = '%s' ORDER BY sample_name ASC" % ( data_table, column, tss_group[0] ) )
114 for sample in samples:
115 out_data.extend( list( sample ) )
116 self.__write_line( out_data )
117
118 def __get_count_data( self, table, column ):
119 output_cols = [ 'tracking_id' ]
120 tss_groups = self.session.execute( 'SELECT %s FROM %s LIMIT 1' % ( column, table ) )
121 output_cols.extend( self.__get_per_sample_count_cols( identifiers=tss_groups, table=table, column=column ) )
122 self.__write_line( output_cols )
123 self.__get_per_sample_count_data( table=table, column=column )
124
125 def __get_per_sample_count_data( self, table, column ):
126 result = self.session.execute( 'SELECT DISTINCT(%s) FROM %s' % ( column, table ) )
127 for row in result:
128 isoform_id = row[0]
129 output_data = [ isoform_id ]
130 per_sample = self.session.execute( "SELECT count, variance, uncertainty, dispersion, status FROM %s WHERE %s = '%s' ORDER BY sample_name ASC" % ( table, column, isoform_id ) )
131 for samplerow in per_sample:
132 output_data.extend( list( samplerow ) )
133 self.__write_line( output_data )
134
135 def __get_per_sample_count_cols( self, identifiers, table, column ):
136 columns = []
137 for identifier in identifiers:
138 samples = self.session.execute( "SELECT sample_name FROM %s WHERE %s = '%s' ORDER BY sample_name ASC" % ( table, column, identifier[0] ) )
139 for sample in samples:
140 sample_name = sample[0]
141 columns.extend( [ '%s_count' % sample_name,
142 '%s_count_variance' % sample_name,
143 '%s_count_uncertainty_var' % sample_name,
144 '%s_count_dispersion_var' % sample_name,
145 '%s_status' % sample_name ] )
146 return columns
147
148 def splicing_diff( self ):
149 self.__get_diff_from_table( 'splicingDiffData', 'TSS_group_id' )
150
151 def promoters_diff( self ):
152 self.__get_diff_from_table( 'promoterDiffData', 'gene_id' )
153
154 def cds_diff( self ):
155 self.__get_diff_from_table( 'CDSDiffData', 'gene_id' )
156
157 def tss_fpkm( self ):
158 data_table = 'TSSData'
159 table = 'TSS'
160 column = 'TSS_group_id'
161 self.__get_fpkms( data_table=data_table, table=table, column=column )
162
163 def isoform_fpkm( self ):
164 data_table = 'isoformData'
165 table = 'isoforms'
166 column = 'isoform_id'
167 self.__get_fpkms( data_table=data_table, table=table, column=column )
168
169 def genes_fpkm( self ):
170 output_cols = [ 'tracking_id', 'class_code', 'nearest_ref_id', 'gene_id', 'gene_short_name',
171 'tss_id', 'locus', 'length', 'coverage' ]
172 iso_groups = self.session.execute( 'SELECT gene_id FROM genes LIMIT 1' )
173 output_cols.extend( self.__get_per_sample_fpkm( identifiers=iso_groups, column='gene_id', table='geneData' ) )
174 self.__write_line( output_cols )
175 data_columns = [ 'genes.gene_id', 'genes.class_code', 'genes.nearest_ref_id', 'genes.gene_id', 'genes.gene_short_name',
176 'GROUP_CONCAT(TSS.TSS_group_id)', 'genes.locus', 'genes.length', 'genes.coverage' ]
177 query = 'SELECT %s FROM genes JOIN TSS on TSS.gene_id = genes.gene_id GROUP BY genes.gene_id' % ', '.join( data_columns )
178 result = self.session.execute( query )
179 for row in result:
180 gene_id = row[0]
181 output_data = list( row )
182 per_sample = self.session.execute( "SELECT fpkm, conf_lo, conf_hi, quant_status FROM geneData WHERE gene_id = '%s' ORDER BY sample_name ASC" % gene_id )
183 for samplerow in per_sample:
184 output_data.extend( list( samplerow ) )
185 self.__write_line( output_data )
186
187 def cds_fpkm( self ):
188 output_cols = [ 'tracking_id', 'class_code', 'nearest_ref_id', 'gene_id', 'gene_short_name',
189 'tss_id', 'locus', 'length', 'coverage' ]
190 iso_groups = self.session.execute( 'SELECT CDS_id FROM CDS LIMIT 1' )
191 output_cols.extend( self.__get_per_sample_fpkm( identifiers=iso_groups, column='CDS_id', table='CDSData' ) )
192 self.__write_line( output_cols )
193 data_columns = [ 'CDS_id', 'class_code', 'nearest_ref_id', 'gene_id', 'gene_short_name',
194 'GROUP_CONCAT(TSS_group_id)', 'locus', 'length', 'coverage' ]
195 query = 'SELECT %s FROM CDS GROUP BY CDS_id' % ', '.join( data_columns )
196 result = self.session.execute( query )
197 for row in result:
198 CDS_id = row[0]
199 output_data = list( row )
200 per_sample = self.session.execute( "SELECT fpkm, conf_lo, conf_hi, quant_status FROM CDSData WHERE CDS_id = '%s' ORDER BY sample_name ASC" % CDS_id )
201 for samplerow in per_sample:
202 output_data.extend( list( samplerow ) )
203 self.__write_line( output_data )
204
205 def tss_count_tracking( self ):
206 self.__get_count_data( table='TSSCount', column='TSS_group_id' )
207
208 def isoform_count( self ):
209 self.__get_count_data( table='isoformCount', column='isoform_id' )
210
211 def genes_count( self ):
212 self.__get_count_data( table='geneCount', column='gene_id' )
213
214 def cds_count( self ):
215 self.__get_count_data( table='CDSCount', column='CDS_id' )
216
217 def tss_group_exp( self ):
218 columns = [ 'TEDD.TSS_group_id', 'TSS.gene_id', 'TSS.gene_short_name', 'TSS.locus',
219 'TEDD.sample_1', 'TEDD.sample_2', 'TEDD.status',
220 'TEDD.value_1', 'TEDD.value_2', 'TEDD.log2_fold_change',
221 'TEDD.test_stat', 'TEDD.p_value', 'TEDD.q_value', 'TEDD.significant' ]
222 query = [ 'SELECT %s FROM TSSExpDiffData AS TEDD' % ', '.join(columns),
223 'JOIN TSS on TEDD.TSS_group_id = TSS.TSS_group_id' ]
224 self.__write_line( [ 'test_id', 'gene_id', 'gene', 'locus',
225 'sample_1', 'sample_2', 'status', 'value_1',
226 'value_2', 'log2(fold_change)', 'test_stat',
227 'p_value', 'q_value', 'significant' ] )
228 for row in self.session.execute( ' '.join( query ) ):
229 self.__write_line( row )
230
231 def run_info( self ):
232 self.__write_line( [ 'param', 'value' ] )
233 for row in self.session.execute( 'SELECT param, value FROM runInfo' ):
234 self.__write_line( row )
235
236 def read_groups( self ):
237 self.__write_line( [ 'file', 'condition', 'replicate_num', 'total_mass', 'norm_mass', 'internal_scale', 'external_scale' ] )
238 for row in self.session.execute( 'SELECT file, sample_name, replicate, total_mass, norm_mass, internal_scale, external_scale FROM replicates' ):
239 self.__write_line( row )
240
241 def isoform_exp_diff( self ):
242 self.__get_exp_diff( table='isoforms', data_table='isoformExpDiffData', data_table_as='iED', column='isoform_id' )
243
244 def gene_exp_diff( self ):
245 self.__get_exp_diff( table='genes', data_table='geneExpDiffData', data_table_as='gEDD', column='gene_id' )
246
247 def cds_exp_diff( self ):
248 self.__get_exp_diff( table='CDS', data_table='CDSExpDiffData', data_table_as='CED', column='CDS_id' )
249
250 def tss_rg( self ):
251 self.__get_read_group_data( table='TSSReplicateData', identifier='TSS_group_id' )
252
253 def isoform_rg( self ):
254 self.__get_read_group_data( table='isoformReplicateData', identifier='isoform_id' )
255
256 def gene_rg( self ):
257 self.__get_read_group_data( table='geneReplicateData', identifier='gene_id' )
258
259 def cds_rg( self ):
260 self.__get_read_group_data( table='CDSReplicateData', identifier='CDS_id' )
261
262 def var_model( self ):
263 header = [ 'condition', 'locus', 'compatible_count_mean', 'compatible_count_var', 'total_count_mean', 'total_count_var', 'fitted_var' ]
264 self.__write_line( header )
265 for row in self.session.execute( 'SELECT %s FROM varModel' % ', '.join( header ) ):
266 self.__write_line( row )
267
268 if __name__ == '__main__':
269 parser = argparse.ArgumentParser()
270 parser.add_argument( '--file', dest='filename' )
271 parser.add_argument( '--tables', dest='tables', action='append' )
272 opts = parser.parse_args()
273 cb = CummerbundParser( opts )
274 for table in opts.tables:
275 cb.generate_file( table )