Pandas dataframe transpose columns into rows

Question

I have a dataframe as follows:

Variable  Params   Min_4    Min_3   Min_2   Min_1   Min_0   1_Min   2_Min   3_Min   Max_4
  Scores  Scores   4.0       3.0     2.0     1.0     0.0     1.0     2.0     3.0    4.0
  Phys     MAP     160.0    130.0   110.0    NaN     70.0    NaN     50.0    NaN    49.0

I want this dataframe to get transposed like below:

Scores   Values
  4.0      160
  3.0      130
  2.0      110
  1.0      NaN
  0.0      70
  1.0      NaN
  2.0      50
  3.0      NaN
  4.0      49

I am tryting with df.melt() approach (shown below). But with no luck

df_MAP_S = df_MAP.melt(id_vars=['Params','Variable'],var_name = 'Scores_lvl',value_name='Scores_Val')

But the above approach is not giving the correct result. \

If I try using df.set_index().T as belows

cols = df_MAP.columns.tolist()
cols = cols[3:]
df_MAP_S = df_MAP.set_index(cols).T
df_n = df_MAP_S.iloc[:-3]

Then this whole df_n becomes an object

What I am missing here.

mozway · Accepted Answer · 2022-07-08 17:17:46Z

1

IIUC, just drop the useless columns and transpose:

df.drop(columns=['Variable', 'Params']).set_axis(['Scores', 'Values']).T

output:

       Scores  Values
Min_4     4.0   160.0
Min_3     3.0   130.0
Min_2     2.0   110.0
Min_1     1.0     NaN
Min_0     0.0    70.0
1_Min     1.0     NaN
2_Min     2.0    50.0
3_Min     3.0     NaN
Max_4     4.0    49.0

answered Jul 8, 2022 at 17:17

mozway

267k13 gold badges56 silver badges106 bronze badges

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

Comments

eightlay · Accepted Answer · 2022-07-08 17:18:16Z

0

Transpose is a very basic operation. So you can find it in many libraries

df = pd.DataFrame([[1, 2, 4], [5, 6, 7]], index=['a', 'b'], columns=['c1', 'c2', 'c3'])
df.T

will do the trick

answered Jul 8, 2022 at 17:18

eightlay

5423 silver badges13 bronze badges

Collectives™ on Stack Overflow

Pandas dataframe transpose columns into rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related