BUG: groupby(group_keys=False).apply(func=transform_function) with duplicate index does not preserve original dataframe order

[sfc-gh-mvashishtha]

Pandas version checks

• 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 main branch of pandas.

Reproducible Example

import pandas as pd
df = pd.DataFrame(                    [
                        ["k0", 13, "e"],
                        ["k1", 14, "d"],
                        ["k0", 15, "c"],
                        ["k0", 16, "b"],
                        [None, 17, "a"],
                    ],
                    index=pd.Index(["i1", None, "i0", "i2", None], name="index"),
                    columns=pd.Index(["string_col_1", "int_col", "string_col_2"], name="x"),)
print(df.index)
result = df.groupby("index", sort=False, dropna=False, group_keys=False)['int_col'].apply(lambda v: v)
print(result.index)
assert result.index.equals(df.index)

Issue Description

groupby.apply transforms should restore the original dataframe order.

The current implementation loses the original order so when the axis has duplicates, there's no way to correctly reindex the result back to the original order here.

Expected Behavior

when func acts as a transform, groupby.apply should produce a result that has the same index as the original. The result for the nth value with group key a in the input dataframe should be the nth value with group key a in the output dataframe.

Installed Versions

INSTALLED VERSIONS

commit : bdc79c1
python : 3.9.18.final.0
python-bits : 64
OS : Darwin
OS-release : 23.4.0
Version : Darwin Kernel Version 23.4.0: Wed Feb 21 21:45:49 PST 2024; root:xnu-10063.101.15~2/RELEASE_ARM64_T6020
machine : arm64
processor : arm
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 2.2.1
numpy : 1.26.3
pytz : 2023.3.post1
dateutil : 2.8.2
setuptools : 68.2.2
pip : 23.3.1
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : 8.18.1
pandas_datareader : None
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : None
bottleneck : None
dataframe-api-compat : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : None
numba : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pyreadstat : None
python-calamine : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
zstandard : None
tzdata : 2023.4
qtpy : None
pyqt5 : None

[ChaikhiBelaid]

Hello I would like to work on this issue please.

[rhshadrach]

Thanks for the report; agreed apply should reorder the result here. For a workaround, groupby.transform appears to behave correctly.

[undermyumbrella1]

Hi @ChaikhiBelaid, are you currently working on this?

[AfonsoOrmonde]

Hi @rhshadrach, i was looking at this issue with intent on completing it but i must have missed something, shouldnt the behave of said function group the indexes which are the same? I seem to not be comprehending what exactly is wrong. Is the problem that when we use group_key = False the indexes should remain in the original order?

[rhshadrach]

@AfonsoOrmonde - here is a slightly simpler reproducer. result below is incorrect, and should be the same as expected.

df = pd.DataFrame({"key": [np.nan, 1, np.nan], "value": [1, 2, 3]}).set_index("key")
result = df.groupby("key", dropna=False, group_keys=False)["value"].apply(lambda v: v + 1)
print(result)
# key
# NaN    2
# NaN    4
# 1.0    3
# Name: value, dtype: int64

expected = df.groupby("key", dropna=False)["value"].transform(lambda v: v + 1)
print(expected)
# key
# NaN    2
# 1.0    3
# NaN    4
# Name: value, dtype: int64

[AfonsoOrmonde]

OK thank you! I think i might have a solution

[AfonsoOrmonde]

take

[AfonsoOrmonde]

@rhshadrach i was able to get the desired output, however some tests fail due to reindexing not being possible with duplicate labels. Is this not supposed to happens with the new changes? Because if they are supposed to regain their original order, shouldnt they have their correspondent starting indexes (which are not reindexable)? I apologize upfront if i misunderstood something.

[rhshadrach]

some tests fail due to reindexing not being possible with duplicate labels. Is this not supposed to happens with the new changes?

Correct - such a case should not lead to failure here. If your solution is to call Series/DataFrame.reindex, I do not think that is the right approach. Rather, we'd want to use the groupby internals (likely, _grouper.result_ilocs) to reorder the result with a .take.