title: Is there a way to rank some items in a pandas dataframe and exclude others?
body: <p>I have a pandas dataframe called ranks with my clusters and their key metrics. I rank them them using <code>rank()</code> however there are two specific clusters which I want ranked differently to the others.</p>
  
  <pre class="lang-py prettyprint-override"><code>ranks = pd.DataFrame(data={'Cluster': ['0', '1', '2',
                                     '3', '4', '5','6', '7', '8', '9'],
                          'No. Customers': [145118, 
                                          2, 
                                          1236, 
                                          219847, 
                                          9837,
                                          64865,
                                          3855,
                                          219549,
                                          34171,
                                          3924120],  
                          'Ave. Recency': [39.0197, 
                                          47.0, 
                                          15.9716, 
                                          41.9736, 
                                          23.9330,
                                          24.8281,
                                          26.5647,
                                          17.7493,
                                          23.5205,
                                          24.7933],
                          'Ave. Frequency': [1.7264, 
                                          19.0, 
                                          24.9101, 
                                          3.0682, 
                                          3.2735,
                                          1.8599,
                                          3.9304,
                                          3.3356,
                                          9.1703,
                                          1.1684],
                          'Ave. Monetary': [14971.85, 
                                          237270.00, 
                                          126992.79, 
                                          17701.64, 
                                          172642.35,
                                          13159.21,
                                          54333.56,
                                          17570.67,
                                          42136.68,
                                          4754.76]})
  ranks['Ave. Spend'] = ranks['Ave. Monetary']/ranks['Ave. Frequency']
  </code></pre>
  
  <pre><code>   Cluster   No. Customers| Ave. Recency| Ave. Frequency| Ave. Monetary| Ave. Spend|
  0    0           145118        39.0197       1.7264         14,971.85     8,672.07
  1    1           2             47.0          19.0          237,270.00    12,487.89
  2    2           1236          15.9716       24.9101       126,992.79     5,098.02
  3    3           219847        41.9736       3.0682         17,701.64     5,769.23
  4    4           9837          23.9330       3.2735        172,642.35    52,738.42
  5    5           64865         24.8281       1.8599         13,159.21     7,075.19
  6    6           3855          26.5647       3.9304         54,333.56    13,823.64
  7    7           219549        17.7493       3.3356         17,570.67     5,267.52
  8    8           34171         23.5205       9.1703         42,136.68     4,594.89
  9    9           3924120       24.7933       1.1684          4,754.76     4,069.21 
  </code></pre>
  
  <p>I then apply the <code>rank()</code> method like this:</p>
  
  <pre><code>ranks['r_rank'] = ranks['Ave. Recency'].rank()
  ranks['f_rank'] = ranks['Ave. Frequency'].rank(ascending=False)
  ranks['m_rank'] = ranks['Ave. Monetary'].rank(ascending=False)
  ranks['s_rank'] = ranks['Ave. Spend'].rank(ascending=False)
  ranks['overall'] = ranks.apply(lambda row: row.r_rank + row.f_rank + row.m_rank + row.s_rank, axis=1)
  ranks['overall_rank'] = ranks['overall'].rank(method='first')
  </code></pre>
  
  <p>Which gives me this:</p>
  
  <pre><code>   Cluster  No. Customers|Ave. Recency|Ave. Frequency|Ave. Monetary|Ave. Spend|r_rank|f_rank|m_rank|s_rank|overall|overall_rank
  0    0          145118       39.0197      1.7264        14,971.85    8,672.07     8     9       8      4      29        9     
  1    1          2            47.0         19.0         237,270.00   12,487.89     10    2       1      3      16        3 
  2    2          1236         15.9716      24.9101      126,992.79    5,098.02     1     1       3      8      13        1
  3    3          219847       41.9736      3.0682        17,701.64    5,769.23     9     7       6      6      28        7
  4    4          9837         23.9330      3.2735       172,642.35   52,738.42     4     6       2      1      13        2
  5    5          64865        24.8281      1.8599        13,159.21    7,075.19     6     8       9      5      28        8
  6    6          3855         26.5647      3.9304        54,333.56   13,823.64     7     4       4      2      17        4
  7    7          219549       17.7493      3.3356        17,570.67    5,267.52     2     5       7      7      21        6
  8    8          34171        23.5205      9.1703        42,136.68    4,594.89     3     3       5      9      20        5
  9    9          3924120      24.7933      1.1684         4,754.76    4,069.21     5     10      10     10     35        10
  </code></pre>
  
  <p>This does what it's suppose to do, however the cluster with the highest <code>Ave. Spend</code> needs to be ranked 1 at all times and the cluster with the highest <code>Ave. Recency</code> needs to be ranked last at all times.</p>
  
  <p>So I modified the code above to look like this:</p>
  
  <pre><code>if(ranks['s_rank'].min() == 1):
      ranks['overall_rank_2'] = 1
  elif(ranks['r_rank'].max() == len(ranks)):
      ranks['overall_rank_2'] = len(ranks)
  else:
      ranks_2 = ranks.drop(ranks.index[[ranks[ranks['s_rank'] == ranks['s_rank'].min()].index[0],ranks[ranks['r_rank'] == ranks['r_rank'].max()].index[0]]])
      ranks_2['r_rank'] = ranks_2['Ave. Recency'].rank()
      ranks_2['f_rank'] = ranks_2['Ave. Frequency'].rank(ascending=False)
      ranks_2['m_rank'] = ranks_2['Ave. Monetary'].rank(ascending=False)
      ranks_2['s_rank'] = ranks_2['Ave. Spend'].rank(ascending=False)
      ranks_2['overall'] = ranks.apply(lambda row: row.r_rank + row.f_rank + row.m_rank + row.s_rank, axis=1)
      ranks['overall_rank_2'] = ranks_2['overall'].rank(method='first')
  </code></pre>
  
  <p>Then I get this</p>
  
  <pre><code>   Cluster  No. Customers|Ave. Recency|Ave. Frequency|Ave. Monetary|Ave. Spend|r_rank|f_rank|m_rank|s_rank|overall|overall_rank|overall_rank_2
  0    0          145118       39.0197      1.7264        14,971.85    8,672.07     8     9       8      4      29        9             1     
  1    1          2            47.0         19.0         237,270.00   12,487.89     10    2       1      3      16        3             1 
  2    2          1236         15.9716      24.9101      126,992.79    5,098.02     1     1       3      8      13        1             1
  3    3          219847       41.9736      3.0682        17,701.64    5,769.23     9     7       6      6      28        7             1
  4    4          9837         23.9330      3.2735       172,642.35   52,738.42     4     6       2      1      13        2             1
  5    5          64865        24.8281      1.8599        13,159.21    7,075.19     6     8       9      5      28        8             1
  6    6          3855         26.5647      3.9304        54,333.56   13,823.64     7     4       4      2      17        4             1
  7    7          219549       17.7493      3.3356        17,570.67    5,267.52     2     5       7      7      21        6             1
  8    8          34171        23.5205      9.1703        42,136.68    4,594.89     3     3       5      9      20        5             1
  9    9          3924120      24.7933      1.1684         4,754.76    4,069.21     5     10      10     10     35        10            1
  </code></pre>
  
  <p>Please help me modify the above if statement or perhaps recommend a different approach altogether. This ofcourse needs to be as dynamic as possible.</p>