XLRD/ Entrez: Search through Pubmed and extract the counts
<p>I am working on a project that requires me to search through <code>pubmed</code> using inputs from an <code>Excel</code> spreadsheet and print counts of the results. I have been using <code>xlrd</code> and <code>entrez</code> to do this job. Here is what I have tried.</p>

<ol>
<li><p>I need to search through <code>pubmed</code> using the name of the author, his/her medical school, a range of years, and his/her mentor's name, which are all in an <code>Excel</code> spreadsheet. I have used <code>xlrd</code> to turn each column with the required information into lists of strings.</p>

<pre><code>from xlrd import open_workbook
book = xlrd.open_workbook("HEENT.xlsx").sheet_by_index(0)
med_name = []
for row in sheet.col(2):
    med_name.append(row)
med_school = []
for row in sheet.col(3):
    med_school.append(row)
mentor = []
for row in sheet.col(9):
    mentor.append(row)
</code></pre></li>
<li><p>I have managed to print the counts of my specific queries using Entrez.</p>

<pre><code>from Bio import Entrez
Entrez.email = "your@email.edu"
handle = Entrez.egquery(term="Jennifer Runch AND ((2012[Date - Publication] : 2017[Date - Publication])) ")
handle_1 = Entrez.egquery(term = "Jennifer Runch AND ((2012[Date - Publication] : 2017[Date - Publication])) AND Leoard P. Byk")
handle_2 = Entrez.egquery(term = "Jennifer Runch AND ((2012[Date - Publication] : 2017[Date - Publication])) AND Southern Illinois University School of Medicine")
record = Entrez.read(handle)
record_1 = Entrez.read(handle_1)
record_2 = Entrez.read(handle_2)
pubmed_count = []
for row in record["eGQueryResult"]:
    if row["DbName"] == "pubmed":
        pubmed_count.append(row["Count"])

for row in record_1["eGQueryResult"]:
    if row["DbName"] == "pubmed":
         pubmed_count.append(row["Count"])

for row in record_2["eGQueryResult"]:
     if row["DbName"] == "pubmed":
         pubmed_count.append(row["Count"])
print(pubmed_count)   
&gt;&gt;&gt;['3', '0', '0']
</code></pre>

<p>The problem is that I need to replace the student name ("Jennifer Runch") with the next student name in the list of student names("med_name"), the medical school with the next school, and the current mentor's name with the next mentor's name from the list.</p></li>
</ol>

<p>I think I should write a for loop after declaring my email to <code>pubmed</code>, but I am not sure how to link the two blocks of code together. Does anyone know of an efficient way to connect the two blocks of code or know how to do this with a more efficient way than the one I have tried?
Thank you!</p>

<p>You got most of the code in place. It just needed to be modified slightly.</p>

<p>Assuming your table looks like this:</p>

<pre><code>Jennifer Bunch  |Southern Illinois University School of Medicine|Leonard P. Rybak
Philipp Robinson|Stanford University School of Medicine         |Roger Kornberg
</code></pre>

<p>you could use the following code</p>

<pre><code>import xlrd
from Bio import Entrez
sheet = xlrd.open_workbook("HEENT.xlsx").sheet_by_index(0)

med_name = list()
med_school = list()
mentor = list()
search_terms = list()
for row in range(0, sheet.nrows):
    search_terms.append([sheet.cell_value(row, 0), sheet.cell_value(row,1), sheet.cell_value(row, 2)])

pubmed_counts = list()

for search_term in search_terms:
    handle = Entrez.egquery(term="{0} AND ((2012[Date - Publication] : 2017[Date - Publication])) ".format(search_term[0]))
    handle_1 = Entrez.egquery(term = "{0} AND ((2012[Date - Publication] : 2017[Date - Publication])) AND {1}".format(search_term[0], search_term[2]))
    handle_2 = Entrez.egquery(term = "{0} AND ((2012[Date - Publication] : 2017[Date - Publication])) AND {1}".format(search_term[0], search_term[1]))
    record = Entrez.read(handle)
    record_1 = Entrez.read(handle_1)
    record_2 = Entrez.read(handle_2)

    pubmed_count = ['', '', '']

    for row in record["eGQueryResult"]:
        if row["DbName"] == "pubmed":
            pubmed_count[0] = row["Count"]
    for row in record_1["eGQueryResult"]:
        if row["DbName"] == "pubmed":
            pubmed_count[1] = row["Count"]
    for row in record_2["eGQueryResult"]:
        if row["DbName"] == "pubmed":
            pubmed_count[2] = row["Count"]

    print(pubmed_count)
    pubmed_counts.append(pubmed_count)
</code></pre>

<p>Output</p>

<hr>

<pre><code>['3', '0', '0']
['1', '0', '0']
</code></pre>

<p>The required modification is to make the queries variable using <a href="https://docs.python.org/3.6/library/string.html#string.Formatter" rel="nofollow">format</a>.</p>

<p>Some other modifications which are not necessary but might be helpful:</p>

<ul>
<li>loop over the <code>Excel</code> sheet only once</li>
<li>store the <code>pubmed_count</code> in a predefined list because if values come back empty, the size of the output will vary making it hard to guess which value belongs to which query</li>
<li>everything could be even further optimized and prettified, e.g. store the queries in a list and loop over them which would give less code repetition but now it does the job.</li>
</ul>

python xlrd biopython pubmed