Sorting and Filtering Pandas Dataframe

Question

I'm trying to filter and sort a Pandas dataframe to clean my data. I've looked on StackOverflow and can't seem to find a method that will give me the sort and filter I need. The data I'm working with looks something like this:

| Name 1 | Name 2 | Score |
| ------ | ------ | ----- | 
| Amy | Jack | 2.456 | 
| Amy | Jack | 3.234 | 
| Amy | Jack | 5.124 | 
| ... | ... | ... | 
| Max | Jane | 8.569 |
| Max | Jane | 4.654 |
| Max | Jane | 6.349 |

What I want to do make a new dataframe out of the lowest score of every pair of names. So the resulting dataframe would be something like this:

| Name 1 | Name 2 | Score |
| ------ | ------ | ----- | 
| Amy | Jack | 2.456 | 
| ... | ... | ...|
| Max | Jane | 4.654 |

if you have more columns df.loc[df.groupby(['Name 1', 'Name 2'])['Score'].idxmin()] — anky
– anky, Commented Apr 15, 2021 at 17:53

Amit Vikram Singh · Accepted Answer · 2021-04-15 17:51:09Z

3

Use:

df = df.groupby(['Name 1', 'Name 2'], as_index = False).agg(Score = ('Score', 'min'))

Output:

>>> df
  Name1 Name2  Score
0   Amy  Jack  2.456
1   Max  Jane  4.654

answered Apr 15, 2021 at 17:51

Amit Vikram Singh

2,12812 silver badges22 bronze badges

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

Comments

anky · Accepted Answer · 2021-04-15 17:57:58Z

3

You can also use sort_values() and groupby() method:

df.sort_values(by='Score').groupby(['Name 1', 'Name 2'], as_index = False).first()

OR

Use sort_values() and drop_duplicates() method:

df.sort_values(by='Score').drop_duplicates(subset=['Name 1', 'Name 2'])

edited Apr 15, 2021 at 17:57

anky

75.3k11 gold badges46 silver badges76 bronze badges

answered Apr 15, 2021 at 17:53

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

Collectives™ on Stack Overflow

Sorting and Filtering Pandas Dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related