Convert row wise data into columns using Pandas

Question

I have read several similar questions and their answers but I was still unable to perform the conversion.
This is how my sample dataframe, df looks like:

pd.DataFrame({0: ['Destiantion', 'Switch Location', 'Driver', 'Company'],
1: ['CALGARY', np.nan, 'BALJIT', 'SUPERIOR'],
2: ['CALGARY', np.nan, 'ROBERT', 'APPS'],
3: ['CALGARY', np.nan, 'MARIUS', 'APPS'],
4: ['DELTA', np.nan, np.nan, 'ATC']})

I would like to reformat it such that the values in the column 0, df[0] become the new column headers and the data for the new header columns reside in the same row in the old dataframe.

Expected outcome:

pd.DataFrame({'Destiantion': ['CALGARY', 'CALGARY', 'CALGARY', 'DELTA'],
              'Switch Location': [np.nan, np.nan, np.nan, np.nan],
              'Driver': ['BALJIT', 'ROBERT', 'MARIUS', np.nan],
              'Company': ['SUPERIOR', 'APPS', 'APPS', 'ATC']})

I looked into .pivot() method but I wasn't able to shape the data as I wanted with that and I wasn't sure what the index value was going to be. I can still make this conversion by converting rows into list and extracting headers from the list and creating a new dataframe but I don't feel like it's very "pythonic" and was wondering if there's a better way to do this that I could make use of now and in the future. Any help would be appreciated. Thanks.

Look into pandas.transpose()

Aryerez
– Aryerez

2019-10-06 14:01:16 +00:00
Commented Oct 6, 2019 at 14:01 — Aryerez
– Aryerez, Commented Oct 6, 2019 at 14:01

ansev · Accepted Answer · 2019-10-06 14:04:18Z

3

Use:

 df=df.set_index(0).T.reset_index(drop=True)

or if the name of columns are str:

df=df.set_index('0').T.reset_index(drop=True)

edited Oct 6, 2019 at 14:04

answered Oct 6, 2019 at 14:02

ansev

31k5 gold badges21 silver badges33 bronze badges

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

2 Comments

yatu Over a year ago

simpler than mine +1

adrianp Over a year ago

Add a reset_index(drop=True) to start the index at 0.

Collectives™ on Stack Overflow

Convert row wise data into columns using Pandas

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related