Repository: pandas-dev/pandas (python)

Issue:
BUG: Parquet size grows exponential for categorical data 
### 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](https://pandas.pydata.org/docs/whatsnew/index.html) of pandas.

- [ ] I have confirmed this bug exists on the [main branch](https://pandas.pydata.org/docs/dev/getting_started/install.html#installing-the-development-version-of-pandas) of pandas.


### Reproducible Example

```python
import pandas as pd
import os

if __name__ == "__main__":
    for n in [10, 1e2, 1e3, 1e4, 1e5]:
        for n_col in [1, 10, 100, 1000, 10000]:
            input = pd.DataFrame([{"{i}": f"{i}_cat" for col in range(n_col)} for i in range(int(n))])
            input.iloc[0:100].to_parquet("a.parquet")
            for col in input.columns:
                input[col] = input[col].astype("category")
            input.iloc[0:100].to_parquet("b.parquet")
            a_size_mb = os.stat("a.parquet").st_size / (1024 * 1024)
            b_size_mb = os.stat("b.parquet").st_size / (1024 * 1024)
            print(f"{n} {n_col} {a_size_mb} {b_size_mb} {100*b_size_mb/a_size_mb:.2f}")
```


### Issue Description


It seems that when saving a data frame with a categorical column inside the size can grow exponentially.

This seems to happen because when we save the categorical data to parquet, we are saving the data + all the categories existing in the original data. This happens even when the categories are not present in the original data.

To reproduce the bug, it is enough to run the script above.

That produces this output:

```
10 1 0.0015506744384765625 0.001689910888671875 108.98
10 10 0.0015506744384765625 0.001689910888671875 108.98
10 100 0.0015506744384765625 0.001689910888671875 108.98
10 1000 0.0015506744384765625 0.001689910888671875 108.98
10 10000 0.0015506744384765625 0.001689910888671875 108.98
100.0 1 0.0019960403442382812 0.0021104812622070312 105.73
100.0 10 0.0019960403442382812 0.0021104812622070312 105.73
100.0 100 0.0019960403442382812 0.0021104812622070312 105.73
100.0 1000 0.0019960403442382812 0.0021104812622070312 105.73
100.0 10000 0.0019960403442382812 0.0021104812622070312 105.73
1000.0 1 0.0019960403442382812 0.0053577423095703125 268.42
1000.0 10 0.0019960403442382812 0.0053577423095703125 268.42
1000.0 100 0.0019960403442382812 0.0053577423095703125 268.42
1000.0 1000 0.0019960403442382812 0.0053577423095703125 268.42
1000.0 10000 0.0019960403442382812 0.0053577423095703125 268.42
10000.0 1 0.0019960403442382812 0.042061805725097656 2107.26
10000.0 10 0.0019960403442382812 0.042061805725097656 2107.26
10000.0 100 0.0019960403442382812 0.042061805725097656 2107.26
10000.0 1000 0.0019960403442382812 0.042061805725097656 2107.26
10000.0 10000 0.0019960403442382812 0.042061805725097656 2107.26
100000.0 1 0.0019960403442382812 0.43596935272216797 21841.71
100000.0 10 0.0019960403442382812 0.43596935272216797 21841.71
100000.0 100 0.0019960403442382812 0.43596935272216797 21841.71
100000.0 1000 0.0019960403442382812 0.43596935272216797 21841.71
100000.0 10000 0.0019960403442382812 0.43596935272216797 21841.71
```



### Expected Behavior

In my opinion either:

1.   The two file should have (almost) the same size
2.   There should be warning telling the user that such difference in size is possible

### Installed Versions

<details>
INSTALLED VERSIONS
------------------
commit              : ba1cccd19da778f0c3a7d6a885685da16a072870
python              : 3.10.12.final.0
python-bits         : 64
OS                  : Linux
OS-release          : 5.15.120+
Version             : #1 SMP Wed Aug 30 11:19:59 UTC 2023
machine             : x86_64
processor           : x86_64
byteorder           : little
LC_ALL              : en_US.UTF-8
LANG                : en_US.UTF-8
LOCALE              : en_US.UTF-8

pandas              : 2.1.0
numpy               : 1.23.5
pytz                : 2023.3.post1
dateutil            : 2.8.2
setuptools          : 67.7.2
pip                 : 23.1.2
Cython              : 3.0.4
pytest              : 7.4.3
hypothesis          : None
sphinx              : 5.0.2
blosc               : None
feather             : None
xlsxwriter          : None
lxml.etree          : 4.9.3
html5lib            : 1.1
pymysql             : None
psycopg2            : 2.9.9
jinja2              : 3.1.2
IPython             : 7.34.0
pandas_datareader   : 0.10.0
bs4                 : 4.11.2
bottleneck          : None
dataframe-api-compat: None
fastparquet         : None
fsspec              : 2023.6.0
gcsfs               : 2023.6.0
matplotlib          : 3.7.1
numba               : 0.56.4
numexpr             : 2.8.7
odfpy               : None
openpyxl            : 3.1.2
pandas_gbq          : 0.17.9
pyarrow             : 9.0.0
pyreadstat          : None
pyxlsb              : None
s3fs                : None
scipy               : 1.11.3
sqlalchemy          : 2.0.22
tables              : 3.8.0
tabulate            : 0.9.0
xarray              : 2023.7.0
xlrd                : 2.0.1
zstandard           : None
tzdata              : 2023.3
qtpy                : None
pyqt5               : None
</details>


Pull request:
DOC: Add documentation on parquet categorical data handling
- [x] closes #55776 
- [x] All [code checks passed]


Patch:
diff --git a/ci/code_checks.sh b/ci/code_checks.sh
index fbd71af9b6..211af5999c 100755
--- a/ci/code_checks.sh
+++ b/ci/code_checks.sh
@@ -83,15 +83,11 @@ if [[ -z "$CHECK" || "$CHECK" == "docstrings" ]]; then
         -i "pandas.DataFrame.__iter__ SA01" \
         -i "pandas.DataFrame.at_time PR01" \
         -i "pandas.DataFrame.columns SA01" \
-        -i "pandas.DataFrame.copy SA01" \
         -i "pandas.DataFrame.droplevel SA01" \
-        -i "pandas.DataFrame.first_valid_index SA01" \
         -i "pandas.DataFrame.hist RT03" \
         -i "pandas.DataFrame.infer_objects RT03" \
-        -i "pandas.DataFrame.keys SA01" \
         -i "pandas.DataFrame.kurt RT03,SA01" \
         -i "pandas.DataFrame.kurtosis RT03,SA01" \
-        -i "pandas.DataFrame.last_valid_index SA01" \
         -i "pandas.DataFrame.max RT03" \
         -i "pandas.DataFrame.mean RT03,SA01" \
         -i "pandas.DataFrame.median RT03,SA01" \
@@ -310,7 +306,6 @@ if [[ -z "$CHECK" || "$CHECK" == "docstrings" ]]; then
         -i "pandas.Series.cat.rename_categories PR01,PR02" \
         -i "pandas.Series.cat.reorder_categories PR01,PR02" \
         -i "pandas.Series.cat.set_categories PR01,PR02" \
-        -i "pandas.Series.copy SA01" \
         -i "pandas.Series.div PR07" \
         -i "pandas.Series.droplevel SA01" \
         -i "pandas.Series.dt.as_unit PR01,PR02" \
@@ -354,7 +349,6 @@ if [[ -z "$CHECK" || "$CHECK" == "docstrings" ]]; then
         -i "pandas.Series.dtype SA01" \
         -i "pandas.Series.empty GL08" \
         -i "pandas.Series.eq PR07,SA01" \
-        -i "pandas.Series.first_valid_index SA01" \
         -i "pandas.Series.floordiv PR07" \
         -i "pandas.Series.ge PR07,SA01" \
         -i "pandas.Series.gt PR07,SA01" \
@@ -364,10 +358,8 @@ if [[ -z "$CHECK" || "$CHECK" == "docstrings" ]]; then
         -i "pandas.Series.is_monotonic_increasing SA01" \
         -i "pandas.Series.is_unique SA01" \
         -i "pandas.Series.item SA01" \
-        -i "pandas.Series.keys SA01" \
         -i "pandas.Series.kurt RT03,SA01" \
         -i "pandas.Series.kurtosis RT03,SA01" \
-        -i "pandas.Series.last_valid_index SA01" \
         -i "pandas.Series.le PR07,SA01" \
         -i "pandas.Series.list.__getitem__ SA01" \
         -i "pandas.Series.list.flatten SA01" \
diff --git a/doc/source/whatsnew/v3.0.0.rst b/doc/source/whatsnew/v3.0.0.rst
index 177a38b526..42416f70d9 100644
--- a/doc/source/whatsnew/v3.0.0.rst
+++ b/doc/source/whatsnew/v3.0.0.rst
@@ -37,6 +37,7 @@ Other enhancements
 - Support reading value labels from Stata 108-format (Stata 6) and earlier files (:issue:`58154`)
 - Users can globally disable any ``PerformanceWarning`` by setting the option ``mode.performance_warnings`` to ``False`` (:issue:`56920`)
 - :meth:`Styler.format_index_names` can now be used to format the index and column names (:issue:`48936` and :issue:`47489`)
+- :class:`.errors.DtypeWarning` improved to include column names when mixed data types are detected (:issue:`58174`)
 - :meth:`DataFrame.cummin`, :meth:`DataFrame.cummax`, :meth:`DataFrame.cumprod` and :meth:`DataFrame.cumsum` methods now have a ``numeric_only`` parameter (:issue:`53072`)
 - :meth:`DataFrame.fillna` and :meth:`Series.fillna` can now accept ``value=None``; for non-object dtype the corresponding NA value will be used (:issue:`57723`)
 
diff --git a/pandas/core/frame.py b/pandas/core/frame.py
index b595e4d215..0185ca8241 100644
--- a/pandas/core/frame.py
+++ b/pandas/core/frame.py
@@ -2877,9 +2877,16 @@ class DataFrame(NDFrame, OpsMixin):
 
         Notes
         -----
-        This function requires either the `fastparquet
-        <https://pypi.org/project/fastparquet>`_ or `pyarrow
-        <https://arrow.apache.org/docs/python/>`_ library.
+        * This function requires either the `fastparquet
+          <https://pypi.org/project/fastparquet>`_ or `pyarrow
+          <https://arrow.apache.org/docs/python/>`_ library.
+        * When saving a DataFrame with categorical columns to parquet,
+          the file size may increase due to the inclusion of all possible
+          categories, not just those present in the data. This behavior
+          is expected and consistent with pandas' handling of categorical data.
+          To manage file size and ensure a more predictable roundtrip process,
+          consider using :meth:`Categorical.remove_unused_categories` on the
+          DataFrame before saving.
 
         Examples
         --------
diff --git a/pandas/core/generic.py b/pandas/core/generic.py
index 028492f561..6632fba43d 100644
--- a/pandas/core/generic.py
+++ b/pandas/core/generic.py
@@ -1884,6 +1884,11 @@ class NDFrame(PandasObject, indexing.IndexingMixin):
         Index
             Info axis.
 
+        See Also
+        --------
+        DataFrame.index : The index (row labels) of the DataFrame.
+        DataFrame.columns: The column labels of the DataFrame.
+
         Examples
         --------
         >>> d = pd.DataFrame(
@@ -6392,6 +6397,11 @@ class NDFrame(PandasObject, indexing.IndexingMixin):
         Series or DataFrame
             Object type matches caller.
 
+        See Also
+        --------
+        copy.copy : Return a shallow copy of an object.
+        copy.deepcopy : Return a deep copy of an object.
+
         Notes
         -----
         When ``deep=True``, data is copied but actual Python objects
@@ -11616,6 +11626,14 @@ class NDFrame(PandasObject, indexing.IndexingMixin):
         type of index
             Index of {position} non-missing value.
 
+        See Also
+        --------
+        DataFrame.last_valid_index : Return index for last non-NA value or None, if
+            no non-NA value is found.
+        Series.last_valid_index : Return index for last non-NA value or None, if no
+            non-NA value is found.
+        DataFrame.isna : Detect missing values.
+
         Examples
         --------
         For Series:
diff --git a/pandas/core/series.py b/pandas/core/series.py
index 8b36bd0f38..a72eb8e261 100644
--- a/pandas/core/series.py
+++ b/pandas/core/series.py
@@ -1746,6 +1746,10 @@ class Series(base.IndexOpsMixin, NDFrame):  # type: ignore[misc]
         Index
             Index of the Series.
 
+        See Also
+        --------
+        Series.index : The index (axis labels) of the Series.
+
         Examples
         --------
         >>> s = pd.Series([1, 2, 3], index=[0, 1, 2])
diff --git a/pandas/errors/__init__.py b/pandas/errors/__init__.py
index 402bbdb872..f01fe8ecef 100644
--- a/pandas/errors/__init__.py
+++ b/pandas/errors/__init__.py
@@ -178,7 +178,7 @@ class DtypeWarning(Warning):
     ... )  # doctest: +SKIP
     >>> df.to_csv("test.csv", index=False)  # doctest: +SKIP
     >>> df2 = pd.read_csv("test.csv")  # doctest: +SKIP
-    ... # DtypeWarning: Columns (0) have mixed types
+    ... # DtypeWarning: Columns (0: a) have mixed types
 
     Important to notice that ``df2`` will contain both `str` and `int` for the
     same input, '1'.
diff --git a/pandas/io/parsers/c_parser_wrapper.py b/pandas/io/parsers/c_parser_wrapper.py
index 67f3e5a9f4..6e5d36ad39 100644
--- a/pandas/io/parsers/c_parser_wrapper.py
+++ b/pandas/io/parsers/c_parser_wrapper.py
@@ -235,7 +235,7 @@ class CParserWrapper(ParserBase):
             if self.low_memory:
                 chunks = self._reader.read_low_memory(nrows)
                 # destructive to chunks
-                data = _concatenate_chunks(chunks)
+                data = _concatenate_chunks(chunks, self.names)  # type: ignore[has-type]
 
             else:
                 data = self._reader.read(nrows)
@@ -358,7 +358,9 @@ class CParserWrapper(ParserBase):
         return values
 
 
-def _concatenate_chunks(chunks: list[dict[int, ArrayLike]]) -> dict:
+def _concatenate_chunks(
+    chunks: list[dict[int, ArrayLike]], column_names: list[str]
+) -> dict:
     """
     Concatenate chunks of data read with low_memory=True.
 
@@ -381,10 +383,12 @@ def _concatenate_chunks(chunks: list[dict[int, ArrayLike]]) -> dict:
         else:
             result[name] = concat_compat(arrs)
             if len(non_cat_dtypes) > 1 and result[name].dtype == np.dtype(object):
-                warning_columns.append(str(name))
+                warning_columns.append(column_names[name])
 
     if warning_columns:
-        warning_names = ",".join(warning_columns)
+        warning_names = ", ".join(
+            [f"{index}: {name}" for index, name in enumerate(warning_columns)]
+        )
         warning_message = " ".join(
             [
                 f"Columns ({warning_names}) have mixed types. "


Test patch:
diff --git a/pandas/tests/io/parser/common/test_chunksize.py b/pandas/tests/io/parser/common/test_chunksize.py
index cdf4d6ae77..78a0b016bd 100644
--- a/pandas/tests/io/parser/common/test_chunksize.py
+++ b/pandas/tests/io/parser/common/test_chunksize.py
@@ -253,7 +253,7 @@ def test_warn_if_chunks_have_mismatched_type(all_parsers):
     else:
         df = parser.read_csv_check_warnings(
             warning_type,
-            r"Columns \(0\) have mixed types. "
+            r"Columns \(0: a\) have mixed types. "
             "Specify dtype option on import or set low_memory=False.",
             buf,
         )
diff --git a/pandas/tests/io/parser/test_concatenate_chunks.py b/pandas/tests/io/parser/test_concatenate_chunks.py
index 1bae2317a2..3e418faeff 100644
--- a/pandas/tests/io/parser/test_concatenate_chunks.py
+++ b/pandas/tests/io/parser/test_concatenate_chunks.py
@@ -16,7 +16,7 @@ def test_concatenate_chunks_pyarrow():
         {0: ArrowExtensionArray(pa.array([1.5, 2.5]))},
         {0: ArrowExtensionArray(pa.array([1, 2]))},
     ]
-    result = _concatenate_chunks(chunks)
+    result = _concatenate_chunks(chunks, ["column_0", "column_1"])
     expected = ArrowExtensionArray(pa.array([1.5, 2.5, 1.0, 2.0]))
     tm.assert_extension_array_equal(result[0], expected)
 
@@ -28,8 +28,10 @@ def test_concatenate_chunks_pyarrow_strings():
         {0: ArrowExtensionArray(pa.array([1.5, 2.5]))},
         {0: ArrowExtensionArray(pa.array(["a", "b"]))},
     ]
-    with tm.assert_produces_warning(DtypeWarning, match="have mixed types"):
-        result = _concatenate_chunks(chunks)
+    with tm.assert_produces_warning(
+        DtypeWarning, match="Columns \\(0: column_0\\) have mixed types"
+    ):
+        result = _concatenate_chunks(chunks, ["column_0", "column_1"])
     expected = np.concatenate(
         [np.array([1.5, 2.5], dtype=object), np.array(["a", "b"])]
     )