[WIP] ENH: Add only_unique group-by aggregation method
State: closed

I think it would be useful to have a "only unique value" aggregation method. I've implemented a straw-man version so far to get some feedback before I do any more.

- [x] closes #xxxx N/A
- [ ] tests added / passed
- [ ] Ensure all linting tests pass, see [here](https://pandas.pydata.org/pandas-docs/dev/development/contributing.html#code-standards) for how to run them
- [ ] whatsnew entry

Idea is that you often get data-frames where some of the columns are constant across a group-by. Usually, this is collapsed using "first" when doing a group-by, but it would be good to have a version that verifies that the columns are constant as well.

For example:

```python
df = pd.DataFrame({"id": [0, 0, 1, 1], "name": ["Alice", "Alice", "Bob", "Bob"], "score": [0, 1, 2, 3]})
pd.concat([
    df.groupby("id")["name"].only_unique(),
    df.groupby("id")["score"].max(),
], axis=1)
```

```raw
     name  score
id              
0   Alice    0.5
1     Bob    2.5
```

```python
df = pd.DataFrame({"id": [0, 0, 1, 1], "name": ["Alice", "Alice", "Bob", "Charlie"], "score": [0, 1, 2, 3]})
pd.concat([
    df.groupby("id")["name"].only_unique(),
    df.groupby("id")["score"].max(),
], axis=1)
```

```raw
...
ValueError: Expected exactly one unique item in series, but got 2.
```

I've chosen not to drop NA values, so that the exact null value is maintained, but I'm definitely open to changing it.

```python
df = pd.DataFrame({"id": [0, 0, 1, 1], "name": ["Alice", "Alice", None, None], "score": [0, 1, 2, 3]})
pd.concat([
    df.groupby("id")["name"].only_unique(),
    df.groupby("id")["score"].max(),
], axis=1)
```

```raw
     name  score
id              
0   Alice    0.5
1    None    2.5
```


Labels:
name: Groupby
name: API Design

Comments:
Hello @jamesmyatt! Thanks for updating this PR. We checked the lines you've touched for [PEP 8](https://www.python.org/dev/peps/pep-0008) issues, and found:



There are currently no PEP 8 issues detected in this Pull Request. Cheers! :beers: 

##### Comment last updated at 2021-02-15 15:39:03 UTC
> Usually, this is collapsed using "first" when doing a group-by

Hi @jamesmyatt - not sure I've understood - could you give an example please of when this happens?

---

> it would be good to have a version that verifies that the columns are constant as well.

Is there any difference between this and
```
df.groupby("id")["name"].unique().explode()
```
, followed by a check that the index doesn't contain new duplicates? If so, this seems simple enough and I'm not sure it's worth adding a new aggregation method

---

Also, your example doesn't seem right, if you're taking the max the output would be
```
     name  score
id              
0   Alice      1
1     Bob      3
```
> pls open an issue for discussion first. not sure I understand the usecase / need here. we have a quite high bar for new api.

Thanks @jreback for looking at this. I thought the use case was clear (I've seen it or something like it in many of my projects) but I think I've butchered in trying to simplify it. Also, I thought that the implementation was going to be not-too-hard, so it would be worth jumping straight-in with a straw-man proposal. But it turns out that neither of those assumptions hold, so I'll close this one and open an issue for discussion with better examples, then re-open if appropriate.
Thanks @MarcoGorelli for looking at this.

> > Usually, this is collapsed using "first" when doing a group-by
> 
> Hi @jamesmyatt - not sure I've understood - could you give an example please of when this happens?

I'll elaborate this in an issue. I think I've butchered the use case while trying to simplify it.

In the meantime, maybe it helps if I change it to:

```
pd.concat([
    df.drop(columns=cols_to_average).groupby(index_cols).only_unique(),
    df.groupby(index_cols)[cols_to_average].mean(),
], axis=1)
```

The simplest solution (which only works for the happy path) is:

```
pd.concat([
    df.drop(columns=cols_to_average).groupby(index_cols).first(),
    df.groupby(index_cols)[cols_to_average].mean(),
], axis=1)
```

> > it would be good to have a version that verifies that the columns are constant as well.
> 
> Is there any difference between this and
> 
> ```
> df.groupby("id")["name"].unique().explode()
> ```
> 
> , followed by a check that the index doesn't contain new duplicates? If so, this seems simple enough and I'm not sure it's worth adding a new aggregation method

1. Your version doesn't work for the dataframe case (e.g. `df.drop(columns="score").groupby("id").unique().explode()`): 
    - `AttributeError: 'DataFrameGroupBy' object has no attribute 'unique'`
    - `DataFrame.explode` requires you to explode one column at a time.
2. The fact that you have to the describe the check on the index in words rather than code shows that your version is not a good replacement.

It's funny that you mention `explode` because what I'm looking for is more-or-less the opposite, since the original dataframe can be reproduced using:

```
pd.DataFrame({"id": [0, 1], "name": ["A", "B"], "scores": [[0, 1], [2, 3]]}).explode("scores")
```

> Also, your example doesn't seem right, if you're taking the max the output would be
> 
> ```
>      name  score
> id              
> 0   Alice      1
> 1     Bob      3
> ```

You're right. I changed "max" to "mean" but didn't change the code.