Flatten multiindex dataframe in Pandas

Question

I've got a pandas dataframe that looks like this:

                                  M
AAL XXX                 1.244000e+09
    YYY                 9.036011e+00
    ZZZ                 0.000000e+00
    WWW                 1.124000e+09
    UUU                -2.220000e+08
...                                           ...
XPO XXX                -3.221000e+06
    YYY                 2.450590e+08
    ZZZ                 1.770200e+07
    III                 1.770200e+07
    NNN                 2.719070e+08

With index 0 being AAL, ... and XPO. There are also some rows in index 1 that differ from index 0 to index 0. For example feature UUU is present only in AAL while III is present only in XPO. They both contain feature XXX, YYY and ZZZ though.

I want the desired output to be:

Index_0_Orignal XXX         YYY          ZZZ          WWW          UUU ...       III          NNN
AAL             1.244000e+0 9.036011e+00 0.000000e+00 1.124000e+09 -2.220000e+08 
XPO             -3.221000e  2.450590e+08 1.770200e+07                            1.770200e+07 2.719070e+08

Trying the solutions posted in the marked duplicate post does not work and yields a ValueError: Index contains duplicate entries, cannot reshape.

score 0 · Accepted Answer · answered Jul 26 '23 at 13:01

0

Use unstack:

>>> df.unstack(level=1).droplevel(0,1)

answered Jul 26 '23 at 13:01

not_speshal

22,093
2
15
30

this unfortunately does not work and yields a ValueError: Index contains duplicate entries, cannot reshape – sword134 Jul 26 '23 at 15:21
Can you update your post to provide a sample of your data using `df.head(10).to_dict()`? – not_speshal Jul 26 '23 at 15:30

Flatten multiindex dataframe in Pandas

1 Answers1