how to transpose only part of a dataframe or interchange rows and columns?

user_13935104 0
Viewed 166

the input dataframe is this:

branch   Round   letter   var1   var2   var3
  A        1       a       1      2     4
  A        1       b       3      4     5
  B        2       a       6      7     9
  B        2       b       2      3     8

I want to interchange only column 3 i.e. 'letter' with columns 'var1', 'var2', 'var3' like this:

Output Dataframe:

branch   Round   category   a   b
  A        1        var1    1   3
  A        1        var2    2   4
  A        1        var3    4   5
  B        2        var1    6   2
  B        2        var2    7   3
  B        2        var3    9   8

How to do this using pandas??

pandas dataframe python
2 Answers

set branch and letter as index, then stack the dataframe, and unstack at level 1. You can reset the index if needed.

>>>  (df.set_index(['branch', 'letter'])
    .stack()
    .unstack(1)
    .reset_index(-1)
    .rename(columns={'level_1':'category'}))

OUTPUT:

letter category  a  b
branch               
A          var1  1  3
A          var2  2  4
A          var3  4  5
B          var1  6  2
B          var2  7  3
B          var3  9  8

You can even use DataFrame.pivot along with stack:

>>> df.pivot('branch', 'letter').stack(0)

letter       a  b
branch           
A      var1  1  3
       var2  2  4
       var3  4  5
B      var1  6  2
       var2  7  3
       var3  9  8
user_91363480

You can first set ['letter', 'branch'] as index and then use stack().

(
    df.set_index(['letter', 'branch']).T
    .stack()
    .reset_index(level=0)
    .sort_index()
    .rename(columns={'level_0':'category'})
)

letter  category    a   b
branch          
A       var1        1   3
A       var2        2   4
A       var3        4   5
B       var1        6   2
B       var2        7   3
B       var3        9   8
user_6678830
Related