pandas: pivot_table not displaying values columns in expected order

import pandas as pd

tuples_list = [(“John”, “Foo”, 47, 173), (“Michael”, “Bar”, 33, 182)] df = pd.DataFrame(tuples_list, columns=[“firstname”, “lastname”, “age”, “height”]) print(df.pivot_table(index=[“firstname”, “lastname”], values=[“age”, “height”])) print(df.pivot_table(index=[“firstname”, “lastname”], values=[“height”, “age”]))

Create a re-index for your pivot data. See my example below.

import pandas as pd

tuples_list = [("John", "Foo", 47, 173), ("Michael", "Bar", 33, 182)]
df = pd.DataFrame(tuples_list, columns=["firstname", "lastname", "age", "height"])
my_report = df.pivot_table(index=["firstname", "lastname"], values=["age", "height"])

new_order= ["height", "age"]
my_report = my_report.reindex(new_order, axis=1)
my_report

Is someone working on this? I’m not convinced that alphabetical ordering is desirable compared to just taking the order of the values supplied.

After further investigation with other column names, I have determined that the default behavior of pivot_table() is to display the contents of the value parameter in alphabetical order. I will look into making a code contribution to allow for user-defined sorting.

It seems that pivot_table explicitly sorts the index: https://github.com/pandas-dev/pandas/blob/d49a2446e0709f5f3a0c41589a95fccd81ced8cc/pandas/core/reshape/pivot.py#L219-L220

Since v1.3.0, a new parameter sort=True is added to pivot_table. Should we disable index sorting when sort=False is explicitly passed?

I agree with the general sentiment in this discussion. In my case, I have a report where I would like to have the output dataframe showing QTY (quantity), followed by AMT (amount). When I pass {‘QTY’: ‘sum’, ‘AMT’: ‘sum’} to pd.pivot_table, I expected the output dataframe to maintain this order. Currently, pd.pivot_table sorts the output columns alphabetically, putting AMT before QTY, which is not how usually such variables are presented.

This is really not expected behavior. Many students on my courses are asking the same question. It is very confusing behavior. Sorting columns in order - as they are specified in pivot-table - would be much more reasonable & intuitive.

I wanted to add, that not only columns - are currently sorted alphabetically (in output), but also applied functions, if they are specified in list like here: df.pivot_table(index='Region', aggfunc={'OrderAmount': ['sum', 'count', 'mean']})

Final columns will be in different order - 'count', 'mean', 'sum' (i.e. alphabetically again, not in specified order)

the solution from princeinzion is the way, I just want to add that depending on df you are working with , yo could need to add the level when reindexing:

my_report = df.pivot_table(index=[ “ticker”], values=[“c_within_l1”, “c_beyond_l1”, “c_beyond_l2”, “c_beyond_l3”], aggfunc=[np.mean]) new_order=[“c_within_l1”, “c_beyond_l1”, “c_beyond_l2”, “c_beyond_l3”] my_report = my_report.reindex([ new_order, axis=1, level=1) my_report

in this case if you don’t add the level, it throws the tuple error

princeinzion … thanks a lot . reindex resolved the issue

I am having the same issue, here’s a simple example code:

import pandas as pd

tuples_list = [("John", "Foo", 47, 173), ("Michael", "Bar", 33, 182)]
df = pd.DataFrame(tuples_list, columns=["firstname", "lastname", "age", "height"])
print(df.pivot_table(index=["firstname", "lastname"], values=["age", "height"]))
print(df.pivot_table(index=["firstname", "lastname"], values=["height", "age"]))

Both pivot_tables return the same output, however I’d expect the second one to have the height and age columns swapped. If I change the order in ‘index=’ field, it will be reflected in the resulting pivot_table