Pandas KeyError: value not in index

Question

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.

Solution 1

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)

Solution 2

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())

Solution 3

please try this to clean and format your column names:

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

Comments

[xpt]

I am afraid that mine is a totally differnct case: "It has always been working until the csv file doesn't have enough coverage", even though the symtoms are the same. But thanks for your input though.

[inthy]

You've covered a really non obvious case, thank you very much

[abhijat_saxena]

This should be marked as the right answer - as this is a GENERIC answer