Sort column in pandas, then sort another column while maintaining previous column sorted

Question

So I have some data on lots of publicly traded stock. Each data row contains an id, a date, and some other information. Naturally, a stock might appear many times in the dataframe (i.e Google might have several entries that correspond to different dates at which the price was updated).

I want to be able to sort the ids, then for each sorted block, sort the dates.

NOTE: sorting is done in ascending order for the sake of the example.

    id        date price
0  123  2015/01/13     x
1  114  2017/02/15     y
2   12  2016/12/02     z
3  123  1996/04/26     w
4  114  2014/02/23     u
5  114  1995/05/25     v

Sorting the ids gives:

    id        date price
0   12  2016/12/02     z
1  123  2015/01/13     x
2  123  1996/04/26     w
3  114  2017/02/15     y
4  114  2014/02/23     u
5  114  1995/05/25     v

Sorting the dates WHILE the ids are fixed gives:

    id        date price
0   12  2016/12/02     z
1  123  1996/04/26     w
2  123  2015/01/13     x
3  114  1995/05/25     v
4  114  2014/02/23     u
5  114  2017/02/15     y

jezrael · Accepted Answer · 2017-06-24 15:34:00Z

13

It seems you need DataFrame.sort_values:

df['date'] = pd.to_datetime(df['date'])
df = df.sort_values(['id','date'])
print (df)
    id       date price
2   12 2016-12-02     z
5  114 1995-05-25     v
4  114 2014-02-23     u
1  114 2017-02-15     y
3  123 1996-04-26     w
0  123 2015-01-13     x

Or if id column is string:

df['id'] = df['id'].astype(str)
df['date'] = pd.to_datetime(df['date'])
df = df.sort_values(['id','date'])
print (df)
    id       date price
5  114 1995-05-25     v
4  114 2014-02-23     u
1  114 2017-02-15     y
2   12 2016-12-02     z
3  123 1996-04-26     w
0  123 2015-01-13     x

You can also sort one column descending and another ascending:

df['id'] = df['id'].astype(str)
df['date'] = pd.to_datetime(df['date'])
df = df.sort_values(['id','date'], ascending=[False, True])
print (df)
    id       date price
3  123 1996-04-26     w
0  123 2015-01-13     x
2   12 2016-12-02     z
5  114 1995-05-25     v
4  114 2014-02-23     u
1  114 2017-02-15     y

answered Jun 24, 2017 at 15:34

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

3 Comments

Ziad BHA Over a year ago

Thank you so much! Worked like a charm. I didn't realize you could sort dataframes by a group of column headers.

Ziad BHA Over a year ago

At first I did accept it but I was not allowed since I'm new. I guess now it works! Sorry :)

jezrael Over a year ago

No problem. Now you can also upvote - if find some solution in StackOverflow helpful in future. Nice day!

Collectives™ on Stack Overflow

Sort column in pandas, then sort another column while maintaining previous column sorted

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related