BUG: Transform() function returns unexpected results with list

[J-ZW-Wang]

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

game = pd.DataFrame({
    'team' : ['A', 'A', 'B', 'B', 'C', 'C', 'C'],
    'members' : [1, 2, 3, 4, 5, 6, 7]
})

game['all_team_members'] = game.groupby('team').members.transform(lambda x : x.tolist())
game

Issue Description

I would like to concatenate the values from column 'members' by group 'team' to form a list, and attach them to a new variable all_team_members. I got the following results:

  team  members  all_team_members
0    A        1                 1
1    A        2                 2
2    B        3                 3
3    B        4                 4
4    C        5                 5
5    C        6                 6
6    C        7                 7

Expected Behavior

The expected results are:

  team  members  all_team_members
0    A        1            [1, 2]
1    A        2            [1, 2]
2    B        3            [3, 4]
3    B        4            [3, 4]
4    C        5         [5, 6, 7]
5    C        6         [5, 6, 7]
6    C        7         [5, 6, 7]

Note, game.groupby('team').members.apply(lambda x : x.tolist()) worked as expected, so this problem is isolated to just the transform() function

Installed Versions

INSTALLED VERSIONS ------------------ commit : bdc79c1 python : 3.11.5.final.0 python-bits : 64 OS : Windows OS-release : 10 Version : 10.0.22631 machine : AMD64 processor : AMD64 Family 23 Model 96 Stepping 1, AuthenticAMD byteorder : little LC_ALL : None LANG : None LOCALE : English_United States.1252

pandas : 2.2.1
numpy : 1.24.3
pytz : 2023.3.post1
dateutil : 2.8.2
setuptools : 68.0.0
pip : 23.2.1
Cython : None
pytest : 7.4.0
hypothesis : None
sphinx : 5.0.2
blosc : None
feather : None
xlsxwriter : None
lxml.etree : 4.9.3
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.1.2
IPython : 8.15.0
pandas_datareader : None
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : 4.12.2
bottleneck : 1.3.7
dataframe-api-compat : None
fastparquet : None
fsspec : 2023.4.0
gcsfs : None
matplotlib : 3.7.2
numba : 0.57.1
numexpr : 2.8.4
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : 11.0.0
pyreadstat : None
python-calamine : None
pyxlsb : None
s3fs : 2023.4.0
scipy : 1.11.1
sqlalchemy : None
tables : 3.8.0
tabulate : None
xarray : 2023.6.0
xlrd : None
zstandard : 0.19.0
tzdata : 2023.3
qtpy : None
pyqt5 : None

[rhshadrach]

Thanks for the report. I agree this would be useful to have, but the trouble is in the inference of what to do with the result. groupby.transform can take methods that are transforms (return the same shape as the input) or reducers (return a scalar). When a User Defined Function (UDF) returns a list of the same length as the input as is the case here, how do we know which of the two the user desires?

The only option I see is to add an argument to transform, e.g. is_reducer=[[False]|True] or is_reducer=[["infer"]|True|False] which provides the user control over how the result is interpreted.

Another option I've considered is to be very strict and require the UDF return a Series or DataFrame with an index the same as an input to be considered a transform, otherwise we assume it's a reducer. However I do not think this is a viable path - e.g. in the past we've recommended users use .to_numpy() if they wish to avoid transform's automatic alignment with the input, and this would be incompatible with that.

Yet another option would be to infer scalars (via pandas' is_scalar) and assume transform otherwise. This would notably not help the use case of the OP.

[J-ZW-Wang]

Thank you for investigating this. I believe that, intuitively, the behavior of groupby('x').y.transform() should be consistent with that of groupby('x').y.apply(). When opting for transform() over apply(), users anticipate obtaining the same results from the groupby() aggregation, with the distinction that the original DataFrame's structure is preserved. It is expected that the newly created variable captures the aggregate outcomes for the elements within each group. Therefore, distributing these aggregated values across individual rows seems counterintuitive. Furthermore, the official documentation does not address this aspect of behavior within the context of groupby()

[rhshadrach]

When opting for transform() over apply(), users anticipate obtaining the same results from the groupby() aggregation, with the distinction that the original DataFrame's structure is preserved. It is expected that the newly created variable captures the aggregate outcomes for the elements within each group.

I think you also need to consider the use case of something like:

df.groupby(...).transform(lambda x: x.cumsum())

That is, where func doesn't reduce but transforms. That is currently supported today, and it seems reasonable to me that users expect it to be supported.