How to get read excel data into an array with python

Question

In the lab that I work in, we process a lot of data produced by a 96 well plate reader. I'm trying to write a script that will perform a few calculations and output a bar graph using matplotlib.

The problem is that the plate reader outputs data into a .xlsx file. I understand that some modules like pandas have a read_excel function, can you explain how I should go about reading the excel file and putting it into a dataframe?

Thanks

Data sample of a 24 well plate (for simplicity):

0.0868  0.0910  0.0912  0.0929  0.1082  0.1350
0.0466  0.0499  0.0367  0.0445  0.0480  0.0615
0.6998  0.8476  0.9605  0.0429  1.1092  0.0644
0.0970  0.0931  0.1090  0.1002  0.1265  0.1455

You can also have a look at the xlrd module - see python-excel.org - but yes, pandas will be a good choice if you're doing analysis work/plotting on the data - I suggest starting with the docs for pandas.read_excel — Jon Clements
– Jon Clements, Commented Feb 27, 2015 at 21:58
I edited in a sample of 24 well plate data, for simplicity's sake, I am using this data to test the code. — griffinc
– griffinc, Commented Feb 27, 2015 at 22:05

fpes · Accepted Answer · 2015-02-27 22:00:10Z

14

I'm not exactly sure what you mean when you say array, but if you mean into a matrix, might you be looking for:

import pandas as pd
df = pd.read_excel([path here])
df.as_matrix()

This returns a numpy.ndarray type.

answered Feb 27, 2015 at 22:00

fpes

97411 silver badges22 bronze badges

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

2 Comments

fpes Over a year ago

So given the data you just posted, this is exactly what you are looking for.

Raptor Jan 13 at 7:21

Version note: as_matrix() has been deprecated since v0.23, use df.values instead.

griffinc · Accepted Answer · 2019-11-20 23:51:26Z

7

This task is super easy in Pandas these days.

import pandas as pd

df = pd.read_excel('file_name_here.xlsx', sheet_name='Sheet1')

or

df = pd.read_csv('file_name_here.csv')

This returns a pandas.DataFrame object which is very powerful for performing operations by column, row, over an entire df, or over individual items with iterrows. Not to mention slicing in different ways.

edited Nov 20, 2019 at 23:51

answered Jul 22, 2017 at 17:36

griffinc

7091 gold badge11 silver badges17 bronze badges

1 Comment

dennis-tra Over a year ago

Thanks for your answer. I had to use sheetname instead of just sheet.

Oleksii M · Accepted Answer · 2015-02-27 22:03:54Z

2

There is awesome xlrd package with quick start example here. You can just google it to find code snippets. I have never used panda's read_excel function, but xlrd covers all my needs, and can offer even more, I believe.

answered Feb 27, 2015 at 22:03

Oleksii M

1,51814 silver badges22 bronze badges

1 Comment

griffinc Over a year ago

I've heard a bit about that one, I'll look into it more thanks.

chfw · Accepted Answer · 2015-03-09 15:42:40Z

1

You could also try it with my wrapper library, which uses xlrd as well:

import pyexcel as pe     # pip install pyexcel
import pyexcel.ext.xls   # pip install pyexcel-xls
your_matrix = pe.get_array(file_name=path_here) # done

answered Mar 9, 2015 at 15:42

chfw

4,6122 gold badges32 silver badges32 bronze badges

Collectives™ on Stack Overflow

How to get read excel data into an array with python

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related