CODEWITHSUNDEEP
pythonpandas
TangoAlee
TangoAlee

Reputation: 1380

Sorting column label pandas pivot table

Another pandas sort question (tried all current SO ones and didn't find any solutions).

I have a pandas pivot_table like so:

rows = ['Tool_Location']
cols = ['shift_date','Part_Number', 'shift']
pt = df.pivot_table(index='Tool_Location', values='Number_of_parts', dropna=False, fill_value ='0', columns=cols, aggfunc='count')

produces:

Shift date     10/19
Part_number   40001
shift        first  second  third
tool_loc
T01            0     1     0       
T02            2     1     0

I'd like to switch the order of shift labels so it is third first second

EDIT:

Getting closer to a solution but not seeing it.

Using:

 col_list = pt.columns.tolist()
 output:
 [('10/20/16', 'first'), ('10/20/16', 'second'), ('10/20/16', 'third'), ('10/21/16', 'first'), ('10/21/16', 'second'), ('10/21/16', 'third')]

Anyone know how to dynamically reorder the items so its:

[('10/20/16', 'third'), ('10/20/16', 'first'), ('10/20/16', 'second'), ('10/21/16', 'first'), ('10/21/16', 'second'), ('10/21/16', 'second')]

Because then we could reorder the columns by using pt = pt[col_list]

Upvotes: 0

Views: 2903

Answers (2)

Wojciech Jarmosz
Wojciech Jarmosz

Reputation: 26

You can use lambda and dict to sort your tuples:

pt = pt[sorted(df.columns.tolist(), key=lambda tup: list(your_dict.keys())[list(your_dict.values()).index(tup[1])])]

To do this, you have also declare a dict with needed order:

your_dict = {1: 'first', 3: 'third', 2: 'second'}

Upvotes: 1

somesingsomsing
somesingsomsing

Reputation: 3350

df.pivot_table produces a dataframe. What if you do something like this after your lines:

pt = pt[["third","first","second"]]

Upvotes: 2

Related Questions