-2

There are three columns in a dataFrame Ticker, Attribute, and Value.

The original dataFrame can be seen here

I want to set the Attribute values as a column which can easily be done by setting it as an index and then taking the transpose but the problem is that I want to keep the Ticker as a column when I take the transpose it become the row. When I take Attribute as an index and take its transpose

When I set both as an index and then take transpose it looks something like this which I don't want When both the Attribute and Ticker taken as index and transposed

What I want is this The required output

Adnan Khan
  • 3
  • 5

2 Answers2

0

Use pivot_table:

df = df.pivot_table(index= 'Ticker', columns='Attribute', values = 'Value')
Nk03
  • 14,699
  • 2
  • 8
  • 22
0

Example dataframe:

df = pd.DataFrame({
    'Ticker': list('AAAAABBBB'),
    'Key': list('112341234'),
    'Value':list('ZQWERQWER')
})

To deal with duplicates group by your index and key and aggregate your groups in any way you like. Here I use first, you could also accumulate entire groups using list, for example:

df = df.groupby(['Ticker', 'Key'])['Value'].first().reset_index()

Then, finally, pivot the table:

df = df.pivot(index='Ticker', columns='Key', values='Value').reset_index()
rudolfovic
  • 3,163
  • 2
  • 14
  • 38
  • Ticker contains duplicates pivot doesn't allow duplicates so when i try to drop duplicates i left with single columns Market Cap – Adnan Khan May 21 '21 at 14:07
  • 1
    While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value. – mufazmi May 21 '21 at 18:27
  • @AdnanKhan - edited the answer and added some code that deals with duplicates. Let me know if anything else needs clarifying. – rudolfovic May 22 '21 at 17:36
  • @rudolfovic Thanks I was trying to concatenate the two dataframes so I applied the pivot separately on both the dataframes and joined after pivoting it worked for me – Adnan Khan May 23 '21 at 10:46