CODEWITHSUNDEEP
pythonpandasindexingdataframe
xpt
xpt

Reputation: 23084

Pandas KeyError: value not in index

I have the following code, 

df = pd.read_csv(CsvFileName)

p = df.pivot_table(index=['Hour'], columns='DOW', values='Changes', aggfunc=np.mean).round(0)
p.fillna(0, inplace=True)

p[["1Sun", "2Mon", "3Tue", "4Wed", "5Thu", "6Fri", "7Sat"]] = p[["1Sun", "2Mon", "3Tue", "4Wed", "5Thu", "6Fri", "7Sat"]].astype(int)

It has always been working until the csv file doesn't have enough coverage (of all week days). For e.g., with the following .csv file, 

DOW,Hour,Changes
4Wed,01,237
3Tue,07,2533
1Sun,01,240
3Tue,12,4407
1Sun,09,2204
1Sun,01,240
1Sun,01,241
1Sun,01,241
3Tue,11,662
4Wed,01,4
2Mon,18,4737
1Sun,15,240
2Mon,02,4
6Fri,01,1
1Sun,01,240
2Mon,19,2300
2Mon,19,2532

I'll get the following error:

KeyError: "['5Thu' '7Sat'] not in index"

It seems to have a very easy fix, but I'm just too new to Python to know how to fix it.

Upvotes: 49

Views: 347921

Answers (5)

Dinesh vishe
Dinesh vishe

Reputation: 3608

There is one column mismatch train sheet. enter image description here

When we try add column, it will show. -> 6178 raise KeyError(f"{not_found} not in index")

KeyError: "['Junk'] not in index"

you have add column bd_test['Junk']=np.nan

enter image description here

Upvotes: 0

Edwin Paul
Edwin Paul

Reputation: 51

please try this to clean and format your column names:

df.columns = (df.columns.str.strip().str.upper()
              .str.replace(' ', '_')
              .str.replace('(', '')
              .str.replace(')', ''))

Upvotes: 5

Paul Gheno
Paul Gheno

Reputation: 11

I had the same issue.

During the 1st development I used a .csv file (comma as separator) that I've modified a bit before saving it. After saving the commas became semicolon.

On Windows it is dependent on the "Regional and Language Options" customize screen where you find a List separator. This is the char Windows applications expect to be the CSV separator.

When testing from a brand new file I encountered that issue.

I've removed the 'sep' argument in read_csv method before: 

df1 = pd.read_csv('myfile.csv', sep=',');

after: 

df1 = pd.read_csv('myfile.csv');

That way, the issue disappeared.

Upvotes: 0

piRSquared
piRSquared

Reputation: 294516

Use reindex to get all columns you need. It'll preserve the ones that are already there and put in empty columns otherwise.

p = p.reindex(columns=['1Sun', '2Mon', '3Tue', '4Wed', '5Thu', '6Fri', '7Sat'])

So, your entire code example should look like this:

df = pd.read_csv(CsvFileName)

p = df.pivot_table(index=['Hour'], columns='DOW', values='Changes', aggfunc=np.mean).round(0)
p.fillna(0, inplace=True)

columns = ["1Sun", "2Mon", "3Tue", "4Wed", "5Thu", "6Fri", "7Sat"]
p = p.reindex(columns=columns)
p[columns] = p[columns].astype(int)

Upvotes: 51

ILikeWhiskey
ILikeWhiskey

Reputation: 581

I had a very similar issue. I got the same error because the csv contained spaces in the header. My csv contained a header "Gender " and I had it listed as: 

[['Gender']]

If it's easy enough for you to access your csv, you can use the excel formula trim() to clip any spaces of the cells.

or remove it like this

df.columns = df.columns.to_series().apply(lambda x: x.strip())

Upvotes: 32

Related Questions