KeyError when filtering excel data with pandas

Question

I am trying to read and filter from excel data in Python. I used the code below:

import pandas as pd
import numpy as np
df = pd.read_excel('file.xlsx') 
df['apples'] = (pd.cut(df['apples'],bins=[-np.inf,2,5,np.inf],labels=['WOW','ok','BOB']))
print(df)

This is my excel file

But KeyError: 'apples' occurs. Do you have any advice about how can I fix this?

When I tried this one, "TypeError: list indices must be integers, not list" error occurs @MaxU — OykuA
– OykuA, Commented Feb 17, 2017 at 11:49
@OykuA, do you expect people to type your data manually from that screenshot? ;-) — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Feb 17, 2017 at 12:10

jezrael · Accepted Answer · 2017-02-17 12:38:40Z

1

There is problem you have header with 2 rows, so by default columns of DataFrame are created by first row.

So need skip this first row by:

df = pd.read_excel('file.xlsx', skiprows=1)

Or:

df = pd.read_excel('file.xlsx', header=1)

answered Feb 17, 2017 at 12:38

jezrael

867k102 gold badges1.4k silver badges1.3k bronze badges

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

Comments

CrisH · Accepted Answer · 2017-02-17 12:01:05Z

1

Do you also want to modify the xlsx file? Or you just want to read it and apply some code to it? In the second case you could do:

df = df.drop(['apples'])

And you can input:

inputX = df.loc[:, ['oranges', 'lemons']].as_matrix()

It depends what do you want to do with it.

answered Feb 17, 2017 at 12:01

CrisH

2701 silver badge10 bronze badges

Collectives™ on Stack Overflow

KeyError when filtering excel data with pandas

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related