How to reorder the columns of a MultiIndex columns pandas DataFrame?

Question

After having read the pandas reindex docs, it is still not clear to me how to re-arrange the order of the columns in a MultiIndex DataFrame and re-assign it to the original DataFrame. For example, say that I have the following DataFrame

df = 
kind                            A               B
names          u1      u2      u3      y1      y2
Time
0.0        0.5083  0.1007  0.8001  0.7373  0.1387
0.1        0.6748  0.0354  0.0076  0.8421  0.2670
0.2        0.1753  0.1013  0.5231  0.8060  0.0040

and I want to re-order some columns such that at the end I get the following

df = 
kind                            A               B
names          u3      u2      u1      y1      y2
Time
0.0        0.8001  0.1007  0.5083  0.7373  0.1387
0.1        0.0076  0.0354  0.6748  0.8421  0.2670
0.2        0.5231  0.1013  0.1753  0.8060  0.0040

If I do df.reindex(level="names", columns=["u3","u1"]), then I got

kind                    A      
names          u3      u1      
Time
0.0        0.8001  0.5083  
0.1        0.0076  0.6748  
0.2        0.5231  0.1753

which is encouraging, but then I cannot figure out how to re-assign it to the original df. I tried df.loc[:,"A"] = df.reindex(level="names", columns=["u3","u1"]) but what I get is the following

kind                          A               B
names          u1    u2      u3      y1      y2
Time
0.0        0.5083   NaN  0.8001  0.7373  0.1387  
0.1        0.6748   NaN  0.0076  0.8421  0.2670
0.2        0.1753   NaN  0.5231  0.8060  0.0040

jezrael · Answer 1 · 2022-11-03T13:31:55.920

Here is solution for recreate MultiIndex with expected ordering:

print (df)
kind        C       A                       B
names      d1      u2      u3      u1      y2
0.0    0.5083  0.1007  0.8001  0.7373  0.1387
0.1    0.6748  0.0354  0.0076  0.8421  0.2670
0.2    0.1753  0.1013  0.5231  0.8060  0.0040

#original columns
cols = df.columns

#columns after change order
new = df.reindex(level="names", columns=["u3","u2", "u1"]).columns

#filter only tuples from new
d = dict(zip(cols[cols.isin(new)], new))

#recreate Multiindex with mapping and reindex all columns
mux = pd.MultiIndex.from_tuples([d.get(x, x) for x in cols],names=["kind","names"])
df = df.reindex(mux, axis=1)
print (df)
kind        C       A                       B
names      d1      u3      u2      u1      y2
0.0    0.5083  0.8001  0.1007  0.7373  0.1387
0.1    0.6748  0.0076  0.0354  0.8421  0.2670
0.2    0.1753  0.5231  0.1013  0.8060  0.0040

Thanks. I must say that I am shocked that one has to do all such a complex juggling to just re-ordering columns of a DataFrame. — Barzi2001, Nov 03 '22 at 13:00

score 0 · Answer 2 · edited Nov 03 '22 at 13:41

0

df2 = df.reindex(level="names", columns=["u3", "u2", "u1", "y1", "y2"])

edited Nov 03 '22 at 13:41

j__carlson

1,346
3
12
20

answered Nov 03 '22 at 12:55

user19795989

39
8

It does not work. You lose all the `df["B"] DataFrame`. – Barzi2001 Nov 03 '22 at 13:22
Oh sorry, I didn't realise it was multi-index. I think what you're looking for is here however: https://stackoverflow.com/questions/53286882/how-to-reindex-a-multiindex-dataframe – user19795989 Nov 03 '22 at 13:27
1

Thanks, but the use-case looks different. – Barzi2001 Nov 03 '22 at 13:35

How to reorder the columns of a MultiIndex columns pandas DataFrame?

2 Answers2