CODEWITHSUNDEEP
python-3.xpandasdataframepivot
Alberto F
Alberto F

Reputation: 95

Pandas pivot index level3 and change with columns

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.

Upvotes: 2

Views: 70

Answers (1)

jezrael
jezrael

Reputation: 862791

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

Upvotes: 1

Related Questions