Transpose Pandas DataFrame and change the column headers to a list

Question

I have the following Pandas sub-dataframe

         col1  name1  name2
522      a     10     0.2
1021     b     72    -0.1

col1 has no duplicate. I want to transpose the dataframe and change the column header to col1 values. Ideally the output should look like

Variable  a     b
name1     10    72
name2     0.2  -0.1

it is easy to transpose the df and label the first column as Variable

df.transpose().reset_index().rename(columns={'index':'Variable'})

the resulting DF will have indices of original DF as column headers (and they are not sorted and don't start from 1 in my data!). How can I change the rest of column names?

Karilla

Answer 1

Need set_index + T:

df = df.set_index('col1').T
print (df)
col1      a     b
name1  10.0  72.0
name2   0.2  -0.1

df = df.set_index('col1').T.rename_axis('Variable').rename_axis(None, 1)
print (df)
             a     b
Variable            
name1     10.0  72.0
name2      0.2  -0.1

If need column from index:

df = df.set_index('col1').T.rename_axis('Variable').rename_axis(None, 1).reset_index()
print (df)
  Variable     a     b
0    name1  10.0  72.0
1    name2   0.2  -0.1

Shillyshally

Answer 2

It's also possible to do the task with a combination of melt and pivot. The idea is to

df = df.melt('col1').pivot(index='variable', columns='col1', values='value')

or set_index and unstack twice:

df = df.set_index('col1').unstack().unstack()

If the variable column needs to be a column instead of an index, then use reset_index():

df.set_index('col1').unstack().unstack().reset_index(names='Variable').rename_axis(columns=None)

Propeller