How to transpose dataframe using classification variable

Question

I have dataframe in following format-

  Index Stock Open
    1    ABC   10
    2    ABC   12
    :     :    :
    1    PQR   12
    2    PQR   23
    :     :    :
    1    XYZ   0.5
    2    XYZ   0.9
    :     :    :

I would like transform above dataframe using variable(Stock) which classification variable, required format is shown below-

   Index    ABC      PQR      XYZ
     1      10       12       0.5
     2      12       23       0.9
     :      :        :         :

Note-there might be multiple variable...

Please tell me how can transform or transpose dataframe in above format.

score 1 · Accepted Answer · answered Dec 04 '18 at 09:51

I think you are searching for a pivot table:

df.pivot('Index', 'Stock', ['Open', 'Close'])

       Open            Close            
Stock   ABC   PQR  XYZ   ABC   PQR   XYZ
Index                                   
1      10.0  12.0  0.5  13.0  15.0  0.13
2      12.0  23.0  0.9  14.0  16.0  0.14

I used a test dataframe constructed like this:

s = '''Index Stock Open Close
1    ABC   10   13
2    ABC   12   14
1    PQR   12   15
2    PQR   23   16
1    XYZ   0.5   .13
2    XYZ   0.9   .14'''

df = pd.read_table(StringIO(s), sep='\s+', engine='python')

   Index Stock  Open  Close
0      1   ABC  10.0  13.00
1      2   ABC  12.0  14.00
2      1   PQR  12.0  15.00
3      2   PQR  23.0  16.00
4      1   XYZ   0.5   0.13
5      2   XYZ   0.9   0.14

How to transpose dataframe using classification variable

1 Answers1