CODEWITHSUNDEEP
pythonpandas
Stramzik
Stramzik

Reputation: 306

How to Convert rows into columns headers and values of other column as data?

I've a below dataframe and I want to pivot the data to change the Name column values into multiple columns and values from Data column into values for the columns created by the Name column.

As the Data column has all types of date when I pivot the data I dont get the required result can someone please advise what I'm doing wrong?

import numpy as np
dict_d = {'Name': {0: 'Number', 1: 'Purpose', 2: 'Approver', 3: 'internal/external', 4: 'Name', 5: 'N Mnemonic'}, 'Data': {0: '123456', 1: 'BC', 2: np.nan, 3: 'internal', 4: np.nan, 5: 'xyz'}}
df = pd.DataFrame(dict_d)
df

o/p

Name    Data
0   Number  123456
1   Purpose BC
2   Approver    NaN
3   internal/external   internal
4   Name    NaN
5   N Mnemonic  xyz

I've tried this

df.pivot_table(columns='Name', values='Data', aggfunc=lambda x: ''.join(str(x)))

Name    Approver    N Mnemonic  Name    Number  Purpose internal/external
Data    NameData    NameData    NameData    NameData    NameData    NameData

But in row 1 I want the data values.

Upvotes: 1

Views: 586

Answers (1)

jezrael
jezrael

Reputation: 863751

I think you need convert Name to index, select by double [] for one column DataFrame and transpose:

df1 = df.set_index('Name')[['Data']].T

Upvotes: 1

Related Questions