Convert table format - long to wide - table with two columns - Pandas / Excel

mah65 0
Viewed 24

I have a table like this

Program     Seg     Percent
N           A       1%
N           B       13%
N           C       4%
Y           A       6%
Y           B       5%
Y           C       8%

Would like to convert it to:

Seg        A           A          B           B           C           C
Program    N           Y          N           Y           N           Y
Percent    1%          6%         13%         5%          4%          8%

Whether seg or program appear as title of columns or row index is not really important. Just want it to be selectable in some way in python.

Note that simply transposing is not what I'm looking for. I need to have 'Seg' as the first variable, then 'Program'.

Looking for the solution in both Pandas and possibly Excel.

Thanks

pivot dataframe pandas excel python
1 Answers

Create subset with expected order, sort by both columns by DataFrame.sort_values and last transpose by DataFrame.T:

df = df[['Seg','Program','Percent']].sort_values(['Seg','Program']).T
print (df)
          0   3    1   4   2   5
Seg       A   A    B   B   C   C
Program   N   Y    N   Y   N   Y
Percent  1%  6%  13%  5%  4%  8%

Solution with MulitIndex (repeated values are not displayed only):

df = df.set_index(['Seg','Program'])[['Percent']].sort_index().T
print (df)
Seg       A        B       C    
Program   N   Y    N   Y   N   Y
Percent  1%  6%  13%  5%  4%  8%
jezrael0
Related