Pandas converting Rows to Columns

Question

I have a CSV that produces a Dataframe in the following format

--------------------------------------------------------------
|Date       | Fund | TradeGroup | LongShort | Alpha | Details|
--------------------------------------------------------------
|2018-05-22 |A     | TGG-A      | Long      | 3.99  | Misc   |
|2018-05-22 |A     | TGG-B      | Long      | 4.99  | Misc   |
|2018-05-22 |B     | TGG-A      | Long      | 5.99  | Misc   |
|2018-05-22 |B     | TGG-B      | Short     | 6.99  | Misc   |
|2018-05-22 |C     | TGG-A      | Long      | 1.99  | Misc   |
|2018-05-22 |C     | TGG-B      | Long      | 5.29  | Misc   |
--------------------------------------------------------------

What I would like to Do is, Group TradeGroups together and convert Fund to columns. So, the final dataframe should look like this:

  --------------------------------------------------------
  |TradeGroup| Date      | A         | B         | C     |
  --------------------------------------------------------
  | TGG-A    |2018-05-22 | 3.99      | 5.99      | 1.99  |
  | TGG-B    |2018-05-22 | 4.99      | 6.99      | 5.29  | 
  --------------------------------------------------------

Also, I don't really care about the LongShort Column and Details Column. So, it's okay if they're dropped. Thanks!! I have tried df.pivot() but it doesn't give the required format

Try df.set_index(['Date','TradeGroup','Fund']).unstack(level=2)['Alpha'] — Anton vBR
– Anton vBR, Commented May 22, 2018 at 22:50

jpp · Accepted Answer · 2018-05-22 23:39:32Z

7

Using pd.pivot_table:

res = df.pivot_table(index=['Date', 'TradeGroup'], columns='Fund',
                     values='Alpha', aggfunc='first').reset_index()

print(res)

Fund        Date TradeGroup     A     B     C
0     2018-05-22      TGG-A  3.99  5.99  1.99
1     2018-05-22      TGG-B  4.99  6.99  5.29

answered May 22, 2018 at 23:39

jpp

166k37 gold badges301 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anton vBR · Accepted Answer · 2018-05-22 23:01:48Z

Looks like you are attempting to unstack a column from a multiindex.

Try this:

import pandas as pd

data = '''\
Date        Fund  TradeGroup  LongShort  Alpha  Details
2018-05-22 A      TGG-A       Long       3.99   Misc   
2018-05-22 A      TGG-B       Long       4.99   Misc   
2018-05-22 B      TGG-A       Long       5.99   Misc   
2018-05-22 B      TGG-B       Short      6.99   Misc   
2018-05-22 C      TGG-A       Long       1.99   Misc   
2018-05-22 C      TGG-B       Long       5.29   Misc'''

fileobj = pd.compat.StringIO(data)

df = pd.read_csv(fileobj, sep='\s+')

dfout = df.set_index(['TradeGroup','Date','Fund']).unstack()['Alpha']
print(dfout)

Returns:

Fund                      A     B     C
TradeGroup Date                        
TGG-A      2018-05-22  3.99  5.99  1.99
TGG-B      2018-05-22  4.99  6.99  5.29

If you want you can also apply a .reset_index() after and you get:

Fund TradeGroup        Date     A     B     C
0         TGG-A  2018-05-22  3.99  5.99  1.99
1         TGG-B  2018-05-22  4.99  6.99  5.29

Collectives™ on Stack Overflow

Pandas converting Rows to Columns

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related