I have a pandas dataframe with multi-index. I have couple of questions on this.
The indices are week numbers (38 to 42) and for each week, day of the week (DOW). So it looks like
The problem is the 2nd level index, that is, DofWeek, is automatically sorted in lexicographic ordering. But I want to have the usual weekdays ordering, i,e for week 39, I want it to appear as Monday, Tuesday, Wednesday, Thoursday, Friday, Saturday and Sunday and NOT in the order they appear. So when I graph it, it appears this way:
I want it to appear in the usual Monday, Tuesday, Wednesday etc. I have tried reindexing, but it didn't work. This is my first question.
My second question is regarding querying such dataframes. For example when I do
dataframe.query('DofWeek == 'Saturday'), it is throwing an error saying
Saturday is not defined. However, if I swap levels and then query, it works. For example
dataframe.swaplevel().query('weekNum == 39'), it works perfectly. As far as I know, the multi-indices should be sorted in order for certain methods to work. But in my case, they are. Then why isn't it working.
Any help would be hugely appreciated.