Filtering in pandas by index - Keyerror

Question

I am trying to filter in Pandas for a selected period. (picture added) The start and the end date is to be entered in an input box.

"Day" is found in the index column, however the third line gives an error message.

df = pd.read_excel('prices.xlsx', index_col=0)
df.iloc[::-1]
filtered_date = df[(df['Day'] >= 'start_date') & (df['Day'] <= 'end_date')]

I get a "KeyError" message. I googled that key_error happens if a key is not available in a dictionary. I do not use a dictionary in my code, and I do not understand how to fix it. The key "Day" is indeed the first value of the first(index) row.

Thank you.

This is my dataframe

Answer 1

Look at the picture of your DataFrame:

• All names of "regular" columns are located a bit higher.
• "Day" is located a bit lower and it indicates that it is the name of the index column.

Your code contains df['Day'], so you attempt to reference a regular column named Day. As regular column of this name does not exist, an exception is thrown.

There are 2 ways to cope with this:

1. Drop index_col=0 from the call to read_excel. This way Day will be a regular column, so your following code should work.
2. Change df['Day'] to df.index. This way you refer to the index.

Of course, put any valid date string instead of start_date and end_date.

And one more thing to consider: As Day is a matter of fact a column holding dates, it should have datetime type. So probably you should add parse_dates=[0] parameter to read_excel, to have this column converted from string to datetime.