-
Notifications
You must be signed in to change notification settings - Fork 42
/
__init__.py
602 lines (475 loc) · 17.9 KB
/
__init__.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
"""
cruzdb: library for pythonic access to UCSC genome-browser's MySQL database
"""
from __future__ import print_function
from . import soup
import six
import sys
if sys.version_info[0] >= 3:
from past.builtins import long
import os
import re
from sqlalchemy.orm.query import Query
import pymysql
pymysql.install_as_MySQLdb()
__version__ = "0.5.6"
class BigException(Exception): pass
from .tests import test
def _open(filelike, mode='r'):
if hasattr(filelike, 'read'): return filelike
return open(filelike, mode)
class Genome(soup.Genome):
"""
Connect to a particular database
Returns a new Genome object
Parameters
----------
db : str
either an sqlalchemy dburl, or just the database name.
user : str
if `db` is a dburl, this is not needed. otherwise it's
the database user
host : str
if `db` is a dburl, this is not needed. otherwise it's
the database host
password : str
if `db` is a dburl, this is not needed. otherwise it's
the database password
dialect : str
If specified, use a specific dialect to connect to the database
(e.g. "mysqldb", "oursql" etc.). See the SQLalchemy documentation
for more details. Defaults to "mysqldb".
engine : sqlalchemy.engine
if specified, all other parameters must be unused. just forces
use of an existing engine
"""
url = "mysql+{dialect}://{user}{password}@{host}/{db}"
db_regex = re.compile(r"^(sqlite|mysql|postgresql)(.+[^:]+){0,1}://")
def __init__(self, db="", user="genome", host="genome-mysql.cse.ucsc.edu",
password="", dialect="mysqldb", engine=None):
self.create_url(db, user, host, password, dialect)
soup.Genome.__init__(self, self.dburl)
self.session.autoflush = False
def create_url(self, db="", user="genome", host="genome-mysql.cse.ucsc.edu",
password="", dialect="mysqldb"):
"""
internal: create a dburl from a set of parameters or the defaults on
this object
"""
if os.path.exists(db):
db = "sqlite:///" + db
# Is this a DB URL? If so, use it directly
if self.db_regex.match(db):
self.db = self.url = db
self.dburl = db
self.user = self.host = self.password = ""
else:
self.db = db
if user == "genome" and host != "genome-mysql.cse.ucsc.edu":
import getpass
user = getpass.getuser()
self.host = host
self.user = user
self.password = (":" + password) if password else ""
self.dburl = self.url.format(db=self.db, user=self.user,
host=self.host, password=self.password, dialect=dialect)
def mirror(self, tables, dest_url):
"""
miror a set of `tables` from `dest_url`
Returns a new Genome object
Parameters
----------
tables : list
an iterable of tables
dest_url: str
a dburl string, e.g. 'sqlite:///local.db'
"""
from mirror import mirror
return mirror(self, tables, dest_url)
def dataframe(self, table):
"""
create a pandas dataframe from a table or query
Parameters
----------
table : table
a table in this database or a query
limit: integer
an integer limit on the query
offset: integer
an offset for the query
"""
from pandas import DataFrame
if isinstance(table, six.string_types):
table = getattr(self, table)
try:
rec = table.first()
except AttributeError:
rec = table[0]
if hasattr(table, "all"):
records = table.all()
else:
records = [tuple(t) for t in table]
cols = [c.name for c in rec._table.columns]
return DataFrame.from_records(records, columns=cols)
@property
def tables(self):
return self._cache.keys()
def load_file(self, fname, table=None, sep="\t", bins=False, indexes=None):
"""
use some of the machinery in pandas to load a file into a table
Parameters
----------
fname : str
filename or filehandle to load
table : str
table to load the file to
sep : str
CSV separator
bins : bool
add a "bin" column for efficient spatial queries.
indexes : list[str]
list of columns to index
"""
convs = {"#chr": "chrom", "start": "txStart", "end": "txEnd", "chr":
"chrom", "pos": "start", "POS": "start", "chromStart": "txStart",
"chromEnd": "txEnd"}
if table is None:
import os.path as op
table = op.basename(op.splitext(fname)[0]).replace(".", "_")
print("writing to:", table, file=sys.stderr)
from pandas.io import sql
import pandas as pa
from toolshed import nopen
needs_name = False
for i, chunk in enumerate(pa.read_csv(nopen(fname), iterator=True,
chunksize=100000, sep=sep, encoding="latin-1")):
chunk.columns = [convs.get(k, k) for k in chunk.columns]
if not "name" in chunk.columns:
needs_name = True
chunk['name'] = chunk.get('chrom', chunk[chunk.columns[0]])
if bins:
chunk['bin'] = 1
if i == 0 and not table in self.tables:
flavor = self.url.split(":")[0]
schema = sql.get_schema(chunk, table, flavor)
print(schema)
self.engine.execute(schema)
elif i == 0:
print >>sys.stderr,\
"""adding to existing table, you may want to drop first"""
tbl = getattr(self, table)._table
cols = chunk.columns
data = list(dict(zip(cols, x)) for x in chunk.values)
if needs_name:
for d in data:
d['name'] = "%s:%s" % (d.get("chrom"), d.get("txStart", d.get("chromStart")))
if bins:
for d in data:
d['bin'] = max(Genome.bins(int(d["txStart"]), int(d["txEnd"])))
self.engine.execute(tbl.insert(), data)
self.session.commit()
if i > 0:
print >>sys.stderr, "writing row:", i * 100000
if "txStart" in chunk.columns:
if "chrom" in chunk.columns:
ssql = """CREATE INDEX "%s.chrom_txStart" ON "%s" (chrom, txStart)""" % (table, table)
else:
ssql = """CREATE INDEX "%s.txStart" ON "%s" (txStart)""" % (table, table)
self.engine.execute(ssql)
for index in (indexes or []):
ssql = """CREATE INDEX "%s.%s" ON "%s" (%s)""" % (table,
index, table, index)
self.engine.execute(ssql)
if bins:
ssql = """CREATE INDEX "%s.chrom_bin" ON "%s" (chrom, bin)""" % (table, table)
self.engine.execute(ssql)
self.session.commit()
@staticmethod
def david_go(refseq_list, annot=('SP_PIR_KEYWORDS', 'GOTERM_BP_FAT',
'GOTERM_CC_FAT', 'GOTERM_MF_FAT')):
"""
open a web-browser to the DAVID online enrichment tool
Parameters
----------
refseq_list : list
list of refseq names to check for enrichment
annot : list
iterable of DAVID annotations to check for enrichment
"""
URL = "http://david.abcc.ncifcrf.gov/api.jsp?type=REFSEQ_MRNA&ids=%s&tool=term2term&annot="
import webbrowser
webbrowser.open(URL % ",".join(set(refseq_list)) + ",".join(annot))
def bin_query(self, table, chrom, start, end):
"""
perform an efficient spatial query using the bin column if available.
The possible bins are calculated from the `start` and `end` sent to
this function.
Parameters
----------
table : str or table
table to query
chrom : str
chromosome for the query
start : int
0-based start postion
end : int
0-based end position
"""
if isinstance(table, six.string_types):
table = getattr(self, table)
try:
tbl = table._table
except AttributeError:
tbl = table.column_descriptions[0]['type']._table
q = table.filter(tbl.c.chrom == chrom)
if hasattr(tbl.c, "bin"):
bins = Genome.bins(start, end)
if len(bins) < 100:
q = q.filter(tbl.c.bin.in_(bins))
if hasattr(tbl.c, "txStart"):
return q.filter(tbl.c.txStart <= end).filter(tbl.c.txEnd >= start)
return q.filter(tbl.c.chromStart <= end).filter(tbl.c.chromEnd >= start)
def upstream(self, table, chrom_or_feat, start=None, end=None, k=1):
"""
Return k-nearest upstream features
Parameters
----------
table : str or table
table against which to query
chrom_or_feat : str or feat
either a chromosome, e.g. 'chr3' or a feature with .chrom, .start,
.end attributes
start : int
if `chrom_or_feat` is a chrom, then this must be the integer start
end : int
if `chrom_or_feat` is a chrom, then this must be the integer end
k : int
number of upstream neighbors to return
"""
res = self.knearest(table, chrom_or_feat, start, end, k, "up")
end = getattr(chrom_or_feat, "end", end)
start = getattr(chrom_or_feat, "start", start)
rev = getattr(chrom_or_feat, "strand", "+") == "-"
if rev:
return [x for x in res if x.end > start]
else:
return [x for x in res if x.start < end]
def downstream(self, table, chrom_or_feat, start=None, end=None, k=1):
"""
Return k-nearest downstream features
Parameters
----------
table : str or table
table against which to query
chrom_or_feat : str or feat
either a chromosome, e.g. 'chr3' or a feature with .chrom, .start,
.end attributes
start : int
if `chrom_or_feat` is a chrom, then this must be the integer start
end : int
if `chrom_or_feat` is a chrom, then this must be the integer end
k : int
number of downstream neighbors to return
"""
res = self.knearest(table, chrom_or_feat, start, end, k, "down")
end = getattr(chrom_or_feat, "end", end)
start = getattr(chrom_or_feat, "start", start)
rev = getattr(chrom_or_feat, "strand", "+") == "-"
if rev:
return [x for x in res if x.start < end]
else:
return [x for x in res if x.end > start]
def knearest(self, table, chrom_or_feat, start=None, end=None, k=1,
_direction=None):
"""
Return k-nearest features
Parameters
----------
table : str or table
table against which to query
chrom_or_feat : str or feat
either a chromosome, e.g. 'chr3' or a feature with .chrom, .start,
.end attributes
start : int
if `chrom_or_feat` is a chrom, then this must be the integer start
end : int
if `chrom_or_feat` is a chrom, then this must be the integer end
k : int
number of downstream neighbors to return
_direction : (None, "up", "down")
internal (don't use this)
"""
assert _direction in (None, "up", "down")
# they sent in a feature
if start is None:
assert end is None
chrom, start, end = chrom_or_feat.chrom, chrom_or_feat.start, chrom_or_feat.end
# if the query is directional and the feature as a strand,
# adjust...
if _direction in ("up", "down") and getattr(chrom_or_feat,
"strand", None) == "-":
_direction = "up" if _direction == "down" else "up"
else:
chrom = chrom_or_feat
qstart, qend = long(start), long(end)
res = self.bin_query(table, chrom, qstart, qend)
i, change = 1, 350
try:
while res.count() < k:
if _direction in (None, "up"):
if qstart == 0 and _direction == "up": break
qstart = max(0, qstart - change)
if _direction in (None, "down"):
qend += change
i += 1
change *= (i + 5)
res = self.bin_query(table, chrom, qstart, qend)
except BigException:
return []
def dist(f):
d = 0
if start > f.end:
d = start - f.end
elif f.start > end:
d = f.start - end
# add dist as an attribute to the feature
return d
dists = sorted([(dist(f), f) for f in res])
if len(dists) == 0:
return []
dists, res = zip(*dists)
if len(res) == k:
return res
if k > len(res): # had to break because of end of chrom
if k == 0: return []
k = len(res)
ndist = dists[k - 1]
# include all features that are the same distance as the nth closest
# feature (accounts for ties).
while k < len(res) and dists[k] == ndist:
k = k + 1
return res[:k]
def sql(self, query):
"""
show the sql of a query
"""
return self.engine.execute(query)
def annotate(self, fname, tables, feature_strand=False, in_memory=False,
header=None, out=sys.stdout, parallel=False):
"""
annotate a file with a number of tables
Parameters
----------
fname : str or file
file name or file-handle
tables : list
list of tables with which to annotate `fname`
feature_strand : bool
if this is True, then the up/downstream designations are based on
the features in `tables` rather than the features in `fname`
in_memoory : bool
if True, then tables are read into memory. This usually makes the
annotation much faster if there are more than 500 features in
`fname` and the number of features in the table is less than 100K.
header : str
header to print out (if True, use existing header)
out : file
where to print output
parallel : bool
if True, use multiprocessing library to execute the annotation of
each chromosome in parallel. Uses more memory.
"""
from .annotate import annotate
return annotate(self, fname, tables, feature_strand, in_memory, header=header,
out=out, parallel=parallel)
@staticmethod
def bins(start, end):
"""
Get all the bin numbers for a particular interval defined by
(start, end]
"""
if end - start < 536870912:
offsets = [585, 73, 9, 1]
else:
raise BigException
offsets = [4681, 585, 73, 9, 1]
binFirstShift = 17
binNextShift = 3
start = start >> binFirstShift
end = (end - 1) >> binFirstShift
bins = [1]
for offset in offsets:
bins.extend(range(offset + start, offset + end + 1))
start >>= binNextShift
end >>= binNextShift
return frozenset(bins)
def __repr__(self):
return "%s('%s')" % (self.__class__.__name__, self.dburl)
@classmethod
def save_bed(cls, query, filename=sys.stdout):
"""
write a bed12 file of the query.
Parameters
----------
query : query
a table or query to save to file
filename : file
string or filehandle to write output
"""
out = _open(filename, 'w')
for o in query:
out.write(o.bed() + '\n')
if __name__ == "__main__":
#g = Genome(db="hg18", host="localhost", user="")
import sys
#g = Genome(db="hg18", host="localhost")
g = Genome("sqlite:///hg18.db")
#g.load_file('GSM882245.hg18.bed', bins=True)
#g.load_file('http://rafalab.jhsph.edu/CGI/model-based-cpg-islands-hg18.txt',
# table='cpgRafaLab', bins=True)
#1/0
print(g.cpgIslandExt[12].bed())
print(g.cpgIslandExt[12].bed('length', 'perCpg'))
#sys.exit()
print("refGene")
f = g.refGene[19]
print(f.bed12())
f = g.refGene[19]
print(repr(f), f.cdsStart, f.cdsEnd)
print("exons", f.exons)
print("coding exons", f.coding_exons)
print("cds", f.cds)
print("introns", f.introns)
print("5'utr", f.utr5)
print("3'utr", f.utr3)
print(f.browser_link)
#f.txEnd = f.txStart + 30
#print list(f.blat())
#print f.cds_sequence
import time
from sqlalchemy import and_
query = g.refGene.filter(and_(g.refGene.txStart > 10000, g.refGene.txEnd < 40000))
t = time.time()
print(query)
query.all()
print(time.time() - t)
query = g.refGene.filter(and_(g.refGene.txStart > 10000, g.refGene.txEnd < 40000))
query = query.filter(g.refGene.bin.in_(Genome.bins(10000, 40000)))
t = time.time()
query = g.bin_query(g.refGene, "chr1", 10000, 40000)
query.all()
print(time.time() - t)
g = Genome('hg19')
t = time.time()
q = g.snp135Common
q = q.filter(q.bin.in_(Genome.bins(1000, 2000)))
print(q)
q.first()
print(time.time() - t)
Genome.save_bed(query)
for transcript in g.refGene:
print(transcript, transcript.sequence()[:100] + "...")
if transcript.txEnd > 8000: break
kg = g.refGene._table
q = kg.select(kg.c.txStart < 5000)
print(list(g.session.execute(q)))