@follows( loadCapseqTranscriptTSSDistance, loadCapseqGenesetOverlap, mkdir("long_intervals") )
@transform( copyCapseqReplicatedBedFiles, regex(r"(\S+).replicated.bed"), r"long_intervals/\1.short.genelist" )
def getShortIntervalGeneList( infile, outfile ):
    # Connect to DB
    dbhandle = sqlite3.connect( PARAMS["database"] )
    track = P.snip( os.path.basename( infile ), ".replicated.bed" ).replace("-","_").replace(".","_")
    geneset_name = PARAMS["geneset_name"]
    cc = dbhandle.cursor()
    statement = "ATTACH DATABASE '%s' AS annotations; "  % (PARAMS["geneset_database"])
    cc.execute(statement)
    # Extract data from db
    query = '''SELECT distinct b.gene_id
               FROM (SELECT distinct s.closest_id, i.interval_id, i.contig, i.start, i.end, i.length, i.avgval, i.fold, o.genes_pover1, o.genes_pover2
               FROM %(track)s_replicated_intervals i, %(track)s_replicated_%(geneset_name)s_transcript_tss_distance s, 
               %(track)s_replicated_%(geneset_name)s_overlap o
               WHERE  i.interval_id=s.gene_id
               AND o.gene_id=i.interval_id
               AND i.length < 2000
               AND o.genes_pover2 > 0) a,
               (SELECT "%%" || transcript_id || "%%" as pattern, t.gene_id, t.gene_biotype 
               FROM annotations.transcript_info t
               WHERE t.gene_biotype='protein_coding') b
               WHERE a.closest_id like b.pattern  
               ORDER BY RANDOM()
               LIMIT 500''' % locals()
    cc.execute( query )
    # Write to file
    outs = open( outfile, "w")
    for result in cc:
        pre = ""
        for r in result:
            outs.write("%s%s" % (pre, str(r)) )
            pre = "\t"
        outs.write("\n")
    cc.close()
    outs.close()

############################################################
Generate bed file of 500 random intervals of normal size (<2kb)
dataset/ETHPy150Open CGATOxford/cgat/obsolete/pipeline_proj007.py/getShortIntervalGeneList