Python Pandas: Sorting Pivot Table column by another column-CodePudding

I am trying to pivot some data in Python pandas package by using the pivot_table feature but as part of this I have a specific, bespoke order that I want to see my columns returned in - determined by a Sort_Order field which is already in the dataframe. So for test example with:


raw_data = {'Support_Reason' : ['LD', 'Mental Health', 'LD', 'Mental Health', 'LD', 'Physical', 'LD'],
            'Setting' : ['Nursing', 'Nursing', 'Residential', 'Residential', 'Community', 'Prison', 'Residential'],
            'Setting_Order' : [1, 1, 2, 2, 3, 4, 2],
            'Patient_ID' : [6789, 1234, 4567, 5678, 7890, 1235, 3456]}

Data = pd.DataFrame(raw_data, columns = ['Support_Reason', 'Setting', 'Setting_Order', 'Patient_ID'])

Data

Then pivot:

pivot = pd.pivot_table(Data, values='Patient_ID', index=['Support_Reason'],
                   columns=['Setting'], aggfunc='count',dropna = False)
pivot  = pivot.reset_index()

pivot

This is exactly how I want my table to look except that the columns have defaulted to A-Z ordering. I would like them to be ordered Ascending as per the Setting_Order column - so that would be order of Nursing, Residential, Community then Prison. Is there some additional syntax that I could add to my pd.pivot_table code would make this possible please?

I realise there are a few different work-arounds for this, the simplest being re-ordering the columns afterwards(!) but I want to avoid having to hard-code column names as these will change over time (both the headings and their order) and the Setting and Setting_Order fields will be managed in a separate reference table. So any form of answer that will avoid having to list Settings in code would be ideal really.

CodePudding user response：

col_order = list(Data.sort_values('Setting_Order')['Setting'].unique())
pivot[col_order ['Support_Reason']]

Does this help?

CodePudding user response：

Try:

ordered = df.sort_values("Setting_Order")["Setting"].drop_duplicates().tolist()
pivot = pivot[list(pivot.columns.difference(ordered)) ordered]