API/WIP: .sorted
State: closed

closes #9816 
closes #8239

```
Changes to sorting API
^^^^^^^^^^^^^^^^^^^^^^

The sorting API has had some longtime inconsistencies. (:issue:`9816`,:issue:`8239`).

Here is a summary of the **prior** to 0.17.0 API

- ``Series.sort`` is **INPLACE** while ``DataFrame.sort`` returns a new object.
- ``Series.order`` returned a new object
- It was possible to use ``Series/DataFrame.sort_index`` to sort by **values** by passing the ``by`` keyword.
- ``Series/DataFrame.sortlevel`` worked only on a ``MultiIndex`` for sorting by index.

To address these issues, we have revamped the API:

- We have introduced a new method, ``.sort_values()``, which is the merger of ``DataFrame.sort()``, ``Series.sort()``,
  and ``Series.order``, to handle sorting of **values**.
- The existing method ``Series.sort()`` has been deprecated and will be removed in a
  future version of pandas.
- The ``by`` argument of ``DataFrame.sort_index()`` has been deprecated and will be removed in a future version of pandas.
- The methods ``DataFrame.sort()``, ``Series.order()``, will not be recommended to use and will carry a deprecation warning
  in the doc-string.
- The existing method ``.sort_index()`` will gain the ``level`` keyword to enable level sorting.

We now have two distinct and non-overlapping methods of sorting. A ``*`` marks items that
will show a ``PendingDeprecationWarning`` (normally suppressed by python), and a ``+`` marks items that
will show a ``FutureWarning``.

To sort by the **values**:

=================================     ====================================
Previous                              Replacement
=================================     ====================================
*``Series.order()``                   ``Series.sort_values()``
+``Series.sort()``                    ``Series.sort_values(inplace=True)``
``DataFrame.sort(columns=...)``       ``DataFrame.sort_values(by=...)``
=================================     ====================================

To sort by the **index**:

=================================     ====================================
Previous                              Equivalent
=================================     ====================================
``Series.sort_index()``               ``Series.sort_index()``
``Series.sortlevel(level=...)``       ``Series.sort_index(level=...``)
``DataFrame.sort_index()``            ``DataFrame.sort_index()``
``DataFrame.sortlevel(level=...)``    ``DataFrame.sort_index(level=...)``
*``DataFrame.sort()``                 ``DataFrame.sort_index()``
==================================    ====================================
```


Labels:
name: Reshaping
description: Concat, Merge/Join, Stack/Unstack, Explode
name: API Design
name: Needs Discussion
description: Requires discussion from core team before further action

Comments:
xref #10721 

cc @brandon-rhodes
cc @patricktokeeffe

I wonder if we want to take this as a opportunity to clean up this discrepancy for default sort behavior:
- a series is sorted by its values
- a dataframe is sorted by its index

It might make more sense for `.sorted()` to always sort by values, and require another function or argument to sort the index.

> It might make more sense for .sorted() to always sort by values, and require another function or argument to sort the index.

How would that work for dataframes? What does 'values' mean in that context? I think it makes more sense to target their commonality: index.

I think what @shoyer mean is the default could be:

This is False in master

```
s.sorted() == s.sort_index()
```

proposed:
would need to something like: `s.sorted('values')` (could also take the name of the Series)

```
s.sorted('values') == s.sorted('A') == s.order() # s.order() is the current sort the values
```

This is True in master

```
df.sorted() == df.sort_index()
df.sorted('A') == df.sort('A') # this is currently how it works, e.g. column sorting
```

@hayd @sinhrks @TomAugspurger @jorisvandenbossche 

OK, I see. Yes, I had misunderstood @shoyer, if that's what was meant. Thanks 

> How would that work for dataframes? What does 'values' mean in that context? I think it makes more sense to target their commonality: index.

I was thinking that the default for `df.sorted()` might be to sort the values lexicographically in column order, e.g., the current result obtained from `df.sort(list(df.columns))`. But we could also take the approach of sorting the index by default instead in both cases.

Alternatively, instead of adding `sorted`, we might encourage the more explicit `.sort_values()` and `.sort_index()`

yeh I think `.sort_index` should stick around, and I like `.sort_values`.

```
==============================     ===============================
Previous                           Replacement
==============================     ===============================
``Series.order()``                 ``Series.sort_values()``
``Series.sort()``                  ``Series.sort_values(inplace=True)``
``DataFrame.sort(columns=...)``    ``DataFrame.sort_values(by=...)``
==============================     ===============================
```

Furthermore, the following operations are implemented using `.sorted()`; the original methods remain for convenience.

```
==============================     ===============================
Previous                           Equivalent
==============================     ===============================
``Series.sort_index()``            ``Series.sort_index()``
``Series.sortlevel(level=...)``    ``Series.sort_index(level=...``)
``DataFrame.sort_index()``         ``DataFrame.sort_index(level=True)``
``DataFrame.sortlevel(level=...)`` ``DataFrame.sort_index(level=...)``
==============================     ===============================
```

> I was thinking that the default for df.sorted() might be to sort the values lexicographically in column order, e.g., the current result obtained from df.sort(list(df.columns)). 

I think this trades one discrepancy (values vs. index) for another (default sort on axis=0 vs axis=1). Currently df.sort() is by index, which always made more sense to me. (Disclaimer: I work with timeseries.) What happens if/when `panel` gets `sorted`? For default behaviors, acting on index seems more appropriate.

> I think this trades one discrepancy (values vs. index) for another (default sort on axis=0 vs axis=1).

Actually, `df.sort` already defaults to `axis=0`. I agree that this is confusing, though -- it's not obviously clear whether `axis=0` or even `axis='rows'` means that the result will be sorted _by_ rows or sorted _along_ rows.

> Currently df.sort() is by index, which always made more sense to me. (Disclaimer: I work with timeseries.)

I agree that sorting by the index usually makes sense -- if you have defined a meaningful index. This is often not the case with pandas, and for such users the default behavior of `df.sort()` will be confusing.

I think there is a lot to be said for requiring and/or encouraging users to be more explicit by having separate `sort_index` and `sort_values` methods.

The main downside is that it forces users to considering whether something is in the index or the values, which is another confusing distinction we are trying to slowly get away with. So, there could still be a case for having a generic `.sort`/`.sorted` method on DataFrames that _requires_ the `by` argument and parses the listed names as either columns or index levels.

@jreback Thanks for working on this!

I made an overview of the issue (gathered from the different issues, for who wants to catch up): https://github.com/jorisvandenbossche/pandas/blob/sorting-api/doc/proposals/sorting-API.md

I can make a PR from that if that is easier to comment, but copying the discussion points I see here:

Discussion points:
1. Default sorting by labels or values?
   - Proposed PR: keep inconsistency of Series by values and DataFrame by labels.
   - Alternative: As sorting by the values (certainly for a Series) is very convenient, unify the `sorted` method to sort by values/columns for both Series/DataFrame. To have a convenient method to sort by the index, keep the specific `sort_index` method.
     
     a) Should `sorted` still be able to sort on the index? If not (as `sort_index` does this), we can leave `level` and `sort_remaining` keywords out of the signature.
     
     b) Should `DataFrame.sorted` require at least one column to be specified, or should it default to sort the values lexicographically in column order (equivalent to `df.sorted(by=list(df.columns))`)?
     
     c) If we go with this clear separation of sorting by index/values in two separate functions, do we use `sorted`, or something more specific as `sort_values()` alongside `sort_index`
2. The keyword to select the columns to sort?
   - Current PR: chooses `by` over `columns`
3. Should `Series.sort`, `Series.order` and `DataFrame.sort` be deprecated?
   - Current PR does deprecate these.
   - As these are widespread functions, a real deprecation/removal can have a large impact, and maybe a clear 'documented' deprecations is enough?
4. And for the integration of `sortlevel` into `sort_index`: 
   - Not controversial: add `level` and `sort_remaining` to `sort_index()`
5. Should `sort_index` still be able to sort by the columns?
   - It is very strange to use `sort_index` to sort by the columns (but even the implementation of `DataFrame.sort` uses `DataFrame.sort_index`).
   - We could deprecate this ability (the deprecating/discouraging the `by` keyword)

And my 2 cents at the moment:

1) I would like to see the default of sorting by index/values cleaned-up and consistent between dataframe and series. And I like the proposal of keeping a separate function (as we already have this: `sort_index`) for this, and having `sorted` default to the values. 
BTW, I also find `df.sorted(level=True)` very unintuitive as the recommended way to sort by the index, so this also argues for a clear separate `sort_index` IMO

1a) I think the new `sorted` should not be able to sort the index. As this is not needed (you use `sort_index` for that), and simplifies the API (less keyword arguments). A possible argument to keep this is to do combined sorting on index an columns, but this is currently also not possible.

Related to this, I also don't think `sort_index` should be able to sort on the columns (I know this is already there, but we could deprecate this, or at least change the documentation (it is now even an example in the docstring of sort_index))

FYI, the `level=True` stuff was just to get all in one method! Its bad I agree :)

ok so here are my thoughts
1. so a) agree

`.sorted(..)` is for values
`.sort_index(..)` is for the index
- so we enforce the `by` argument to `.sorted` (on `DataFrame`)
- remove the index sorting capability, IOW, `levels/sort_remaining` & add to `sort_index`.
- leave `.sortlevel` as an alias for now (maybe soft-deprecate)

b) I don't think sorting by all columns is intuitive at all, user should simply have to specify, -1 here.

c) sorted is good. SQL-like and matches the name in python, also clear sep from `sort_index`.

2) `by` just for consistency, but I guess it really doesn't matter as the signature is specific to `DataFrame`, could go either way here

3) I think we _MUST_ deprecate `Series.sort`. Ok with soft-deprecating `Series.order` (e.g. via doc-string). though I think it IS cleaner to deprecate it now.
`DataFrame.sort` sort is like `Series.order`, we are not really changing it, (except removing the sort index capability, but that would be in `.sorted`, I don't think we can touch it here). So soft-deprecation also ok.

4) yes, add `.levels/sort_remaining` to `.sort_index`

5) I am torn here. and I see why this is allowed in `.sort_index`. You want to be able to sort by a column, but you don't care if its set as the index. But that is inconsistent with the model of having a method do a single well-defined thing. E.g. `.sorted` sorts values, `.sort_index` sorts the index.

FWIW, I agree with these recent comments. Had to take several steps back and consider how other generalized functions are handled before deciding the index isn't the best target after all. 

Sorting dataframes by all their columns doesn't feel right, though - the user should have to decide.

I'm perfectly fine with requiring an explicit `by` argument when sorting dataframes by values with `sorted`. That will also ensure an easier transition from `DataFrame.sort`.

I agree current proposals, great work:)

5) As functionality,  `sort_index` should cover sorting by columns. I don't feel the name is inappropriate because there are some methods named `index..` covers all axis, like `reindex` and `idxmin`. 

So It think that I understand now why @wesm has a `.sortlevel` rather than just have this allowed in `.sort_index`. 

Imagine

```
In [13]: df = DataFrame(np.arange(12,0,-1).reshape(3,-1),index=pd.MultiIndex.from_tuples([(0,0),(0,1),(1,0)]))

In [14]: df
Out[14]: 
      0   1   2  3
0 0  12  11  10  9
  1   8   7   6  5
1 0   4   3   2  1

In [15]: df.sortlevel(0)
Out[15]: 
      0   1   2  3
0 0  12  11  10  9
  1   8   7   6  5
1 0   4   3   2  1
```

in the new `.sort_index`, then this is ambiguous
`df.sort_index(by=0)` ,IS that the 0'th column or the 0th level of the index?

so we would now have to do
`df.sort_index(by=None,level=0)` to be unambiguous (this adds the `.level` kw)

If we were NOT to allow pass thru columns, then the signature would be almost exactly like `.sortlevel`
e.g.
`df.sort_index(level=...)`

currently this will fail (in master) as you cannot sort a non-MultiIndex by level (though that is easily fixed).

so, I think to avoid ambiguity then we must either pick:
- 5a) leave `.sort_index` alone, and consequently `.sortlevel`
- 5b) add `level` kw to `.sort_index` IN ADDITION to the current `by` kw, these are discreet sorters in that `by` would be for the columns and `level` for the levels of the index, so avoids any ambiguity. The user is then in charge of saying, hey sort by these columns or these levels (in theory we could actually do a multi-sort).
- 5c) deprecate `by` and add `level`, avoids ambiguity but disallows the columns sorting pass thru
- 5d) for labels, you can actually figure out whether its a level or a column (if their are duplicates then you multi-sort or raise), so we _could_ use the `by` keyword only, BUT, the issue is that you can sort on a level number! So we could not allow level numbers in ambiguous cases. more complicated but maybe more natural.

+1 for 5b. `df.sort_index(level=...)` is very readable/clear.

I don't really see a reason to not integrate `sortlevel` in `sort_index`. I would use the `level` keyword in any case for this (and not `by`, as `level` is more consistent with other functions).

So I would go for 5c, although 5b is also OK. 
I am in favor of deprecating the `by` keyword (but soft-deprecation is enough I think -> not encouraging it in the docs), as this would make the API just cleaner: `sorted` for the values, `sort_index` for the labels. 
I don't see a reason that `sort_index` should also be able to sort on the columns (apart from historical ones of course: it already can) 

~~Further, another design question: what should `level` by default do when having a multi-index?~~
- ~~6 a) Only sorting the first level (so default of `level=0`) -> this is the current behaviour of `sortlevel`~~
- ~~6 b) Sorting the different levels in order (so default of `level=None`, the equivalent of `level=[0,1, ..n]` when having `n` levels)~~

~~I would be in favor of 6b, so in contrast to `sorted` (for which we want to require providing a column to sort on with `by`), I would go with sorting all levels for the index. I find this the most logical default behaviour.~~

EDIT: Ignore this, see https://github.com/pydata/pandas/pull/10726#issuecomment-128065395

@jorisvandenbossche maybe I wasn't clear

if u do 5c (remove by) then you CANNOT do sort by column in sort_index
5b adds`level` so that you then have unambiguous level OR column sorting (via `by`)

further `level` cannot default to anything but None (as `b`y also has to be None as a default)

however if both `by` and `level` are `None` then 6 comes into play 
(note that the default for `.sort` in master is to do 6b - fully sort a multi index, so this differs from master of `.sortlevel`)

anybody confused yet????

@jreback no, I understood it correctly, probably I wasn't clear :-) 
As I don't think `sort_index` should be able to sort by columns, I am in favor of (soft)deprecating `by`, so this is option `5c`

Yes, you are right about the default of `level`. I forgot the current behaviour of `sort_index` by looking at `sortlevel` .. So anyways, for back compat, `sort_index` will by default sort all levels (further, this is _actually_ also the behaviour of `sortlevel` as this sorts level 0, but also sorts the remaining, as `sort_remaining=True` by default). So ignore that question!

> if u do 5c (remove by) then you CANNOT do sort by column in sort_index

Isn't sorting by column(s) is sorting by values?? If so, it seems wrong that you could do that with `sort_index`. Therefor I was confused... I prefer 5c. :p

To clarify: in the _current_ situation (no possibility to mix sorting of columns/index levels), I prefer 5c

If we want to add this enhancement to simultaneously specify to sort on index levels and columns (the 5d option of above), then the question is: where do we add this functionality and how? In `sorted`, `sort_index` or both? I would then lean towards saying: only add it in `sorted`, where the `by` keyword can also denote a index level name.

@hayd yes, there was some interest (see 5), in preserving the behavior of `.sort_index` to sort the columns when `by` is passed (event though this IS confusing)

@jreback deprecate it with :fire:!!

so it IS possible, to add mixed index-column sorting in `.sorted`, e.g by specificying columns and/or named levels (but that's the rub, you lose the ability to specify by position at all). But I think that's an ok compromise. 

`.sort_index` because like `.sortlevel` (with a few extra args), and is unambiguous if you specify ints or names of the levels. So 5c looks good to me. 

Then in `2` (for `.sorted`) if we change to `by` from `columns` (no deprecation needed as this is a new method), you can do this mixed index-column sorting.

so something like

```
df = DataFrame(...., columns=list('ABCD'))

df.sorted(by=['A','B'])
df.set_index('A').sorted(by=['A','B'])
```

yield the same,  (and is a pretty reasonable compromise).

one small point though.

```
df = Dataframe(...., columns=[0,1,2])

# this is slightly ambiguous
# do I mean the *index* level 0? or column 0?
df.sorted(by=[0,1])
```

@hayd is that a _soft_ deprecation? or a `blow-them-away` type?

@jreback For your dataframe without index or level names:

```
In [14]: df
Out[14]: 
      0   1   2  3
0 0  12  11  10  9
  1   8   7   6  5
1 0   4   3   2  1
```

I would support ordered by column values by supplying an axis. e.g., `df.sort_index(axis=1)`. In fact this already works:

```
df.iloc[:, ::-1].sort_index(axis=1)
#       0   1   2  3
# 0 0  12  11  10  9
#   1   8   7   6  5
# 1 0   4   3   2  1
```

So I don't think we need any action there on 5 at all?