How to sort a Pandas DataFrame by multiple columns in Python with ordered number

Question

I have a dataframe which has 3 columns.

Code	Volume	Trade Value
Apple	500	1000
Amazon	1000	500
Facebook	250	750
Samsung	100	1500

Firstly, I want to sort volume columns ascending: False that means largest to smallest. Afterwards, making new column which name is "Volume Order" and giving number as a sorting number. Secondly, I want to sort Trade Value ascending: False that means largest to smallest. Afterwards, making new column which name is "Trade Order" and giving number as a sorting number. But, when I sort Trade value, Sorted volume column order is changing according to trade volume order. Finally, I have 2 sorted columns and 2 ordered columns. Lastly, I want to sort and create a new and final column by taking the maximum of 2 separate columns sorted.(Volume Order- Trade Order).

Expected output(when volume and trade value sorting in order:

Code	Volume Order	Trade Order
Samsung	4	1
Apple	2	2
Facebook	3	3
Amazon	1	4

Final output:

Code	Max Ordered Number	Final Sorted Number
Samsung	4	1
Amazon	4	2
Facebook	3	3
Apple	2	4

Note: if they are the same max ordered number, the trade value will be the more important, which will be expressed by the finale of the larger one. Example; samsung 4-4. But samsung has biggest trade order. That's why it is the larger one as final sorted numbers.

When I try this code; I can't make it. Can you help this issue?

ordered = volume_df.sort_values(by=['Volume'], ascending=False,ignore_index=True)
ordered['Volume Order'] = ordered.index+1 
ordered = trade_value_df.sort_values(by=['Trade Value'], ascending=False,ignore_index=True)
ordered['Trade Order'] = ordered.index+1

Great effort at a first question! But can you provide a minimal example to help us reproduce the code? So we don't have to create the dataframe ourselves — RMRiver, Sep 29 '21 at 08:44
Thank you. my dataframe is located above. İf you reproduce the code to this example, I can use it. I need really help — Bianconera, Sep 29 '21 at 08:46
You can add some code to show us how you create `volume_df` and `trade_value_df` :) — Be Chiller Too, Sep 29 '21 at 08:49
Does this answer your question? [How to sort a dataFrame in python pandas by two or more columns?](https://stackoverflow.com/questions/17141558/how-to-sort-a-dataframe-in-python-pandas-by-two-or-more-columns) — bromate, Sep 29 '21 at 08:51
Note that when you write `ordered = trade_value_df.sort_values(by=['Trade Value'], ascending=False,ignore_index=True)`, you are assigning something new to the name `ordered`, so you're effectively losing the dataframe you had previously assigned to that name. — Stef, Sep 29 '21 at 08:55
exactly. the previous order disappears and only trade value order remains. Can you help me — Bianconera, Sep 29 '21 at 08:59
Here is the dataframe for the question. `pd.DataFrame({'Code' : ['Apple', 'Amazon', 'Facebook', 'Samsung'],'Volume' : [500, 1000, 250, 100],'Trade Value' : [1000, 500, 750, 1500]})` — Peacepieceonepiece, Sep 29 '21 at 09:00
@Bianconera In the future it's better if you can provide a minimum example so that we don't have to rewrite the dataset ourselves; see for instance [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) — Stef, Sep 29 '21 at 09:29

score 3 · Answer 1 · edited May 30 '22 at 17:34

3

Instead of using sort_values and index use rank:

df['Volume Order'] = df['Volume'].rank(ascending=False)
df['Trade Order']  = df['Trade'].rank(ascending=False)
df['Trade Order2']  = df['Trade'].rank(ascending=True)
df['Max Ordered Number']  = df[['Volume Order', 'Trade Order']].max(axis=1)
df['Final Sorted Number'] = df[['Max Ordered Number', 'Trade Order2']].apply(tuple, axis=1).rank(ascending=False)
df.drop('Trade Order2', axis=1)

output:

       Code  Volume  Trade  Value  Volume Order  Trade Order  Max Ordered Number  Final Sorted Number
0     Apple     500   1000   <NA>             2            2                   2                    4
1    Amazon    1000    500   <NA>             1            4                   4                    2
2  Facebook     250    750   <NA>             3            3                   3                    3
3   Samsung     100   1500   <NA>             4            1                   4                    1

edited May 30 '22 at 17:34

Sunderam Dubey

1
11
20
40

answered Sep 29 '21 at 09:06

mozway

194,879
13
39
75

@Bianconera I use a temporary 'Trade Order2' column that is sorted in the same expected order as 'Max Ordered Number' – mozway Sep 29 '21 at 09:15
Yes, it will, this is sorting on the first key and then on the second in case of a tie – mozway Sep 29 '21 at 09:41
@Bianconera maybe open a new question, it is difficult to do follow-ups as comments – mozway Sep 29 '21 at 09:59
https://stackoverflow.com/questions/69375616/how-can-i-sort-number-that-according-to-this-order-the-codes-will-take-shapepa can you help me ? – Bianconera Sep 29 '21 at 11:36

score 0 · Accepted Answer · answered Sep 29 '21 at 09:01

There is an issue when you write:

ordered = trade_value_df.sort_values(by=['Trade Value'], ascending=False,ignore_index=True)

You are assigning something new to the name ordered, so you're effectively losing the dataframe you had previously assigned to that name.

A possibility is to do all the operations on the same dataframe, rather than have multiple dataframe:

import pandas as pd

df = pd.DataFrame({'Code':['Apple', 'Amazon', 'Facebook', 'Samsung'], 'Volume':[500, 1000, 250, 100], 'Trade Value': [1000, 500, 750, 1500]})

df = df.sort_values(by=['Volume'], ascending=False,ignore_index=True)
df['Volume Order'] = df.index + 1

df = df.sort_values(by=['Trade Value'], ascending=False,ignore_index=True)
df['Trade Order'] = df.index + 1

print(df)
#        Code  Volume  Trade Value  Volume Order  Trade Order
# 0   Samsung     100         1500             4            1
# 1     Apple     500         1000             2            2
# 2  Facebook     250          750             3            3
# 3    Amazon    1000          500             1            4

what a great answer. I made it. Finally, can you produce code Max Ordered Number and Final Sorted Number according to located above example — Bianconera, Sep 29 '21 at 09:12
@Bianconera mozway's suggestion to use `rank` instead of `sort_values().index` is better. — Stef, Sep 29 '21 at 09:17

How to sort a Pandas DataFrame by multiple columns in Python with ordered number

2 Answers2