Suppose I have a dataframe where my columns are a MultiIndex
col = pd.MultiIndex.from_product(
[[1, 2], ['A', 'B'], ['First', 'Second']],
names=['Cat', 'Dog', 'Bird']
)
dat = np.arange(16).reshape(2, -1)
df = pd.DataFrame(dat, columns=col)
df
Cat 1 2
Dog A B A B
Bird First Second First Second First Second First Second
0 0 1 2 3 4 5 6 7
1 8 9 10 11 12 13 14 15
I want to adjust the columns such that the Bird level is on top, Cat level shifts down to the middle, and Dog goes to the bottom.
Using swaplevel can be used in succession but feels clumsy to create a whole dataframe in the middle just to augment the columns.
df.swaplevel(0, 2, 1).swaplevel(1, 2, 1).sort_index(1)
Bird First Second
Cat 1 2 1 2
Dog A B A B A B A B
0 0 2 4 6 1 3 5 7
1 8 10 12 14 9 11 13 15
Create new MultiIndex should be efficient but is not as intuitive and may be unnecessarily verbose.
def roll(x):
return x[-1:] + x[:-1]
df.set_axis(
pd.MultiIndex.from_tuples(
[roll(x) for x in df.columns.values],
names=roll(df.columns.names)
), axis=1, inplace=False).sort_index(1)
Bird First Second
Cat 1 2 1 2
Dog A B A B A B A B
0 0 2 4 6 1 3 5 7
1 8 10 12 14 9 11 13 15
Is there a clean and intuitive way to do this without creating an intermediate dataframe in the middle?
Is this what you need Sir ? Using reorder_levels:
df.reorder_levels(['Bird','Cat','Dog'],axis=1).sort_index(level=0,axis=1)
Out[396]:
Bird First Second
Cat 1 2 1 2
Dog A B A B A B A B
0 0 2 4 6 1 3 5 7
1 8 10 12 14 9 11 13 15