Pandas Error- None of Index are in the columns

Issue

Problem Statement
I have the CSV data as shown in the image. From this, I have to use only keep RegionName, State and the quarterly mean values from 2000 – 2016. Also, I want to use multi-indexing with [State, RegionName].
enter image description here enter image description here

I am working on a CSV file with pandas in python. As shown in the screenshot.

Thank you in advance.

Solution

Right before the troublesome for year in range(...) loop, you did:

house_data.columns = pd.to_datetime(house_data.columns).to_period('M')

That means your columns are no longer strings. So inside the for loop:

house_data[str(year)+'q2'] = house_data[[str(year)+'-04',...]].mean(axis=1)

would fail and throw that error since there are no column with string name. To fix this, do this instead:

house_data.columns = pd.to_datetime(house_data.columns).to_period('M').strftime('%Y-%m')

However, you are better do:

house_data.columns = pd.to_datetime(house_data.columns).to_period('Q')

house_data.groupby(level=0, axis=1).mean()

Answered By – Quang Hoang

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published