How to read specific rows from excel file using pandas

Question

I have an excel file and I need to extract certain data from the rows of a certain sheet. So far I have

import pandas as pd
xl_file = pd.ExcelFile((xlfilePath)
dfs = {sheet_name: xl_file.parse(sheet_name) for sheet_name in xl_file.sheet_names}

Now I would like to read the numerical values found in a particular row. The row structure is something like:

Length (mm) 10.1 - 16.0 - 19.5 - 16.4 - 11.3

where I am attempting to show what is in each cell of a row. The dashes indicate an empty entry in a cell! How can I read in a row like this using the pandas library? I happen to know what row number the above row has but would there be a way for pandas to look through the data frame and find the entry length (mm) instead of having to specify the row number?

Edit: The actual df.loc['length (mm)'] as suggested by EdChum looks like this:

0              17.92377
Unnamed: 1          NaN
0.05           18.55764
Unnamed: 3          NaN
0.1            19.17039
Unnamed: 5          NaN
0.15            19.7507
Unnamed: 7          NaN
0.2            20.29776
Unnamed: 9          NaN
0.25           20.80492
Unnamed: 11         NaN
0.3             21.2667
Unnamed: 13         NaN
0.35           21.67687
Unnamed: 15         NaN
0.4            22.02884
Unnamed: 17         NaN
0.45            22.3156
Unnamed: 19         NaN
0.5            22.53051
Unnamed: 21         NaN
0.55           22.66691
Unnamed: 23         NaN
0.6            22.71949
Unnamed: 25         NaN
0.65           22.68477
Unnamed: 27         NaN
0.7            22.56162
Unnamed: 29         NaN
0.75           22.35258
Unnamed: 31         NaN
0.8            22.06432
Unnamed: 33         NaN
0.85            21.7079
Unnamed: 35         NaN
0.9            21.29801
Unnamed: 37         NaN
0.95           20.85419
Unnamed: 39         NaN
1                20.394
Name: length (mm), dtype: object

can you post what the df looks like, are you saying that your index value is 'Length (mm)'? if so then won't df.loc['Length (mm)'] work? — EdChum
– EdChum, Commented Jun 4, 2015 at 13:39
@EdChum Yes please see my edit. How can load this into a numpy array? — Dipole
– Dipole, Commented Jun 4, 2015 at 14:07
If you want a np.array from this then just do df.loc['length (mm)'].values — EdChum
– EdChum, Commented Jun 4, 2015 at 14:10

EdChum · Accepted Answer · 2015-06-04 14:13:11Z

2

After loading your df you can select a specific row using label indexing loc:

df.loc['length (mm)']

If you want a np.array from this just do:

df.loc['length (mm)'].values

answered Jun 4, 2015 at 14:13

EdChum

397k204 gold badges836 silver badges583 bronze badges

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

Comments

Guillaume Jacquenot · Accepted Answer · 2020-01-16 11:08:00Z

1

While reading the file you can always specify the column name in the form of parameter to data frame.

import pandas as pd
fields = ['employee_name']

d_frame = pd.read_csv('data_file.csv', skipinitialspace=True, usecols=fields)
# get the required key or column name
print(d_frame.keys())
# Get data from column name
print(d_frame.employee_name)

I assumed employee_name would be the column name that you want to fetch the value from.

edited Jan 16, 2020 at 11:08

Guillaume Jacquenot

11.8k6 gold badges45 silver badges50 bronze badges

answered Jun 4, 2015 at 13:43

Pralhad Narsinh Sonar

1,4541 gold badge14 silver badges23 bronze badges

Collectives™ on Stack Overflow

How to read specific rows from excel file using pandas

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related