0

I have a dataframe like

multiindex1 = pd.MultiIndex.from_product([['a'], np.arange(3, 8)]) df1 = pd.DataFrame(np.random.randn(5, 3), index=multiindex1) multiindex2 = pd.MultiIndex.from_product([['s'], np.arange(1, 6)]) df2 = pd.DataFrame(np.random.randn(5, 3), index=multiindex2) multiindex3 = pd.MultiIndex.from_product([['d'], np.arange(2, 7)]) df3 = pd.DataFrame(np.random.randn(5, 3), index=multiindex3) df = pd.concat([df1, df2, df3]) >>> 0 1 2 a 3 0.872208 -0.145098 -0.519966 4 -0.976089 -0.730808 -1.463151 5 -0.026869 0.227912 1.525924 6 -0.161126 -0.415913 -1.211737 7 -0.893881 -0.769385 0.346436 s 1 -0.972483 0.202820 0.265304 2 0.007303 0.802974 -0.254106 3 1.619309 -1.545089 0.161493 4 2.847376 0.951796 -0.877882 5 1.749237 -0.327026 0.467784 d 2 1.440793 -0.697371 0.902004 3 0.390181 -0.449725 -0.462104 4 0.056916 0.140066 0.918281 5 0.164234 -2.491176 2.035113 6 -1.648948 0.372179 0.600297

Now I want to change it into multicolumns like

 a b c 0 1 2 0 1 2 0 1 2 1 Nan Nan Nan ... 2 Nan Nan Nan ... 3 0.872208 -0.145098 -0.519966 ... 4 -0.976089 -0.730808 -1.463151 ... 5 -0.026869 0.227912 1.525924 ... 6 -0.161126 -0.415913 -1.211737 ... 7 -0.893881 -0.769385 0.346436 ...

That's to say I want two goals to be done:

  1. change level0 index(multi index into single index) into level0 columns(single column into multi column)
  2. merge level1 index togather and reindex by it

I've tried stack unstack pivot, but couldn't get the target form. So is there any elegant way to achieve it?

Improve this question

1 Answer 1

Reset to default
1

This seemed to work for me. I used to work with someone who was amazing with multi-indices and he taught me a lot of this.

df.unstack(level=0).reorder_levels([0,1], axis=1).swaplevel(axis=1)[["a", "s", "d"]]

Output:

 a s d 0 1 2 0 1 2 0 1 2 1 NaN NaN NaN 0.206957 1.329804 -0.037481 NaN NaN NaN 2 NaN NaN NaN 0.244912 1.880180 1.447138 -1.009454 0.215560 0.126756 3 0.871496 -1.247274 -0.458660 0.514475 0.989567 -1.653646 -0.623382 -0.799157 0.119259 4 -0.756771 0.523621 0.067967 1.066499 -1.436044 -1.045745 0.440954 -1.997325 -1.223662 5 0.707063 1.019831 0.422577 0.964631 -0.034742 -0.891528 0.891096 -0.724778 -0.043314 6 -0.140548 -0.093853 -1.060963 NaN NaN NaN 0.643902 -0.062267 -0.505484 7 -0.449435 0.360956 -0.769459 NaN NaN NaN NaN NaN NaN
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.