transpose column and bind it to a row in pandas

Question

I have a dataset with the following format:

df
----------------------------
ID |  T1 |   C1 |  C2 | C3 
----------------------------
ID1  1-0w    Yes   No  
ID1  1-0a    Yes   No  XYZ
ID2  1-2w    No   Yes  
ID2  1-0a    Yes   No  YZ

I am interested in transposing column T1 such that, ID rows are unique. For example, as follows:

---------------------------------------------------------------------------------------------
ID |  1-0w-C1 |  1-0w-C2 | 1-0w-C3| 1-0a-C1 | 1-0a-C2 | 1-0a-C3| 1-2w-C1 | 1-2w-C2 | 1-2w-C3|
---------------------------------------------------------------------------------------------
ID1   Yes          No        XYZ      Yes        No        XYZ
ID2                                   Yes        No        YZ       No      Yes

I tried a simple transpose but it does not work as I intend. I am not interested in just changing rows to columns or vice-versa but I am more interested in making each row unique in a way that first column 2 (T1) is transformed into columns based on the unique values in T1+the original col names (C1,C2, and C3) and then imputing the corresponding values.

Any suggestions regarding this problem?

Look into pivot, df.pivot(index = 'ID', columns = 'T1', values = ['C1', 'C2', 'C3']) — Vaishali, Feb 12 '19 at 17:22

score 2 · Accepted Answer · answered Feb 12 '19 at 17:23

IIUC pivot + column flatten

s=df.pivot_table(['C1','C2','C3'],index='ID',columns='T1',aggfunc='sum').sort_index(level=1,axis=1)
s.columns=s.columns.map('{0[1]}-{0[0]}'.format) 
s
Out[297]: 
    1-0a-C1 1-0a-C2 1-0a-C3 1-0w-C1   ...   1-0w-C3 1-2w-C1 1-2w-C2 1-2w-C3
ID                                    ...                                  
ID1     Yes      No     XYZ     Yes   ...         0     NaN     NaN     NaN
ID2     Yes      No      YZ     NaN   ...       NaN      No     Yes       0
[2 rows x 9 columns]

transpose column and bind it to a row in pandas

1 Answers1