I have this pandas dataframe consists of ids, gender, names and rank.
+-----------------------------------+
| ids | gnr | nms | rnk |
|-----------------------------------+
| C | F | H | 8 |
| A | F | Q | 3 |
| B | F | A | 2 |
| C | M | J | 1 |
| A | M | E | 2 |
| A | F | J | 2 |
| B | M | X | 6 |
| B | M | X | 2 |
| C | F | F | 3 |
| C | M | J | 8 |
| A | M | X | 9 |
| C | M | O | 5 |
| B | M | L | 7 |
| B | F | T | 7 |
| A | M | W | 9 |
+-----------------------------------+
data.groupby(['ids', 'gnr'])['rnk'].nsmallest(2).reset_index().drop(columns='level_2')
I group them by ids, gender, and rank (get the 2 smallest in rank per gender and ids) with the following command and I got the results below
+--------------------------+
| ids | gnr | rnk |
|--------------------------|
| A | M | 2 |
| A | M | 5 |
| A | F | 2 |
| A | F | 3 |
| B | M | 2 |
| B | M | 6 |
| B | F | 2 |
| B | F | 7 |
| C | M | 1 |
| C | M | 5 |
| C | F | 3 |
| C | F | 8 |
+--------------------------+
I want to get that top 2 names each ids and gender using the top 2 ranks result i get from nsmallest()
something like this.
+-----------------------------------+
| ids | gnr | rnk | nms |
|--------------------------|--------|
| A | M | 2 | E |
| A | M | 5 | W |
| A | F | 2 | J |
| A | F | 3 | Q |
| B | M | 2 | X |
| B | M | 6 | X |
| B | F | 2 | A |
| B | F | 7 | T |
| C | M | 1 | J |
| C | M | 5 | O |
| C | F | 3 | F |
| C | F | 8 | H |
+-----------------------------------+