@transform(loadSVOverlap, 
           suffix("_sv_overlap.load"),
           "_sv_genes.load" )
def summarizeSVsPerGene( infile, outfile ):
    
    tablename = outfile[:-len(".load")]
    track = infile[:-len(".load")]

    dbhandle = sqlite3.connect( PARAMS["database"] )
    patterns = ("ins", "del", "other", "all" )

    tables = ["%s_%s AS a%i" \
                  % (track,pattern,x) for x,pattern in enumerate(patterns) ]
    sqltables = ",".join(tables)

    sqlcount = ["SUM( case when a%i.nover > 0 THEN 1 ELSE 0 END) AS naffected_%s" \
                    % (x,pattern) for x,pattern in enumerate(patterns) ]
    sqlcount = ", ".join( sqlcount)

    sqldummy = [ "0 AS is_%s" % pattern for pattern in patterns ]
    sqldummy = ", ".join( sqldummy )

    sqlwhere = ["a%i.transcript_id = i.transcript_id" \
                    % x for x,pattern in enumerate( patterns) ]

    sqlwhere = " AND ".join( sqlwhere )

    statement = '''
        CREATE TABLE %(tablename)s AS
        SELECT DISTINCT 
               i.gene_id AS gene_id,
               COUNT( DISTINCT i.transcript_id ) AS ntranscripts,
               %(sqlcount)s,
               %(sqldummy)s
        FROM transcript_info AS i,
             %(sqltables)s
        WHERE %(sqlwhere)s
        GROUP BY i.gene_id
        ''' % locals()
        
    Database.executewait( dbhandle, "DROP TABLE IF EXISTS %(tablename)s" % locals() )
    Database.executewait( dbhandle, statement )
    Database.executewait( dbhandle, "CREATE INDEX %(tablename)s_gene_id ON %(tablename)s (gene_id)" % locals())

    for pattern in patterns:
        Database.executewait( dbhandle, 
                              "UPDATE %(tablename)s SET is_%(pattern)s = naffected_%(pattern)s == ntranscripts" % locals())
        
    dbhandle.commit()

    P.touch(outfile)

###################################################################
###################################################################
###################################################################
## gene list analyses
###################################################################
summarize effects on a per-gene level.
dataset/ETHPy150Open CGATOxford/cgat/obsolete/pipeline_snps.py/summarizeSVsPerGene