BUG: `DataFrame.pivot` fails with `pyarrow` backend if variable is already encoded as `dictionary`

[randolf-scholz]

Pandas version checks

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

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

• [X] I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd

df = (
    pd.DataFrame([("A", 1), ("B", 2), ("C", 3)], columns=["var", "val"])
    .astype({"var": "string", "val": "float32"})
    .astype({"var": "category", "val": "float32"})
)

# write and reload as parquet with pyarrow backend
df.to_parquet("demo.parquet")
df = pd.read_parquet("demo.parquet", dtype_backend="pyarrow")
print(df.dtypes)  # var is now dictionary[int32,string]

df.pivot(columns=["var"], values=["val"])  # ✘ ArrowNotImplementedError

Issue Description

This bug is caused by https://github.com/apache/arrow/issues/34890: currently, pyarrow's dictionary_encode is not idempotent, i.e. it fails with ArrowNotImplementedError instead of returning the data as-is if the array is already of dictionary type.

So, either one waits until it is fixed upstream by pyarrow, or an additional check needs to be added to test whether the series is already of dictionary data type.

Expected Behavior

Pivot should work with categorical data when using the pyarrow backend.

Installed Versions

INSTALLED VERSIONS ------------------ commit : 37ea63d540fd27274cad6585082c91b1283f963d python : 3.11.3.final.0 python-bits : 64 OS : Linux OS-release : 5.19.0-41-generic Version : #42~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Tue Apr 18 17:40:00 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.1 numpy : 1.23.5 pytz : 2023.3 dateutil : 2.8.2 setuptools : 67.7.2 pip : 23.1.2 Cython : 0.29.34 pytest : 7.3.1 hypothesis : None sphinx : 7.0.0 blosc : None feather : None xlsxwriter : None lxml.etree : 4.9.2 html5lib : None pymysql : 1.0.3 psycopg2 : None jinja2 : 3.1.2 IPython : 8.13.1 pandas_datareader: None bs4 : 4.12.2 bottleneck : None brotli : None fastparquet : 2023.4.0 fsspec : 2023.4.0 gcsfs : None matplotlib : 3.7.1 numba : 0.57.0rc1 numexpr : 2.8.4 odfpy : None openpyxl : 3.1.2 pandas_gbq : None pyarrow : 11.0.0 pyreadstat : None pyxlsb : None s3fs : None scipy : 1.10.1 snappy : None sqlalchemy : 1.4.48 tables : 3.8.0 tabulate : 0.9.0 xarray : 2023.4.2 xlrd : None zstandard : None tzdata : 2023.3 qtpy : None pyqt5 : None

[lithomas1]

Thanks for the report, looks like we should probably avoid factorizing then in pivot for dictionary pyarrow types. For reference, this is the traceback.

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/thomasli/pandas/pandas/core/frame.py", line 8792, in pivot
    return pivot(self, index=index, columns=columns, values=values)
  File "/Users/thomasli/pandas/pandas/core/reshape/pivot.py", line 557, in pivot
    multiindex = MultiIndex.from_arrays(index_list)
  File "/Users/thomasli/pandas/pandas/core/indexes/multi.py", line 523, in from_arrays
    codes, levels = factorize_from_iterables(arrays)
  File "/Users/thomasli/pandas/pandas/core/arrays/categorical.py", line 2762, in factorize_from_iterables
    codes, categories = zip(*(factorize_from_iterable(it) for it in iterables))
  File "/Users/thomasli/pandas/pandas/core/arrays/categorical.py", line 2762, in <genexpr>
    codes, categories = zip(*(factorize_from_iterable(it) for it in iterables))
  File "/Users/thomasli/pandas/pandas/core/arrays/categorical.py", line 2735, in factorize_from_iterable
    cat = Categorical(values, ordered=False)
  File "/Users/thomasli/pandas/pandas/core/arrays/categorical.py", line 443, in __init__
    codes, categories = factorize(values, sort=True)
  File "/Users/thomasli/pandas/pandas/core/algorithms.py", line 743, in factorize
    return values.factorize(sort=sort, use_na_sentinel=use_na_sentinel)
  File "/Users/thomasli/pandas/pandas/core/base.py", line 1051, in factorize
    codes, uniques = algorithms.factorize(
  File "/Users/thomasli/pandas/pandas/core/algorithms.py", line 759, in factorize
    codes, uniques = values.factorize(use_na_sentinel=use_na_sentinel)
  File "/Users/thomasli/pandas/pandas/core/arrays/arrow/array.py", line 891, in factorize
    encoded = data.dictionary_encode(null_encoding=null_encoding)
  File "pyarrow/table.pxi", line 586, in pyarrow.lib.ChunkedArray.dictionary_encode
  File "pyarrow/_compute.pyx", line 560, in pyarrow._compute.call_function
  File "pyarrow/_compute.pyx", line 355, in pyarrow._compute.Function.call
  File "pyarrow/error.pxi", line 144, in pyarrow.lib.pyarrow_internal_check_status
  File "pyarrow/error.pxi", line 121, in pyarrow.lib.check_status
pyarrow.lib.ArrowNotImplementedError: Function 'dictionary_encode' has no kernel matching input types (dictionary<values=string, indices=int32, ordered=0>)

[aswinj18]

take

[chrisburesi]

For information, same type of issue , with example: On 16Gb PC: from a read_parquet with py_arrow engine, df pivots on 4 categorical types with one int as sum -> memory allocation overload.

_**<class 'pandas.core.frame.DataFrame'> Index: 3711683 entries, 14 to 9070 Data columns (total 5 columns): Column Dtype

0 ntusername category 1 user category 2 techno category 3 server category 4 calls_count int64
memory usage: 276.1+ MB

test= pd.pivot_table(df, values=['calls_count'], index=[ 'textdata_tables','user','ntusername','date'], aggfunc=np.sum).resetindex()**

=> MemoryError: Unable to allocate 10.8 GiB for an array with shape (8510, 682784) and data type int16

Saving in csv , reloading (pass categories in object) and executing the pivot returns valid pivot in a second.

[lithomas1]

Fixed as of #53232 I think.

[randolf-scholz]

With current libraries, (arrow=16.1, pandas=2.2.2, numpy=2.0.0) the example in my OP fails with

AttributeError: 'Series' object has no attribute '_pa_array'

[randolf-scholz]

@lithomas1 The bug seems to be this line:

https://github.com/pandas-dev/pandas/blob/39a3bf355574df76df44cededffac96fddb48a04/pandas/core/arrays/categorical.py#L452

The issue is that values can be one of (ABCIndex, ABCSeries, ExtensionArray), but not all of these have the _pa_array attribute. I wrote a naive patch here: #59099