[WIP] adding overby to core api
State: closed

**I'd love to get some feedback on this before I proceed further.** @jreback and @TomAugspurger available?
### What is this?

This adds a new function to core DataFrames called `overby`. The main use case for this is to group data, sort within groups and apply a function to the resulting iterator. 

It's very similar to the `OVER` clauses in some [SQL engines](https://my.vertica.com/docs/4.1/HTML/Master/10955.htm). 
### Examples:
#### Cumulative Sum within group

Suppose I want to do groupby + cumulative sum within groups. The current best answer is to do [groupby twice](http://stackoverflow.com/questions/22650833/pandas-groupby-cumulative-sum):

```
df.groupby(by=['name','day']).sum().groupby(level=[0]).cumsum()
```

With overby, this becomes:

``` python
def cumulative_sum(iterrows):
    cumsum = 0
    for _, row in iterrows:
        cumsum += row['no']
        yield cumsum

result = df.overby('name', 'day', cumulative_sum)
```

This is more efficient than two `groupby`s.

In a different example, to have a better understanding of what's going on:

``` python
print df
"""
   id  t  x
0   0  1  2
1   2  1  0
2   1  0  2
3   1  0  1
4   0  0  1
5   2  1  1
6   2  1  1
7   0  0  1
"""

def cumulative_sum(iterrows):
    sum_ = 0
    for _, row in iterrows:
        sum_ += row['x']
        yield {'cumsum':sum_, 'value_added': row['x']}

print df.overby('id', 't', cumulative_sum)
"""
      cumsum  value_added
id
0  0       1            1
   1       2            1
   2       4            2
2  0       0            0
   1       1            1
   2       2            1
1  0       1            1
   1       3            2
"""

```
#### `nlargest` simplified

`nlargest` is a Pandas function that operates on grouped objects to find the largest values. With overby, an alternative implementation is clear:

``` python

df = pd.DataFrame([
    {'id': 1,'x': 0},
    {'id': 1,'x': 2},
    {'id': 1,'x': 1},
    {'id': 2,'x': 1},
    {'id': 2,'x': 3},
    {'id': 2,'x': 10},
])

## more verbose
def first_n(iterrows, n=2):
    counter = 0
    for _, row in iterrows:
       yield row['x']
       counter += 1

       if counter >= n:
          break

top_2 = df.overby('id', 'x', first_n, ascending=False)

## less verbose and a one liner
top_2 = df.overby('id', 'x', lambda iter: (row['x'] for _, row in islice(iter,2)), ascending=False)
```

This implementation is obviously much slower than the builtin version, _but_ it offers much more flexibility (example: one can put conditionals before the yield to filter some things out)
### Accumulating fact tables

This is an example from my production environment at work. Using `overby`, it's possible to build very powerful accumulating fact tables for use in data warehousing. Consider the following:

``` python
previous_input = pd.DataFrame([
  {'user': 1, 'number_of_purchases`: 9, 'first_purchase_at': Jan 01, 2015, '10th_purchase_at`: None}
])

new_purchases = pd.DataFrame([
  {'user': 1, 'first_purchase_at': Jan 05, 2015, 'number_of_purchases': 1,  '10th_purchase_at`: None},
  {'user': 2, 'first_purchase_at': Jan 05, 2015, 'number_of_purchases': 1,  '10th_purchase_at`: None},
])

all_data = pd.concat([previous_input, new_purchases])

def reduce_metrics(iterrows):
    return reduce(iterrows)

def reduce(current_row, next_row):
     return 
         {
          'user': current_row['user'],
          'first_purchase_at': min(current_row['first_purchase_at'], next_row['first_purchase_at']),
          '10th_purchase_at': something_more_complicated_than_min(current_row, next_row),
          'number_of_purchases': current_row['number_of_purchases'] + next_row[''number_of_purchases']
       }

all_data.overby('user', 'first_purchase_at', reduce_metrics)

"""
results look like:
 {'user': 1, 'first_purchase_at': Jan 01, 2015, 'number_of_purchases': 10,  '10th_purchase_at`: Jan 05, 2015},
 {'user': 2, 'first_purchase_at': Jan 05, 2015, 'number_of_purchases': 1,  '10th_purchase_at`: None},
"""

```
### To Do
- [ ] tests
- [ ] docs
- [ ] reviews


Labels:

Comments:
@CamDavidsonPilon 

I am not sure why you think this is necessary.

for example

`df.sort_values(col).groupby(col2).cumsum()` is WAY more performant that what you are doing above.

and MUCH more clear.

groupby are quite efficient. Yielding rows is NOT PERFORMANT at all.

I don't find this syntax useful and IMHO is is much less clear. To be honest its to magicy.

If you want to open an issue for discussion, ok, but this would have to have be performance neutral and add to the API in a clear way.

I'll take a look later. My initial reaction was similar to Jeff's, that `overby` is combining multiple steps into a single function (I don't quite understand it yet, so I could be wrong; will look more over the weekend).

Could you explain a bit more about your "Accumulating fact tables" example? I could be wrong, but I think that it can be achieved with a `groupby`and `rolling_apply`, where the window is 2 and the aggregation functions are `{'first_purchase_at': 'min', '10th_purchase_at': something_more_complicated, 'number_of_purchases': 'sum'}`.

Right, so sorting before grouping will preserve order within groups. That settles this then. 

Context: this is a port of a very useful function/API in PySpark, where order is not guaranteed when doing a groupBy.

Thanks for the discussion!