Transpose columns and rank values in Pandas

user_10681730 0
Viewed 59

I have a question about transposing or stacking values in pandas.

I have 1 dataframe

| product | s1_value | s2_value | s3_value |
| --------| -------- | -------- | -------- |
| First   | 105      | 103      | 108      |
| Second  | 205      | 186      | 204      |

and I want to transform and join them to make the next outcome ( ¿can be in list for then iterate over it and rank? )

| stacked_values_p1 |
| ----------------- |
| 105               |
| 103               |
| 108               |

Rank the above column with rank function in Pandas, and then have a dataFrame with the inicial data + rank columns, like next based on which value is higher

| initial data | ... | s1_rank | s2_rank | s3_rank |
| ------------ | --- | ------- | ------- | ------- |
|     ...      | ... |    2    |    3    |    1    |
|     ...      | ... |    1    |    3    |    2    |

I've been reading about stack, transpose, and merge and I'm a bit confused.

Thanks!

pandas rank dataframe python
1 Answers

For first use transpose after create index by column product:

df1 = df.set_index('product').T
print (df1)
product   First  Second
s1_value    105     205
s2_value    103     186
s3_value    108     204

For second use DataFrame.rank for all columns without first and add to original DataFrame:

f = lambda x: x.replace('value','rank')
df = df.join(df.iloc[:, 1:].rank(axis=1, ascending=False).astype(int).rename(columns=f))
print (df)
  product  s1_value  s2_value  s3_value  s1_rank  s2_rank  s3_rank
0   First       105       103       108        2        3        1
1  Second       205       186       204        1        3        2
user_29010020
Related