REGR: DataFrame.stack was sometimes sorting resulting index

[rhshadrach]

#53825 removed the sort argument for DataFrame.stack that was added in #53282 due to #15105, the default being sort=True. Prior to #53825, I spent a while trying the implementation in #53921 (where I had implemented sort=True), and test after test was failing. Implementing instead sort=False made almost every test pass, and looking at a bunch of examples I concluded that sort wasn't actually sorting. It turns out this is not exactly the case.

Note both #53282 and #53825 were added as part of 2.1 development, so below I'm comparing to / running on 2.0.x.

On 2.0.x, DataFrame.stack will not sort any part of the resulting index when the input's columns are not a MultiIndex. When the input columns are a MultiIndex, the result will sort the levels of the resulting index that come from the columns. In this case, the result will be sorted not necessarily in lexicographic order, but rather the order of levels that are in the input's columns. This is opposed to e.g. sort_index which always sorts lexicographically rather than relying on the order in levels.

I think we're okay with the behavior in main as is: it replaces the sometimes sorting behavior with a consistent never-sort behavior. From #15105, it seems like sort=False is the more desirable of the two; users can always sort the index after stacking if they so choose. However, I wanted to raise this issue now that I have a better understanding of the behavior on 2.0.x to make sure others agree.

If we are okay with the behavior, I'm going to make this change more prominent in the whatsnew.

Examples on 2.0.x

df = pd.DataFrame([[1, 2], [3, 4]], index=["y", "x"], columns=["b", "a"])
print(df)
#    b  a
# y  1  2
# x  3  4
print(df.stack())
# y  b    1
#    a    2
# x  b    3
#    a    4
# dtype: int64

columns = MultiIndex(
    levels=[["A", "B"], ["a", "b"]],
    codes=[[1, 0], [1, 0]],
)
df = pd.DataFrame([[1, 2], [3, 4]], index=["y", "x"], columns=columns)
print(df)
#    B  A
#    b  a
# y  1  2
# x  3  4
print(df.stack(0))
#        a    b
# y A  2.0  NaN
#   B  NaN  1.0
# x A  4.0  NaN
#   B  NaN  3.0
print(df.stack(1))
#        A    B
# y a  2.0  NaN
#   b  NaN  1.0
# x a  4.0  NaN
#   b  NaN  3.0
print(df.stack([0, 1]))
# y  A  a    2.0
#    B  b    1.0
# x  A  a    4.0
#    B  b    3.0
# dtype: float64


columns = MultiIndex(
    levels=[["B", "A"], ["b", "a"]],
    codes=[[0, 1], [0, 1]],
)
df = pd.DataFrame([[1, 2], [3, 4]], index=["y", "x"], columns=columns)
print(df)
#    B  A
#    b  a
# y  1  2
# x  3  4

print(df.stack(0))
#        b    a
# y B  1.0  NaN
#   A  NaN  2.0
# x B  3.0  NaN
#   A  NaN  4.0

print(df.stack(0).sort_index(level=1))
#        b    a
# x A  NaN  4.0
# y A  NaN  2.0
# x B  3.0  NaN
# y B  1.0  NaN

print(df.stack(1))
#        B    A
# y b  1.0  NaN
#   a  NaN  2.0
# x b  3.0  NaN
#   a  NaN  4.0

print(df.stack([0, 1]))
# y  B  b    1.0
#    A  a    2.0
# x  B  b    3.0
#    A  a    4.0
# dtype: float64

columns = MultiIndex(
    levels=[["A", "B"], ["a", "b"]],
    codes=[[1, 0], [1, 0]],
)
index = MultiIndex(
    levels=[["X", "Y"], ["x", "y"]],
    codes=[[1, 0], [1, 0]],
)
df = pd.DataFrame([[1, 2], [3, 4]], index=["y", "x"], columns=columns)
print(df.stack(0))
#        a    b
# y A  2.0  NaN
#   B  NaN  1.0
# x A  4.0  NaN
#   B  NaN  3.0

print(df.stack(1))
#        A    B
# y a  2.0  NaN
#   b  NaN  1.0
# x a  4.0  NaN
#   b  NaN  3.0

print(df.stack([0, 1]))
# y  A  a    2.0
#    B  b    1.0
# x  A  a    4.0
#    B  b    3.0
# dtype: float64

cc @mroeschke @jorisvandenbossche

[jorisvandenbossche]

To check my understanding, using a small variation on your first example above with a MultiIndex columns, having both non-sorted level and codes in the MultiIndex level in the columns we are stacking:

columns = MultiIndex(
    levels=[["B", "A"], ["a", "b"]],
    codes=[[1, 0], [1, 0]],
)
df = pd.DataFrame([[1, 2], [3, 4]], index=["y", "x"], columns=columns)

>>> df
   A  B
   b  a
y  1  2
x  3  4

With pandas 2.0.x, we get:

>>> df.stack(0)
       a    b
y B  2.0  NaN
  A  NaN  1.0
x B  4.0  NaN
  A  NaN  3.0

>>> df.stack(0).index.levels
FrozenList([['x', 'y'], ['B', 'A']])
>>> df.stack(0).index.codes
FrozenList([[1, 1, 0, 0], [0, 1, 0, 1]])

So in this case, the level are kept as is, but the codes (for the added level) are sorted (and thus in this case the result actually appears to be unsorted because of the way I constructed the original dataframe ..).

With pandas main I currently get:

>>> df.stack(0)
       b    a
y A  1.0  NaN
  B  NaN  2.0
x A  3.0  NaN
  B  NaN  4.0

>>> df.stack(0).index.levels
FrozenList([['x', 'y'], ['B', 'A']])
>>> df.stack(0).index.codes
FrozenList([[1, 1, 0, 0], [1, 0, 1, 0]])

In this case, both the level order as codes order is left as is (i.e. not any kind of sorting), and thus preserves the order of the column level in the original dataframe.

In practice, this also means that if your the codes were sorted in your original dataframe (which I think is probably the most common case?), regardless of the order of the labels in the level, nothing will change compared to the current behaviour on pandas 2.0.x? (since we were only sorting the codes, not level labels)

Given the above, I think this is probably fine. Although, in theory, it's a case that we can relatively easily detect and raise a warning in advance of changing it? (if we would keep the actual change for 3.0, for example together with switching to the new implementation)

---

One other observation: it seems that on main, we now also preserve the order of the remaining columns in the result (see a b
vs b a in the result of stack in both cases).
I think that is also an improvement, but it's another potentially breaking change to be aware of.

[rhshadrach]

Although, in theory, it's a case that we can relatively easily detect and raise a warning in advance of changing it? (if we would keep the actual change for 3.0, for example together with switching to the new implementation)
...
I think [not sorting columns] is also an improvement, but it's another potentially breaking change to be aware of.

Correct. I am suggesting we call these inconsistencies (sometimes sorting, sometimes not) bugs and can highlight them in the notable bug fixes. The other option would be to revert the changes and introduce deprecations.

[rhshadrach]

Friendly ping @mroeschke.

@jorisvandenbossche - are you okay calling these bugs (see my previous comment)?

[mroeschke]

I think I would agree to call inconsistent sorting a notable bug fix, especially since consistently not sorting seems to be a better desired behavior from users given the past issues

[jorisvandenbossche]

I am OK with that.

One more question: how much is the change in behaviour that is already present in main tied to the current default implementation? Or, asked differently, how hard would it be to only have the change in sorting in your new implementation? We will probably already add a keyword for that anyway (to opt in to that new implementation), and then switch to use that by default in 3.0. In theory this sorting behaviour change could be part of that v3 impl and upgrade path?

[rhshadrach]

how much is the change in behaviour that is already present in main tied to the current default implementation?

None, they are entirely independent implementations.

In theory this sorting behaviour change could be part of that v3 impl and upgrade path?

That would work. I thought of doing this too, but was worried about the downside of replacing the implementation and having more significant behavior changes. Rethinking it, I'm now seeing this is better than potentially breaking code given the long standing, even if undesirable, behavior.