5

I'm trying to do an unbelievably simple thing: load parts of an Excel worksheet into a Numpy array. I've found a kludge that works, but it is embarrassingly unpythonic: say my worksheet was loaded as "ws", the code:

A = np.zeros((37,3))
for i in range(2,39):
   for j in range(1,4):
      A[i-2,j-1]= ws.cell(row = i, column = j).value

loads the contents of "ws" into array A.

There MUST be a more elegant way to do this. For instance, csvread allows to do this much more naturally, and while I could well convert the .xlsx file into a csv one, the whole purpose of working with openpyxl was to avoid that conversion. So there we are, Collective Wisdom of the Mighty Intertubes: what's a more pythonic way to perform this conceptually trivial operation?

Thank you in advance for your answers.

PS: I operate Python 2.7.5 on a Mac via Spyder, and yes, I did read the openpyxl tutorial, which is the only reason I got this far.

Improve this question

2 Answers 2

Reset to default
9

You could do

A = np.array([[i.value for i in j] for j in ws['C1':'E38']])

EDIT - further explanation. (firstly thanks for introducing me to openpyxl, I suspect I will use it quite a bit from time to time)

  1. the method of getting multiple cells from the worksheet object produces a generator. This is probably much more efficient if you want to work your way through a large sheet as you can start straight away without waiting for it all to load into your list.
  2. to force a generator to make a list you can either use list(ws['C1':'E38']) or a list comprehension as above
  3. each row is a tuple (even if only one column wide) of
  4. Cell objects. These have a lot more about them than just a number but if you want to get the number for your array you can use the .value attribute. This is really the crux of your question, csv files don't contain the structured info of an excel spreadsheet.
  5. there isn't (as far as I can tell) a built in method for extracting values from a range of cells so you will have to do something effectively as you have sketched out.

The advantages of doing it my way are: no need to work out the dimension of the array and make an empty one to start with, no need to work out the corrected index number of the np array, list comprehensions faster. Disadvantage is that it needs the "corners" defining in "A1" format. If the range isn't know then you would have to use iter_rows, rows or columns

A = np.array([[i.value for i in j[2:5]] for j in ws.rows])

if you don't know how many columns then you will have to loop and check values more like your original idea

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

4 Comments

Thank you. That is indisputably more elegant. Would you care to explain what concepts are involved here? I think I understand the looping style you use (i've seen it applied to integer or to list elements), but I have trouble understanding how to loop over the ws[] elements. For instance, if I type type(ws['C1']), I get openpyxl.cell.cell.Cell, and it's not obvious to me how one could loop over this sort of object. I'm trying to learn how to program better, instead of just asking questions on StackOverflow... Thanks for your patience.
@ElNiño there are a few things, I will edit my answer and add a bit more info.
Aaaah, "list comprehension". That's what this magical trick is called? I read up a bit about it. It will take some brain reformatting to get used to it, but it looks very powerful. So in this case it knows to loop using the generator embedded in ws? I had to look up "generator" too. Anyway, thanks for the tips, and glad I could point you to one useful package I did not write!
@ElNiño in some ways they are tidier and they are a bit faster, but they can be cryptic and it's often clearer to use an old-fashioned loop. In terms of brain reformatting: numpy is the thing to concentrate on.
1

If you don't need to load data from multiple files in an automated manner, the package tableconvert I recently wrote may help. Just copy and paste the relevant cells from the excel file into a multiline string and use the convert() function.

import numpy as np
from tableconvert.converter import convert

array = convert("""
123    456    3.14159
SOMETEXT    2,71828    0
""")

print(type(array))
print(array)

Output:

<class 'numpy.ndarray'>
[[ 123.       456.         3.14159]
 [       nan    2.71828    0.     ]]
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.