Inconsistent behavior for df.replace() with NaN, NaT and None

[K3UL]

Code Sample, a copy-pastable example if possible

import datetime as dt
import pandas as pd
import numpy as np
data = [
    ['one', 1.0, pd.NaT],
    ['two', np.NaN, dt.datetime(2019, 2, 2)],
    [None, 3.0, dt.datetime(2019, 3, 3)]
    ]
df = pd.DataFrame(data, columns=["Name", "Value", "Event_date"])

>>> df
   Name  Value Event_date
0   one    1.0        NaT
1   two    NaN 2019-02-02
2  None    3.0 2019-03-03

>>> df.replace({pd.NaT: None})
   Name Value           Event_date
0   one     1                 None
1   two  None  2019-02-02 00:00:00
2  None     3  2019-03-03 00:00:00
>>> df.replace({np.NaN: None})
   Name Value           Event_date
0   one     1                 None
1   two  None  2019-02-02 00:00:00
2  None     3  2019-03-03 00:00:00

>>> df.replace({np.NaN: None}).replace({np.NaN: None})
   Name  Value           Event_date
0   one    1.0                 None
1   two    NaN  2019-02-02 00:00:00
2  None    3.0  2019-03-03 00:00:00

>>> df.replace({np.NaN: None}).replace({np.NaN: None}).replace({np.NaN: None})
   Name Value           Event_date
0   one     1                 None
1   two  None  2019-02-02 00:00:00
2  None     3  2019-03-03 00:00:00

>>> df.replace({pd.NaT: None, np.NaN: None})
   Name  Value           Event_date
0   one    1.0                 None
1   two    NaN  2019-02-02 00:00:00
2  None    3.0  2019-03-03 00:00:00

Problem description

This might seem somewhat related to #17494. Here I am using a dict to replace (which is the recommended way to do it in the related issue) but I suspect the function calls itself and passes None (replacement value) to the value arg, hitting the default arg value.

When calling df.replace() to replace NaN or NaT with None, I found several behaviours which don't seem right to me :

• Replacing NaT with None (only) also replaces NaN with None.
• Replacing NaN with None also replaces NaT with None
• Replacing NaT and NaN with None, replaces NaT but leaves the NaN
• Linked to previous, calling several times a replacement of NaN or NaT with None, switched between NaN and None for the float columns. An even number of calls will leave NaN, an odd number of calls will leave None.

This is a problem because I'm unable to replace only NaT or only NaN. This is also a problem because if I want to replace both, I intuitively call replace with the dict {pd.NaT: None, np.NaN: None} but end up with NaNs.

I suspect two problems here : NaN, NaT and None being all considered as equals, and replace() calling itself with None as value argument.

Expected Output

>>> df.replace({pd.NaT: None, np.NaN: None})
   Name  Value           Event_date
0   one    1.0                 None
1   two    None  2019-02-02 00:00:00
2  None    3.0  2019-03-03 00:00:00

>>> df.replace({pd.NaT: None})
   Name Value           Event_date
0   one     1                 None
1   two   NaN  2019-02-02 00:00:00
2  None     3  2019-03-03 00:00:00

>>> df.replace({np.NaN: None})
   Name Value           Event_date
0   one     1                  NaT
1   two  None  2019-02-02 00:00:00
2  None     3  2019-03-03 00:00:00

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit: None python: 3.7.4.final.0 python-bits: 64 OS: Darwin OS-release: 18.7.0 machine: x86_64 processor: i386 byteorder: little LC_ALL: None LANG: None LOCALE: en_US.UTF-8

pandas: 0.24.2
pytest: None
pip: 19.2.2
setuptools: 41.0.1
Cython: None
numpy: 1.16.4
scipy: None
pyarrow: None
xarray: None
IPython: None
sphinx: None
patsy: None
dateutil: 2.7.5
pytz: 2018.7
blosc: None
bottleneck: None
tables: 3.5.1
numexpr: 2.7.0
feather: None
matplotlib: None
openpyxl: 2.6.2
xlrd: 1.2.0
xlwt: 1.3.0
xlsxwriter: 1.1.8
lxml.etree: 4.2.5
bs4: None
html5lib: 1.0.1
sqlalchemy: 1.2.14
pymysql: None
psycopg2: 2.8.3 (dt dec pq3 ext lo64)
jinja2: 2.10.1
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None

[s-scherrer]

Most of this is caused by BlockManager.replace_list in pandas/core/internals/managers.py:

First of all, this function does not differentiate between NaN and NaT, which explains your first and second result.

The other issue is the switching between NaN and None in the "Value" column when calling replace multiple times. For this we have to consider in more detail how pandas actually replaces values: pandas first splits the DataFrame into multiple blocks, and then replaces the values in each block. The block type depends on the data type. In the above example, the DataFrame is split into 3 blocks: "Name" becomes an ObjectBlock, "Value" a FloatBlock, and "Event_date" a DatetimeBlock. Replacing values is then done by calling the _replace_coerce method of the block.
This method does the same for all block types except ObjectBlock: it replaces what is has to replace, and coerces the block to have a data type which fits the replacement value. However, in the case of an ObjectBlock, pandas will additionally try to convert the Block to a more "convenient" data type. During this conversion, None is handled similarly to NaN, and blocks that consist only of floats and Nones will be converted to floats.

This means that on first replacement, as in your example 1 and 2, the "Value" column will contain None, as it started out as FloatBlock. However, after that first replacement, the "Value" column will be an ObjectBlock, which means that pandas will convert the block back to a FloatBlock.

Your last example is basically the same, as the replacements are performed sequentially.

I'm unsure what the best way to fix this would be, but maybe this helps someone who wants to try.

[samize]

I've been having similar issues with counter-intuitive handling of NaT and NaN values when dealing with the DataFrame.replace() method. Has this issue been worked on at all or is it still open?

[KonstantinPR]

Thanks a lot, bro. It's so valuable information
!!!!!!!!!!

[vietpm]

Any fix on this issue ? I still encounter this error on pandas 1.1.3

[matthew1davis]

From my limited testing it seems this specific issue was fixed in pandas 1.2.5 but has resurfaced again in 1.3.3

[jschelbert]

Can conform, this is again broken in 1.3.3. See small example below:

import pandas as pd
import numpy as np
import datetime as dt

df = pd.DataFrame({'X': [1, 2, 3, 4, 5],
                   'A': ["2021-01-01", "2021-01-02", "2021-01-03", "", "2021-01-05"],
                   'B': [np.NaN, 6, 7, 8, np.NaN],
                   'C': ['a', 'b', 'c', 'd', 'e']})
df = df.astype({'A': 'datetime64[ns]'})

print(df)
   X          A    B  C
0  1 2021-01-01  NaN  a
1  2 2021-01-02  6.0  b
2  3 2021-01-03  7.0  c
3  4        NaT  8.0  d
4  5 2021-01-05  NaN  e

# replace NaT and NaN with None for usage with SQLAlchemy
df.replace({pd.NaT: None}, inplace=True)
print(df)
   X                    A    B  C
0  1  2021-01-01 00:00:00  NaN  a
1  2  2021-01-02 00:00:00  6.0  b
2  3  2021-01-03 00:00:00  7.0  c
3  4                 None  8.0  d
4  5  2021-01-05 00:00:00  NaN  e

df.replace({np.NaN: None}, inplace=True)
print(df)
   X          A     B  C
0  1 2021-01-01  None  a
1  2 2021-01-02   6.0  b
2  3 2021-01-03   7.0  c
3  4        NaT   8.0  d
4  5 2021-01-05  None  e

# maybe this helps?
df.replace({'A': pd.NaT}, None, inplace=True)
print(df)
   X          A    B  C
0  1 2021-01-01  NaN  a
1  2 2021-01-02  6.0  b
2  3 2021-01-03  7.0  c
3  4        NaT  8.0  d
4  5 2021-01-05  NaN  e

Replace pd.NaT seems to work but replacing np.NaN reverts it back. Also, if I try to explicitly state the column in which I want to replace pd.NaT it does not work and instead changes None to NaN in another column 🤯.

Show output from `pd.show_versions()`:

INSTALLED VERSIONS
------------------
commit           : 73c68257545b5f8530b7044f56647bd2db92e2ba
python           : 3.7.9.final.0
python-bits      : 64
OS               : Windows
OS-release       : 10
Version          : 10.0.18362
machine          : AMD64
processor        : Intel64 Family 6 Model 78 Stepping 3, GenuineIntel
byteorder        : little
LC_ALL           : None
LANG             : None
LOCALE           : None.None
pandas           : 1.3.3
numpy            : 1.21.2
pytz             : 2021.1
dateutil         : 2.8.2
pip              : 21.2.4
setuptools       : 56.0.0
Cython           : None
pytest           : 6.2.5
hypothesis       : None
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : 3.0.1
lxml.etree       : 4.6.3
html5lib         : None
pymysql          : None
psycopg2         : 2.9.1 (dt dec pq3 ext lo64)
jinja2           : 3.0.1
IPython          : None
pandas_datareader: None
bs4              : 4.9.3
bottleneck       : None
fsspec           : None
fastparquet      : None
gcsfs            : None
matplotlib       : 3.4.3
numexpr          : None
odfpy            : None
openpyxl         : 3.0.9
pandas_gbq       : None
pyarrow          : None
pyxlsb           : None
s3fs             : None
scipy            : None
sqlalchemy       : 1.4.25
tables           : None
tabulate         : None
xarray           : None
xlrd             : 2.0.1
xlwt             : None
numba            : None

[jreback]

this issue was not closed as it needs thorough tests and likely a patch - anyone in the community can push a PR and the core team can review

[stevenleeg]

I've also been banging my face against a keyboard because of this bug. For those of you looking for a quick and easy workaround to making sure NaNs and NaTs are out of your dataframes, I've found replacing NaT first then NaN the way to go:

# Note that the order here matters!
df = df.replace({pd.NaT: None}).replace({np.NaN: None})

As a good practice, I've also been dropping a comment above this line linking to this issue to make sure other engineers don't get tripped up on this down the road (at least until this issue is resolved).

[stgreensl]

Thanks stevenleeg, you inspired my own workaround. For some reason, your approach worked for me for NaN values but not NaT, which is what I was more concerned with. So instead, I replaced all NaN values with 0 and then replaced with None.

df1 = df1.replace(np.NaN, 0).replace(0, None)

[albertogiacomini]

Hi, based on @stevenleeg solution this worked for me in order to remove NaT values. Thank you!!

df = df.replace({pd.NaT: None}).replace({'NaT': None}).replace({np.NaN: None})