Sorting a column in pandas then sorting another column while keeping the previous sorted column
So, I have some data on a large number of publicly traded stocks. Each line of data contains an identifier, date and some other information. Naturally, the stock can appear many times in the data frame (i.e. Google can have multiple records corresponding to different dates when the price was updated).
I want to be able to sort the ids and then sort the dates for each sorted block.
NOTE: sorting is done in ascending order for 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 WHILE dates when 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
source to share
You seem to 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 the column is id
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 in descending order and another in ascending order:
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
source to share