`apply_value_formats=True` produces mixed-type object columns that break `to_parquet()`

[EwoutH]

When reading an SPSS .sav file with apply_value_formats=True (the default when called via pandas.read_spss()), numeric variables that have value labels applied end up as object-dtype columns containing a mix of numeric-looking strings and actual label strings.

For example, an SPSS variable for household size might have values 1–9 stored as doubles, with value label "10 personen of meer" for the code 10. After apply_value_formats, the resulting pandas column has dtype object with values like 1.0, 2.0, ..., 9.0, "10 personen of meer".

This is problematic because pyarrow's type inference (used by df.to_parquet(), df.to_feather(), polars, HuggingFace datasets, etc.) sees the first values, infers double, and then crashes on the string values:

ArrowInvalid: ("Could not convert '10 personen of meer' with type str:
tried to convert to double", 'Conversion failed for column HHLft1 with type object')

This affects both category-dtype columns (from formats_as_category=True) and object-dtype columns where not all values in the variable have labels (so formats_as_category doesn't apply and the column stays object with mixed content).

Reproducer

import pyreadstat
import pandas as pd

# Any SPSS file where a numeric variable has partial value labels
# (e.g. codes 1-9 have no labels, code 10 has label "10 or more")
df, meta = pyreadstat.read_sav("example.sav", apply_value_formats=True)

# Column with partial labels is now object-dtype with mixed content
print(df["HHLft1"].dtype)       # object
print(df["HHLft1"].unique())    # [1.0, 2.0, ..., '10 personen of meer']

# This crashes:
df.to_parquet("test.parquet")
# ArrowInvalid: tried to convert to double

Suggested fix

When apply_value_formats=True replaces some (or all) numeric values with their string labels, the entire column should be cast to str dtype. Once you've substituted labels for codes, the column is semantically a string column — the remaining numeric values are just codes that happened to not have a label, and keeping them as floats in an object column creates a type inconsistency.

This could be done in pyreadstat's post-processing step that applies value formats: after substitution, if any value in the column is a string, cast the entire column to str.

Workaround

for col in df.columns:
    if df[col].dtype == "object" or df[col].dtype.name == "category":
        df[col] = df[col].astype(str)

Related issues

• [Python] from_pandas fails on mixed types apache/arrow#3280 and [Python] When converting from Pandas, coerce mixed columns to String apache/arrow#20719: pyarrow from_pandas fails on mixed-type object columns (open since 2018, unresolved)
• [Python] Pandas to Pyarrow Data Type Conversion Error for mixed-type columns apache/arrow#15133: same issue via to_feather()
• BUG: to_parquet failing for integer-like string values in categorical column pandas-dev/pandas#46863: related categorical + parquet failures

[ofajardo]

Thanks for the report! very interesting, I understand the issue.

First, I am in general against changing defaults in pyreadstat (it would be a breaking change). Second, I personally think that this should not happen automatically unless the user explicitly indicates it, because it can mask strange things in the data silently (for example the user thinking all the values should be labeled). It is better that this raises an issue and let the user fix it as she thinks it better fits her use case.

I also see that it is a wider issue (the second and third issues do not seem to relate to pyreadstat), so I would like to wait to see what they do about it. As you suggested the issue is not difficult to work around. They could potentially do the work around in pandas itself.

An option would be to introduce an argument that would cast those columns automatically to strings, but the user would need to pass it explicitly, I understand this would not fix the issue in pandas, since they are using the defaults (unless they would like to change their defaults ...). I would like to hear pandas dev opinion on that as well.