CODEWITHSUNDEEP
pythonexcelpandas
griffinc
griffinc

Reputation: 679

How to get read excel data into an array with python

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

Upvotes: 8

Views: 120662

Answers (4)

griffinc
griffinc

Reputation: 679

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.

Upvotes: 7

chfw
chfw

Reputation: 4592

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

Upvotes: 1

Oleksii M
Oleksii M

Reputation: 1518

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.

Upvotes: 2

fpes
fpes

Reputation: 974

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.

Upvotes: 14

Related Questions