Pivot Table Column Ordering

[aarondr77]

Describe the bug Two users have expressed that their biggest frustration with Mito is that the pivot table automatically orders the columns in alphabetical order. They want to be able to at the minimum have the order of the column be the same that the columns are added to the pivot table values section. And a better solution would being able to reorder the value cards so you could change the order.

I looked into the pivot table API, and there is no way to accomplish this, as the sort argument sorts the values in the pivot table and has no effect on the order of the columns.

So the solution relies on manually setting the column order after the pivot table. This isn't a super straight forward task because the column headers are determined on the fly by the pandas pivot table function, knowing which columns in the original configuration map to the new columns in the pivot table isn't trivial -- especially if the column parameter of the pivot table is set and there are an unbounded number of pivot table columns for each value parameter.

@naterush thoughts on how to implement this? I'd love to be able to deliver this to the user has been requesting it for a while.

[naterush]

If the user sets columns in their pivot table, this seems quite hard to do (we'd have to figure out what it elevates to the highest-level and makes columns). So I'm ignoring the case where they set columns, as this is much harder (and probably more rare, though we should look into this). Anyways, not letting the perfect being the enemy of the good:

1. If the user only has rows and values set
2. Then (at least in this version of Pandas), there is one column for each row set, and one column for each value (including aggregation method).
3. We could just add a step at the end that orders the columns in that same order. Note that this isn't really our standard reordering step, as it wants to reorder all the columns to a specific order (aka, substeps don't really help things here).

The main questions are how this works with older versions of pandas (as it changes how it generates pivot tables on historical versions, as you can see in the test_pivot.py. What do we want to do here? I think we can just make this transpiration step only occur if users are on pandas >= 1.0... thoughts?

If we do that it's only on pandas >= 1.0, and we ignore the case where the user set columns, I think we can figure out the mapping from columns set -> columns created fairly simply.

[aarondr77]

+1 a second Mito power user requested this