I have a csv which is generated in a format that I can not change. The file has a multi index. The file looks like this.
The end goal is to turn the top row (hours) into an index, and index it with the "ID" column, so that the data looks like this.
I have imported the file into pandas...
myfile = 'c:/temp/myfile.csv'
df = pd.read_csv(myfile, header=[0, 1], tupleize_cols=True)
pd.set_option('display.multi_sparse', False)
df.columns = pd.MultiIndex.from_tuples(df.columns, names=['hour', 'field'])
df
But that gives me three unnamed fields:
My final step is to stack on hour:
df.stack(level=['hour'])
But I a missing what comes before that, where I can index the other columns, even though there's a blank multiindex line above them.