Transpose DataFrame in Pandas while preserving Index column

4
votes

The problem is, when I transpose the DataFrame, the header of the transposed DataFrame becomes the Index numerical values and not the values in the "id" column. See below original data for examples:

Original data that I wanted to transpose (but keep the 0,1,2,... Index intact and change "id" to "id2" in final transposed DataFrame).
enter image description here DataFrame after I transpose, notice the headers are the Index values and NOT the "id" values (which is what I was expecting and needed) enter image description here

Logic Flow

First this helped to get rid of the numerical index that got placed as the header: How to stop Pandas adding time to column title after transposing a datetime index?

Then this helped to get rid of the index numbers as the header, but now "id" and "index" got shuffled around: Reassigning index in pandas DataFrame & Reassigning index in pandas DataFrame

enter image description here

But now my id and index values got shuffled for some reason.

How can I fix this so the columns are [id2,600mpe, au565...]?

How can I do this more efficiently?

Here's my code:

DF = pd.read_table(data,sep="\t",index_col = [0]).transpose() #Add index_col = [0] to not have index values as own row during transposition
m, n = DF.shape
DF.reset_index(drop=False, inplace=True)
DF.head()

This didn't help much: Add indexed column to DataFrame with pandas

1
pandasindexingdataframetranspose

1 Answers

7
votes

If I understand your example, what seems to happen to you is that you transpose takes your actual index (the 0...n sequence as column headers. First, if you then want to preserve the numerical index, you can store that as id2.

DF['id2'] = DF.index

Now if you want id to be the column headers then you must set that as an index, overriding the default one:

DF.set_index('id',inplace=True)
DF.T

I don't have your data reproduced, but this should give you the values of id across columns.