BUG: Cannot replace NaNs with None

[RenaLu]

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

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

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

---

Previously, we were able to replace NaN values with None for easier insertion into databases like mysql, as listed here:
https://stackoverflow.com/questions/14162723/replacing-pandas-or-numpy-nan-with-a-none-to-use-with-mysqldb

python

df = pd.DataFrame([[1, None, np.NaN], [2, np.NaN, np.NaN]])
df = df.where(pd.notnull(df), None)
df

Output of df:

0	1	2
1	NaN	NaN
2	NaN	NaN

Problem description

Previously, all np.NaN were replaced with None. Now, they still remain as np.NaN, and breaks database insertions

Expected Output

0	1	2
1	None	None
2	None	None

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit : c7f7443 python : 3.8.8.final.0 python-bits : 64 OS : Linux OS-release : 5.4.0-1049-aws Version : #51~18.04.1-Ubuntu SMP Fri May 14 18:38:46 UTC 2021 machine : x86_64 processor : x86_64 byteorder : little LC_ALL : None LANG : C.UTF-8 LOCALE : en_US.UTF-8

pandas : 1.3.1
numpy : 1.19.2
pytz : 2020.1
dateutil : 2.8.1
pip : 20.2.4
setuptools : 50.3.1
Cython : 0.29.21
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : 2.8.5 (dt dec pq3 ext lo64)
jinja2 : None
IPython : 7.19.0
pandas_datareader: None
bs4 : None
bottleneck : None
fsspec : None
fastparquet : None
gcsfs : None
matplotlib : 3.2.2
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : 1.0.1
pyxlsb : None
s3fs : None
scipy : 1.5.2
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
numba : None

[dylancaponi]

Confirmed we see this same issue on 1.3.0 and 1.3.1 but not 1.2.5.

[mzeitlin11]

xref #42423

[dylancaponi]

I think including this info in the migration guide would be a big help.

[mzeitlin11]

What do you mean by migration guide @dylancaponi? There was a note in the 1.3 whatsnew (but it's small and probably wouldn't be noticed unless read carefully). I'm not sure how amending an existing whatsnew works, but given the large number of users who seem to rely on this functionality, devoting a section to this showing new vs old behavior sounds helpful.

[dylancaponi]

Yes, maybe a "breaking changes and suggested workarounds" section.

@mzeitlin11 I just skimmed the whatsnew again and don't see where this change is mentioned. Can you point me to it?

[RenaLu]

What's the best way around this for now? I tried to revert to 1.2.5 but I got an import error

Python 3.8.5 (default, Jan 27 2021, 15:41:15) 
[GCC 9.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pandas
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.8/dist-packages/pandas/__init__.py", line 144, in <module>
    from pandas.io.api import (
  File "/usr/local/lib/python3.8/dist-packages/pandas/io/api.py", line 8, in <module>
    from pandas.io.excel import ExcelFile, ExcelWriter, read_excel
  File "/usr/local/lib/python3.8/dist-packages/pandas/io/excel/__init__.py", line 1, in <module>
    from pandas.io.excel._base import ExcelFile, ExcelWriter, read_excel
  File "/usr/local/lib/python3.8/dist-packages/pandas/io/excel/_base.py", line 33, in <module>
    from pandas.io.parsers import TextParser
  File "/usr/local/lib/python3.8/dist-packages/pandas/io/parsers/__init__.py", line 1, in <module>
    from pandas.io.parsers.readers import (
  File "/usr/local/lib/python3.8/dist-packages/pandas/io/parsers/readers.py", line 17, in <module>
    from pandas._typing import (
ImportError: cannot import name 'DtypeArg' from 'pandas._typing' (/usr/local/lib/python3.8/dist-packages/pandas/_typing.py)

[mzeitlin11]

@mzeitlin11 I just skimmed the whatsnew again and don't see where this change is mentioned. Can you point me to it?

It's in the other section :) https://pandas.pydata.org/pandas-docs/dev/whatsnew/v1.3.0.html#other

[mzeitlin11]

What's the best way around this for now? I tried to revert to 1.2.5 but I got an import error

The other issue has a good workaround: #42423 (comment)
Not sure about your 1.2.5 installation issue, I'd just try installing a fresh one (or fresh env if using something like conda)

[jreback]

closing as duplicate of #42423