Molly Zhao Molly Zhao - 1 year ago 92
Python Question

Python remove 'text' from lists made by xlrd

I used xlrd to read through each cell of three columns to make three lists. Then, I appended the ith element of all three lists to a new list, making a new list of lists.

for row in range(0, book.nrows):
search_terms.append([med_name[row], med_school[row], mentor[row]])
print(*search_terms[0:15], sep='\n')
[text:'Andrew Burkeland', 'Weill Cornell Medical College', 'Dave Cutler ']
[text:'Andrew Pence', 'University of Alabama at Birmingham School of Medicine', 'Jack Warran ']

Is there a way to take out the 'text:'? I am inputing each list in
to search for results on
, and with 'text:' in the query line, I keep on getting 0 results.

Answer Source

Let's assume a simple table which is called 'students.xlsx'

Student     School     Mentor
John Doe    Harvard    Kornberg
Jane Done   Stanford   Pauling

and now open it with xlrd

import xlrd
xl_workbook = xlrd.open_workbook('students.xlsx')
xl_sheet = xl_workbook.sheet_by_index(0)
row = xl_sheet.row(1)

Now let's look at the individual parts


[text:'John Doe', text:'Harvard', text:'Kornberg']


text:'John Doe'


'John Doe'

The problem is that row[0] is an xlrd cell and not a string which is reason why it is necessary to get the content via value.

Now let's do it for all rows (except the header):

raw_data = list()
for row in range(1, xl_sheet.nrows):

author_list = list()
for raw in raw_data:
    for r in raw:
> [['John Doe', 'Harvard', 'Kornberg'], ['Jane Done', 'Stanford',
> 'Pauling']]

or short:

author_list = [[c.value for c in xl_sheet.row(n)] for n in range(1, xl_sheet.nrows)]
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download