def main(dbN, geneN):
(con, cursor) = mycgi.connectDB(db=dbN)
# prep RNA-Seq data availability table
cursor.execute(
'create temporary table t_avail_RNASeq as select distinct samp_id from rpkm_gene_expr'
)
# prep exonSkip info
cursor.execute(
'select delExons,frame,loc1,loc2, count(*) cnt from splice_skip where gene_sym = "%s" and nPos>=5 group by delExons order by count(*) desc'
% geneN)
results = cursor.fetchall()
conditionL_exonSkip = []
for (delExons, frame, loc1, loc2, cnt) in results:
if ':Y' in frame:
frame_code = 'in'
elif ':N' in frame:
frame_code = 'off'
else:
frame_code = 'utr'
conditionL_exonSkip.append( [
('nReads', 'splice_skip', 'loc1="%s" and loc2="%s" and nPos>=5' % (loc1,loc2), '%3d', '%s<br><sup>(n=%s, %s)</sup>' % (delExons.split(',')[0], cnt,frame_code),), \
# ('avg(nReads)', 'splice_normal', 'loc1="%s" or loc2="%s"' % (loc1,loc2), '%d') ])
('sum(nReads)', 'splice_normal', 'loc1="%s"' % (loc1,), '%d') ])
# prep mutation info
# where gene_symL like "%s%s%s" and ch_type != "synonymous_variant" and ch_type != "nc_transcript_variant,synonymous_variant" and ch_type != "intron_variant,synonymous_variant" \
if dbN == 'CancerSCAN':
cursor.execute('create temporary table t_mut as \
select concat(substring(chrom,4,4),":",cast(chrSta as char),ref,">",alt) as ch_pos, ch_dna,ch_aa,ch_type,cosmic from mutation_cs \
where find_in_set("%s",gene_sym) > 0 and ch_type != "synonymous_variant" and ch_type != "nc_transcript_variant,synonymous_variant" and ch_type != "intron_variant,synonymous_variant" \
and ch_type != "nc_transcript_variant" and ch_type != "intron_variant,nc_transcript_variant" and ch_type != "intron_variant" and ch_type != "Substitution - coding silent"\
and nReads_alt<>2 order by ch_type desc' % (geneN))
else:
cursor.execute('create temporary table t_mut as \
select concat(substring(chrom,4,4),":",cast(chrSta as char),ref,">",alt) as ch_pos, ch_dna,ch_aa,ch_type,cosmic from mutation_rxsq \
where find_in_set("%s",gene_symL) > 0 and ch_type != "synonymous_variant" and ch_type != "nc_transcript_variant,synonymous_variant" and ch_type != "intron_variant,synonymous_variant" \
and ch_type != "nc_transcript_variant" and ch_type != "intron_variant,nc_transcript_variant" and ch_type != "intron_variant" and ch_type != "Substitution - coding silent"\
and nReads_alt<>2 order by ch_type desc' % (geneN))
cursor.execute(
'select *,count(*) cnt from t_mut group by ch_pos order by count(*) desc, cosmic desc'
)
results = cursor.fetchall()
conditionL_mutation = []
for (ch_pos, ch_dna, ch_aa, ch_type, cosmic, cnt) in results:
ch_aa = ch_aa.replace(',', '<br>')
if (dbN == 'CancerSCAN' and 'cosmic' in cosmic) or (dbN != 'CancerSCAN'
and cosmic):
cosmic_fmt = '<font color="red">%s</font><br><sup>(n=%d, %s)</sup>'
else:
cosmic_fmt = '%s<br><sup>(n=%d, %s)</sup>'
if ch_aa:
cnd = ch_aa
elif ch_dna:
cnd = ch_dna
else:
cnd = ch_pos
if dbN == 'CancerSCAN':
conditionL_mutation.append([
('nReads_alt', 'mutation_cs', 'nReads_alt<>2 and concat(substring(chrom,4,4),":",cast(chrSta as char),ref,">",alt)="%s"' % ch_pos, '%d', cosmic_fmt % (cnd, cnt, mutation_map(ch_type, dbN))), \
('nReads_ref', 'mutation_cs', 'nReads_alt<>2 and concat(substring(chrom,4,4),":",cast(chrSta as char),ref,">",alt)="%s"' % ch_pos, '%d') ])
else:
conditionL_mutation.append( [
('nReads_alt,r_nReads_alt', 'mutation_rxsq', 'nReads_alt<>2 and concat(substring(chrom,4,4),":",cast(chrSta as char),ref,">",alt)="%s"' % ch_pos, '%d', cosmic_fmt % (cnd, cnt, mutation_map(ch_type, dbN))), \
('nReads_ref,r_nReads_ref', 'mutation_rxsq', 'nReads_alt<>2 and concat(substring(chrom,4,4),":",cast(chrSta as char),ref,">",alt)="%s"' % ch_pos, '%d') ])
# prep fusion table
cursor.execute('create temporary table t_fusion as \
select samp_id,locate(":Y",frame)>1 frame,count(nPos) nEvents \
from splice_fusion where nPos>=2 and (find_in_set("%s",gene_sym1) or find_in_set("%s",gene_sym2)) group by samp_id, locate(":Y",frame)>1'
% (geneN, geneN))
# prep eiJunc info
cursor.execute(
'select loc,juncAlias, count(*) cnt from splice_eiJunc where gene_sym="%s" and nReads>=10 group by loc'
% geneN)
results = cursor.fetchall()
conditionL_eiJunc = []
for (loc, juncAlias, cnt) in results:
conditionL_eiJunc.append([
('nReads', 'splice_eiJunc', 'loc="%s" and nReads>=10' % loc, '%3d',
'%s<br><sup>(n=%s)</sup>' % (juncAlias, cnt)),
('sum(nReads)', 'splice_normal', 'loc1="%s"' % (loc, ), '%d')
])
# outlier
outlier_sId = []
if dbN in ['ircr1', 'tcga1', 'ccle1']:
cursor.execute(
'select samp_id, expr_MAD from array_gene_expr_MAD mad, gene_expr_stat stat where mad.gene_sym = stat.gene_sym and (mad.expr_MAD >= stat.q75 + 3*(stat.q75 - stat.q25) or mad.expr_MAD <= stat.q25 - 3*(stat.q75 - stat.q25)) and mad.gene_sym = "%s"'
% geneN)
results3 = cursor.fetchall()
for i in range(len(results3)):
outlier_sId.append(results3[i][0])
## corrected CN value available?
cncorr_sId = []
if dbN in ['ircr1']:
cursor.execute(
'select distinct samp_id from xsq_purity where tumor_frac != "ND"')
resultsCorr = cursor.fetchall()
for i in range(len(resultsCorr)):
cncorr_sId.append(resultsCorr[i][0])
normal_sId = []
if dbN in ['ircr1']:
cursor.execute('select distinct samp_id from xsq_purity')
tttt = cursor.fetchall()
for i in range(len(tttt)):
normal_sId.append(tttt[i][0])
conditionL = conditionL_preH[
dbN] + conditionL_mutation + conditionL_fusion + conditionL_exonSkip + conditionL_eiJunc
print '<p><h4>%s status of %s panel <small><a href="http://www.genecards.org/cgi-bin/carddisp.pl?gene=%s">[GeneCard]</a> <a href="http://www.ncbi.nlm.nih.gov/pubmed/?term=%s">[PubMed]</a></small></h4></p>' % (
geneN, mycgi.db2dsetN(dbN), geneN, geneN)
# census
cursor.execute(
'select tumor_soma, tumor_germ, syndrome, mut_type from common.census where gene_sym="%s"'
% geneN)
census = cursor.fetchall()
print('\n<font size=2> <table border="1" cellpadding="0" cellspacing="0">')
print(
'<tr>\n<td rowspan=2>Census</td>\n<td>tumor_soma</td>\n<td>tumor_germ</td>\n<td>syndrome</td>\n<td>mut_type</td>\n</tr>\n'
)
if len(census) != 0:
print(
'<tr>\n<td>%s</td>\n<td>%s</td>\n<td>%s</td>\n<td>%s</td>\n</tr>\n'
% (census[0][0], census[0][1], census[0][2], census[0][3]))
else:
print('<tr>\n<td></td>\n<td></td>\n<td></td>\n<td></td>\n</tr>\n')
# drugbank
cursor.execute('select drug from common.drugbank where gene_sym="%s"' %
geneN)
drug = [x for (x, ) in cursor.fetchall()]
print('\n<font size=2> <table border="1" cellpadding="0" cellspacing="0">')
print('<br><tr>\n<td>Drug</td>\n')
print('<td>%s</td>\n</tr>\n' % ('</br>\n'.join(drug)))
# pathway
cursor.execute(
'select biocarta_id, biocarta_desc from common.biocarta where gene_sym="%s"'
% geneN)
biocarta = cursor.fetchall()
cursor.execute(
'select kegg_id, kegg_desc from common.kegg where gene_sym="%s"' %
geneN)
kegg = cursor.fetchall()
cursor.execute('select go_id, go_desc from common.go where gene_sym="%s"' %
geneN)
go = cursor.fetchall()
print('\n<font size=2> <table border="1" cellpadding="0" cellspacing="0">')
print('<br><tr>\n<td>Biocarta</td>\n<td>KEGG</td>\n<td>GO</td>\n</tr>\n')
print('<tr><td><div style="width:100%; height:50px; overflow:auto">')
for (id, desc) in biocarta:
print(
'<a href="http://cgap.nci.nih.gov/Pathways/BioCarta/%s">%s</br>' %
(id, desc))
print('</td>\n<td><div style="width:100%; height:50px; overflow:auto">')
for (id, desc) in kegg:
print(
'<a href="http://www.genome.jp/dbget-bin/www_bget?pathway+%s">%s</br>'
% (id, desc))
print('</td>\n<td><div style="width:100%; height:50px; overflow:auto">')
for (id, desc) in go:
print(
'<a href="http://amigo.geneontology.org/cgi-bin/amigo/term_details?term=GO:%s">%s</br>'
% (id, desc))
print('</div></td>\n</tr>\n')
if dbN == 'CancerSCAN':
cursor.execute(
'''CREATE TEMPORARY TABLE t_id AS SELECT DISTINCT samp_id FROM mutation_cs UNION SELECT DISTINCT samp_id FROM cs_cn'''
)
elif dbN == 'tcga1':
cursor.execute('create temporary table t_id as \
select distinct samp_id from array_gene_expr union select distinct samp_id from array_cn union select distinct samp_id from splice_normal union select distinct samp_id from mutation_rxsq union select distinct samp_id from rpkm_gene_expr'
)
else:
cursor.execute('create temporary table t_id as \
select distinct samp_id from array_gene_expr union select distinct samp_id from array_cn union select distinct samp_id from splice_normal union select distinct samp_id from mutation_rxsq union select distinct samp_id from rpkm_gene_expr union select distinct samp_id from xsq_cn'
)
cursor.execute('alter table t_id add index (samp_id)')
cursor.execute(
'create temporary table t_expr as select * from array_gene_expr where gene_sym="%s"'
% geneN)
#cursor.execute('create temporary table t_cn as select * from xsq_cn where gene_sym="%s"' % geneN)
cursor.execute('alter table t_expr add index (samp_id,gene_sym)')
#cursor.execute('alter table t_cn add index (samp_id,gene_sym)')
cursor.execute(
'select samp_id from t_id left join t_expr using (samp_id) order by z_score desc'
)
#cursor.execute('select samp_id from t_id left join t_cn using (samp_id) order by value_log2 desc')
results = cursor.fetchall()
numTotSamp = len(results)
print('\n<font size=3> <table border="1" cellpadding="0" cellspacing="0">')
# header: row1
print '<br><tr>\n<td rowspan=2><div class="verticaltext" align="middle">samples<br><sup>n=%s</sup></div></td>' % numTotSamp,
for i in range(len(conditionL)):
row = conditionL[i]
if type(row) == list:
row = row[0]
if i < len(conditionL_preH[dbN]):
if ('tag' in row[1]) or ('t_avail' in row[1]) or (
'subtype' in row[1]) or ('purity' in row[1]):
cursor.execute('select count(*) from %s where %s' %
(row[1], row[2]))
elif ('loh' in row[1]):
cursor.execute(
'select count(distinct samp_id) from xsq_purity'
) # of samples for which purity pipeline was applied = # of samples with matched blood exome
else:
cursor.execute(
'select count(*) from %s where %s and gene_sym ="%s"' %
(row[1], row[2], geneN))
count = cursor.fetchone()
if 'MAD' in row[4]:
print(
'<td rowspan=2 align="middle"><div class="verticaltext">%s, n=%s)</sup></div></td>'
% (row[-1], count[0]))
else:
print(
'<td rowspan=2 align="middle"><div class="verticaltext">%s<br><sup>(n=%s)</sup></div></td>'
% (row[-1], count[0]))
else:
if i == len(conditionL_preH[dbN]) and len(conditionL_mutation) > 0:
print('<td align="middle" colspan=%s>mutation (mt/wt)</td>' %
len(conditionL_mutation))
elif i == len(conditionL_preH[dbN]) + len(conditionL_mutation):
print(
'<td align="middle" colspan=%s><a href="ircr_samp.py?dbN=%s&dType=Fusion">fusion</a></td>'
% (len(conditionL_fusion), dbN))
elif i == len(
conditionL_preH[dbN]) + len(conditionL_mutation) + len(
conditionL_fusion) and len(conditionL_exonSkip) > 0:
print(
'<td align="middle" colspan=%s><a href="ircr_samp.py?dbN=%s&dType=ExonSkipping">exonSkip</a> (mt/wt)</td>'
% (len(conditionL_exonSkip), dbN))
elif i == len(conditionL_preH[dbN]) + len(
conditionL_mutation) + len(conditionL_fusion) + len(
conditionL_exonSkip) and len(conditionL_eiJunc) > 0:
print(
'<td align="middle" colspan=%s><a href="ircr_samp.py?dbN=%s&dType=3pDeletion">3p deletion</a> (mt/wt)</td>'
% (len(conditionL_eiJunc), dbN))
print('\n</tr>\n')
# header: row2
print '<tr>\n',
for i in range(len(conditionL)):
row = conditionL[i]
if type(row) == list:
row = row[0]
if i < len(conditionL_preH[dbN]):
pass
else:
print(
'<td height="100"><div class="verticaltext" align="middle">%s</div></td>'
% row[-1])
print('\n</tr>\n')
for (sId, ) in results:
print '<tr>',
# print '<td nowrap><a href="ircr_samp.py?dbN=%s&sId=%s">%s</td>' % (dbN,sId,sId),
new_id = mycgi.get_new_id(sId)
print '<td nowrap><a href="ircr_samp.py?dbN=%s&sId=%s">%s</td>' % (
dbN, new_id, new_id),
d_flag = None
r_flag = None
for row in conditionL:
outlier = None
if type(row) == list:
row_wt = row[1]
row = row[0]
else:
row_wt = None
(col, tbl, cnd, fmt) = row[:4]
cursor.execute('show columns from %s like "gene_sym"' % tbl)
if cursor.fetchone():
cnd += ' and gene_sym="%s"' % geneN
if tbl == 'mutation_rxsq':
cursor.execute(
'select distinct %s from %s where samp_id="%s" and (%s)' %
(col, tbl, sId, cnd))
else:
cursor.execute(
'select %s from %s where samp_id="%s" and (%s)' %
(col, tbl, sId, cnd))
results2 = cursor.fetchall()
if len(results2) > 1:
print sId
raise Exception
if type(row) == tuple and row[-1] == 'RSq':
if len(results2) > 0:
r_flag = True
else:
r_flag = False
if type(row) == tuple and row[-1] == 'XSq':
if len(results2) > 0:
d_flag = True
else:
d_flag = False
if sId not in normal_sId: # flag for matched normal
p_flag = False
else:
p_flag = True
if type(row) == tuple and row[-1] == 'expr<br><sup>(MAD':
if sId in outlier_sId:
outlier = True
else:
outlier = False
if len(results2) == 1: # and results2[0][0] not in (0,'0'):
value = ('%s' % fmt) % results2[0][0]
if len(results2[0]) >= 2:
n_value = ('%s' % fmt) % results2[0][1]
else:
n_value = 0
if row_wt:
(col, tbl, cnd, fmt) = row_wt[:4]
if tbl == 'mutation_rxsq':
cursor.execute(
'select distinct %s from %s where samp_id="%s" and %s'
% (col, tbl, sId, cnd))
else:
cursor.execute(
'select %s from %s where samp_id="%s" and %s' %
(col, tbl, sId, cnd))
results_wt = cursor.fetchone()
n_count_wt = 0
if results_wt[0]:
count_wt = ('%s' % fmt) % results_wt[0]
else:
count_wt = 0
if len(results_wt) >= 2:
count_wt = ('%s' % fmt) % results_wt[0]
n_count_wt = ('%s' % fmt) % results_wt[1]
if row[1] == 'mutation_rxsq':
# if int(n_value)!=0 and int(n_count_wt)!=0 and int(value)!=0 and int(count_wt)!=0:
if int(n_value) != 0 and int(value) != 0:
if int(value) > (int(value) + int(count_wt)
) * cutoff and int(n_value) > (
int(n_value) +
int(n_count_wt)) * cutoff:
print '<td><font color=red><b>%s</b></font><sub>/%s</sub>,<font color=468847><b>%s</b><sub>/%s</sub></font></td>' % (
value, count_wt, n_value, n_count_wt),
elif int(n_value) > (int(n_value) +
int(n_count_wt)) * cutoff:
print '<td>%s<sub>/%s</sub>,<font color=468847><b>%s</b><sub>/%s</sub></font></td>' % (
value, count_wt, n_value, n_count_wt),
elif int(value) > (int(value) +
int(count_wt)) * cutoff:
print '<td><font color=red><b>%s</b></font><sub>/%s</sub>,<font color=468847>%s<sub>/%s</sub></font></td>' % (
value, count_wt, n_value, n_count_wt),
else:
print '<td>%s<sub>/%s</sub>,<font color=468847>%s<sub>/%s</sub></font></td>' % (
value, count_wt, n_value, n_count_wt),
elif int(value) == 0 and int(
count_wt) == 0: ## no exome
if int(n_value) > (int(n_value) +
int(n_count_wt)) * cutoff:
print '<td><font color=468847><b>%s</b><sub>/%s</sub></font></td>' % (
n_value, n_count_wt),
else:
print '<td><font color=468847>%s<sub>/%s</sub></font></td>' % (
n_value, n_count_wt),
elif int(n_value) == 0 and int(
n_count_wt) == 0: ## no normal
if int(value) > (int(value) +
int(count_wt)) * cutoff:
print '<td><font color=red><b>%s</b></font><sub>/%s</sub></td>' % (
value, count_wt),
else:
print '<td>%s<sub>/%s</sub></td>' % (value,
count_wt),
else:
if int(value) > (int(value) + int(count_wt)) * cutoff:
tmp = row[4].split('<br>')[0].split('/')
if len(tmp) > 1 and tmp[0] == tmp[1]:
print '<td>%s<sub>/%s</sub></td>' % (value,
count_wt),
else:
print '<td><font color=red><b>%s</b></font><sub>/%s</sub></td>' % (
value, count_wt),
else:
print '<td>%s<sub>/%s</sub></td>' % (value,
count_wt),
else:
if row[1] == 't_fusion':
html_content = ""
if row[4] == 'in':
print '<a name="%s"></a>' % sId
html_content = mycgi.compose_fusion_table(
cursor, dbN, geneN, sId, "in")
print '''
<td><div class="tooltip_content">%s</div><div class="tooltip_link"><a href="#current">%s</a></div></td>
''' % (html_content, value)
else:
html_content = mycgi.compose_fusion_table(
cursor, dbN, geneN, sId, "off")
print '''
<td><div class="tooltip_content">%s</div><div class="tooltip_link"><a href="#current">%s</a></div></td>
''' % (html_content, value)
elif outlier:
print '<td><font color=red><b>%s</b></font></td>' % value
elif tbl == 'array_cn' or tbl == 'cs_cn': ##aCGH
(cl, bL) = cn_format(value)
print '<td><font color=%s>%s%s%s</font></td>' % (
cl, bL[0], value, bL[1])
elif tbl == 'xsq_cn':
(cl1, bL1) = cn_format(value)
if sId in cncorr_sId:
cursor.execute(
'select %s from xsq_cn_corr where samp_id="%s" and (%s)'
% (col, sId, cnd))
val_corr = ('%s' % fmt) % cursor.fetchone()[0]
(cl2, bL2) = cn_format(val_corr)
print '<td><font color=%s>%s%s%s</font>,<font color=%s>%s%s%s</font></td>' % (
cl1, bL1[0], value, bL1[1], cl2, bL2[0],
val_corr, bL2[1])
else:
print '<td><font color=%s>%s%s%s</font></td>' % (
cl1, bL1[0], value, bL1[1])
else:
print '<td>%s</td>' % value
else:
#grey out
if (r_flag == False
and row[1] in ('splice_skip', 't_fusion',
'splice_eiJunc', 'rpkm_gene_expr')
) or (r_flag == False and d_flag == False
and row[1] in ('mutation_rxsq')) or (
(d_flag == False or p_flag == False)
and row[1] in ('xsq_loh')):
print '<td bgcolor=silver></td>'
else:
print '<td></td>'
print '</tr>'
print('\n</table> </font>\n')
return
def main(dbN,geneN):
(con,cursor) = mycgi.connectDB(db=dbN)
if dbN == 'ccle1':
mutation_map = {'DEL:3\'UTR':'UTR', 'SNP:3\'UTR':'UTR', 'SNP:Missense_Mutation':'MS', 'SNP:Intron':'INT', 'DEL:Frame_Shift_Del':'FS', \
'SNP:5\'UTR':'UTR', 'DEL:In_Frame_Del':'FP', 'INS:3\'UTR':'UTR', 'SNP:Splice_Site_SNP':'SS', 'SNP:Nonsense_Mutation':'NS', \
'INS:Frame_Shift_Ins':'FS', 'DEL:Intron':'INT', 'INS:Intron':'INT', 'DNP:Missense_Mutation':'MS', 'INS:Splice_Site_Ins':'SS', \
'TNP:Intron':'INT', 'DNP:Nonsense_Mutation':'NS', 'DNP:Intron':'INT', 'SNP:De_novo_Start_OutOfFrame':'SOF', 'DNP:5\'UTR':'UTR', \
'INS:5\'UTR':'UTR', 'SNP:Nonstop_Mutation':'NM', 'DNP:3\'UTR':'UTR', 'SNP:5\'Flank':'FLK', 'DEL:5\'UTR':'UTR', 'DNP:Splice_Site_DNP':'SS', \
'INS:In_Frame_Ins':'FP', 'DEL:Splice_Site_Del':'SS', 'SNP:De_novo_Start_InFrame':'SIF', 'DEL:5\'Flank':'FLK', 'INS:5\'Flank':'FLK', \
'DNP:De_novo_Start_InFrame':'SIF', 'DNP:Stop_Codon_DNP':'STC', 'INS:Stop_Codon_Ins':'STC', 'TNP:Nonsense_Mutation':'NS', \
'TNP:Missense_Mutation':'MS', 'DEL:Start_Codon_Del':'SC'}
else:
mutation_map = {'':'UK','SNP:Intron':'INT', 'SNP:5\'UTR':'UTR', 'SNP:3\'UTR':'UTR', 'SNP:RNA':'RNA', 'SNP:5\'Flank':'FLK', \
'DEL:Frame_Shift_Del':'FS', 'DEL:In_Frame_Del':'FP', 'DEL:Splice_Site':'SS', 'DEL:Translation_Start_Site':'TSS', \
'DNP:Missense_Mutation':'MS', 'DNP:Nonsense_Mutation':'NS', 'DNP:Splice_Site':'SS', \
'INS:Frame_Shift_Ins':'FS', 'INS:In_Frame_Ins':'FP', 'INS:Splice_Site':'SS', \
'SNP:Missense_Mutation':'MS', 'SNP:Nonsense_Mutation':'NS', 'SNP:Nonstop_Mutation':'NM', 'SNP:Splice_Site':'SS', 'SNP:Translation_Start_Site':'TSS', \
'Substitution - Missense':'MS', 'Substitution - Nonsense':'NS', 'Substitution - Missense,Substitution - coding silent':'MS', 'Nonstop extension':'rNS'}
# prep RNA-Seq data availability table
cursor.execute('create temporary table t_avail_RNASeq as select distinct samp_id from splice_normal')
# prep exonSkip info
cursor.execute('select delExons,frame,loc1,loc2, count(*) cnt from splice_skip where gene_sym = "%s" and nPos>=5 group by delExons order by count(*) desc' % geneN)
results = cursor.fetchall()
conditionL_exonSkip = []
for (delExons,frame,loc1,loc2, cnt) in results:
if ':Y' in frame:
frame_code = 'in'
elif ':N' in frame:
frame_code = 'off'
else:
frame_code = 'utr'
conditionL_exonSkip.append( [
('nReads', 'splice_skip', 'loc1="%s" and loc2="%s" and nPos>=5' % (loc1,loc2), '%3d', '%s<br><sup>(n=%s, %s)</sup>' % (delExons.split(',')[0], cnt,frame_code),), \
# ('avg(nReads)', 'splice_normal', 'loc1="%s" or loc2="%s"' % (loc1,loc2), '%d') ])
('sum(nReads)', 'splice_normal', 'loc1="%s"' % (loc1,), '%d') ])
# prep mutation info
cursor.execute('create temporary table t_mut as \
select concat(substring(chrom,4,4),":",cast(chrSta as char),ref,">",alt) as ch_pos, ch_dna,ch_aa,ch_type,cosmic from mutation_normal \
where find_in_set("%s",gene_symL)>0 and nReads_alt>2 order by ch_type desc' % (geneN))
cursor.execute('select *,count(*) cnt from t_mut group by ch_pos order by count(*) desc, cosmic desc limit 20')
results = cursor.fetchall()
conditionL_mutation = []
for (ch_pos,ch_dna,ch_aa,ch_type,cosmic,cnt) in results:
ch_aa = ch_aa.replace(',','<br>')
if cosmic:
cosmic_fmt = '<font color="red">%s</font><br><sup>(n=%d, %s)</sup>'
else:
cosmic_fmt = '%s<br><sup>(n=%d, %s)</sup>'
if ch_aa:
cnd = ch_aa
elif ch_dna:
cnd = ch_dna
else:
cnd = ch_pos
conditionL_mutation.append( [
('nReads_alt', 'mutation_normal', 'nReads_alt>2 and concat(substring(chrom,4,4),":",cast(chrSta as char),ref,">",alt)="%s"' % ch_pos, '%d', cosmic_fmt % (cnd, cnt, mutation_map[ch_type])), \
('nReads_ref', 'mutation_normal', 'nReads_alt>2 and concat(substring(chrom,4,4),":",cast(chrSta as char),ref,">",alt)="%s"' % ch_pos, '%d') ])
# prep fusion table
cursor.execute('create temporary table t_fusion as \
select samp_id,locate(":Y",frame)>1 frame,count(nPos) nEvents \
from splice_fusion where nPos>=2 and (find_in_set("%s",gene_sym1) or find_in_set("%s",gene_sym2)) group by samp_id, locate(":Y",frame)>1' % (geneN,geneN))
# prep eiJunc info
cursor.execute('select loc,juncAlias, count(*) cnt from splice_eiJunc where gene_sym="%s" and nReads>=10 group by loc' % geneN)
results = cursor.fetchall()
conditionL_eiJunc = []
for (loc,juncAlias,cnt) in results:
conditionL_eiJunc.append( [
('nReads', 'splice_eiJunc', 'loc="%s" and nReads>=10' % loc, '%3d', '%s<br><sup>(n=%s)</sup>' % (juncAlias, cnt)),
('sum(nReads)', 'splice_normal', 'loc1="%s"' % (loc,), '%d') ])
# outlier
outlier_sId = []
cursor.execute('select samp_id, expr_MAD from array_gene_expr_MAD mad, gene_expr_stat stat where mad.gene_sym = stat.gene_sym and (mad.expr_MAD >= stat.q75 + 3*(stat.q75 - stat.q25) or mad.expr_MAD <= stat.q25 - 3*(stat.q75 - stat.q25)) and mad.gene_sym = "%s"' % geneN)
results3 = cursor.fetchall()
for i in range(len(results3)):
outlier_sId.append(results3[i][0])
conditionL = conditionL_preH[dbN] + conditionL_mutation + conditionL_fusion + conditionL_exonSkip + conditionL_eiJunc
print '<p><h4>%s status of %s panel <small><a href="http://www.genecards.org/cgi-bin/carddisp.pl?gene=%s">[GeneCard]</a> <a href="http://www.ncbi.nlm.nih.gov/pubmed/?term=%s">[PubMed]</a></small></h4></p>' % (geneN,mycgi.db2dsetN[dbN],geneN,geneN)
# census
cursor.execute('select tumor_soma, tumor_germ, syndrome, mut_type from common.census where gene_sym="%s"' % geneN)
census = cursor.fetchall()
print('\n<font size=2> <table border="1" cellpadding="0" cellspacing="0">')
print('<tr>\n<td rowspan=2>Census</td>\n<td>tumor_soma</td>\n<td>tumor_germ</td>\n<td>syndrome</td>\n<td>mut_type</td>\n</tr>\n')
if len(census) != 0:
print('<tr>\n<td>%s</td>\n<td>%s</td>\n<td>%s</td>\n<td>%s</td>\n</tr>\n' % (census[0][0],census[0][1],census[0][2],census[0][3]))
else:
print('<tr>\n<td></td>\n<td></td>\n<td></td>\n<td></td>\n</tr>\n')
# drugbank
cursor.execute('select drug from common.drugbank where gene_sym="%s"' % geneN)
drug = [x for (x,) in cursor.fetchall()]
print('\n<font size=2> <table border="1" cellpadding="0" cellspacing="0">')
print('<br><tr>\n<td>Drug</td>\n')
print('<td>%s</td>\n</tr>\n' % ('</br>\n'.join(drug)))
# pathway
cursor.execute('select biocarta_id, biocarta_desc from common.biocarta where gene_sym="%s"' % geneN)
biocarta = cursor.fetchall()
cursor.execute('select kegg_id, kegg_desc from common.kegg where gene_sym="%s"' % geneN)
kegg = cursor.fetchall()
cursor.execute('select go_id, go_desc from common.go where gene_sym="%s"' % geneN)
go = cursor.fetchall()
print('\n<font size=2> <table border="1" cellpadding="0" cellspacing="0">')
print('<br><tr>\n<td>Biocarta</td>\n<td>KEGG</td>\n<td>GO</td>\n</tr>\n')
print('<tr><td><div style="width:100%; height:50px; overflow:auto">')
for (id,desc) in biocarta:
print('<a href="http://cgap.nci.nih.gov/Pathways/BioCarta/%s">%s</br>' % (id,desc))
print('</td>\n<td><div style="width:100%; height:50px; overflow:auto">')
for (id,desc) in kegg:
print('<a href="http://www.genome.jp/dbget-bin/www_bget?pathway+%s">%s</br>' % (id,desc))
print('</td>\n<td><div style="width:100%; height:50px; overflow:auto">')
for (id,desc) in go:
print('<a href="http://amigo.geneontology.org/cgi-bin/amigo/term_details?term=GO:%s">%s</br>' % (id,desc))
print('</div></td>\n</tr>\n')
cursor.execute('create temporary table t_id as \
select distinct samp_id from array_gene_expr union select distinct samp_id from array_cn union select distinct samp_id from splice_normal union select distinct samp_id from mutation_normal')
cursor.execute('alter table t_id add index (samp_id)')
cursor.execute('create temporary table t_expr as select * from array_gene_expr where gene_sym="%s"' % geneN)
cursor.execute('alter table t_expr add index (samp_id,gene_sym)')
cursor.execute('select samp_id from t_id left join t_expr using (samp_id) order by z_score desc')
results = cursor.fetchall()
numTotSamp = len(results)
print('\n<font size=3> <table border="1" cellpadding="0" cellspacing="0">')
# header: row1
print '<br><tr>\n<td rowspan=2><div class="verticaltext" align="middle">samples<br><sup>n=%s</sup></div></td>' % numTotSamp,
for i in range(len(conditionL)):
row = conditionL[i]
if type(row) == list:
row = row[0]
if i < len(conditionL_preH[dbN]):
if ('tag' in row[1]) or ('t_avail' in row[1]):
cursor.execute('select count(*) from %s where %s' % (row[1], row[2]))
else:
cursor.execute('select count(*) from %s where %s and gene_sym ="%s"' % (row[1], row[2], geneN))
count = cursor.fetchone()
if 'MAD' in row[4]:
print('<td rowspan=2 align="middle"><div class="verticaltext">%s, n=%s)</sup></div></td>' % (row[-1],count[0]))
else:
print('<td rowspan=2 align="middle"><div class="verticaltext">%s<br><sup>(n=%s)</sup></div></td>' % (row[-1],count[0]))
else:
if i == len(conditionL_preH[dbN]) and len(conditionL_mutation)>0:
print('<td align="middle" colspan=%s>mutation (mt/wt)</td>' % len(conditionL_mutation))
elif i == len(conditionL_preH[dbN])+len(conditionL_mutation):
print('<td align="middle" colspan=%s><a href="ircr_samp.py?dbN=%s&dType=Fusion">fusion</a></td>' % (len(conditionL_fusion),dbN))
elif i == len(conditionL_preH[dbN])+len(conditionL_mutation)+len(conditionL_fusion) and len(conditionL_exonSkip)>0:
print('<td align="middle" colspan=%s><a href="ircr_samp.py?dbN=%s&dType=ExonSkipping">exonSkip</a> (mt/wt)</td>' % (len(conditionL_exonSkip),dbN))
elif i == len(conditionL_preH[dbN])+len(conditionL_mutation)+len(conditionL_fusion)+len(conditionL_exonSkip) and len(conditionL_eiJunc)>0:
print('<td align="middle" colspan=%s><a href="ircr_samp.py?dbN=%s&dType=3pDeletion">3p deletion</a> (mt/wt)</td>' % (len(conditionL_eiJunc),dbN))
print('\n</tr>\n')
# header: row2
print '<tr>\n',
for i in range(len(conditionL)):
row = conditionL[i]
if type(row) == list:
row = row[0]
if i < len(conditionL_preH[dbN]):
pass
else:
print('<td height="100"><div class="verticaltext" align="middle">%s</div></td>' % row[-1])
print('\n</tr>\n')
for (sId,) in results:
print '<tr>',
print '<td nowrap><a href="ircr_samp.py?dbN=%s&sId=%s">%s</td>' % (dbN,sId,sId),
d_flag = None
r_flag = None
for row in conditionL:
outlier = None
if type(row) == list:
row_wt = row[1]
row = row[0]
else:
row_wt = None
(col,tbl,cnd,fmt) = row[:4]
cursor.execute('show columns from %s like "gene_sym"' % tbl)
if cursor.fetchone():
cnd += ' and gene_sym="%s"' % geneN
cursor.execute('select %s from %s where samp_id="%s" and (%s)' % (col,tbl,sId,cnd))
results2 = cursor.fetchall()
if len(results2) > 1:
print sId
raise Exception
if type(row)==tuple and row[-1]=='RSq':
if len(results2) > 0:
r_flag = True
else:
r_flag = False
if type(row)==tuple and row[-1]=='XSq':
if len(results2) > 0:
d_flag = True
else:
d_flag = False
if type(row)==tuple and row[-1]=='expr<br><sup>(MAD':
if sId in outlier_sId:
outlier = True
else:
outlier = False
if len(results2) == 1 and results2[0][0] not in (0,'0'):
value = ('%s' % fmt) % results2[0][0]
if row_wt:
(col,tbl,cnd,fmt) = row_wt[:4]
cursor.execute('select %s from %s where samp_id="%s" and %s' % (col,tbl,sId,cnd))
results_wt = cursor.fetchone()
if results_wt[0]:
count_wt = ('%s' % fmt) % results_wt[0]
else:
count_wt = 0
#here -highlight
if int(value) > (int(value)+int(count_wt)) * cutoff:
tmp = row[4].split('<br>')[0].split('/')
if len(tmp)>1 and tmp[0]==tmp[1]:
print '<td>%s<sub>/%s</sub></td>' % (value, count_wt),
else:
print '<td><font color=red><b>%s</b></font><sub>/%s</sub></td>' % (value, count_wt),
else:
print '<td>%s<sub>/%s</sub></td>' % (value, count_wt),
else:
if row[1] == 't_fusion':
html_content = ""
if row[4] == 'in':
print '<a name="%s"></a>' %sId
html_content = mycgi.compose_fusion_table(cursor,dbN, geneN, sId, "in")
print '''
<td><div class="tooltip_content">%s</div><div class="tooltip_link"><a href="#current">%s</a></div></td>
''' % (html_content, value)
else :
html_content = mycgi.compose_fusion_table(cursor,dbN, geneN, sId, "off")
print '''
<td><div class="tooltip_content">%s</div><div class="tooltip_link"><a href="#current">%s</a></div></td>
''' % (html_content, value)
elif outlier:
print '<td><font color=red><b>%s</b></font></td>' % value
else :
print '<td>%s</td>' % value
else:
#grey out
if (r_flag==False and row[1] in ('splice_skip','t_fusion','splice_eiJunc')) or (d_flag==False and row[1] in ('mutation_normal')):
print '<td bgcolor=silver></td>'
else:
print '<td></td>'
print '</tr>'
print('\n</table> </font>\n')
return
# ('Mutation', ["concat(strand,chrom,':',chrSta,'-',chrEnd) coord_hg19", "ref", "alt", "n_nReads_ref", "n_nReads_alt", "nReads_ref", "nReads_alt", \
('Mutation', ["concat(chrom,':',chrSta,'-',chrEnd) coord_hg19", "ref", "alt", "n_nReads_ref", "n_nReads_alt", "nReads_ref", "nReads_alt", \
"r_nReads_ref", "r_nReads_alt","if(count is NULL,'0',count) nIRCRb", "gene_symL", "ch_dna", "ch_aa", "ch_type", "cosmic", "mutsig", "if(census is NULL,'',census) census"], 't_mut', 'True', 'gene_symL,chrSta'),
('Mutation_CS', ["concat(chrom,':',chrSta,'-',chrEnd) coord_hg19", "ref", "alt", "n_nReads_ref", "n_nReads_alt", "nReads_ref", "nReads_alt", \
"r_nReads_ref", "r_nReads_alt","ifnull(count,'0') nIRCRb", "strand_cnt", "gene_sym", "ch_dna", "ch_aa", "ch_type", "cosmic", "mutsig", "ifnull(census,'')"], 't_mut_cs', 'True', 'gene_sym,chrSta'),
('Fusion', ["loc1 coord1", "loc2 coord2", "gene_sym1", "gene_sym2", "frame", "ftype", "exon1", "exon2", "nPos","nReads","nReads_w1","nReads_w2"], 'splice_fusion_AF', 'nPos>2', 'nPos desc'),
('ExonSkipping', ["loc1 coord1", "loc2 coord2", "gene_sym", "frame", "delExons", "exon1", "exon2", "nPos", "nReads","nReads_w1","nReads_w2"], 'splice_skip_AF', 'nPos>5 and nReads>20', 'nPos desc'),
('3pDeletion', ["loc coord_hg19", "gene_sym", "juncInfo", "juncAlias", "nReads","nReads_w"], 'splice_eiJunc_AF', 'nReads_w and nReads>50 and (nReads/nReads_w)>0.9', '(nReads/nReads_w) desc'),
('ExprOutlier',["gene_sym","expr_MAD","q25","median","q75"],'t_outlier', '(expr_MAD >= q75 + 3*(q75-q25) or expr_MAD <= q25 - 3*(q75-q25))', 'gene_sym'),
('ExprCensus',["gene_sym","z_score","rpkm"],'t_expr', 'True', 'z_score desc'),
('ExprCS',["gene_sym","rpkm"], 'rpkm_gene_expr', 'rpkm >= 10 and gene_sym in (select * from common.cs_gene)', 'gene_sym'),
('xCN', ["gene_sym","value_log2"],'xsq_cn','abs(value_log2)>=0.58','abs(value_log2) desc'),
('csCN', ["gene_sym","value_log2"], 'cs_cn', 'abs(value_log2)>=0.9', 'abs(value_log2) desc')
]
(con,cursor) = mycgi.connectDB(db=dbN)
if dbN != 'CancerSCAN':
specL = [specL[0]] + specL[2:9]
if dbN == 'ircr1':
# cursor.execute('SELECT tumor_frac FROM xsq_purity WHERE samp_id="%s"' % (old_id)) # old ID
cursor.execute('SELECT tumor_frac FROM xsq_purity WHERE samp_id="%s" or samp_id="%s"' % (old_id, new_id))
tfrac = cursor.fetchone()
if tfrac and tfrac[0] != 'ND':
specL[-1] = ('xCN', ["gene_sym","raw_value_log2","corrected_value_log2"], 'xsq_cn_tmp', 'abs(corrected_value_log2)>=0.58', 'abs(corrected_value_log2) desc')
if mode == 'samp':
# cursor.execute('create temporary table t_mut as \
# select mutation_rxsq.*,concat(tumor_soma,";",tumor_germ,";",mut_type,";",tloc_partner) census \
# from mutation_rxsq left join common.census on find_in_set(gene_sym,gene_symL) where samp_id="%s"' % sId)
def main(dbN, geneN):
(con, cursor) = mycgi.connectDB(db=dbN)
if dbN == 'ccle1':
mutation_map = {'DEL:3\'UTR':'UTR', 'SNP:3\'UTR':'UTR', 'SNP:Missense_Mutation':'MS', 'SNP:Intron':'INT', 'DEL:Frame_Shift_Del':'FS', \
'SNP:5\'UTR':'UTR', 'DEL:In_Frame_Del':'FP', 'INS:3\'UTR':'UTR', 'SNP:Splice_Site_SNP':'SS', 'SNP:Nonsense_Mutation':'NS', \
'INS:Frame_Shift_Ins':'FS', 'DEL:Intron':'INT', 'INS:Intron':'INT', 'DNP:Missense_Mutation':'MS', 'INS:Splice_Site_Ins':'SS', \
'TNP:Intron':'INT', 'DNP:Nonsense_Mutation':'NS', 'DNP:Intron':'INT', 'SNP:De_novo_Start_OutOfFrame':'SOF', 'DNP:5\'UTR':'UTR', \
'INS:5\'UTR':'UTR', 'SNP:Nonstop_Mutation':'NM', 'DNP:3\'UTR':'UTR', 'SNP:5\'Flank':'FLK', 'DEL:5\'UTR':'UTR', 'DNP:Splice_Site_DNP':'SS', \
'INS:In_Frame_Ins':'FP', 'DEL:Splice_Site_Del':'SS', 'SNP:De_novo_Start_InFrame':'SIF', 'DEL:5\'Flank':'FLK', 'INS:5\'Flank':'FLK', \
'DNP:De_novo_Start_InFrame':'SIF', 'DNP:Stop_Codon_DNP':'STC', 'INS:Stop_Codon_Ins':'STC', 'TNP:Nonsense_Mutation':'NS', \
'TNP:Missense_Mutation':'MS', 'DEL:Start_Codon_Del':'SC'}
else:
mutation_map = {'':'UK','SNP:Intron':'INT', 'SNP:5\'UTR':'UTR', 'SNP:3\'UTR':'UTR', 'SNP:RNA':'RNA', 'SNP:5\'Flank':'FLK', \
'DEL:Frame_Shift_Del':'FS', 'DEL:In_Frame_Del':'FP', 'DEL:Splice_Site':'SS', 'DEL:Translation_Start_Site':'TSS', \
'DNP:Missense_Mutation':'MS', 'DNP:Nonsense_Mutation':'NS', 'DNP:Splice_Site':'SS', \
'INS:Frame_Shift_Ins':'FS', 'INS:In_Frame_Ins':'FP', 'INS:Splice_Site':'SS', \
'SNP:Missense_Mutation':'MS', 'SNP:Nonsense_Mutation':'NS', 'SNP:Nonstop_Mutation':'NM', 'SNP:Splice_Site':'SS', 'SNP:Translation_Start_Site':'TSS', \
'Substitution - Missense':'MS', 'Substitution - Nonsense':'NS', 'Substitution - Missense,Substitution - coding silent':'MS', 'Nonstop extension':'rNS'}
# prep RNA-Seq data availability table
cursor.execute(
'create temporary table t_avail_RNASeq as select distinct samp_id from splice_normal'
)
# prep exonSkip info
cursor.execute(
'select delExons,frame,loc1,loc2, count(*) cnt from splice_skip where gene_sym = "%s" and nPos>=5 group by delExons order by count(*) desc'
% geneN)
results = cursor.fetchall()
conditionL_exonSkip = []
for (delExons, frame, loc1, loc2, cnt) in results:
if ':Y' in frame:
frame_code = 'in'
elif ':N' in frame:
frame_code = 'off'
else:
frame_code = 'utr'
conditionL_exonSkip.append( [
('nReads', 'splice_skip', 'loc1="%s" and loc2="%s" and nPos>=5' % (loc1,loc2), '%3d', '%s<br><sup>(n=%s, %s)</sup>' % (delExons.split(',')[0], cnt,frame_code),), \
# ('avg(nReads)', 'splice_normal', 'loc1="%s" or loc2="%s"' % (loc1,loc2), '%d') ])
('sum(nReads)', 'splice_normal', 'loc1="%s"' % (loc1,), '%d') ])
# prep mutation info
cursor.execute('create temporary table t_mut as \
select concat(substring(chrom,4,4),":",cast(chrSta as char),ref,">",alt) as ch_pos, ch_dna,ch_aa,ch_type,cosmic from mutation_normal \
where find_in_set("%s",gene_symL)>0 and nReads_alt>2 order by ch_type desc' %
(geneN))
cursor.execute(
'select *,count(*) cnt from t_mut group by ch_pos order by count(*) desc, cosmic desc limit 20'
)
results = cursor.fetchall()
conditionL_mutation = []
for (ch_pos, ch_dna, ch_aa, ch_type, cosmic, cnt) in results:
ch_aa = ch_aa.replace(',', '<br>')
if cosmic:
cosmic_fmt = '<font color="red">%s</font><br><sup>(n=%d, %s)</sup>'
else:
cosmic_fmt = '%s<br><sup>(n=%d, %s)</sup>'
if ch_aa:
cnd = ch_aa
elif ch_dna:
cnd = ch_dna
else:
cnd = ch_pos
conditionL_mutation.append( [
('nReads_alt', 'mutation_normal', 'nReads_alt>2 and concat(substring(chrom,4,4),":",cast(chrSta as char),ref,">",alt)="%s"' % ch_pos, '%d', cosmic_fmt % (cnd, cnt, mutation_map[ch_type])), \
('nReads_ref', 'mutation_normal', 'nReads_alt>2 and concat(substring(chrom,4,4),":",cast(chrSta as char),ref,">",alt)="%s"' % ch_pos, '%d') ])
# prep fusion table
cursor.execute('create temporary table t_fusion as \
select samp_id,locate(":Y",frame)>1 frame,count(nPos) nEvents \
from splice_fusion where nPos>=2 and (find_in_set("%s",gene_sym1) or find_in_set("%s",gene_sym2)) group by samp_id, locate(":Y",frame)>1'
% (geneN, geneN))
# prep eiJunc info
cursor.execute(
'select loc,juncAlias, count(*) cnt from splice_eiJunc where gene_sym="%s" and nReads>=10 group by loc'
% geneN)
results = cursor.fetchall()
conditionL_eiJunc = []
for (loc, juncAlias, cnt) in results:
conditionL_eiJunc.append([
('nReads', 'splice_eiJunc', 'loc="%s" and nReads>=10' % loc, '%3d',
'%s<br><sup>(n=%s)</sup>' % (juncAlias, cnt)),
('sum(nReads)', 'splice_normal', 'loc1="%s"' % (loc, ), '%d')
])
# outlier
outlier_sId = []
cursor.execute(
'select samp_id, expr_MAD from array_gene_expr_MAD mad, gene_expr_stat stat where mad.gene_sym = stat.gene_sym and (mad.expr_MAD >= stat.q75 + 3*(stat.q75 - stat.q25) or mad.expr_MAD <= stat.q25 - 3*(stat.q75 - stat.q25)) and mad.gene_sym = "%s"'
% geneN)
results3 = cursor.fetchall()
for i in range(len(results3)):
outlier_sId.append(results3[i][0])
conditionL = conditionL_preH[
dbN] + conditionL_mutation + conditionL_fusion + conditionL_exonSkip + conditionL_eiJunc
print '<p><h4>%s status of %s panel <small><a href="http://www.genecards.org/cgi-bin/carddisp.pl?gene=%s">[GeneCard]</a> <a href="http://www.ncbi.nlm.nih.gov/pubmed/?term=%s">[PubMed]</a></small></h4></p>' % (
geneN, mycgi.db2dsetN[dbN], geneN, geneN)
# census
cursor.execute(
'select tumor_soma, tumor_germ, syndrome, mut_type from common.census where gene_sym="%s"'
% geneN)
census = cursor.fetchall()
print('\n<font size=2> <table border="1" cellpadding="0" cellspacing="0">')
print(
'<tr>\n<td rowspan=2>Census</td>\n<td>tumor_soma</td>\n<td>tumor_germ</td>\n<td>syndrome</td>\n<td>mut_type</td>\n</tr>\n'
)
if len(census) != 0:
print(
'<tr>\n<td>%s</td>\n<td>%s</td>\n<td>%s</td>\n<td>%s</td>\n</tr>\n'
% (census[0][0], census[0][1], census[0][2], census[0][3]))
else:
print('<tr>\n<td></td>\n<td></td>\n<td></td>\n<td></td>\n</tr>\n')
# drugbank
cursor.execute('select drug from common.drugbank where gene_sym="%s"' %
geneN)
drug = [x for (x, ) in cursor.fetchall()]
print('\n<font size=2> <table border="1" cellpadding="0" cellspacing="0">')
print('<br><tr>\n<td>Drug</td>\n')
print('<td>%s</td>\n</tr>\n' % ('</br>\n'.join(drug)))
# pathway
cursor.execute(
'select biocarta_id, biocarta_desc from common.biocarta where gene_sym="%s"'
% geneN)
biocarta = cursor.fetchall()
cursor.execute(
'select kegg_id, kegg_desc from common.kegg where gene_sym="%s"' %
geneN)
kegg = cursor.fetchall()
cursor.execute('select go_id, go_desc from common.go where gene_sym="%s"' %
geneN)
go = cursor.fetchall()
print('\n<font size=2> <table border="1" cellpadding="0" cellspacing="0">')
print('<br><tr>\n<td>Biocarta</td>\n<td>KEGG</td>\n<td>GO</td>\n</tr>\n')
print('<tr><td><div style="width:100%; height:50px; overflow:auto">')
for (id, desc) in biocarta:
print(
'<a href="http://cgap.nci.nih.gov/Pathways/BioCarta/%s">%s</br>' %
(id, desc))
print('</td>\n<td><div style="width:100%; height:50px; overflow:auto">')
for (id, desc) in kegg:
print(
'<a href="http://www.genome.jp/dbget-bin/www_bget?pathway+%s">%s</br>'
% (id, desc))
print('</td>\n<td><div style="width:100%; height:50px; overflow:auto">')
for (id, desc) in go:
print(
'<a href="http://amigo.geneontology.org/cgi-bin/amigo/term_details?term=GO:%s">%s</br>'
% (id, desc))
print('</div></td>\n</tr>\n')
cursor.execute('create temporary table t_id as \
select distinct samp_id from array_gene_expr union select distinct samp_id from array_cn union select distinct samp_id from splice_normal union select distinct samp_id from mutation_normal'
)
cursor.execute('alter table t_id add index (samp_id)')
cursor.execute(
'create temporary table t_expr as select * from array_gene_expr where gene_sym="%s"'
% geneN)
cursor.execute('alter table t_expr add index (samp_id,gene_sym)')
cursor.execute(
'select samp_id from t_id left join t_expr using (samp_id) order by z_score desc'
)
results = cursor.fetchall()
numTotSamp = len(results)
print('\n<font size=3> <table border="1" cellpadding="0" cellspacing="0">')
# header: row1
print '<br><tr>\n<td rowspan=2><div class="verticaltext" align="middle">samples<br><sup>n=%s</sup></div></td>' % numTotSamp,
for i in range(len(conditionL)):
row = conditionL[i]
if type(row) == list:
row = row[0]
if i < len(conditionL_preH[dbN]):
if ('tag' in row[1]) or ('t_avail' in row[1]):
cursor.execute('select count(*) from %s where %s' %
(row[1], row[2]))
else:
cursor.execute(
'select count(*) from %s where %s and gene_sym ="%s"' %
(row[1], row[2], geneN))
count = cursor.fetchone()
if 'MAD' in row[4]:
print(
'<td rowspan=2 align="middle"><div class="verticaltext">%s, n=%s)</sup></div></td>'
% (row[-1], count[0]))
else:
print(
'<td rowspan=2 align="middle"><div class="verticaltext">%s<br><sup>(n=%s)</sup></div></td>'
% (row[-1], count[0]))
else:
if i == len(conditionL_preH[dbN]) and len(conditionL_mutation) > 0:
print('<td align="middle" colspan=%s>mutation (mt/wt)</td>' %
len(conditionL_mutation))
elif i == len(conditionL_preH[dbN]) + len(conditionL_mutation):
print(
'<td align="middle" colspan=%s><a href="ircr_samp.py?dbN=%s&dType=Fusion">fusion</a></td>'
% (len(conditionL_fusion), dbN))
elif i == len(
conditionL_preH[dbN]) + len(conditionL_mutation) + len(
conditionL_fusion) and len(conditionL_exonSkip) > 0:
print(
'<td align="middle" colspan=%s><a href="ircr_samp.py?dbN=%s&dType=ExonSkipping">exonSkip</a> (mt/wt)</td>'
% (len(conditionL_exonSkip), dbN))
elif i == len(conditionL_preH[dbN]) + len(
conditionL_mutation) + len(conditionL_fusion) + len(
conditionL_exonSkip) and len(conditionL_eiJunc) > 0:
print(
'<td align="middle" colspan=%s><a href="ircr_samp.py?dbN=%s&dType=3pDeletion">3p deletion</a> (mt/wt)</td>'
% (len(conditionL_eiJunc), dbN))
print('\n</tr>\n')
# header: row2
print '<tr>\n',
for i in range(len(conditionL)):
row = conditionL[i]
if type(row) == list:
row = row[0]
if i < len(conditionL_preH[dbN]):
pass
else:
print(
'<td height="100"><div class="verticaltext" align="middle">%s</div></td>'
% row[-1])
print('\n</tr>\n')
for (sId, ) in results:
print '<tr>',
print '<td nowrap><a href="ircr_samp.py?dbN=%s&sId=%s">%s</td>' % (
dbN, sId, sId),
d_flag = None
r_flag = None
for row in conditionL:
outlier = None
if type(row) == list:
row_wt = row[1]
row = row[0]
else:
row_wt = None
(col, tbl, cnd, fmt) = row[:4]
cursor.execute('show columns from %s like "gene_sym"' % tbl)
if cursor.fetchone():
cnd += ' and gene_sym="%s"' % geneN
cursor.execute('select %s from %s where samp_id="%s" and (%s)' %
(col, tbl, sId, cnd))
results2 = cursor.fetchall()
if len(results2) > 1:
print sId
raise Exception
if type(row) == tuple and row[-1] == 'RSq':
if len(results2) > 0:
r_flag = True
else:
r_flag = False
if type(row) == tuple and row[-1] == 'XSq':
if len(results2) > 0:
d_flag = True
else:
d_flag = False
if type(row) == tuple and row[-1] == 'expr<br><sup>(MAD':
if sId in outlier_sId:
outlier = True
else:
outlier = False
if len(results2) == 1 and results2[0][0] not in (0, '0'):
value = ('%s' % fmt) % results2[0][0]
if row_wt:
(col, tbl, cnd, fmt) = row_wt[:4]
cursor.execute(
'select %s from %s where samp_id="%s" and %s' %
(col, tbl, sId, cnd))
results_wt = cursor.fetchone()
if results_wt[0]:
count_wt = ('%s' % fmt) % results_wt[0]
else:
count_wt = 0
#here -highlight
if int(value) > (int(value) + int(count_wt)) * cutoff:
tmp = row[4].split('<br>')[0].split('/')
if len(tmp) > 1 and tmp[0] == tmp[1]:
print '<td>%s<sub>/%s</sub></td>' % (value,
count_wt),
else:
print '<td><font color=red><b>%s</b></font><sub>/%s</sub></td>' % (
value, count_wt),
else:
print '<td>%s<sub>/%s</sub></td>' % (value, count_wt),
else:
if row[1] == 't_fusion':
html_content = ""
if row[4] == 'in':
print '<a name="%s"></a>' % sId
html_content = mycgi.compose_fusion_table(
cursor, dbN, geneN, sId, "in")
print '''
<td><div class="tooltip_content">%s</div><div class="tooltip_link"><a href="#current">%s</a></div></td>
''' % (html_content, value)
else:
html_content = mycgi.compose_fusion_table(
cursor, dbN, geneN, sId, "off")
print '''
<td><div class="tooltip_content">%s</div><div class="tooltip_link"><a href="#current">%s</a></div></td>
''' % (html_content, value)
elif outlier:
print '<td><font color=red><b>%s</b></font></td>' % value
else:
print '<td>%s</td>' % value
else:
#grey out
if (r_flag == False and row[1]
in ('splice_skip', 't_fusion', 'splice_eiJunc')) or (
d_flag == False and row[1] in ('mutation_normal')):
print '<td bgcolor=silver></td>'
else:
print '<td></td>'
print '</tr>'
print('\n</table> </font>\n')
return
def genJson(dbN,af,sampStr,qText):
if dbN == 'tcga1':
otherTypeH = {
'RPKM': ('rpkm_gene_expr', 'rpkm'),
'CNA': ('array_cn', 'value_log2'),
'EXPR': ('array_gene_expr', 'z_score'),
'METH': ('methyl_view', 'fraction')
}
else:
otherTypeH = {
'RPKM': ('rpkm_gene_expr', 'rpkm'),
'CNA': ('array_cn', 'value_log2'),
'EXPR': ('array_gene_expr', 'z_score'),
'xCN': ('xsq_cn','value_log2')
}
qStmtL = qText.rstrip().lstrip().split('\r')
(con,cursor) = mycgi.connectDB(db=dbN)
cursor.execute('select distinct samp_id from array_gene_expr union select distinct samp_id from array_cn union select distinct samp_id from splice_normal union select distinct samp_id from mutation union select distinct samp_id from rpkm_gene_expr')
sIdL = [x for (x,) in cursor.fetchall()]
sIdL.sort()
if sampStr:
sIdL_tmp = list(sIdL)
sIdL = []
for s in sampStr.split(' '):
if s in sIdL_tmp:
sIdL.append(s)
nullL = ["" for x in sIdL]
geneIdxL = []
geneDataL = []
for i in range(len(qStmtL)):
qStmt = qStmtL[i].rstrip().lstrip()
if qStmt[0]=='(' and qStmt[-1]==')':
(qId,col,tbl,cnd) = eval(qStmt)
elif qStmt in sampInfoH:
(qId,col,tbl,cnd) = sampInfoH[qStmt]
elif qStmt.count(':')==2:
(gN,mT,mV) = qStmt.split(':')
if mT in mutTypeH:
(tbl,col,qIdF) = mutTypeH[mT]
if (tbl=='mutation_normal') or (tbl=='mutation_rsq'):
cnd = 'gene_symL="%s" and %s like "%%%s%%"' % (gN,col,mV)
qId = gN + '-' + qIdF(mV) + ':' + mT[3:]
else:
qId = gN + '-' + qIdF(mV)
cnd = 'gene_sym="%s" and %s like "%%%s%%"' % (gN,col,mV)
else:
(tbl,col) = otherTypeH[mT]
cnd = 'gene_sym="%s" and %s %s' % (gN,col,mV)
qId = gN + '-' + mT
elif qStmt.count(':')==1:
(gN, qId) = qStmt.split(':')
(tbl, col) = otherTypeH[qId]
cnd = 'gene_sym="%s"' % gN
qId = gN + '-' + qId
else:
print '<b>Input Error: %s</b><br>' % qStmt
sys.exit(1)
if tbl in afColNameH:
af_cond = 'and %s/(%s+%s) > %s' % (afColNameH[tbl][0],afColNameH[tbl][0],afColNameH[tbl][1],af)
ord_cond = '%s desc' % afColNameH[tbl][0]
af_frequency = ',' + afColNameH[tbl][0] + '/(' + afColNameH[tbl][0] + '+' + afColNameH[tbl][1] + ') as frequency'
af_numerator = ',' + afColNameH[tbl][0]
af_denominator = ',(' + afColNameH[tbl][0] + '+' + afColNameH[tbl][1] + ') as denominator'
else:
af_cond = ''
ord_cond = col
af_frequency = ''
af_numerator = ''
af_denominator = ''
count = 0
dataL = []
frequency_data = []
fraction_data = []
for sId in sIdL:
# tag = "pair_%"
# cursor.execute('select tag from sample_tag where tag like "%s" and samp_id = "%s"' % (tag,sId))
# t = cursor.fetchone()
# if t:
# pair_data.append("%s" % (t[0],))
# else :
# pair_data.append("")
cursor.execute('select %s %s %s %s from %s where samp_id="%s" and %s %s order by %s limit 1' % (col,af_frequency,af_numerator,af_denominator,tbl,sId,cnd,af_cond,ord_cond))
r = cursor.fetchone()
if r:
dataL.append("%s" % (r[0],))
count += 1
if tbl in afColNameH:
if r[1]:
fraction = '(' + str(int(r[2])) + '/' + str(int(r[3])) + ')'
fraction_data.append(fraction)
frequency_data.append(float(r[1]))
else:
fraction_data.append("")
frequency_data.append("nofreq")
else:
dataL.append("")
fraction_data.append("")
frequency_data.append("")
geneIdxL.append((qId,i))
geneDataL.append({"rppa":nullL, "hugo":qId, "mutations":dataL, "mrna":nullL, "cna":nullL, "freq":frequency_data, "fraction":fraction_data, "percent_altered":"%s (%d%s)" % (count, 100.*count/len(sIdL), '%')})
resultH = { \
"dbN":dbN,
"hugo_to_gene_index":dict(geneIdxL), \
"gene_data": geneDataL, \
"samples": dict((sIdL[i],i) for i in range(len(sIdL)))
}
jsonStr = json.dumps(resultH, sort_keys=True).replace('""','null')
#print jsonStr
jsonFile = open('/var/www/html/js/gene_data.json','w')
jsonFile.write(jsonStr)
jsonFile.close()
def main(dbN, geneN):
(con, cursor) = mycgi.connectDB(db=dbN)
# prep RNA-Seq data availability table
cursor.execute("create temporary table t_avail_RNASeq as select distinct samp_id from rpkm_gene_expr")
# prep exonSkip info
cursor.execute(
'select delExons,frame,loc1,loc2, count(*) cnt from splice_skip where gene_sym = "%s" and nPos>=5 group by delExons order by count(*) desc'
% geneN
)
results = cursor.fetchall()
conditionL_exonSkip = []
for (delExons, frame, loc1, loc2, cnt) in results:
if ":Y" in frame:
frame_code = "in"
elif ":N" in frame:
frame_code = "off"
else:
frame_code = "utr"
conditionL_exonSkip.append(
[
(
"nReads",
"splice_skip",
'loc1="%s" and loc2="%s" and nPos>=5' % (loc1, loc2),
"%3d",
"%s<br><sup>(n=%s, %s)</sup>" % (delExons.split(",")[0], cnt, frame_code),
),
# ('avg(nReads)', 'splice_normal', 'loc1="%s" or loc2="%s"' % (loc1,loc2), '%d') ])
("sum(nReads)", "splice_normal", 'loc1="%s"' % (loc1,), "%d"),
]
)
# prep mutation info
# where gene_symL like "%s%s%s" and ch_type != "synonymous_variant" and ch_type != "nc_transcript_variant,synonymous_variant" and ch_type != "intron_variant,synonymous_variant" \
if dbN == "CancerSCAN":
cursor.execute(
'create temporary table t_mut as \
select concat(substring(chrom,4,4),":",cast(chrSta as char),ref,">",alt) as ch_pos, ch_dna,ch_aa,ch_type,cosmic from mutation_cs \
where find_in_set("%s",gene_sym) > 0 and ch_type != "synonymous_variant" and ch_type != "nc_transcript_variant,synonymous_variant" and ch_type != "intron_variant,synonymous_variant" \
and ch_type != "nc_transcript_variant" and ch_type != "intron_variant,nc_transcript_variant" and ch_type != "intron_variant" and ch_type != "Substitution - coding silent"\
and nReads_alt<>2 order by ch_type desc'
% (geneN)
)
else:
cursor.execute(
'create temporary table t_mut as \
select concat(substring(chrom,4,4),":",cast(chrSta as char),ref,">",alt) as ch_pos, ch_dna,ch_aa,ch_type,cosmic from mutation_rxsq \
where find_in_set("%s",gene_symL) > 0 and ch_type != "synonymous_variant" and ch_type != "nc_transcript_variant,synonymous_variant" and ch_type != "intron_variant,synonymous_variant" \
and ch_type != "nc_transcript_variant" and ch_type != "intron_variant,nc_transcript_variant" and ch_type != "intron_variant" and ch_type != "Substitution - coding silent"\
and nReads_alt<>2 order by ch_type desc'
% (geneN)
)
cursor.execute("select *,count(*) cnt from t_mut group by ch_pos order by count(*) desc, cosmic desc")
results = cursor.fetchall()
conditionL_mutation = []
for (ch_pos, ch_dna, ch_aa, ch_type, cosmic, cnt) in results:
ch_aa = ch_aa.replace(",", "<br>")
if (dbN == "CancerSCAN" and "cosmic" in cosmic) or (dbN != "CancerSCAN" and cosmic):
cosmic_fmt = '<font color="red">%s</font><br><sup>(n=%d, %s)</sup>'
else:
cosmic_fmt = "%s<br><sup>(n=%d, %s)</sup>"
if ch_aa:
cnd = ch_aa
elif ch_dna:
cnd = ch_dna
else:
cnd = ch_pos
if dbN == "CancerSCAN":
conditionL_mutation.append(
[
(
"nReads_alt",
"mutation_cs",
'nReads_alt<>2 and concat(substring(chrom,4,4),":",cast(chrSta as char),ref,">",alt)="%s"'
% ch_pos,
"%d",
cosmic_fmt % (cnd, cnt, mutation_map(ch_type, dbN)),
),
(
"nReads_ref",
"mutation_cs",
'nReads_alt<>2 and concat(substring(chrom,4,4),":",cast(chrSta as char),ref,">",alt)="%s"'
% ch_pos,
"%d",
),
]
)
else:
conditionL_mutation.append(
[
(
"nReads_alt,r_nReads_alt",
"mutation_rxsq",
'nReads_alt<>2 and concat(substring(chrom,4,4),":",cast(chrSta as char),ref,">",alt)="%s"'
% ch_pos,
"%d",
cosmic_fmt % (cnd, cnt, mutation_map(ch_type, dbN)),
),
(
"nReads_ref,r_nReads_ref",
"mutation_rxsq",
'nReads_alt<>2 and concat(substring(chrom,4,4),":",cast(chrSta as char),ref,">",alt)="%s"'
% ch_pos,
"%d",
),
]
)
# prep fusion table
cursor.execute(
'create temporary table t_fusion as \
select samp_id,locate(":Y",frame)>1 frame,count(nPos) nEvents \
from splice_fusion where nPos>=2 and (find_in_set("%s",gene_sym1) or find_in_set("%s",gene_sym2)) group by samp_id, locate(":Y",frame)>1'
% (geneN, geneN)
)
# prep eiJunc info
cursor.execute(
'select loc,juncAlias, count(*) cnt from splice_eiJunc where gene_sym="%s" and nReads>=10 group by loc' % geneN
)
results = cursor.fetchall()
conditionL_eiJunc = []
for (loc, juncAlias, cnt) in results:
conditionL_eiJunc.append(
[
(
"nReads",
"splice_eiJunc",
'loc="%s" and nReads>=10' % loc,
"%3d",
"%s<br><sup>(n=%s)</sup>" % (juncAlias, cnt),
),
("sum(nReads)", "splice_normal", 'loc1="%s"' % (loc,), "%d"),
]
)
# outlier
outlier_sId = []
if dbN in ["ircr1", "tcga1", "ccle1"]:
cursor.execute(
'select samp_id, expr_MAD from array_gene_expr_MAD mad, gene_expr_stat stat where mad.gene_sym = stat.gene_sym and (mad.expr_MAD >= stat.q75 + 3*(stat.q75 - stat.q25) or mad.expr_MAD <= stat.q25 - 3*(stat.q75 - stat.q25)) and mad.gene_sym = "%s"'
% geneN
)
results3 = cursor.fetchall()
for i in range(len(results3)):
outlier_sId.append(results3[i][0])
## corrected CN value available?
cncorr_sId = []
if dbN in ["ircr1"]:
cursor.execute('select distinct samp_id from xsq_purity where tumor_frac != "ND"')
resultsCorr = cursor.fetchall()
for i in range(len(resultsCorr)):
cncorr_sId.append(resultsCorr[i][0])
normal_sId = []
if dbN in ["ircr1"]:
cursor.execute("select distinct samp_id from xsq_purity")
tttt = cursor.fetchall()
for i in range(len(tttt)):
normal_sId.append(tttt[i][0])
conditionL = (
conditionL_preH[dbN] + conditionL_mutation + conditionL_fusion + conditionL_exonSkip + conditionL_eiJunc
)
print '<p><h4>%s status of %s panel <small><a href="http://www.genecards.org/cgi-bin/carddisp.pl?gene=%s">[GeneCard]</a> <a href="http://www.ncbi.nlm.nih.gov/pubmed/?term=%s">[PubMed]</a></small></h4></p>' % (
geneN,
mycgi.db2dsetN(dbN),
geneN,
geneN,
)
# census
cursor.execute('select tumor_soma, tumor_germ, syndrome, mut_type from common.census where gene_sym="%s"' % geneN)
census = cursor.fetchall()
print ('\n<font size=2> <table border="1" cellpadding="0" cellspacing="0">')
print (
"<tr>\n<td rowspan=2>Census</td>\n<td>tumor_soma</td>\n<td>tumor_germ</td>\n<td>syndrome</td>\n<td>mut_type</td>\n</tr>\n"
)
if len(census) != 0:
print (
"<tr>\n<td>%s</td>\n<td>%s</td>\n<td>%s</td>\n<td>%s</td>\n</tr>\n"
% (census[0][0], census[0][1], census[0][2], census[0][3])
)
else:
print ("<tr>\n<td></td>\n<td></td>\n<td></td>\n<td></td>\n</tr>\n")
# drugbank
cursor.execute('select drug from common.drugbank where gene_sym="%s"' % geneN)
drug = [x for (x,) in cursor.fetchall()]
print ('\n<font size=2> <table border="1" cellpadding="0" cellspacing="0">')
print ("<br><tr>\n<td>Drug</td>\n")
print ("<td>%s</td>\n</tr>\n" % ("</br>\n".join(drug)))
# pathway
cursor.execute('select biocarta_id, biocarta_desc from common.biocarta where gene_sym="%s"' % geneN)
biocarta = cursor.fetchall()
cursor.execute('select kegg_id, kegg_desc from common.kegg where gene_sym="%s"' % geneN)
kegg = cursor.fetchall()
cursor.execute('select go_id, go_desc from common.go where gene_sym="%s"' % geneN)
go = cursor.fetchall()
print ('\n<font size=2> <table border="1" cellpadding="0" cellspacing="0">')
print ("<br><tr>\n<td>Biocarta</td>\n<td>KEGG</td>\n<td>GO</td>\n</tr>\n")
print ('<tr><td><div style="width:100%; height:50px; overflow:auto">')
for (id, desc) in biocarta:
print ('<a href="http://cgap.nci.nih.gov/Pathways/BioCarta/%s">%s</br>' % (id, desc))
print ('</td>\n<td><div style="width:100%; height:50px; overflow:auto">')
for (id, desc) in kegg:
print ('<a href="http://www.genome.jp/dbget-bin/www_bget?pathway+%s">%s</br>' % (id, desc))
print ('</td>\n<td><div style="width:100%; height:50px; overflow:auto">')
for (id, desc) in go:
print ('<a href="http://amigo.geneontology.org/cgi-bin/amigo/term_details?term=GO:%s">%s</br>' % (id, desc))
print ("</div></td>\n</tr>\n")
if dbN == "CancerSCAN":
cursor.execute(
"""CREATE TEMPORARY TABLE t_id AS SELECT DISTINCT samp_id FROM mutation_cs UNION SELECT DISTINCT samp_id FROM cs_cn"""
)
elif dbN == "tcga1":
cursor.execute(
"create temporary table t_id as \
select distinct samp_id from array_gene_expr union select distinct samp_id from array_cn union select distinct samp_id from splice_normal union select distinct samp_id from mutation_rxsq union select distinct samp_id from rpkm_gene_expr"
)
else:
cursor.execute(
"create temporary table t_id as \
select distinct samp_id from array_gene_expr union select distinct samp_id from array_cn union select distinct samp_id from splice_normal union select distinct samp_id from mutation_rxsq union select distinct samp_id from rpkm_gene_expr union select distinct samp_id from xsq_cn"
)
cursor.execute("alter table t_id add index (samp_id)")
cursor.execute('create temporary table t_expr as select * from array_gene_expr where gene_sym="%s"' % geneN)
# cursor.execute('create temporary table t_cn as select * from xsq_cn where gene_sym="%s"' % geneN)
cursor.execute("alter table t_expr add index (samp_id,gene_sym)")
# cursor.execute('alter table t_cn add index (samp_id,gene_sym)')
cursor.execute("select samp_id from t_id left join t_expr using (samp_id) order by z_score desc")
# cursor.execute('select samp_id from t_id left join t_cn using (samp_id) order by value_log2 desc')
results = cursor.fetchall()
numTotSamp = len(results)
print ('\n<font size=3> <table border="1" cellpadding="0" cellspacing="0">')
# header: row1
print '<br><tr>\n<td rowspan=2><div class="verticaltext" align="middle">samples<br><sup>n=%s</sup></div></td>' % numTotSamp,
for i in range(len(conditionL)):
row = conditionL[i]
if type(row) == list:
row = row[0]
if i < len(conditionL_preH[dbN]):
if ("tag" in row[1]) or ("t_avail" in row[1]) or ("subtype" in row[1]) or ("purity" in row[1]):
cursor.execute("select count(*) from %s where %s" % (row[1], row[2]))
elif "loh" in row[1]:
cursor.execute(
"select count(distinct samp_id) from xsq_purity"
) # of samples for which purity pipeline was applied = # of samples with matched blood exome
else:
cursor.execute('select count(*) from %s where %s and gene_sym ="%s"' % (row[1], row[2], geneN))
count = cursor.fetchone()
if "MAD" in row[4]:
print (
'<td rowspan=2 align="middle"><div class="verticaltext">%s, n=%s)</sup></div></td>'
% (row[-1], count[0])
)
else:
print (
'<td rowspan=2 align="middle"><div class="verticaltext">%s<br><sup>(n=%s)</sup></div></td>'
% (row[-1], count[0])
)
else:
if i == len(conditionL_preH[dbN]) and len(conditionL_mutation) > 0:
print ('<td align="middle" colspan=%s>mutation (mt/wt)</td>' % len(conditionL_mutation))
elif i == len(conditionL_preH[dbN]) + len(conditionL_mutation):
print (
'<td align="middle" colspan=%s><a href="ircr_samp.py?dbN=%s&dType=Fusion">fusion</a></td>'
% (len(conditionL_fusion), dbN)
)
elif (
i == len(conditionL_preH[dbN]) + len(conditionL_mutation) + len(conditionL_fusion)
and len(conditionL_exonSkip) > 0
):
print (
'<td align="middle" colspan=%s><a href="ircr_samp.py?dbN=%s&dType=ExonSkipping">exonSkip</a> (mt/wt)</td>'
% (len(conditionL_exonSkip), dbN)
)
elif (
i
== len(conditionL_preH[dbN])
+ len(conditionL_mutation)
+ len(conditionL_fusion)
+ len(conditionL_exonSkip)
and len(conditionL_eiJunc) > 0
):
print (
'<td align="middle" colspan=%s><a href="ircr_samp.py?dbN=%s&dType=3pDeletion">3p deletion</a> (mt/wt)</td>'
% (len(conditionL_eiJunc), dbN)
)
print ("\n</tr>\n")
# header: row2
print "<tr>\n",
for i in range(len(conditionL)):
row = conditionL[i]
if type(row) == list:
row = row[0]
if i < len(conditionL_preH[dbN]):
pass
else:
print ('<td height="100"><div class="verticaltext" align="middle">%s</div></td>' % row[-1])
print ("\n</tr>\n")
for (sId,) in results:
print "<tr>",
# print '<td nowrap><a href="ircr_samp.py?dbN=%s&sId=%s">%s</td>' % (dbN,sId,sId),
new_id = mycgi.get_new_id(sId)
print '<td nowrap><a href="ircr_samp.py?dbN=%s&sId=%s">%s</td>' % (dbN, new_id, new_id),
d_flag = None
r_flag = None
for row in conditionL:
outlier = None
if type(row) == list:
row_wt = row[1]
row = row[0]
else:
row_wt = None
(col, tbl, cnd, fmt) = row[:4]
cursor.execute('show columns from %s like "gene_sym"' % tbl)
if cursor.fetchone():
cnd += ' and gene_sym="%s"' % geneN
if tbl == "mutation_rxsq":
cursor.execute('select distinct %s from %s where samp_id="%s" and (%s)' % (col, tbl, sId, cnd))
else:
cursor.execute('select %s from %s where samp_id="%s" and (%s)' % (col, tbl, sId, cnd))
results2 = cursor.fetchall()
if len(results2) > 1:
print sId
raise Exception
if type(row) == tuple and row[-1] == "RSq":
if len(results2) > 0:
r_flag = True
else:
r_flag = False
if type(row) == tuple and row[-1] == "XSq":
if len(results2) > 0:
d_flag = True
else:
d_flag = False
if sId not in normal_sId: # flag for matched normal
p_flag = False
else:
p_flag = True
if type(row) == tuple and row[-1] == "expr<br><sup>(MAD":
if sId in outlier_sId:
outlier = True
else:
outlier = False
if len(results2) == 1: # and results2[0][0] not in (0,'0'):
value = ("%s" % fmt) % results2[0][0]
if len(results2[0]) >= 2:
n_value = ("%s" % fmt) % results2[0][1]
else:
n_value = 0
if row_wt:
(col, tbl, cnd, fmt) = row_wt[:4]
if tbl == "mutation_rxsq":
cursor.execute('select distinct %s from %s where samp_id="%s" and %s' % (col, tbl, sId, cnd))
else:
cursor.execute('select %s from %s where samp_id="%s" and %s' % (col, tbl, sId, cnd))
results_wt = cursor.fetchone()
n_count_wt = 0
if results_wt[0]:
count_wt = ("%s" % fmt) % results_wt[0]
else:
count_wt = 0
if len(results_wt) >= 2:
count_wt = ("%s" % fmt) % results_wt[0]
n_count_wt = ("%s" % fmt) % results_wt[1]
if row[1] == "mutation_rxsq":
# if int(n_value)!=0 and int(n_count_wt)!=0 and int(value)!=0 and int(count_wt)!=0:
if int(n_value) != 0 and int(value) != 0:
if (
int(value) > (int(value) + int(count_wt)) * cutoff
and int(n_value) > (int(n_value) + int(n_count_wt)) * cutoff
):
print "<td><font color=red><b>%s</b></font><sub>/%s</sub>,<font color=468847><b>%s</b><sub>/%s</sub></font></td>" % (
value,
count_wt,
n_value,
n_count_wt,
),
elif int(n_value) > (int(n_value) + int(n_count_wt)) * cutoff:
print "<td>%s<sub>/%s</sub>,<font color=468847><b>%s</b><sub>/%s</sub></font></td>" % (
value,
count_wt,
n_value,
n_count_wt,
),
elif int(value) > (int(value) + int(count_wt)) * cutoff:
print "<td><font color=red><b>%s</b></font><sub>/%s</sub>,<font color=468847>%s<sub>/%s</sub></font></td>" % (
value,
count_wt,
n_value,
n_count_wt,
),
else:
print "<td>%s<sub>/%s</sub>,<font color=468847>%s<sub>/%s</sub></font></td>" % (
value,
count_wt,
n_value,
n_count_wt,
),
elif int(value) == 0 and int(count_wt) == 0: ## no exome
if int(n_value) > (int(n_value) + int(n_count_wt)) * cutoff:
print "<td><font color=468847><b>%s</b><sub>/%s</sub></font></td>" % (
n_value,
n_count_wt,
),
else:
print "<td><font color=468847>%s<sub>/%s</sub></font></td>" % (n_value, n_count_wt),
elif int(n_value) == 0 and int(n_count_wt) == 0: ## no normal
if int(value) > (int(value) + int(count_wt)) * cutoff:
print "<td><font color=red><b>%s</b></font><sub>/%s</sub></td>" % (value, count_wt),
else:
print "<td>%s<sub>/%s</sub></td>" % (value, count_wt),
else:
if int(value) > (int(value) + int(count_wt)) * cutoff:
tmp = row[4].split("<br>")[0].split("/")
if len(tmp) > 1 and tmp[0] == tmp[1]:
print "<td>%s<sub>/%s</sub></td>" % (value, count_wt),
else:
print "<td><font color=red><b>%s</b></font><sub>/%s</sub></td>" % (value, count_wt),
else:
print "<td>%s<sub>/%s</sub></td>" % (value, count_wt),
else:
if row[1] == "t_fusion":
html_content = ""
if row[4] == "in":
print '<a name="%s"></a>' % sId
html_content = mycgi.compose_fusion_table(cursor, dbN, geneN, sId, "in")
print """
<td><div class="tooltip_content">%s</div><div class="tooltip_link"><a href="#current">%s</a></div></td>
""" % (
html_content,
value,
)
else:
html_content = mycgi.compose_fusion_table(cursor, dbN, geneN, sId, "off")
print """
<td><div class="tooltip_content">%s</div><div class="tooltip_link"><a href="#current">%s</a></div></td>
""" % (
html_content,
value,
)
elif outlier:
print "<td><font color=red><b>%s</b></font></td>" % value
elif tbl == "array_cn" or tbl == "cs_cn": ##aCGH
(cl, bL) = cn_format(value)
print "<td><font color=%s>%s%s%s</font></td>" % (cl, bL[0], value, bL[1])
elif tbl == "xsq_cn":
(cl1, bL1) = cn_format(value)
if sId in cncorr_sId:
cursor.execute('select %s from xsq_cn_corr where samp_id="%s" and (%s)' % (col, sId, cnd))
val_corr = ("%s" % fmt) % cursor.fetchone()[0]
(cl2, bL2) = cn_format(val_corr)
print "<td><font color=%s>%s%s%s</font>,<font color=%s>%s%s%s</font></td>" % (
cl1,
bL1[0],
value,
bL1[1],
cl2,
bL2[0],
val_corr,
bL2[1],
)
else:
print "<td><font color=%s>%s%s%s</font></td>" % (cl1, bL1[0], value, bL1[1])
else:
print "<td>%s</td>" % value
else:
# grey out
if (
(r_flag == False and row[1] in ("splice_skip", "t_fusion", "splice_eiJunc", "rpkm_gene_expr"))
or (r_flag == False and d_flag == False and row[1] in ("mutation_rxsq"))
or ((d_flag == False or p_flag == False) and row[1] in ("xsq_loh"))
):
print "<td bgcolor=silver></td>"
else:
print "<td></td>"
print "</tr>"
print ("\n</table> </font>\n")
return
def genJson(dbN, af, qText):
qStmtL = qText.rstrip().lstrip().split('\r')
(con, cursor) = mycgi.connectDB(db=dbN)
tag = "pair_R%"
cursor.execute(
'select distinct samp_id from sample_tag where tag like "%s" and tag not like "%%,%%"'
% tag)
sIdL = [x for (x, ) in cursor.fetchall()]
sIdL.sort()
nullL = ["" for x in sIdL]
geneIdxL = []
geneDataL = []
for i in range(len(qStmtL)):
qStmt = qStmtL[i].rstrip().lstrip()
if qStmt[0] == '(' and qStmt[-1] == ')':
(qId, col, tbl, cnd) = eval(qStmt)
elif qStmt in sampInfoH:
(qId, col, tbl, cnd) = sampInfoH[qStmt]
elif qStmt.count(':') == 2:
(gN, mT, mV) = qStmt.split(':')
(tbl, col, qIdF) = mutTypeH[mT]
if (tbl == 'mutation_normal') or (tbl == 'mutation_rsq'):
qId = gN + '-' + qIdF(mV) + ':' + mT[3:]
cnd = 'gene_symL="%s" and %s like "%%%s%%"' % (gN, col, mV)
else:
qId = gN + '-' + qIdF(mV)
cnd = 'gene_sym="%s" and %s like "%%%s%%"' % (gN, col, mV)
elif qStmt.count(':') == 1:
(gN, qId) = qStmt.split(':')
(tbl, col) = otherTypeH[qId]
if 'PATH' in qId:
cnd = 'pathway="%s"' % gN
elif 'TYPE' in qId:
cnd = '%s' % gN
col = gN
else:
cnd = 'gene_sym="%s"' % gN
qId = gN + '-' + qId
else:
print '<b>Input Error: %s</b><br>' % qStmt
sys.exit(1)
if tbl in afColNameH:
af_cond = 'and %s/(%s+%s) > %s' % (
afColNameH[tbl][0], afColNameH[tbl][0], afColNameH[tbl][1], af)
ord_cond = '%s desc' % afColNameH[tbl][0]
af_frequency = ',' + afColNameH[tbl][0] + '/(' + afColNameH[tbl][
0] + '+' + afColNameH[tbl][1] + ') as frequency'
af_numerator = ',' + afColNameH[tbl][0]
af_denominator = ',(' + afColNameH[tbl][0] + '+' + afColNameH[tbl][
1] + ') as denominator'
else:
af_cond = ''
ord_cond = col
af_frequency = ''
af_numerator = ''
af_denominator = ''
count = 0
dataL = []
frequency_data = []
pair_data = []
fraction_data = []
for sId in sIdL:
pair_fraction = ''
count_flag = 0
tag = "pair_P:"
cursor.execute(
'select samp_id from sample_tag where tag like "%s%s"' %
(tag, sId))
t = cursor.fetchone()
pair_id = "%s" % (t[0], )
cursor.execute(
'select %s %s %s %s from %s where samp_id="%s" and %s %s order by %s limit 1'
% (col, af_frequency, af_numerator, af_denominator, tbl,
pair_id, cnd, af_cond, ord_cond))
p = cursor.fetchone()
if p:
count += 1
count_flag = 1
if tbl in afColNameH:
if p[1]:
pair_freq = pair_id + ":" + str(float(p[1]))
pair_data.append(pair_freq)
pair_fraction += str(int(p[2])) + '/' + str(int(p[3]))
elif (tbl in 'rpkm_gene_expr') or (tbl in 'array_cn') or (
tbl in 'array_pathway'
) or (tbl in 'rpkm_pathway') or (tbl in 'array_gene_expr') or (
tbl in 'array_subtype') or (tbl in 'rpkm_subtype'):
pair_value = pair_id + ":" + str(float(p[0]))
pair_data.append(pair_value)
else:
pair_d = pair_id + ":nofreq"
pair_data.append(pair_d)
pair_fraction = ':'
else:
if tbl in afColNameH:
if tbl in "mutation_normal":
tag = "Xseq_%"
cursor.execute(
'select samp_id from sample_tag where samp_id = "%s" and tag like "%s"'
% (pair_id, tag))
x = cursor.fetchone()
if x:
pair_flag = pair_id + ":" + str(0)
else:
pair_flag = pair_id + ":null"
else:
cursor.execute(
'select samp_id from splice_normal where samp_id = "%s" limit 1'
% pair_id)
m = cursor.fetchone()
if m:
pair_flag = pair_id + ":" + str(0)
else:
pair_flag = pair_id + ":null"
else:
pair_flag = pair_id + ":null"
pair_data.append(pair_flag)
cursor.execute(
'select %s %s %s %s from %s where samp_id="%s" and %s %s order by %s limit 1'
% (col, af_frequency, af_numerator, af_denominator, tbl, sId,
cnd, af_cond, ord_cond))
r = cursor.fetchone()
if r:
dataL.append("%s" % (r[0], ))
if count_flag == 0:
count += 1
if tbl in afColNameH:
if r[1]:
fraction = str(int(r[2])) + "/" + str(int(r[3]))
fraction_data.append(fraction + ":" + pair_fraction)
frequency_data.append(float(r[1]))
else:
fraction_data.append("")
frequency_data.append('nofreq')
else:
if tbl in afColNameH:
if tbl in "mutation_normal":
tag = "Xseq_%"
cursor.execute(
'select samp_id from sample_tag where samp_id ="%s" and tag like "%s"'
% (sId, tag))
x = cursor.fetchone()
if x:
data_flag = qId
else:
data_flag = ""
else:
cursor.execute(
'select samp_id from splice_normal where samp_id = "%s" limit 1'
% sId)
m = cursor.fetchone()
if m:
data_flag = qId
else:
data_flag = ""
else:
data_flag = ""
dataL.append(data_flag)
fraction_data.append(pair_fraction)
frequency_data.append(0)
geneIdxL.append((qId, i))
if 'RPKM' in qId:
for i in range(len(pair_data)):
try:
dataL[i] = str(
log2(float(pair_data[i].split(':')[1]) + 1) -
log2(float(dataL[i]) + 1))
except:
dataL[i] = ""
elif 'PATH' in qId or 'TYPE' in qId or 'EXPR' in qId or 'CNA' in qId:
for i in range(len(pair_data)):
try:
dataL[i] = str(
float(pair_data[i].split(':')[1]) - float(dataL[i]))
except:
dataL[i] = ""
else:
for i in range(len(pair_data)):
try:
frequency_data[i] = str(
log2(float(pair_data[i].split(':')[1]) + 0.01) -
log2(float(frequency_data[i]) + 0.01))
except:
frequency_data[i] = ""
dataL[i] = ""
geneDataL.append({
"rppa":
nullL,
"hugo":
qId,
"mutations":
dataL,
"mrna":
nullL,
"cna":
nullL,
"freq":
frequency_data,
"fraction":
fraction_data,
"percent_altered":
"%s (%d%s)" % (count, 100. * count / len(sIdL), '%')
})
resultH = { \
"dbN":dbN,
"af":af,
"hugo_to_gene_index":dict(geneIdxL), \
"gene_data": geneDataL, \
"samples": dict((sIdL[i],i) for i in range(len(sIdL)))
}
jsonStr = json.dumps(resultH, sort_keys=True).replace('""', 'null')
#print jsonStr
jsonFile = open('/var/www/html/js/gene_data.json', 'w')
jsonFile.write(jsonStr)
jsonFile.close()
def genJson(dbN,af,sampStr,qText):
qStmtL = qText.rstrip().lstrip().split('\r')
(con,cursor) = mycgi.connectDB(db=dbN)
tag = "pair_R%"
cursor.execute('select distinct samp_id from sample_tag where tag like "%s" and tag not like "%%,%%"' % tag)
sIdL = [x for (x,) in cursor.fetchall()]
sIdL.sort()
if sampStr:
sIdL_tmp = list(sIdL)
sIdL = []
for s in sampStr.split(' '):
if s in sIdL_tmp:
sIdL.append(s)
nullL = ["" for x in sIdL]
geneIdxL = []
geneDataL = []
for i in range(len(qStmtL)):
qStmt = qStmtL[i].rstrip().lstrip()
if qStmt[0]=='(' and qStmt[-1]==')':
(qId,col,tbl,cnd) = eval(qStmt)
elif qStmt in sampInfoH:
(qId,col,tbl,cnd) = sampInfoH[qStmt]
elif qStmt.count(':')==2:
(gN,mT,mV) = qStmt.split(':')
(tbl,col,qIdF) = mutTypeH[mT]
if (tbl=='mutation_normal') or (tbl=='mutation_rsq'):
qId = gN + '-' + qIdF(mV) + ':' + mT[3:]
cnd = 'gene_symL="%s" and %s like "%%%s%%"' % (gN,col,mV)
else:
qId = gN + '-' + qIdF(mV)
cnd = 'gene_sym="%s" and %s like "%%%s%%"' % (gN,col,mV)
elif qStmt.count(':')==1:
(gN, qId) = qStmt.split(':')
(tbl,col) = otherTypeH[qId]
if 'PATH' in qId:
cnd='pathway="%s"' % gN
elif 'TYPE' in qId:
cnd='%s' % gN
col = gN
else:
cnd = 'gene_sym="%s"' % gN
qId = gN + '-' +qId
else:
print '<b>Input Error: %s</b><br>' % qStmt
sys.exit(1)
if tbl in afColNameH:
af_cond = 'and %s/(%s+%s) > %s' % (afColNameH[tbl][0],afColNameH[tbl][0],afColNameH[tbl][1],af)
ord_cond = '%s desc' % afColNameH[tbl][0]
af_frequency = ',' + afColNameH[tbl][0] + '/(' + afColNameH[tbl][0] + '+' + afColNameH[tbl][1] + ') as frequency'
af_numerator = ',' + afColNameH[tbl][0]
af_denominator = ',(' + afColNameH[tbl][0] + '+' + afColNameH[tbl][1] + ') as denominator'
else:
af_cond = ''
ord_cond = col
af_frequency = ''
af_numerator = ''
af_denominator = ''
count = 0
dataL = []
frequency_data = []
pair_data = []
fraction_data = []
for sId in sIdL:
pair_fraction = ''
count_flag = 0
tag = "pair_R%"
cursor.execute('select tag from sample_tag where samp_id="%s" and tag like "%s"' % (sId, tag))
t = cursor.fetchone()
pair_id = "%s" % (t[0].split(':')[1],)
cursor.execute('select %s %s %s %s from %s where samp_id="%s" and %s %s order by %s limit 1' % (col,af_frequency,af_numerator,af_denominator,tbl,pair_id,cnd,af_cond,ord_cond))
p = cursor.fetchone()
if p:
count += 1
count_flag = 1
if tbl in afColNameH:
if p[1]:
pair_freq = pair_id + ":" + str(float(p[1]))
pair_data.append(pair_freq)
pair_fraction += str(int(p[2])) + '/' + str(int(p[3]))
elif (tbl in 'rpkm_gene_expr') or (tbl in 'array_cn') or (tbl in 'array_pathway') or (tbl in 'rpkm_pathway') or (tbl in 'array_gene_expr') or (tbl in 'array_subtype') or (tbl in 'rpkm_subtype') or (tbl in 'xsq_cn'):
pair_value = pair_id + ":" + str(float(p[0]))
pair_data.append(pair_value)
else:
pair_d = pair_id +":nofreq"
pair_data.append(pair_d)
pair_fraction = ':'
else:
if tbl in afColNameH:
if tbl in "mutation_normal":
tag = "Xseq_%"
cursor.execute('select samp_id from sample_tag where samp_id = "%s" and tag like "%s"' % (pair_id, tag))
x = cursor.fetchone()
if x:
pair_flag = pair_id + ":" + str(0);
else:
pair_flag = pair_id + ":null"
else:
cursor.execute('select samp_id from splice_normal where samp_id = "%s" limit 1' % pair_id)
m = cursor.fetchone()
if m:
pair_flag = pair_id + ":" + str(0);
else:
pair_flag = pair_id + ":null"
else:
pair_flag = pair_id + ":null"
pair_data.append(pair_flag)
cursor.execute('select %s %s %s %s from %s where samp_id="%s" and %s %s order by %s limit 1' % (col,af_frequency,af_numerator,af_denominator,tbl,sId,cnd,af_cond,ord_cond))
r = cursor.fetchone()
if r:
dataL.append("%s" % (r[0],))
if count_flag == 0:
count += 1
if tbl in afColNameH:
if r[1]:
fraction = str(int(r[2])) + "/" + str(int(r[3]))
fraction_data.append(fraction+ ":" +pair_fraction)
frequency_data.append(float(r[1]))
else:
fraction_data.append("")
frequency_data.append('nofreq')
else:
if tbl in afColNameH:
if tbl in "mutation_normal":
tag = "Xseq_%"
cursor.execute('select samp_id from sample_tag where samp_id ="%s" and tag like "%s"' % (sId, tag))
x = cursor.fetchone()
if x:
data_flag = qId
else:
data_flag = ""
else:
cursor.execute('select samp_id from splice_normal where samp_id = "%s" limit 1' % sId)
m = cursor.fetchone()
if m:
data_flag = qId
else:
data_flag = ""
else:
data_flag = ""
dataL.append(data_flag)
fraction_data.append(pair_fraction)
frequency_data.append(0)
geneIdxL.append((qId,i))
geneDataL.append({"rppa":nullL, "hugo":qId, "mutations":dataL, "mrna":nullL, "cna":nullL, "freq":frequency_data, "pair":pair_data, "fraction":fraction_data, "percent_altered":"%s (%d%s)" % (count, 100.*count/len(sIdL), '%')})
resultH = { \
"dbN":dbN,
"af":af,
"hugo_to_gene_index":dict(geneIdxL), \
"gene_data": geneDataL, \
"samples": dict((sIdL[i],i) for i in range(len(sIdL)))
}
jsonStr = json.dumps(resultH, sort_keys=True).replace('""','null')
#print jsonStr
jsonFile = open('/var/www/html/js/gene_data.json','w')
jsonFile.write(jsonStr)
jsonFile.close()
