title: Python pandas: Assign label based on sorted values per row, excluding NaNs
body: <p>I want to assign the position of every value based on its position within the sorted value-list per row (excluding NaNs). I just can't figure out how to do this in an elegant way with pandas.</p>
  
  <p>I think it's easier to explain in an example:</p>
  
  <pre><code>                A         B        C           D
  Date                                              
  2002-02-28 -0.051272 -0.005851 -0.012669       NaN
  2002-03-29  0.103416  0.050121  0.050203       0.5
  2002-04-30 -0.090579 -0.042308  0.019293       0.03
  2002-05-31  0.160239 -0.078983  0.047319       0.66
  </code></pre>
  
  <p>For every row, I want to do the following:</p>
  
  <ul>
  <li>Exclude NaNs</li>
  <li>Calculate the position of the value within the list of sorted values in that row and assign this number (position 1 being the smallest (negative) number and position N being the biggest positive number)</li>
  </ul>
  
  <p>The result would be:</p>
  
  <pre><code>                A         B        C         D
  Date                                              
  2002-02-28      1         3       2         NaN
  2002-03-29      3         1       2          4
  2002-04-30      1         2       3          4
  2002-05-31      3         1       2          4
  </code></pre>
  
  <p>In a second step, I than want to make a 3-row "rolling" function per column which checks whether the current row and the 2 rows before within one colum were smaller then a certain threshold X and in case it is, then display the average of those 3 values, else just note down NaN. In case any of those 3 values is NaN, than just note down NaN. Can only be calculated from 2002-04-30 ongoing, because needs at least 3 values. For column D, this would yield "NaN" in row 2002-04-30 because there are only two numeric values beforehand. For Column D and row 2002-05-31 it would also yield "NaN" because the 3 values are 4, 4 and 4 with 4 being greater then the treshold.</p>
  
  <p>Let's say the threshold X=3. (I leave out column D because my explanations make the data to wide):</p>
  
  <p>E.g.:</p>
  
  <pre><code>                A           B                C         
  Date                                              
  2002-02-28      NaN        NaN              NaN        
  2002-03-29      NaN        NaN              NaN        
  2002-04-30   Avg(1,3,1)    Avg(3,1,2)    Avg(2+2+3)         
  2002-05-31   Avg(3,1,3)    Avg(1,2,1)    Avg(2+3+2)        
  </code></pre>
  
  <p><strong>EDIT</strong>:
  I think I got both steps myself. Could you please evaluate whether this is correct and sensible?:</p>
  
  <pre><code>import numpy as np
  import pandas as pd
  df = pd.DataFrame(data={'X': [0.1, 0.2, 0.3, 0.4], 'Y': [0.5, -0.2, np.NaN, -1], 'Z': [np.NaN, -0.21, -5, 10]})
  df.apply(lambda row: [sorted([y for y in row if not np.isnan(y)]).index(x)+1 if not np.isnan(x) else np.NaN for x in row], axis=1)
  
  df:
       X    Y      Z
  0  0.1  0.5    NaN
  1  0.2 -0.2  -0.21
  2  0.3  NaN  -5.00
  3  0.4 -1.0  10.00
  
  After .apply:
       X    Y    Z
  0  1.0  2.0  NaN
  1  3.0  2.0  1.0
  2  2.0  NaN  1.0
  3  2.0  1.0  3.0
  
  
  # Step 2 with new examplatory data and only one column
  df = pd.DataFrame(data={'A': [1,2,3,np.NaN,3,1,3,4,3,np.NaN,2,2,1,2]})
  threshold = 3
  df['A_rolling'] = df['A'].rolling(window=3, min_periods=3).apply(lambda x: x.mean() if all([val &lt;= threshold for val in x]) else np.NaN)
  
        A  A_rolling
  0   1.0        NaN
  1   2.0        NaN
  2   3.0   2.000000
  3   NaN        NaN
  4   3.0        NaN
  5   1.0        NaN
  6   3.0   2.333333
  7   4.0        NaN
  8   3.0        NaN
  9   NaN        NaN
  10  2.0        NaN
  11  2.0        NaN
  12  1.0   1.666667
  13  2.0   1.666667
  </code></pre>
  
  <p>So only gotta run it for all columns now :)</p>
  
  <p>Any ideas?
  Thanks</p>