I swap levels on a multindex data frame and when the swap occurs I would like the the "like" entries in levels to be consolidated.
I've tried a simple dataframe.swap(i,j) and that swaps them but it does not perform the consolidation I want.
import pandas as pd
idx2 = pd.MultiIndex.from_tuples([("A5",6),("A5",1), ("A2",2),("A2",1),("A3",1),("A3",2), ("A4",4),("A4",1), ("A1",1),("A1",2)],
names = ['first','second'])
df2 = pd.DataFrame({"A":[10, 11, 7, 8, 5,1,2,3,4,5],
"B":[21, 5, 32, 4, 6,1,2,3,4,5],
"C":[11, 21, 23, 7, 9,1,2,3,4,5],
"D":[1, 5, 3, 8, 6,1,2,3,4,5]},
index = idx2)
df2
A B C D
first second
A5 6 10 21 11 1
1 11 5 21 5
A2 2 7 32 23 3
1 8 4 7 8
A3 1 5 6 9 6
2 1 1 1 1
A4 4 2 2 2 2
1 3 3 3 3
A1 1 4 4 4 4
2 5 5 5 5
df2.swap(0,1)
A B C D
second first
6 A5 10 21 11 1
1 A5 11 5 21 5
2 A2 7 32 23 3
1 A2 8 4 7 8
A3 5 6 9 6
2 A3 1 1 1 1
4 A4 2 2 2 2
1 A4 3 3 3 3
A1 4 4 4 4
2 A1 5 5 5 5
I want
df2.swaplevel(0,1)
A B C D
second first
1 A1 4 4 4 4
A2 8 4 7 8
A3 5 6 9 6
A4 3 3 3 3
A5 11 5 21 5
2 A1 5 5 5 5
2 A2 7 32 23 3
2 A3 1 1 1 1
4 A4 2 2 2 2
6 A5 10 21 11 1