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
...
>Solution :
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