Pandas pivot index level3 and change with columns

user_14377952 0
Viewed 67

I need help with one problem:

I've got a dataframe like:

               a    b    c
0   0   40   1.0  2.0  3.0
        50   4.0  5.0  6.0
    1   40   7.0  8.0  9.0
        50  10.0 11.0 12.0
1   0   40  13.0 14.0 15.0
        50  16.0 17.0 18.0
    1   40  19.0 20.0 21.0
        50  22.0 23.0 24.0

Now, I did some conversions (some of them are neccesary) and ive got something like

             a    b    c
(0,0,40)   1.0  2.0  3.0
(0,0,50)   4.0  5.0  6.0
(0,1,40)   7.0  8.0  9.0
(0,1,50)  10.0 11.0 12.0
(1,0,40)  13.0 14.0 15.0
(1,0,50)  16.0 17.0 18.0
(1,1,40)  19.0 20.0 21.0
(1,1,50)  22.0 23.0 24.0

''' And i would like to pivot the last index (40 or 50) and changed with columns, have something like:

            40     50
0   0  a   1.0    4.0
       b   2.0    5.0
       c   3.0    6.0
    1  a   7.0   10.0
       b   8.0   11.0
       c   9.0   12.0
1   0  a  13.0   16.0
       b  14.0   17.0
       c  15.0   18.0
    1  a  19.0   22.0
       b  20.0   23.0
       c  21.0   24.0

or with tuple form as index like:

  (0,0,a)  1.0   4.0
  (0,0,b)  2.0   5.0
  (0,0,c)  3.0   6.0
  (0,1,a)  7.0   10.0
  (0,1,b)  8.0   11.0
  (0,1,c)  9.0   12.0
  and so on

Thanks all of you for your answers.

pandas dataframe pivot python-3.x
1 Answers

Prosessing index filled with tuples is problematic, need MulitIndex.

#input data
print (df)
               a     b     c
(0, 0, 40)   1.0   2.0   3.0
(0, 0, 50)   4.0   5.0   6.0
(0, 1, 40)   7.0   8.0   9.0
(0, 1, 50)  10.0  11.0  12.0
(1, 0, 40)  13.0  14.0  15.0
(1, 0, 50)  16.0  17.0  18.0
(1, 1, 40)  19.0  20.0  21.0
(1, 1, 50)  22.0  23.0  24.0

First convert values from tuples to MultiIndex by MultiIndex.from_tuples:

df.index = pd.MultiIndex.from_tuples(df.index)
print (df)
           a     b     c
0 0 40   1.0   2.0   3.0
    50   4.0   5.0   6.0
  1 40   7.0   8.0   9.0
    50  10.0  11.0  12.0
1 0 40  13.0  14.0  15.0
    50  16.0  17.0  18.0
  1 40  19.0  20.0  21.0
    50  22.0  23.0  24.0

then DataFrame.stack and Series.unstack:

df = df.stack().unstack(level=2)
print (df)
         40    50
0 0 a   1.0   4.0
    b   2.0   5.0
    c   3.0   6.0
  1 a   7.0  10.0
    b   8.0  11.0
    c   9.0  12.0
1 0 a  13.0  16.0
    b  14.0  17.0
    c  15.0  18.0
  1 a  19.0  22.0
    b  20.0  23.0
    c  21.0  24.0

And last if necessary cobvert back to tuples:

df.index = df.index.map(tuple).tolist()
print (df)
             40    50
(0, 0, a)   1.0   4.0
(0, 0, b)   2.0   5.0
(0, 0, c)   3.0   6.0
(0, 1, a)   7.0  10.0
(0, 1, b)   8.0  11.0
(0, 1, c)   9.0  12.0
(1, 0, a)  13.0  16.0
(1, 0, b)  14.0  17.0
(1, 0, c)  15.0  18.0
(1, 1, a)  19.0  22.0
(1, 1, b)  20.0  23.0
(1, 1, c)  21.0  24.0
user_29010020
Related