How Would I Take An Excel File And Convert Its Columns Into Lists In Python?

I had the following question. Namely, suppose I have an excel file with some names in the first row. Then the next however many rows are single letter entries (so 'A', 'B', 'C' and

Solution 1:

I used a module called xlrd.

Some information on that http://www.blog.pythonlibrary.org/2014/04/30/reading-excel-spreadsheets-with-python-and-xlrd/

And here is the package: https://pypi.python.org/pypi/xlrd

To exclude the first row, and make different lists for different columns you could do something like...

from xlrd import open_workbook

book = open_workbook("mydata.xlsx")
sheet = book.sheet_by_index(0) #If your data is on sheet 1

column1 = []
column2 = []
#...

for row in range(1, yourlastrow): #start from 1, to leave out row 0
    column1.append(sheet.cell(row, 0)) #extract from first col
    column2.append(sheet.cell(row, 1))
    #...

Put the index-1 of the last row that contains data in the 'yourlastrow' placeholder.

Solution 2:

I figured out the answer. Assuming the Excel file is Test.xlsx, we can translate the j-th column (excluding the first row) into list_j as follows:

book = xlrd.open_workbook("Test.xlsx")
sheet = book.sheet_by_index(0)

list_j = []

for k in range(1,sheet.nrows):
    list_j.append(str(sheet.row_values(k)[j-1]))