Monday, December 8, 2014

Exploring Blaze

Continuum's Blaze package provides an interesting way of working with datasets from within Python. Inspired by this blog post, here's a quick exploration of using Blaze Expressions

In [5]: 
%pylab inline
import blaze
import pandas as pd

Populating the interactive namespace from numpy and matplotlib

Some technical details before getting started.

This is the first post I've done using Python 3.4. I'm using the anaconda distribution and its excellent conda package manager to handle installing packages.

The Blaze version I'm using is from the blaze channel: conda install -c blaze blaze and I got psycopg2 for Python3.4 (to allow connections to PostgreSQL) from the pandas channel: conda install -c pandas psycopg2.

In [2]: 
chembl = blaze.Data('postgresql://localhost/chembl_19')

Blaze collects information about the schema while connecting, so I have access to all the tables and their columns. This doesn't show in the static blog post, but I can use IPython's tab completion on table names and/or columns.

Start with a basic join, blaze figures out the column(s) to use:

In [3]: 
docs_and_compounds = blaze.join(chembl.docs,chembl.compound_records)
In [4]: 
docs_and_compounds.fields
Out[4]:
['doc_id',
 'journal',
 'year',
 'volume',
 'issue',
 'first_page',
 'last_page',
 'pubmed_id',
 'doi',
 'chembl_id',
 'title',
 'doc_type',
 'authors',
 'abstract',
 'record_id',
 'molregno',
 'compound_key',
 'compound_name',
 'src_id',
 'src_compound_id']
In [5]: 
docs_and_compounds.nrows
Out[5]:
1637862
In [6]: 
blaze.sort(docs_and_compounds,key='year',ascending=False)
Out[6]:
doc_id journal year volume issue first_page last_page pubmed_id doi chembl_id title doc_type authors abstract record_id molregno compound_key compound_name src_id src_compound_id
0 77449 None None None None None None None 10.6019/CHEMBL3137547 CHEMBL3137547 Open Source Malaria Deposition 2. http://malar... DATASET None None 1979019 1626883 MMV672625 None 23 MMV672625
1 77449 None None None None None None None 10.6019/CHEMBL3137547 CHEMBL3137547 Open Source Malaria Deposition 2. http://malar... DATASET None None 1979020 1626884 MMV672626 None 23 MMV672626
2 77449 None None None None None None None 10.6019/CHEMBL3137547 CHEMBL3137547 Open Source Malaria Deposition 2. http://malar... DATASET None None 1979021 1626885 MMV672686 None 23 MMV672686
3 77449 None None None None None None None 10.6019/CHEMBL3137547 CHEMBL3137547 Open Source Malaria Deposition 2. http://malar... DATASET None None 1979022 1626886 MMV672687 None 23 MMV672687
4 77449 None None None None None None None 10.6019/CHEMBL3137547 CHEMBL3137547 Open Source Malaria Deposition 2. http://malar... DATASET None None 1979023 1626887 MMV672688 None 23 MMV672688
5 77449 None None None None None None None 10.6019/CHEMBL3137547 CHEMBL3137547 Open Source Malaria Deposition 2. http://malar... DATASET None None 1979024 1626888 MMV672689 None 23 MMV672689
6 77449 None None None None None None None 10.6019/CHEMBL3137547 CHEMBL3137547 Open Source Malaria Deposition 2. http://malar... DATASET None None 1979025 1626889 MMV672723 None 23 MMV672723
7 77449 None None None None None None None 10.6019/CHEMBL3137547 CHEMBL3137547 Open Source Malaria Deposition 2. http://malar... DATASET None None 1979026 1626890 MMV672725 None 23 MMV672725
8 77449 None None None None None None None 10.6019/CHEMBL3137547 CHEMBL3137547 Open Source Malaria Deposition 2. http://malar... DATASET None None 1979027 1626891 MMV672726 None 23 MMV672726
9 77449 None None None None None None None 10.6019/CHEMBL3137547 CHEMBL3137547 Open Source Malaria Deposition 2. http://malar... DATASET None None 1979028 1626892 MMV672727 None 23 MMV672727
10 77449 None None None None None None None 10.6019/CHEMBL3137547 CHEMBL3137547 Open Source Malaria Deposition 2. http://malar... DATASET None None 1979029 1626893 MMV672730 None 23 MMV672730

Get rid of the unpublished datasets (doc_id=-1) and datasets where the year is None by doing a query on the docs_and_compounds object:

In [6]: 
docs_and_compounds = docs_and_compounds[(docs_and_compounds['year']>0) & (docs_and_compounds['doc_id']>-1)]
docs_and_compounds.nrows
Out[6]:
1095936
In [7]: 
docs_and_compounds
Out[7]:
doc_id journal year volume issue first_page last_page pubmed_id doi chembl_id title doc_type authors abstract record_id molregno compound_key compound_name src_id src_compound_id
0 1 J. Med. Chem. 2004 47 1 1 9 14695813 None CHEMBL1139451 None PUBLICATION None None 350140 292848 15 (3R,4S)-1,4-Bis-(4-methoxy-phenyl)-3-((Z)-3-ph... 1 None
1 1 J. Med. Chem. 2004 47 1 1 9 14695813 None CHEMBL1139451 None PUBLICATION None None 350141 292849 16 (3R,4S)-1,4-Bis-(4-methoxy-phenyl)-3-((E)-3-ph... 1 None
2 1 J. Med. Chem. 2004 47 1 1 9 14695813 None CHEMBL1139451 None PUBLICATION None None 350142 292858 17 (S)-7-(4-Chloro-phenyl)-3-(4-methoxy-phenyl)-2... 1 None
3 1 J. Med. Chem. 2004 47 1 1 9 14695813 None CHEMBL1139451 None PUBLICATION None None 350143 292858 18 (S)-7-(4-Chloro-phenyl)-3-(4-methoxy-phenyl)-2... 1 None
4 1 J. Med. Chem. 2004 47 1 1 9 14695813 None CHEMBL1139451 None PUBLICATION None None 350144 292870 20 4-[(2S,3R)-1-(4-Hydroxy-phenyl)-4-oxo-3-(3-phe... 1 None
5 1 J. Med. Chem. 2004 47 1 1 9 14695813 None CHEMBL1139451 None PUBLICATION None None 350145 292107 21 (3R,4S)-1,4-Bis-(4-hydroxy-phenyl)-3-(3-phenyl... 1 None
6 1 J. Med. Chem. 2004 47 1 1 9 14695813 None CHEMBL1139451 None PUBLICATION None None 350146 292254 22 4-[1-(4-formylphenyl)-3-[3-(4-hydroxyphenyl)pr... 1 None
7 1 J. Med. Chem. 2004 47 1 1 9 14695813 None CHEMBL1139451 None PUBLICATION None None 350147 292403 23 4-[1-(4-formylphenyl)-3-(3-hydroxy-3-phenylpro... 1 None
8 1 J. Med. Chem. 2004 47 1 1 9 14695813 None CHEMBL1139451 None PUBLICATION None None 350148 292557 24 4-[1-(4-formylphenyl)-3-(3-hydroxy-3-phenylpro... 1 None
9 1 J. Med. Chem. 2004 47 1 1 9 14695813 None CHEMBL1139451 None PUBLICATION None None 350149 292687 25 (3R,4S)-1,4-Bis-(4-acetyl-phenyl)-3-(3-oxo-3-p... 1 None
10 1 J. Med. Chem. 2004 47 1 1 9 14695813 None CHEMBL1139451 None PUBLICATION None None 350150 175106 Ezetimibe (3R,4S)-1-(4-Fluoro-phenyl)-3-[(S)-3-(4-fluoro... 1 None

Find the first year each molregno appeared using blaze.by. This is analogous to SQLs "group by".

In [8]: 
minyear = blaze.by(docs_and_compounds.molregno,yr=docs_and_compounds.year.min())
minyear
Out[8]:
molregno yr
0 1 1983
1 2 1983
2 3 1983
3 4 1983
4 5 1983
5 6 1983
6 7 1983
7 8 1983
8 9 1983
9 10 1997
10 11 1993
In [9]: 
year_counts = blaze.by(minyear.yr,cnt=minyear.molregno.count())
In [10]: 
blaze.sort(year_counts,key='yr',ascending=False)
Out[10]:
yr cnt
0 2014 15131
1 2013 70429
2 2012 69407
3 2011 67026
4 2010 84525
5 2009 65118
6 2008 61158
7 2007 52060
8 2006 33837
9 2005 30778
10 2004 31118

That's what we had before. Excellent.

Let's try something else: the number of documents per compound. We'll use the limited set of docs that have years and doc_ids

In [11]: 
docs_per_compound = blaze.by(docs_and_compounds.molregno,ndocs=docs_and_compounds.doc_id.nunique())
In [12]: 
blaze.sort(docs_per_compound,key='ndocs',ascending=False)
Out[12]:
molregno ndocs
0 78759 1140
1 241 1045
2 6579 761
3 8062 660
4 8873 617
5 173 593
6 13758 564
7 305519 544
8 27307 539
9 365189 530
10 70140 501

That's also more or less what we got before.

Blaze also makes it easy to pull the data from the query (which is only executed in a lazy manner) into other formats. Here's an example of grabbing it as a Pandas DataFrame (for more on this, look at the blaze migrations blog post)

In [13]: 
df = blaze.into(pd.DataFrame,docs_per_compound)
In [14]: 
_=hist(df['ndocs'],bins=20,log=True)
_=xlabel('num docs per compound')
In [18]: 
len(df[df['ndocs']>10])
Out[18]:
2381
In []:
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)