PERF: Huge regression in groupby + sum in dtype_backend == 'pyarrow'.

[pstorozenko]

Pandas version checks

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

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

• I have confirmed this issue exists on the main branch of pandas.

Reproducible Example

There's a huge regression (70x) in groupby + sum when using pyarrow as backend.
The file used:
wiki100.zip
is a small subset of wiki clickstream dataset for March 2022. This zip contains a parquet single file. The regression is the same when working with larger subset when it takes minutes to run pyarrow, compared to seconds in numpy backed Series.

import pandas as pd

wiki100_np = pd.read_parquet("wiki100.parquet")
wiki100_pa = pd.read_parquet("wiki100.parquet", dtype_backend='pyarrow')
wiki100_pa.info()

<class 'pandas.core.frame.DataFrame'>
Index: 8773 entries, 19883848 to 2047474
Data columns (total 4 columns):
 #   Column  Non-Null Count  Dtype          
---  ------  --------------  -----          
 0   prev    8773 non-null   string[pyarrow]
 1   curr    8773 non-null   string[pyarrow]
 2   type    8773 non-null   string[pyarrow]
 3   n       8773 non-null   int64[pyarrow] 
dtypes: int64[pyarrow](1), string[pyarrow](3)
memory usage: 576.7 KB

%%timeit
(
    wiki100_np
    .groupby("curr")
    ['n'].sum()
)
# 6.41 ms ± 325 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

%%timeit
(
    wiki100_pa
    .groupby("curr")
    ['n'].sum()
)
# 449 ms ± 11 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

P.S. Resetting index didn't change anything.

P.S.2 Regression stays the same if I run

(
    wiki100_pa.reset_index(drop=True)
    .groupby("curr")
    .agg({'n': 'sum'})
)

instead.

Installed Versions

INSTALLED VERSIONS ------------------ commit : 965ceca python : 3.10.6.final.0 python-bits : 64 OS : Linux OS-release : 5.19.0-43-generic Version : #44~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Mon May 22 13:39:36 UTC 2 machine : x86_64 processor : x86_64 byteorder : little LC_ALL : None LANG : en_US.UTF-8 LOCALE : en_US.UTF-8

pandas : 2.0.2
numpy : 1.24.3
pytz : 2023.3
dateutil : 2.8.2
setuptools : 67.3.3
pip : 23.0.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.14.0
pandas_datareader: None
bs4 : None
bottleneck : 1.3.7
brotli : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : None
numba : 0.57.0
numexpr : 2.8.4
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : 12.0.1
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : None
snappy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
zstandard : None
tzdata : 2023.3
qtpy : None
pyqt5 : None

Prior Performance

No response

[samukweku]

I dont think groupby operations based on pyarrow has been implemented. I'd expect that it would probably be passed on to the Arrow Table implementation.

[mroeschke]

Thanks but this is a duplicate of #52070.

pandas does not dispatch arrow dtypes to Arrow's groupby implementation yet but will probably come in the next few releases.