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!
CodePudding user response:
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