Pivot some rows to new columns in DataFrame

Question

I'm after a pythonic and pandemic (from pandas, pun not intended =) way to pivot some rows in a dataframe into new columns.

My data has this format:

           dof  foo  bar  qux
idxA idxB                    
100  101     1   10   30   50
     101     2   11   31   51
     101     3   12   32   52
     102     1   13   33   53
     102     2   14   34   54
     102     3   15   35   55
200  101     1   16   36   56
     101     2   17   37   57
     101     3   18   38   58
     102     1   19   39   59
     102     2   20   40   60
     102     3   21   41   61

The variables foo, bar and qux actually have 3 dimensional coordinates, which I would like to call foo1, foo2, foo3, bar1, ..., qux3. These are identified by the column dof. Each row represents one axis in 3D, dof == 1 is the x axis, dof == 2 the y axis and dof == 3 is the z axis.

So, here is the final dataframe I want:

           foo1  bar1  qux1  foo2  bar2  qux2  foo3  bar3  qux3
idxA idxB                                                      
100  101     10    30    50    11    31    51    12    32    52
     102     13    33    53    14    34    54    15    35    55
200  101     16    36    56    17    37    57    18    38    58
     102     19    39    59    20    40    60    21    41    61

Question is: what is the best way to do that?

Here is what I have done.

A code to re-create my dataset in a dataframe:

import pandas as pd

data = [[100, 101, 1, 10, 30, 50],
        [100, 101, 2, 11, 31, 51],
        [100, 101, 3, 12, 32, 52],
        [100, 102, 1, 13, 33, 53],
        [100, 102, 2, 14, 34, 54],
        [100, 102, 3, 15, 35, 55],
        [200, 101, 1, 16, 36, 56],
        [200, 101, 2, 17, 37, 57],
        [200, 101, 3, 18, 38, 58],
        [200, 102, 1, 19, 39, 59],
        [200, 102, 2, 20, 40, 60],
        [200, 102, 3, 21, 41, 61],
        ]

df = pd.DataFrame(data=data, columns=['idxA', 'idxB', 'dof', 'foo', 'bar', 'qux'])
df.set_index(['idxA', 'idxB'], inplace=True)

Code to do what I would like to do:

df2 = df[df.dof == 1].reset_index()[['idxA', 'idxB']]
df2.set_index(['idxA', 'idxB'], inplace=True)
for pivot in [1, 2, 3]:
    df2.loc[:, 'foo%d' % pivot] = df[df.dof == pivot]['foo']
    df2.loc[:, 'bar%d' % pivot] = df[df.dof == pivot]['bar']
    df2.loc[:, 'qux%d' % pivot] = df[df.dof == pivot]['qux']

However I'm not too happy with these .loc calls and incremental column additions to the dataframe. I thought that pandas being awesome as it is would have a neater way of doing that. A one-liner would be super cool.

Ch3steR · Accepted Answer · 2020-06-02 21:22:08Z

4

You can try df.pivot

df = df.pivot(columns='dof')
          foo         bar         qux
dof         1   2   3   1   2   3   1   2   3
idxA idxB
100  101   10  11  12  30  31  32  50  51  52
     102   13  14  15  33  34  35  53  54  55
200  101   16  17  18  36  37  38  56  57  58
     102   19  20  21  39  40  41  59  60  61

Now join using df.columns

df.columns = df.columns.map('{0[0]}{0[1]}'.format) #suggested by @YOBEN_S

          foo1  foo2  foo3  bar1  bar2  bar3  qux1  qux2  qux3
idxA idxB
100  101     10    11    12    30    31    32    50    51    52
     102     13    14    15    33    34    35    53    54    55
200  101     16    17    18    36    37    38    56    57    58
     102     19    20    21    39    40    41    59    60    61

edited Jun 2, 2020 at 21:22

answered Jun 2, 2020 at 20:37

Ch3steR

20.8k4 gold badges34 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BENY Over a year ago

df.columns = df.columns.map('{0[0]}{0[1]}'.format) ;-)

r.ook Over a year ago

@YOBEN_S Or even the needlessly complicated df.columns = df.columns.to_frame().astype(str).sum(1) :)

BENY Over a year ago

@Ch3steR Sure, no problem ~

Quang Hoang · Accepted Answer · 2020-06-02 20:26:55Z

3

You can add dof to index and do a unstack:

new_df = df.set_index('dof',append=True).unstack('dof')
new_df.columns = [f'{x}{y}' for x,y in new_df.columns]

Output:

           foo1  foo2  foo3  bar1  bar2  bar3  qux1  qux2  qux3
idxA idxB                                                      
100  101     10    11    12    30    31    32    50    51    52
     102     13    14    15    33    34    35    53    54    55
200  101     16    17    18    36    37    38    56    57    58
     102     19    20    21    39    40    41    59    60    61

answered Jun 2, 2020 at 20:26

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

1 Comment

Raf Over a year ago

This is beautiful ! =)

Collectives™ on Stack Overflow

Pivot some rows to new columns in DataFrame

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related