BUG: `pd.NA` when it replaces a value in a column, changes its type to "object"

[Demetrio92]

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

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

• I have confirmed this bug exists on the master branch of pandas.

Reproducible Example

import pandas as pd

mock_data = pd.DataFrame({
    'date': ['0', '1', '2', '3'],
    'value': [1, 2, 1, 1.5]
})
assert pd.api.types.is_numeric_dtype(mock_data.value)  # passes

mock_data_pd_na.loc[2, 'value'] = pd.NA
assert pd.api.types.is_numeric_dtype(mock_data.value)  # breaks

Issue Description

Changing one value in a column with an NA/NULL should not change column's data type. That seems reasonable. Also, it seems the functionality is already there. I am not entirely sure if this is a bug or a feature.

Essentially it is due to the default assignment of a column type, which is float64 not pd.Float64Dtype(). I am not sure if the migration is on the roadmap, but this bug could be an argument in its favor.

Expected Behavior

mock_data = pd.DataFrame({
    'date': ['0', '1', '2', '3'],
    'value': [1, 2, 1, 1.5]
})
mock_data.value = mock_data.value.astype(pd.Float64Dtype())  # this should happen by default
mock_data.value.dtype  # Float64Dtype()

mock_data.loc[2, 'value'] = pd.NA
assert pd.api.types.is_numeric_dtype(mock_data.value)  # still passes
mock_data.value.dtype  # Float64Dtype()

Installed Versions

pandas           : 1.3.4

INSTALLED VERSIONS
------------------
commit           : 945c9ed766a61c7d2c0a7cbb251b6edebf9cb7d5
python           : 3.9.7.final.0
python-bits      : 64
OS               : Linux
OS-release       : 5.11.0-34-generic
Version          : #36~20.04.1-Ubuntu SMP Fri Aug 27 08:06:32 UTC 2021
machine          : x86_64
processor        : x86_64
byteorder        : little
LC_ALL           : None
LANG             : en_US.UTF-8
LOCALE           : en_US.UTF-8
pandas           : 1.3.4
numpy            : 1.21.2
pytz             : 2021.3
dateutil         : 2.8.2
pip              : 21.3.1
setuptools       : 58.2.0
Cython           : None
pytest           : None
hypothesis       : None
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : None
lxml.etree       : None
html5lib         : None
pymysql          : None
psycopg2         : None
jinja2           : 3.0.2
IPython          : 7.28.0
pandas_datareader: None
bs4              : None
bottleneck       : None
fsspec           : None
fastparquet      : None
gcsfs            : None
matplotlib       : 3.4.3
numexpr          : None
odfpy            : None
openpyxl         : None
pandas_gbq       : None
pyarrow          : 5.0.0
pyxlsb           : None
s3fs             : None
scipy            : 1.7.1
sqlalchemy       : 1.4.25
tables           : None
tabulate         : 0.8.9
xarray           : None
xlrd             : None
xlwt             : None
numba            : None

[phofl]

I think this is expected, pd.NA ist not a float or an int. If you define your column as Float64 this works as expected.

mock_data = pd.DataFrame({
    'date': ['0', '1', '2', '3'],
    'value': [1, 2, 1, 1.5]
})
mock_data["value"] = mock_data["value"].astype("Float64")
assert pd.api.types.is_numeric_dtype(mock_data.value)  # passes

mock_data.loc[2, 'value'] = pd.NA
assert pd.api.types.is_numeric_dtype(mock_data.value)

[Demetrio92]

@phofl yes, I see that. I used pd.Float64Dtype() instead of 'Float64' as I was (surprisingly) getting different behavior between them. But I see now that it, as expected, does not matter here.

Still,

Presence of null-values should not change the type of a column. This is an overarching principle, and if pandas breaks it, it will be precedential. No SQL-compatible database has such behavior, neither do R data.frames.

So, I see two solutions here:

1. as pandas now introduces null logic, it would make sense to initialize all columns with nullable types.

2. when this mock_data.loc[2, 'value'] = pd.NA happens cast from a non-nullable to nullable type. E.g. int64 -> Int64 and float64 -> Float64.

[phofl]

1 is not feasible right now and it was not decided if this would be the case in the future.

2: We have open issues discussing the behavior of setting incompatible values into a DataFrame column. One option would be raising here

[Demetrio92]

@phofl

1. why not? When the DataFrame is instantiated in my example above dtypes are being inferred. Same goes for reading CVS. Yes, you can specify them manually, but the "infer" behavior is the default. So, change float64 to Float64 and done. Obviously, this is a major architectural move. I am fine if this issue can be closed with "expected behavior".

2. I still don't think pd.NA should be called "incompatible". It is incompatible with numpy types, yes. But there is a difference between replacing a value in numeric column with a string "hello" and setting a value to "missing".

Nonetheless, can you reference the issue? I feel like maybe this one is a duplicate. I did a bit of searching before reporting, but couldn't find if this was discussed already.

[phofl]

1 was discussed on the mailing list I think. No conclusion reached yet.

Would have to look up 2 myself, this is somewhere under Indexing and is a bit older

[phofl]

#39584

[jbrockmendel]

as pandas now introduces null logic, it would make sense to initialize all columns with nullable types.

There has been discussion about this. At the moment these dtypes are a) much buggier/unstable than numpy dtypes and b) much slower when dealing with multiple/many columns. Both of these are improving, but these are going to stay opt-in at least until they are reasonably stable.

For FloatingDtypes in particular #32265 is a tough nut to crack.