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

Question

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.

jezrael · Accepted Answer · 2020-06-05 08:11:21Z

1

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

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

edited Jun 5, 2020 at 8:11

answered Jun 5, 2020 at 7:49

jezrael

868k102 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related