How would I take an excel file and convert its columns into lists in Python?

Question

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 so on).

My goal is to extract the column and make it into a list, so that, say, for column 1 the list would start in row 2, the next entry would be from row 3, and so on, until the end is reached.

How would I do that in Python?

Your title sounds like a simple transpose which Excel can already do, but the question itself describes something I'm not clear on: the list for the first column would contain the whole column (excluding the header row), but the list for the last column would be missing the first row_length entries? Can you provide sample input and output? — TigerhawkT3
– TigerhawkT3, Commented May 24, 2016 at 2:36
@TigerhawkT3, no, every list would contain the entire column except for the first row. — Ryker
– Ryker, Commented May 24, 2016 at 2:38
@TigerhawkT3, I still need to convert it to the list, that's what I don't (or didn't) know. — Ryker
– Ryker, Commented May 24, 2016 at 2:53
@TigerhawkT3, OK, whatever, I answered my own question down below and at least Sonder's answer was helpful. — Ryker
– Ryker, Commented May 24, 2016 at 3:08

marc_s · Accepted Answer · 2016-07-31 10:22:45Z

3

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.

edited Jul 31, 2016 at 10:22

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered May 24, 2016 at 2:36

Fraans

1172 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TigerhawkT3 Over a year ago

The question was about transposing rows and columns, not about what libraries can read and write Excel files. This doesn't answer the question, and would be better suited as a comment. You will be able to leave a comment when you have sufficient reputation.

Ryker Over a year ago

Thanks, that's all I needed.

Ryker · Accepted Answer · 2016-05-24 03:07:36Z

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]))

answered May 24, 2016 at 3:07

Ryker

1471 gold badge1 silver badge6 bronze badges

Collectives™ on Stack Overflow

How would I take an excel file and convert its columns into lists in Python?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related