BUG: pd.concat produces frames with inconsistent order when concating the ones with categorical indices

[dchigarev]

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the master branch of pandas.

Reproducible Example

import pandas

df1 = pandas.DataFrame(
    {"col1": ["a_val", "b_val", "c_val"]},
    index=pandas.CategoricalIndex(["a", "b", "c"], categories=["a", "b", "c"]),
)
df2 = pandas.DataFrame(
    {"col1": ["b_val", "a_val", "c_val"]},
    # The same categories as in the first frame, but the values are in a different order
    index=pandas.CategoricalIndex(["b", "a", "c"], categories=["b", "a", "c"]),
)

print(pandas.concat([df1, df2]))

# Output:
#     col1
# a  a_val
# b  b_val
# c  c_val
# a  b_val <-- index doesn't math the original value
# b  a_val <-- index doesn't math the original value
# c  c_val

Issue Description

When concatenating frames with categorical indices that have the same values but they are in a different order, the order of indices does not match the order of values from the original frame.

In the reproducer above each "x" index value corresponds to the "x_val" value in the frame ("a" -> "a_val", "b" -> "b_val", ...), but in the concated frame this order is broken, the index values appears to be sorted, when the actual frame's values don't.

Expected Behavior

An expected result would be either properly sorted result:

    col1
a  a_val
b  b_val
c  c_val
a  a_val
b  b_val
c  c_val

or the one that is unsorted at all:

    col1
a  a_val
b  b_val
c  c_val
b  b_val
a  a_val
c  c_val

Installed Versions

INSTALLED VERSIONS

commit : 945c9ed
python : 3.8.10.final.0
python-bits : 64
OS : Linux
OS-release : 5.4.0-65-generic
Version : #73-Ubuntu SMP Mon Jan 18 17:25:17 UTC 2021
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 1.3.4
numpy : 1.21.2
pytz : 2021.1
dateutil : 2.8.2
pip : 21.2.4
setuptools : 58.0.4
Cython : None
pytest : 6.2.5
hypothesis : None
sphinx : 4.2.0
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.0.1
IPython : None
pandas_datareader: None
bs4 : 4.10.0
bottleneck : None
fsspec : 2021.08.1
fastparquet : None
gcsfs : None
matplotlib : 3.4.3
numexpr : None
odfpy : None
openpyxl : 3.0.8
pandas_gbq : None
pyarrow : 3.0.0
pyxlsb : None
s3fs : 2021.08.1
scipy : None
sqlalchemy : 1.4.23
tables : None
tabulate : None
xarray : 0.19.0
xlrd : 2.0.1
xlwt : None
numba : None

[tyuyoshi]

Hi, i tried to fix this issue in the PR.
Please give me some comments and reviews.
Thank you.

[flippy1313]

take

[flippy1313]

@dchigarev
@mroeschke

After carefully inspecting the code, I proposed that this is actually not a bug but a vague documentation which doesn’t provide enough information on the performance of the concat function. The evidence will be explained in more detail.

The pipeline of the concat function is shown as below:

pd.concat()
-> _Concatenator.get_result(), where _Concatenator is a class in /pandas/core/reshape/concat.py

-> _Concatenator._get_concat_axis(), which aims to calculate the index of the resulting df

-> _get_concat_axis() calls _concat_indexes(indexes)

-> then _concat_indexes returns indexes[0].append(indexes[1:])

-> the indexes in the previous function is of type CategoricalIndex, which is a children class of Index with has the member function append(), which is implemented in the file /pandas/core/indexes/base.py

-> the append() function then converts the incoming CategoricalIndex into a list of them, and the calls the _concat() member function of the class CategoricalIndex in the file /pandas/core/indexes/category.py

-> here the CategoricalIndex is converted to the class Categorical in /pandas/core/array.categorical

-> then, the program tries to concat the member “Codes” of the class Categorical, where Codes, according to the docstring, is the corresponding numeric “class position” in the dataframe.

Here is the issue. The user defines:

df1 = pandas.DataFrame( {"col1": ["a_val", "b_val", "c_val"]}, index=pandas.CategoricalIndex(["a", "b", "c"], categories=["a", "b", "c"]), ) df2 = pandas.DataFrame( {"col1": ["b_val", "a_val", "c_val"]}, # The same categories as in the first frame, but the values are in a different order index=pandas.CategoricalIndex(["b", "a", "c"], categories=["b", "a", "c"]), )
The resulting Codes object of df2 has the value: (0,1,2) since the user defines the categories as above. I believe this is intendedly designed by the original developer. However, I think this should be specified by some officials because normally, users will not care about the order of the list of categories.

Some further work can be done by checking the interaction between the above pipeline and the argument “ordered : boolean” in the CategoicalIndex class init function.

[zmbc]

I believe this has been fixed. With pandas 2.1.2, the example above gives the correct output, with ordered=False or ordered=True:

    col1
a  a_val
b  b_val
c  c_val
b  b_val
a  a_val
c  c_val

With ordered=False, the resulting index is categorical; with ordered=True it becomes a string index.

I don't see how the old behavior could be correct as @flippy1313 claims -- sure, it may be unclear what to do with Categoricals with different orders, but Categoricals are definitely supposed to hide their underlying int/enum implementation from the user.