Pandas: pivot_table not displaying values columns in expected order

[ericbhanson]

Code Sample, a copy-pastable example if possible

# Your code here
column_order = ['Year', 'Month', 'Currency', 'Total Net', 'Fees']
df_fills = df_fills.reindex_axis(column_order, axis = 1)

pd.pivot_table(df_fills, values = ['Total Net', 'Fees'], index = ['Year', 'Month'], 
               columns = ['Currency'], aggfunc = np.sum, margins = True, fill_value = 0)

Problem description

When creating the dataframe, Fees column comes first (it's from an external data set), while Total Net comes second (it's calculated). I reordered them using reindex_axis and when asking Python to show the dataframe, I get the expected order. However, when creating a pivot table, Fees always comes first, no matter what.

Expected Output

pivot_table should display columns of values in the order entered in the function.

Output of pd.show_versions()

Paste the output here pd.show_versions() here

INSTALLED VERSIONS

commit: None
python: 2.7.13.final.0
python-bits: 64
OS: Darwin
OS-release: 16.7.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: None.None

pandas: 0.20.2
pytest: 3.0.7
pip: 9.0.1
setuptools: 27.2.0
Cython: 0.25.2
numpy: 1.12.1
scipy: 0.19.0
xarray: None
IPython: 5.3.0
sphinx: 1.5.6
patsy: 0.4.1
dateutil: 2.6.0
pytz: 2017.2
blosc: None
bottleneck: 1.2.1
tables: 3.3.0
numexpr: 2.6.2
feather: None
matplotlib: 2.0.2
openpyxl: 2.4.7
xlrd: 1.0.0
xlwt: 1.2.0
xlsxwriter: 0.9.6
lxml: 3.7.3
bs4: 4.6.0
html5lib: 0.999
sqlalchemy: 1.1.9
pymysql: None
psycopg2: None
jinja2: 2.9.6
s3fs: None
pandas_gbq: None
pandas_datareader: None

[gfyoung]

@ericbhanson : Thanks for the issue! For easier evaluation, could you provide (in DataFrame form) the current and expected output from the code?

[ericbhanson]

@gfyoung happy to - are you looking for a simple copy and paste of what I'm seeing in Jupyter versus what I'm expecting to see?

[gfyoung]

Yes, that would work (also the code to construct your expected output would be good too)

[ericbhanson]

@gfyoung

Turns out to be a little more difficult than I expected 😄. Since Git doesn't do pivot tables, and the data is sensitive information, I took a screen shot of the top of the pivot table.

Here's the code, with the source of the data removed.

import pandas as pd
import numpy as np

pd.options.display.float_format = '${:,.2f}'.format

df_fills = pd.DataFrame()

for fills in # [API data source] #:
    for fill in fills:
        df_fills = df_fills.append(pd.Series(fill), ignore_index = True)

df_fills.rename(columns = {'product_id' : 'Currency', 'fee' : 'Fees'}, inplace = True)
df_fills['Year'] = pd.DatetimeIndex(df_fills['created_at']).year
df_fills['Month'] = pd.DatetimeIndex(df_fills['created_at']).month
df_fills['price'] = pd.to_numeric(df_fills['price'])
df_fills['size'] = pd.to_numeric(df_fills['size'])
df_fills['Fees'] = pd.to_numeric(df_fills['Fees'])
df_fills['buy_net'] = (df_fills[(df_fills["side"] == 'buy')]['price'] * 
                       df_fills[(df_fills["side"] == 'buy')]['size']) - df_fills[(df_fills["side"] == 'buy')]['Fees']
df_fills['sell_net'] = ((df_fills[(df_fills["side"] == 'sell')]['price'] * 
                       df_fills[(df_fills["side"] == 'sell')]['size']) - df_fills[(df_fills["side"] == 'sell')]['Fees'])
df_fills = df_fills.fillna(0)
df_fills['Total Net'] = df_fills['sell_net'] - df_fills['buy_net']
df_fills.drop(['liquidity', 'order_id', 'profile_id', 'settled', 'trade_id', 'user_id', 'price', 'size',
               'created_at', 'side', 'buy_net', 'sell_net'], axis = 'columns', inplace = True)

column_order = ['Year', 'Month', 'Currency', 'Total Net', 'Fees']
df_fills = df_fills.reindex_axis(column_order, axis = 1)

pd.pivot_table(df_fills, values = ['Total Net', 'Fees'], index = ['Year', 'Month'], 
               columns = ['Currency'], aggfunc = np.sum, margins = True, fill_value = 0)

As you can see, the Fees column shows up first, but - based on the call to reindex_axis() and how I listed the columns in pivot_table() - it should be showing up after Total Net, which is what I want. Let me know if that makes sense.

[gfyoung]

@ericbhanson : That definitely helps! Thanks for doing this, especially given the sensitive nature of the data (which is actually not even necessary here).

Could you put pictures of what is currently output and what you expected in your original issue description? That would be useful for anyone who just want to get an idea of what's going on.

BTW, feel free to also contribute a PR to patch this!

[jreback]

I am not at all clear what is going on. this needs a code reproducible example.

[ericbhanson]

@jreback in the screen shot I enclosed above, do you see how the column marked Fees is displayed before the column marked Total Net? This is the opposite order of what I would expect, given that I listed Total Net first in my list of values when creating the pivot table.

I can see if I can reproduce with a more generic dataset so I can include a complete code example, but please let me know if what I'm saying makes sense.

[jreback]

@ericbhanson screen shots are not very useful. it makes it impossible to even look at what you are doing. pls post a copy-pastable minimal example.

[ericbhanson]

@jreback A dummy CSV data file that closely replicates my original data source and works with the code below can be found here: https://drive.google.com/open?id=0B5QZk8ir7q-yTEJVOGdPeWtyZ1U

The code I'm using:

import pandas as pd
import numpy as np

pd.options.display.float_format = '${:,.2f}'.format

df_fills = pd.read_csv('pivot_example.csv')

df_fills.rename(columns = {'product_id' : 'Currency', 'fee' : 'Fees'}, inplace = True)
df_fills['Year'] = pd.DatetimeIndex(df_fills['created_at']).year
df_fills['Month'] = pd.DatetimeIndex(df_fills['created_at']).month
df_fills['price'] = pd.to_numeric(df_fills['price'])
df_fills['size'] = pd.to_numeric(df_fills['size'])
df_fills['Fees'] = pd.to_numeric(df_fills['Fees'])
df_fills['buy_net'] = (df_fills[(df_fills["side"] == 'buy')]['price'] * 
                       df_fills[(df_fills["side"] == 'buy')]['size']) - df_fills[(df_fills["side"] == 'buy')]['Fees']
df_fills['sell_net'] = ((df_fills[(df_fills["side"] == 'sell')]['price'] * 
                       df_fills[(df_fills["side"] == 'sell')]['size']) - df_fills[(df_fills["side"] == 'sell')]['Fees'])
df_fills = df_fills.fillna(0)
df_fills['Total Net'] = df_fills['sell_net'] - df_fills['buy_net']
df_fills.drop(['liquidity', 'order_id', 'profile_id', 'settled', 'trade_id', 'user_id', 'price', 'size',
               'created_at', 'side', 'buy_net', 'sell_net'], axis = 'columns', inplace = True)

column_order = ['Year', 'Month', 'Currency', 'Total Net', 'Fees']
df_fills = df_fills.reindex_axis(column_order, axis = 1)

pd.pivot_table(df_fills, values = ['Total Net'], index = ['Year', 'Month'], 
               columns = ['Currency'], aggfunc = np.sum, margins = True, fill_value = 0)

[jreback]

@ericbhanson pls make a much simpler example.all of the things before pivot_table are not relevant. simply construct a sample (short) frame.

[ericbhanson]

@jreback to avoid further back and forth, please let me know specifically what you need to see in the data frame in order to validate the existence of the bug.

[ericbhanson]

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.

[gfyoung]

@ericbhanson : Excellent! Contributions are definitely welcome and appreciated! Thanks for looking into this despite the inability to share your data :smile:

[helikopterodaktyl]

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

[vhlongmore]

@ericbhanson If you haven't already made a code contribution but it is forthcoming, you may also want to consider that when margin=True, the margin row gets sorted automatically as well (and probably shouldn't ever as it is for sub/grand totals).

[princeinzion]

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

[sreehari54]

princeinzion .. thanks a lot . reindex resolved the issue

[skinTest]

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

will this work when multiple columns are passed to the function like pivot_tables(columns=['foo', 'bar'])?

[AngieZhang1025]

Any workaround on this?

[jc085]

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

[mohitthenewj]

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

This was helpful, solved in my case.