Transpose row with column instead of column with row-CodePudding

Can the transpose convert data by go through the first row of all the column then only the second row of all the column, instead of go through the first column of all the row then only the second column of all the row?

Means require to convert the column to row which all the same data can be in one group.

Original data:

 columnA     columnB     columnC     columnD     columnE   ...
   IdA          a           b            c           d
   IdB          5           6            7           8
   IdC          e           f            g           h
   IdD          4           5            6           7

transpose = pd.melt(id_vars = ['columnA']
          ,var_name = ['header']
          ,value_name = 'info')

Output:

 columnA    header     info
    IdA     columnB      a
    IdB     columnB      5
    IdC     columnB      e
    IdD     columnB      4

    IdA     columnC      b
    IdB     columnC      6
    IdC     columnC      f
    IdD     columnC      5

...

Expected output:

 columnA    header     info
    IdA     columnB      a
    IdA     columnC      b
    IdA     columnD      c
    IdA     columnE      d

    IdB     columnB      5
    IdB     columnC      6
    IdB     columnD      7
    IdB     columnE      8

...

CodePudding user response：

You will need to use a stack:

out = (df.set_index('columnA').rename_axis(columns='header')
         .stack(dropna=False).reset_index(name='info')
      )

NB. by default, stack drops the NaN values, to keep them use the dropna=False parameter.

Output:

   columnA   header info
0      IdA  columnB    a
1      IdA  columnC    b
2      IdA  columnD    c
3      IdA  columnE    d
4      IdB  columnB    5
5      IdB  columnC    6
6      IdB  columnD    7
7      IdB  columnE    8
8      IdC  columnB    e
9      IdC  columnC    f
10     IdC  columnD    g
11     IdC  columnE    h
12     IdD  columnB    4
13     IdD  columnC    5
14     IdD  columnD    6
15     IdD  columnE    7