SNOW-902906: Nan values get converted to null when using write_pandas from snowflake-connector-python or snowflake-snowpark-python

[BrunoGarciaTejada]

Python version

3.8

Operating system and processor architecture

Windows-10-10.0.22000-SP0

Installed packages

numpy==1.24.4
pandas==2.0.3
snowflake-connector-python==3.1.0
snowflake-snowpark-python==1.7.0

What did you do?

import numpy as np

import pandas as pd
import snowflake.connector
from snowflake.connector.pandas_tools import write_pandas

connector = snowflake.connector.connect(
    **{
        'user': '',
        'account': '',
        'database': '',
        'schema': '',
        'warehouse': '',
        'role': '',
        'authenticator': ''
    }
)

df = pd.DataFrame({'a': [1,2,3], 'b': [4,5,6], 'c': [np.nan,8.0,np.inf]})
df = df.replace(2, None)

write_pandas(connector, df, "output_table", table_type='', quote_identifiers = False, overwrite = True, auto_create_table=True)

What did you expect to see?

The same problem happens when using snowflake.snowpark.Session.write_pandas

• Expected Results:

• Actual Results:

Can you set logging to DEBUG and collect the logs?

No response

[sfc-gh-aalam]

Hey @BrunoGarciaTejada can you try creating a pandas dataframe like so and then use write_pandas to write to snowflake. When using standard pandas NaN and null values are treated the same semantically (check result of df.isna()) thus they are written to snowflake that way as well. When you construct pandas dataframe as shown below, pandas uses extendedDType which makes does not treat nan and nulls the same therefore they are written correctly in snowflake.

df = pd.DataFrame(
    {
        "a": pd.Series([1, None, 3], dtype=object),
        "b": pd.Series([4, 5, 6]),
        "c": pd.Series(['NaN',8.0,np.inf], dtype = "Float64")
    }
)

[sfc-gh-dszmolka]

marking this one as closed as it seems to be resolved; but if you feel it needs further attention, please do let us know and we can reopen. thank you !

[MaxSmith513]

OK, I'm having the opposite problem where Nulls are converted to nan in snowflake

[sdk3105]

OK, I'm having the opposite problem where Nulls are converted to nan in snowflake

Facing the same issue, did you find any resolution? @MaxSmith513 NVM found it #2147