from trnlib.omeORM import *
from pandas import *

from numpy import array
from sqlalchemy import func

from IPython.core.display import HTML

ome = Session()

s12 = read_table('S12_delta_arcA.txt', index_col='b number')

HTML(s12.to_html())

aa = AllAnalysis
ome.query(aa).filter(and_(or_(aa.target1 == 'delta-arcA', aa.target2 == 'delta-arcA'),\
                          or_(aa.target1 == 'wt', aa.target2 == 'wt'), aa.bnum == 'b1611',\
                          aa.eacceptor1 == 'anaerobic')).all()

aa = AllAnalysis
ome.query(aa).filter(and_(or_(aa.target1 == 'delta-arcA', aa.target2 == 'delta-arcA'),\
                          or_(aa.target1 == 'wt', aa.target2 == 'wt'), aa.fdr < .05, aa.gene_name.ilike('yfb%'),\
                          or_(and_(aa.eacceptor1 == 'anaerobic', aa.eacceptor2 == 'anaerobic'),\
                              and_(aa.eacceptor1 == 'NO3', aa.eacceptor2 == 'NO3'),\
                              and_(aa.eacceptor1 == 'O2', aa.eacceptor2 == 'O2')))).all()

array([x.value for x in ome.query(ExpressionData).filter_by(bnum='b3327').all()]).mean()

unmapped = {
                'b4139':{'tu':'aspA', 'mistake':True, 'reason': 'dcuA is diff expressed in same operon'},\
                'b0356':{'tu':'frmRAB', 'mistake':True, 'reason': 'have frmB under nitrate'},\
                'b1223':{'tu':'narK', 'mistake':False, 'reason': 'have narX nearby but not narK or narG'},\
                'b3042':{'tu':'yqiC', 'mistake':False, 'reason': 'have yqiAB but not on same operon'},\
                'b4057':{'tu':'yjbQR', 'mistake':True, 'reason': 'have yjbQ, first gene in operon'},\
                'b0189':{'tu':'yaeP-rof', 'mistake':False, 'reason': 'have yaeB on the other strand'},\
                'b3865':{'tu':'yihA', 'mistake':False, 'reason': 'have csrC on the other strand'},\
                'b1874':{'tu':'cutC', 'mistake':False, 'reason': 'have cutA on a different operon'},\
                'b3023':{'tu':'ygiV', 'mistake':True, 'reason': 'have ygiW which is first gene in operon'},\
                'b1273':{'tu':'yciN', 'mistake':False, 'reason': 'have yci genes on different operons'},\
                'b1224':{'tu':'narGHJI', 'mistake':True, 'reason': 'have narHJI in the same operon'},\
                'b1143':{'tu':'ymfI', 'mistake':False, 'reason': 'have ymfK nearby but different strand'},\
                'b2290':{'tu':'yfbQ', 'mistake':False, 'reason': 'have yfb genes on different operons'}}


len(unmapped.keys())

aa = AllTUAnalysis
i = 0
for bnum in s12.index:
    if not ome.query(aa).filter(and_(or_(aa.target1 == 'delta-arcA', aa.target2 == 'delta-arcA'),\
                          or_(aa.target1 == 'wt', aa.target2 == 'wt'), aa.fdr < .05, aa.bnum == bnum,\
                          or_(and_(aa.eacceptor1 == 'anaerobic', aa.eacceptor2 == 'anaerobic'),\
                              and_(aa.eacceptor1 == 'NO3', aa.eacceptor2 == 'NO3'),\
                              and_(aa.eacceptor1 == 'O2', aa.eacceptor2 == 'O2')))).all():
        i+=1
        exp_val = array([x.value for x in ome.query(ExpressionData).filter_by(bnum=bnum).all()]).mean()
        print str(i)+'\t'+bnum+'\t'+str(s12['Operon'].ix[bnum])+'\t'+str(exp_val)

s12
42/229 not diff expressed in our data
10/43 not measured
20/43 not measurably expressed
13/43 misses
5/13 FP
8 total misses