Column dtype with pandas read_json

user_5296106 0
Viewed 2464

I have a json file that looks like this:

[{"A": 0, "B": "x"}, {"A": 1, "B": "y", "C": 0}, {"A": 2, "B": "z", "C": 1}]

Since the "C" column contains a NaN value (first row), pandas automatically infers that its dtype is "float64":

>>> pd.read_json(path).C.dtype
dtype('float64')

However, I want the dtype of the "C" column to be "Int32". pd.read_json(path, dtype={"C": "Int32"}) does not work:

>>> pd.read_json(path, dtype={"C": "Int32"}).C.dtype
dtype('float64')

Instead, pd.read_json(path).astype({"C": "Int32"}) does work:

>>> pd.read_json(path).astype({"C": "Int32"}).C.dtype
Int32Dtype()

Why does this happen? How can I set the correct dtype by only using the pd.read_json function?

pandas python
1 Answers

The reason is in this code section:

        dtype = (
            self.dtype.get(name) if isinstance(self.dtype, dict) else self.dtype
        )
        if dtype is not None:
            try:
                dtype = np.dtype(dtype)
                return data.astype(dtype), True
            except (TypeError, ValueError):
                return data, False

It converts 'Int32' to numpy.int32 which then causes a value error (cannot convert non-finite values (NA or inf) to integer) when trying to convert the whole column (array) to this type. Due to this the original (unconverted) data are returned in the exception block.
I guess this is some kind of bug in pandas, at least the behavior is not correctly documented.

astype, on the other hand, works differently: it applies 'astype' elementwise on the series) and therefor can create a mixed-type column.

Interestingly enough, when specifying the extension type pd.Int32Dtype() directly (and not its string alias 'Int32'), you get at first glance the desired result, but if you then look at the types they are still floats:

df = pd.read_json(json, dtype={"C": pd.Int32Dtype})
print(df)
#   A  B    C
#0  0  x  NaN
#1  1  y    0
#2  2  z    1
print(df.C.map(type))
#0    <class 'float'>
#1    <class 'float'>
#2    <class 'float'>
#Name: C, dtype: object

For comparison:

print(df.C.astype('Int32').map(type))
#0    <class 'pandas._libs.missing.NAType'>
#1                            <class 'int'>
#2                            <class 'int'>
#Name: C, dtype: object
user_39443220
Related