Sorting Specific Column in Pandas Dataframe

Question

Below is my data frame:

data = pd.DataFrame([['A',1,15,100,123],['A',2,16,50,7],['A',3,17,100,5],['B',1,20,75,123],['B',2,25,125,7],['B',3,23,100,7],['C',1,5,85,12],['C',2,1,25,6],['C',3,7,100,7]],columns = ['Group','Ranking','Data1','Data2','Correspondence'])

  Group  Ranking  Data1  Data2  Correspondence
0     A        1     15    100             123
1     A        2     16     50               7
2     A        3     17    100               5
3     B        1     20     75             123
4     B        2     25    125               7
5     B        3     23    100               7
6     C        1      5     85              12
7     C        2      1     25               6
8     C        3      7    100               7

I have already sorted the data frame based on 'Group'. However, I still need to sort the data frame based on data for each Group. For each group, Data1 must be sorted based on lowest to highest value and once it is sorted, value in column Data2 will follow the position of Data1. The column Correspondence will not be touched (stay as in original df) and column ranking stays as it is as well. I have used df.sort_values(), but I am unable to get my result as below:

  Group  Ranking  Data1  Data2  Correspondence
0     A        1     15    100             123
1     A        2     16     50               7
2     A        3     17    100               5
3     B        1     20     75             123
4     B        2     23    100               7
5     B        3     25    125               7
6     C        1      1     25              12
7     C        2      5     85               6
8     C        3      7    100               7

So basically my aim is: sort value in Data1 from lowest to highest within each Group, the value in Data2 will follow the movement of Data1 after sorting, while column Correspondence stays where it originally stands.

Thanks.

I hate to tell you this, but you are not sorted on Group (see index 8) — Aryerez
– Aryerez, Commented Nov 12, 2019 at 12:50
ah yes, that is my mistake. index 8 is supposed to be C. My apologies for confusion. — teteh May
– teteh May, Commented Nov 12, 2019 at 13:26

jezrael · Accepted Answer · 2019-11-12 13:28:13Z

1

Use DataFrame.sort_values with both columns and assign back numpy array with .values:

cols = ['Data1','Data2']
data[cols] = data.sort_values(['Group','Data1'])[cols].values
#pandas 0.24+  
#data[cols] = data.sort_values(['Group','Data1'])[cols].to_numpy()
print (data)
  Group  Ranking  Data1  Data2  Correspondence
0     A        1     15    100             123
1     A        2     16     50               7
2     A        3     17    100               5
3     B        1     20     75             123
4     B        2     23    100               7
5     B        3     25    125               7
6     C        1      1     25              12
7     C        2      5     85               6
8     C        3      7    100               7

edited Nov 12, 2019 at 13:28

answered Nov 12, 2019 at 12:53

jezrael

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

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

Comments

Stefano · Accepted Answer · 2019-11-12 12:55:39Z

0

Did you try like this?

data2 = data.sort_values(by = ['Group', 'Data1'], ascending = (True, False)).reset_index()
data2['Correspondence'] = data['Correspondence']

edited Nov 12, 2019 at 12:55

answered Nov 12, 2019 at 12:52

Stefano

2741 silver badge8 bronze badges

2 Comments

Aryerez Over a year ago

Read the demends more carfully

Aryerez Over a year ago

Now it's what he meant :)

M.Sadegh Azarkaman · Accepted Answer · 2019-11-12 12:55:57Z

0

have you tried sort_values function? based on documentation you can do it like this:

data.sort_values(['Group', 'Data1'], ascending=[True, False])

answered Nov 12, 2019 at 12:55

M.Sadegh Azarkaman

13 bronze badges

Comments

Tox · Accepted Answer · 2019-11-12 12:57:34Z

0

You can try something like:

data.sort_values(['Group', 'Data1', 'Data2'], ascending=[True, True, False])

And if you want some columns to be descending you have to set that column to False.

edited Nov 12, 2019 at 12:57

answered Nov 12, 2019 at 12:53

Tox

8543 gold badges14 silver badges36 bronze badges

1 Comment

Aryerez Over a year ago

Not what he asked for

Prince Francis · Accepted Answer · 2019-11-12 12:59:47Z

0

Use left join (merge) as follows

df2 = df.sort_values(['Group', 'Data1']).reset_index()
df3 = df2[['Group', 'Ranking', 'Data1', 'Data2']].join(df[['Correspondence']])
df3

which will give the result as follows

    Group   Ranking     Data1   Data2   Correspondence
0   A   1   15  100     123
1   A   2   16  50       7
2   A   3   17  100     5
3   B   3   7   100     123
4   B   1   20  75      7
5   B   3   23  100     7
6   B   2   25  125     12
7   C   2   1   25      6
8   C   1   5   85      7

answered Nov 12, 2019 at 12:59

Prince Francis

3,1071 gold badge16 silver badges22 bronze badges

Collectives™ on Stack Overflow

Sorting Specific Column in Pandas Dataframe

5 Answers 5

Comments

2 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related