title: SQL-like window functions in PANDAS: Row Numbering in Python Pandas Dataframe
body: <p>I come from a sql background and I use the following data processing step frequently:</p>
  
  <ol>
  <li>Partition the table of data by one or more fields</li>
  <li>For each partition, add a rownumber to each of its rows that ranks the row by one or more other fields, where the analyst specifies ascending or descending</li>
  </ol>
  
  <p>EX:  </p>
  
  <pre><code>df = pd.DataFrame({'key1' : ['a','a','a','b','a'],
             'data1' : [1,2,2,3,3],
             'data2' : [1,10,2,3,30]})
  df
       data1        data2     key1    
  0    1            1         a           
  1    2            10        a        
  2    2            2         a       
  3    3            3         b       
  4    3            30        a        
  </code></pre>
  
  <p>I'm looking for how to do the PANDAS equivalent to this sql window function:</p>
  
  <pre><code>RN = ROW_NUMBER() OVER (PARTITION BY Key1 ORDER BY Data1 ASC, Data2 DESC)
  
  
      data1        data2     key1    RN
  0    1            1         a       1    
  1    2            10        a       2 
  2    2            2         a       3
  3    3            3         b       1
  4    3            30        a       4
  </code></pre>
  
  <p>I've tried the following which I've gotten to work where there are no 'partitions':</p>
  
  <pre><code>def row_number(frame,orderby_columns, orderby_direction,name):
      frame.sort_index(by = orderby_columns, ascending = orderby_direction, inplace = True)
      frame[name] = list(xrange(len(frame.index)))
  </code></pre>
  
  <p>I tried to extend this idea to work with partitions (groups in pandas) but the following didn't work:</p>
  
  <pre><code>df1 = df.groupby('key1').apply(lambda t: t.sort_index(by=['data1', 'data2'], ascending=[True, False], inplace = True)).reset_index()
  
  def nf(x):
      x['rn'] = list(xrange(len(x.index)))
  
  df1['rn1'] = df1.groupby('key1').apply(nf)
  </code></pre>
  
  <p>But I just got a lot of NaNs when I do this.</p>
  
  <p>Ideally, there'd be a succinct way to replicate the window function capability of sql (i've figured out the window based aggregates...that's a one liner in pandas)...can someone share with me the most idiomatic way to number rows like this in PANDAS?</p>