ENH: add Series & DataFrame .agg/.aggregate
State: closed

- to provide convienent function application that mimics the groupby(..).agg/.aggregate
interface
- ``.apply`` is now a synonym for ``.agg/.aggregate``, and will accept dict/list-likes
for aggregations
- Automatic handling of both reductive and transformation functions, e.g.``Series.agg(['min', 'sqrt'])``
- interpretation of string names on series (with a fallback to numpy), e.g. ``sqrt``, ``log``)

closes #1623
closes #14464

custom .describe. I included these issues because its is quite easy to now do custom .describe.
closes #14483
closes #7014

TODO:
- [ ] docs (new section in basics, with linking to groupby, rolling, resample aggregates sections)
- [ ] tests for``Series.agg({'foo' : ['min', 'max']})``
- [ ] update doc-strings
- [ ] example from: https://github.com/pandas-dev/pandas/issues/15386

Series:
```
In [2]: s = Series(range(6))

In [3]: s
Out[3]: 
0    0
1    1
2    2
3    3
4    4
5    5
dtype: int64

In [4]: s.agg(['min', 'max'])
Out[4]: 
min    0
max    5
dtype: int64

In [5]: s.agg(['sqrt', 'min'])
ValueError: cannot combine transform and aggregation operations

In [6]: s.agg({'foo' : 'min'})
Out[6]: 
foo    0
dtype: int64

In [7]: s.agg({'foo' : ['min','max']})
Out[7]: 
     foo
min    0
max    5

In [8]: s.agg({'foo' : ['min','max'], 'bar' : ['sum', 'mean']})
Out[8]: 
      foo   bar
max   5.0   NaN
mean  NaN   2.5
min   0.0   NaN
sum   NaN  15.0
```

DataFrame
```
In [8]: df = pd.DataFrame({'A': range(5), 'B': 5})

In [9]: df
Out[9]: 
   A  B
0  0  5
1  1  5
2  2  5
3  3  5
4  4  5

In [10]: df.agg(['min', 'max'])
Out[10]: 
     A  B
min  0  5
max  4  5

In [11]: df.agg({'A': ['min', 'max'], 'B': ['sum', 'max']})
Out[11]: 
       A     B
max  4.0   5.0
min  0.0   NaN
sum  NaN  25.0

# df.agg is equivalent to a tranfsorm
In [15]: df.transform([np.sqrt, np.abs, lambda x: x**2])
Out[15]: 
          A                           B                  
       sqrt absolute <lambda>      sqrt absolute <lambda>
0  0.000000        0        0  2.236068        5       25
1  1.000000        1        1  2.236068        5       25
2  1.414214        2        4  2.236068        5       25
3  1.732051        3        9  2.236068        5       25
4  2.000000        4       16  2.236068        5       25

```

Not sure what I should do in cases like this. could skip, or maybe raise better message.
```
In [16]: df = pd.DataFrame({'A': range(5), 'B': 5, 'C':'foo'})

In [17]: df
Out[17]: 
   A  B    C
0  0  5  foo
1  1  5  foo
2  2  5  foo
3  3  5  foo
4  4  5  foo

In [18]: df.transform(['log', 'abs'])
AttributeError: 'str' object has no attribute 'log'
```

Labels:
name: Enhancement
name: Reshaping
description: Concat, Merge/Join, Stack/Unstack, Explode
name: API Design

Comments:
cc @wesm @jorisvandenbossche @shoyer @TomAugspurger @sinhrks 

@jreback Cool!

Some quick feedback based on your examples above:
- I would keep `agg`/`aggregate` strictly for aggregations (so only allow functions that reduce the values to a single scalar). IMO, this will make the scope of this function easier to grasp and less corner cases (eg no varying output shape depending on what kind of function is passed)
- This would mean that apply is _not_ an exact synonym, and that both reductive and transformation functions are not automatically handled (but we can do this for `apply`, this would then be the more general and flexible version. Maybe we could also have a `transform` method to fully mimic groupby methods?)
- What happens when you pass a single function (not in a list) like `s.agg('min')`? I suppose you get a scalar result like `s.min()` ?

# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/14668?src=pr&el=h1) Report
> Merging [#14668](https://codecov.io/gh/pandas-dev/pandas/pull/14668?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/9991579c812e5a7c977e69f03b390adf7974445f?src=pr&el=desc) will **decrease** coverage by `0.08%`.
> The diff coverage is `98.52%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/14668/graphs/tree.svg?width=650&height=150&src=pr&token=eZ4WkYLtcO)](https://codecov.io/gh/pandas-dev/pandas/pull/14668?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #14668      +/-   ##
==========================================
- Coverage   91.11%   91.02%   -0.09%     
==========================================
  Files         145      145              
  Lines       50332    50391      +59     
==========================================
+ Hits        45858    45869      +11     
- Misses       4474     4522      +48
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `88.82% <98.52%> (-0.09%)` | :arrow_down: |
| #single | `40.33% <35.29%> (-0.01%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/14668?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/generic.py](https://codecov.io/gh/pandas-dev/pandas/pull/14668?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ2VuZXJpYy5weQ==) | `96.28% <100%> (+0.02%)` | :arrow_up: |
| [pandas/core/base.py](https://codecov.io/gh/pandas-dev/pandas/pull/14668?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvYmFzZS5weQ==) | `96.19% <100%> (+3.87%)` | :arrow_up: |
| [pandas/core/series.py](https://codecov.io/gh/pandas-dev/pandas/pull/14668?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvc2VyaWVzLnB5) | `95.08% <100%> (+0.09%)` | :arrow_up: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/14668?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.65% <96.55%> (+0.07%)` | :arrow_up: |
| [pandas/core/groupby.py](https://codecov.io/gh/pandas-dev/pandas/pull/14668?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ3JvdXBieS5weQ==) | `92.03% <0%> (-3.48%)` | :arrow_down: |
| [pandas/core/algorithms.py](https://codecov.io/gh/pandas-dev/pandas/pull/14668?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvYWxnb3JpdGhtcy5weQ==) | `94.46% <0%> (-0.16%)` | :arrow_down: |
| [pandas/types/cast.py](https://codecov.io/gh/pandas-dev/pandas/pull/14668?src=pr&el=tree#diff-cGFuZGFzL3R5cGVzL2Nhc3QucHk=) | `86.89% <0%> (+0.74%)` | :arrow_up: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/14668?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/14668?src=pr&el=footer). Last update [9991579...ae6c6f6](https://codecov.io/gh/pandas-dev/pandas/pull/14668?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).
@jorisvandenbossche 

```
In [2]: s = Series(range(6))

In [3]: s.agg('min')
Out[3]: 0

In [4]: s.agg(['min'])
Out[4]:
min    0
dtype: int64
```

so typically in groupby/rolling/resample we don't do transform operations, unless we explicity want them (e.g. by using `.transform`).

With a series/dataframe OTOH, I think this is fairly common. `.apply` does both ATM. So easy enough to separate out this behavior for reductions to `.agg` and transforms to `.transform`. 

I _may_ actually have to do the computations before can determine this though (and raise an appropriate error).

update the top section a bit.

Not sure what I should do in cases like this. could skip, or maybe raise better message.

```
In [16]: df = pd.DataFrame({'A': range(5), 'B': 5, 'C':'foo'})

In [17]: df
Out[17]: 
   A  B    C
0  0  5  foo
1  1  5  foo
2  2  5  foo
3  3  5  foo
4  4  5  foo

In [18]: df.transform(['log', 'abs'])
AttributeError: 'str' object has no attribute 'log'
```

we could add a `numeric_only=True` default.

@jorisvandenbossche any more comments?

this is for 0.20 so will be in master for a bit.
any remaining comments? @TomAugspurger @wesm @sinhrks 

@jorisvandenbossche all of your points were addressed.

This may not be perfect and need tweaking, but I think banging on it in master for a while is useful.
Will take a last look tomorrow!
> This may not be perfect and need tweaking, but I think banging on it in master for a while is useful

Won't have time to go through in detail, but based on my last skim I'm +1 on merge and iterate after Joris after today.
High level comments. I still think that:

* `aggregate`/`agg` should be strictly for aggregations. IMO we should better disallow transforming functions in `agg` to keep the scope of `agg` limited. 
* aggregations of a Series should not return a DataFrame. I would rather go for a MultiIndex Series if you pass a nested dict. Current behaviour of PR:
  
    ```
    In [14]: s = pd.Series(range(6))

    In [15]: s.agg({'foo' : ['min','max'], 'bar' : ['sum', 'mean']})
    Out[15]: 
          foo   bar
    max   5.0   NaN
    mean  NaN   2.5
    min   0.0   NaN
    sum   NaN  15.0
    ```

  but this could also result in:

    ```
    foo  max      5.0
         min      0.0
    bar  mean     2.5
         sum     15.0
    dtype: float64
    ```

  Then the return type does not depend on the actual content of the passed dict (but only: single function -> scalar, list of dict of functions -> Series). This keeps it IMO more simple and straightforward.

* reducing functions in `transform` should work (result broadcasting to the origin size). But that can certainly be seen as a further enhancement for a next PR.

Would love to hear some thought from others on this.

Further, just to keep track of this, a possible addition of `numeric_only=True` is also a to do item for future?
@jreback I also had a strange issue when testing:

```
In [17]: df = pd.DataFrame({'A': range(6), 'B': 5}, index=range(6))

In [18]: df
Out[18]: 
   A  B
0  0  5
1  1  5
2  2  5
3  3  5
4  4  5
5  5  5

In [19]: df.agg(lambda x: 1)
Out[19]: 
A    1
B    1
dtype: int64

In [20]: df.agg([lambda x: 1])
Out[20]: 
         A        B
  <lambda> <lambda>
0        1        1
1        1        1
2        1        1
3        1        1
4        1        1
5        1        1
```

I often test with dummy functions like the above with a clear return shape of the single function (so here it is a custom passed reducing function that returns a scalar). In the first case it works, but when passing it in a list, it does not work.

If I slightly adapt the function:

```
In [24]: df.agg([lambda x: min(1, x.max())])
Out[24]: 
          A  B
<lambda>  1  1
```
then this now does work, although the function this returns exactly the same for all columns.
ok updated to return a Series if Series input always. It will be a MI if multiple aggregations are passed.

```
In [1]: s = pd.Series(range(6))
   ...: s.agg({'foo' : ['min','max'], 'bar' : ['sum', 'mean']})
   ...: 
Out[1]: 
bar  mean     2.5
     sum     15.0
foo  max      5.0
     min      0.0
dtype: float64
```

I am not actually naming the levels, but could think about that, maybe ``['keys', 'func']`` or something like that.
Here's what you will now get for a simultaneous agg & transform.

```
In [1]: s = Series(range(5))

In [2]: s.agg({'foo': np.sqrt, 'bar': 'sum'})
ValueError: cannot perform both aggregation and transformation operations simultaneously
```
@jorisvandenbossche 
> Further, just to keep track of this, a possible addition of numeric_only=True is also a to do item for future?

yeah this is not well tested ATM, I think we need to exclude columns that raise by default (its not necessarily numeric though, e.g. min on a date column is fine).

lets make a follow up for this case. #15015
ok @jorisvandenbossche updated.

(still a couple of questions you have raised), but can address later.
Just taking a first look at this now (sorry I wasn't paying attention earlier!).

I like the easy way to express multiple transformations or aggregations at once, but I have some concerns about the consistency of the syntax.

In particularly, the keys of a `dict` are interpreted very differently on `Series` vs `DataFrame`:
- On a `Series`, dict keys are used to name the result.
- On a `DataFrame`, dict keys are used to select the column to which the transform/aggregation is applied.

This makes it hard to understand and predict how these methods work without active experimentation. But, for what it's worth, I do recognize that this is consistent with how `groupby.agg` already works.

I would be inclined to only support one of these use cases, though I'm not sure which one is more important. Ideally, we would have a separate orthogonal way to indicate how to name the result, so that could be used at the same time as column selection. Maybe keyword arguments would be a good way to distinguish, e.g., `df.agg(foo={'A': np.mean})` and `df.A.agg(foo=np.mean)`?

---------

Another (possible) inconsistencies I noticed when trying this out:

```
In [35]: df = pd.DataFrame({'A': range(5), 'B': [1, 1, 2, 2, 3]})

In [36]: df.groupby('B').agg({'A': [np.mean, np.sum], 'B': np.sum})
Out[36]:
    B    A
  sum mean sum
B
1   2  0.5   1
2   4  2.5   5
3   3  4.0   4

In [37]: df.agg({'A': [np.mean, np.sum], 'B': np.sum})
Out[37]:
        B     A
mean  NaN   2.0
sum   9.0  10.0
```

I don't like how `DataFrame.agg` produces a ragged array (with NaNs). It might be a little more consistent with `DataFrame.groupby.agg` to return a Series with a `MultiIndex` (e.g., the columns from the `groupby.agg` result).

That said, I am still struggling to understand the full set of rules for these operations -- it's not obvious  to me when the result should use columns in a DataFrame or MultiIndex levels. Maybe that is an indication that we should consider simplifying these methods, to the cases where it's clear what the right behavior is.
> Maybe keyword arguments would be a good way to distinguish, e.g., df.agg(foo={'A': np.mean}) and df.A.agg(foo=np.mean)?

this is an idea (and its back-compat).

The biggest issue here is consistency between what users *already* do on groupby,rolling,resample. I don't think this PR should deviate at all. Its very familiar, and already quite well documented.

```
In [35]: df = pd.DataFrame({'A': range(5), 'B': [1, 1, 2, 2, 3]})

In [36]: df.groupby('B').agg({'A': [np.mean, np.sum], 'B': np.sum})
Out[36]:
    B    A
  sum mean sum
B
1   2  0.5   1
2   4  2.5   5
3   3  4.0   4

In [37]: df.agg({'A': [np.mean, np.sum], 'B': np.sum})
Out[37]:
        B     A
mean  NaN   2.0
sum   9.0  10.0
```

as far as the above example you pointed.

- this is *exactly* what a typical ``.describe`` type operation does.

```
In [50]: df.describe()
Out[50]: 
              A        B
count  5.000000  5.00000
mean   2.000000  1.80000
std    1.581139  0.83666
min    0.000000  1.00000
25%    1.000000  1.00000
50%    2.000000  2.00000
75%    3.000000  2.00000
max    4.000000  3.00000
```

- the ``.groupby`` actually has another piece of information, namely the group indexer, which we don't have for the ``DataFrame.agg`` case
- showing it like the groupby would be like this (ignore the gymnastics to get it here).

```
In [47]: df.agg({'A': [np.mean, np.sum], 'B': np.sum}).unstack().dropna().to_frame().T.sort_index(axis=1)
Out[47]: 
     A          B
  mean   sum  sum
0  2.0  10.0  9.0
```

but this IMHO is not very useful (and kind of odd, e.g. what is the 0 index mean anyhow).


Here's what the MI Series would look like.
```
In [53]: df.agg({'A': [np.mean, np.sum], 'B': np.sum}).unstack().sort_index()
Out[53]: 
A  mean     2.0
   sum     10.0
B  mean     NaN
   sum      9.0
dtype: float64
```

And for comparison what we *ARE* doing with a Series
```
In [54]: df.A.agg({'foo':[np.mean, np.sum], 'bar':np.sum})
Out[54]: 
bar  sum     10.0
foo  mean     2.0
     sum     10.0
Name: A, dtype: float64
```

So I do like the consistency that Series -> Series, and DataFrame -> DataFrame. I think this is very predictable.
@jorisvandenbossche if you have further thoughts.
unless any more comments, going to push this in soon.
I am still -0 on the API here. Even though this is mostly consistent with how `.groupby(...).agg(...)` works, we should use this opportunity to clean up the API before extending it further.

My concern remains that there does not appear to be a simple set of rules we can write down for how this works without special cases at each leaf of the tree of possible call patterns. I want a short list of rules, not a flowchart.
any cleanup should be down after in another PR

so what are the 'nice' simple rules that you propose?

keeping in mind the myriad of situations that we currently support?
> any cleanup should be down after in another PR

I don't like approach of adding features now and cleaning up the API later if someone gets around to it. As a mature library, we should add new features when we are confident that we have the right API.

In my opinion `.agg` is already one of pandas' features suitable for interactive use but not for production (https://github.com/pandas-dev/pandas/issues/13862), and we should not extend it in its current state.

> so what are the 'nice' simple rules that you propose?
>
> keeping in mind the myriad of situations that we currently support?

I don't have a proposal for handling the full generality of what `groupby(...).agg` allows. I think it's probably too complex already, and it would be better to support a subset of cases that can be done consistently.

Possibly judgment from @wesm would be helpful here.
> In my opinion .agg is already one of pandas' features suitable for interactive use but not for production (#13862), and we should not extend it in its current state.

and do you have *any* example of this? I find this statement pretty ridiculous actually. ``.agg`` is quite well defined.


> I don't have a proposal for handling the full generality of what groupby(...).agg allows. I think it's probably too complex already, and it would be better to support a subset of cases that can be done consistently.

well that's not very helpful.

Sorry I didn't reply earlier on the comments of @shoyer 

I agree with @shoyer that we should clean-up the API now, and not after merging the PR, *if* we want to do that (@jreback said above "I don't think this PR should deviate at all [from the current groupby/rolling api]", so that is a first point of discussion).

>> I don't have a proposal for handling the full generality of what groupby(...).agg allows. I think it's probably too complex already, and it would be better to support a subset of cases that can be done consistently. 
>
> well that's not very helpful.

But that's the point (AFAIU) that @shoyer makes: it will not be possible to handle all cases that groupby.agg currently supports with a cleaned-up API. So this is certainly a possible/helpful, although not yet concrete, proposal. 
Regarding the multiple ways how the dictionary keys are interpreted, @shoyer proposed above "I would be inclined to only support one of these use cases, though I'm not sure which one is more important." 

---

In any case, having the rules very clearly specified is always useful, regardless of whether we end up simplifying them or not. 
(and this is currently not yet the case: the docstring is not fully complete (does not mention the renaming), and in the narrative docs it is scattered over examples (which is not bad, but a summary with the rules could be added).

First try to get overview of current rules (from the top of my head, didn't check everything):

Accepted values / returned output shape:

- Scalar (function or string function name)
  - For Series this gives a scalar
  - For DataFrame this gives a Series with column names as labels
- List of function specific
  - For Series this gives a Series with function names as Series labels
  - For DataFrame this gives a DataFrame with column names as column labels and function names as index labels
- dict of scalar/list:
  - output shape is similar to / depends on whether scalar or list is passed (*is this true?*)
  - how dict keys are interpreted varies, see below

Interpretation of dict structure:

- For Series, the dict keys are use to name the result, instead of the default function name ('renaming' functionality)
- For DataFrame:
  - the dict keys are used to select columns and thus to apply specific functions to specific columns ('selection' functionality)
  - but if the dict has multiple levels, the first level is used for 'selection', and the second level for 'renaming'.

Here, the inconsistency is mainly between how dict keys are interpreted between Series / DataFrame.


Related issue: https://github.com/pandas-dev/pandas/issues/8593. 
See also https://github.com/pandas-dev/pandas/issues/9052#issuecomment-164759860 for a more detailed overview in case of `groupby` (and the comment above to indicate some further inconsistencies in the groupby case).
Regarding the different interpretation of the dict keys on Series vs DataFrame (renaming vs selecting), @shoyer said above:

> This makes it hard to understand and predict how these methods work without active experimentation. But, for what it's worth, I do recognize that this is consistent with how groupby.agg already works. 
> I would be inclined to only support one of these use cases, though I'm not sure which one is more important. Ideally, we would have a separate orthogonal way to indicate how to name the result, so that could be used at the same time as column selection. Maybe keyword arguments would be a good way to distinguish

To explore the use of keyword arguments a bit further. 
First, I think the usage of `{col_name: ..}` is used in different places throughout the pandas API to specify actions on certain columns (eg astype, round, dtype=.., ...), so I think it would be logical to keep the dict keys for selection. That leaves keyword names for renaming (also consistent with eg `assign`)

For Series.agg, this would eliminate the use of dicts entirely:

```
s.agg(average=np.mean, standard_deviation='std')
```

For DataFrame.agg, this would make the "apply several functions to all columns but with custom name"  actually easier:

```
df.agg(average=np.mean, standard_deviation='std')
```

(where before you would have to specify a nested dict with repetition `{'colA': {'average':np.mean, 'standard_deviation':'std'}, 'colB': {'average':np.mean, 'standard_deviation':'std'}, ...}`) 
For DataFrame, applying different functions on different columns stays the same:

```
df.agg({'colA': 'mean', 'colB': 'count', ..})
```

Only, if we keep dicts solely for selecting columns (so not using second level to rename), the complex examples of specifying different functions with custom name on different columns becomes more difficult, but still possible:

```
df.agg(my_mean={'colA': 'mean'}, my_count={'colB': 'count'})
```

(but this is a bit ugly IMO)

From writing this up, I actually seem to like this, but, it's not that the explanation will necessarily become less complex :-) (but more consistent between frame/series)
```
df.agg(my_mean={'colA': 'mean'}, my_count={'colB': 'count'})
```

can be written like
```
df.agg(my_mean=dict(colA='mean'), my_count=dict(colB='count'))
```

which IMHO 'looks' better (though functionally the same)

in fact we talked about this in another issue to support named level lookup e.g.
```df.loc[dict(level_0=1, level_2=['a', 'b'])]```, reminsent of ``xarray.sel()``, but using ``[]`` which support assignment (this obviously is a completely different purpose, I bring it up because the same issue of function call / vs dict syntax was important there).
ok, so if IIUC, @jorisvandenbossche and @shoyer main objections are in the *existing* interface w.r.t. how dict inputs are handled (differenty for Series/DataFrame).

I would like to solve this objection first as its practically much easier to

- fix the base infrastructure
- deprecate what needs to be done
- revise the API before this PR (which is just builds on the base structure)

if we were to deprecate a dictionary passed to agg, in lieu of the using named args (that can accept scalar, list, dict) does this make things more clear?