Python exclude zero while ranking

Question

Please help to create ranking that excludes values = 0, null, NaN for the below df,

Input:

df = pd.DataFrame(data={'Group1': ['A', 'A', 'A',
                                   'B', 'C','D'],
                        'Group2': ['A1', 'A2', 'A3',
                                   'B1', 'B2','D1'],
                        'Number': [3, 2, 4, 0, np.nan,'']

Expected result:

Group1  Group2  Number  Rank
 A       A1      3       2
 A       A2      2       1
 A       A3      4       3
 B       B1      0  
 C       B2    NaN  
 D       D1

Similar post but does not show on excluding zero, null, nan Ranking order per group in Pandas

Mayank Porwal · Answer 1 · 2022-06-29T15:12:03.713

2

Use df.rank, Series.isin with Groupby.transform:

In [1704]: df['Rank'] = df[~df.Number.isin([0, '', np.nan])].groupby('Group1')['Number'].transform('rank')

In [1705]: df
Out[1705]: 
  Group1 Group2 Number  Rank
0      A     A1      3   2.0
1      A     A2      2   1.0
2      A     A3      4   3.0
3      B     B1      0   NaN
4      C     B2    NaN   NaN
5      D     D1          NaN

edited Jun 29 '22 at 15:12

answered Jun 29 '22 at 14:23

Mayank Porwal

33,470
8
37
58

Updated the question with additional columns, the ranking should be for each group. Please recheck and suggest. Thanks, – spartacus8w2039 Jun 29 '22 at 14:39
@spartacus8w2039 I've updated the answer based on your new requirements. Please check. – Mayank Porwal Jun 29 '22 at 15:12
only think I made change is .groupby(['Group1','Group2', other columns....)]. But, getting a "ValueError: cannot reindex from a duplicate axis" – spartacus8w2039 Jun 29 '22 at 15:33
@spartacus8w2039 Why are you including `Group2` and other columns in groupby? If you do, your expected output will change. – Mayank Porwal Jun 29 '22 at 16:45
I am trying to groupby a list that contains 3 other columns (i.e. Date, Group1, Group2) – spartacus8w2039 Jun 29 '22 at 17:14

Martín · Answer 2 · 2022-06-30T08:03:38.103

0

To do what you are planing, I would write something like this: 'Rank':[2,1,3,'', ''], so you have the result that you are expecting at the output without visual information.

df = pd.DataFrame(data={'Animal': ['cat', 'penguin', 'dog',
                               'spider', 'snake'],
                    'Number_legs': [4, 2, 4, 0, np.nan],
                    'Rank':[2,1,3,'', '']})

This would be the full code for what I think you are trying to do.

edited Jun 30 '22 at 08:03

answered Jun 29 '22 at 14:27

Martín

1
1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jun 30 '22 at 04:15

Python exclude zero while ranking

2 Answers2