1
$\begingroup$

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

enter image description here

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: enter image description here

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.

Thanks.

$\endgroup$
  • 1
    $\begingroup$ Just a heads up: you should only ask one question per question. $\endgroup$ – ukemi Mar 15 at 18:03
1
$\begingroup$

Ordering

You can achieve this by changing the datatype to ordered categorical:

df['DofWeek'] = pd.Categorical(
                df['DofWeek'], 
                categories=['Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday'], 
                ordered=True)

And then sorting the index:

df = df.sort_index()

Error

The issue with dataframe.query('DofWeek == 'Saturday') is bad quote encapsulation. You could solve it with e.g. 'DofWeek == "Saturday"'

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.