Geek Answers Handbook

Python Pandas: transpose or stacking?

Hello, I have an example data frame below. I am having problems getting the desired results using transpose ....

x = ('P', 'P', 'O', 'DNP', 'D') y = ('O', 'O', 'D', 'DNP', 'DNP') z = ('P', 'P', 'O', 'U', 'DNP') a = ('O', 'O', 'D', 'DNP', 'DNP') b = ('P', 'DNP', 'O', 'U', 'DNP') ID = ['ID1', 'ID2', 'ID3', 'ID4', 'ID5'] df = DataFrame(zip(ID, a, b, x, y, z), columns = ['id', 'a', 'b', 'x', 'y', 'z']) id abxyz 0 ID1 OPPOP 1 ID2 O DNP POP 2 ID3 DOODO 3 ID4 DNP U DNP DNP U 4 ID5 DNP DNP D DNP DNP

A simple df.transpose () produces ...

 0 1 2 3 4 id ID1 ID2 ID3 ID4 ID5 a OOD DNP DNP b P DNP OU DNP x PPO DNP D y OOD DNP DNP z PPOU DNP

The required output is as follows.

  ID1 a O ID1 b P ID1 x P ID1 y O ID1 z P ID2 a O ID2 b DNP ID2 x P ID2 y O ID2 z P

etc. etc ...... I appreciate any help!

+4
source share
1 answer

You can use pd.melt :

 In [23]: pd.melt(df, id_vars=['id'], var_name='colvals', value_name='DOPU') Out[23]: id colvals DOPU 0 ID1 a O 1 ID2 a O 2 ID3 a D ... 21 ID2 z P 22 ID3 z O 23 ID4 z U 24 ID5 z DNP

Or, alternatively, you can set id as an index before calling stack :

 In [21]: df.set_index('id').stack() Out[21]: id ID1 a O b P x P y O z P ... ID5 a DNP b DNP x D y DNP z DNP dtype: object

stack moves column level values ​​to index. Since the desired result has id values ​​in the index, it is natural to use set_index to move the id column to the index first, and then call stack .

Call reset_index to move the index levels into the DataFrame columns:

 In [164]: df.columns.name = 'colvals' In [165]: df.set_index('id').stack().reset_index() Out[165]: id colvals 0 0 ID1 a O 1 ID1 b P 2 ID1 x P 3 ID1 y O 4 ID1 z P ... 20 ID5 a DNP 21 ID5 b DNP 22 ID5 x D 23 ID5 y DNP 24 ID5 z DNP
+2
source

More articles:


All Articles