Geek Answers Handbook

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 always worked until there was enough coverage in the csv file (in just a week). For example, 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 will get the following error:

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

It seems to have a very simple fix, but I'm too new to Python to find out how to fix it.

+18
source share
2 answers

Use reindex to get all the necessary columns. It will preserve those that already exist, and otherwise place empty columns.

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

So, your whole 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)
+17
source

I had a very similar problem. I got the same error because csv contained spaces in the header. My csv contained the heading "Paul" and I indicated it as:

 [['Gender']]

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

or delete it like that

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

+5
source

More articles:


All Articles