CODEWITHSUNDEEP
pythonpandas
J Spratt
J Spratt

Reputation: 2012

pandas: reorder pivot table columns to an arbitrary order

Suppose I have a pivot table that looks like the following:

                             completed_olns                  total_completed_olns
work_type                             A     B          C                     
employee                                                                     
Employee1                            94  1163          1                 1258
Employee2                           168   770          4                  942
Employee3                           173   746          8                  927

How can I rearrange the columns A, B, C to an arbitrary order, such as B, A, C?

This data is being output from a database and read in through csv with pd.read_csv()

I've tried pivot.sort_index(level=1, axis=1, ascending=False) which gets me closer but not to what I need.

I've also tried pivot = pivot[['B', 'A', 'C']] which gives me:

KeyError: "['B', 'A', 'C'] not in index"

These are the two most common suggestions that I've found.

Upvotes: 1

Views: 293

Answers (1)

ALollz
ALollz

Reputation: 59519

Use .reindex with the level argument

Sample Data

import pandas as pd
import numpy as np

df = pd.DataFrame(data = np.random.randint(1,10,(3,6)))
df.columns = pd.MultiIndex.from_product([['collected', 'total_collected'], ['A','B','C']])
#  collected       total_collected      
#          A  B  C               A  B  C
#0         2  6  9               9  6  6
#1         5  4  4               5  2  6
#2         8  9  3               9  2  7

Code

df.reindex(axis=1, level=1, labels=['B', 'A', 'C'])
#  collected       total_collected      
#          B  A  C               B  A  C
#0         6  2  9               6  9  6
#1         4  5  4               2  5  6
#2         9  8  3               2  9  7

With groups missing labels or labels that never exist, it won't insert all NaN columns

df.iloc[:, :-1].reindex(axis=1, level=1, labels=['B', 'A', 'C', 'D'])
#  collected       total_collected   
#          B  A  C               B  A
#0         6  2  9               6  9
#1         4  5  4               2  5
#2         9  8  3               2  9

Upvotes: 0

Related Questions