Preserve Column Order When Using Pivot

Question

I am trying to do a simple pivot on my dataframe, with one column as the index, one column as the columns, and one column as the values. Here is a screenshot of the code and the result:

As you can see, it is just one simple line of code. You can also notice that once the table is pivoted, the columns get placed into alphabetical order, which is not acceptable for the purposes of this program.

This is supposed to be a general program for many use cases, so I can't reindex it by the column names like some other posts have suggested. Depending on the use case, it may have anywhere from 400 to 500 columns (later in the script these get put into smaller dataframes), but this one only has 32.

How can I fix this to keep the original sorting of the columns when I pivot without using the specific column names? I want to keep it general so it will work for most use cases.

Answer 1

You can reorder the columns after the pivoting by selecting a column list in the original order. This column list is obtained from the original dataframe in generic way by selecting the column names for the first line.

Example:

df = pd.DataFrame({'equipment name':['r1', 'r1', 'r2', 'r2'], 'name': ['col2', 'col1', 'col2', 'col1'], 'value': [1,2,3,4]})

#alphabetic order
pd.pivot(df, 'equipment name', 'name', 'value')
#name            col1  col2
#equipment name            
#r1                 2     1
#r2                 4     3

#original order
cols = df[df['equipment name']==df['equipment name'][0]].name.tolist()
pd.pivot(df, 'equipment name', 'name', 'value')[cols]
#name            col2  col1
#equipment name            
#r1                 1     2
#r2                 3     4