[Python] Ingesting An Null Int Column: Pandas and Pandera

I am using pandas with pandera for schema validation, but I've run into a problem since there's a null integer column in the data.

from prefect import task, Flow #type:ignore
from pandera import Check, Column, DataFrameSchema
import prefect
import pandas as pd
import pandera as pa
import numpy as np


def pschema(d):
logger = prefect.utilities.logging.get_logger() # type: ignore
engine = connect_db(prefect.config.kv.p.staging_db_constring, logger) #type:ignore


table_name = "MyTable"
org = "myOrg"

k = {}
df = pd.read_sql(
f"SELECT NameStrNotQuoted, FieldTypeName, SizeStr, Precision, Scale FROM dbo.vw_cx_meta WHERE [Table] = '{table_name}' and Organization='{org}' AND ETL_Active = 1",
engine,
)
for row in df.itertuples(index=False):
if row.FieldTypeName == "int":
k.update({row.NameStrNotQuoted:Column(int,Check(lambda x: pd.Series([x.fillna(0)],dtype='Int64')),coerce=True, nullable=True)})
elif row.FieldTypeName == 'bit':
k.update({row.NameStrNotQuoted:Column(pa.Bool, coerce=True)})
sch = DataFrameSchema(k)

sch.validate(d)
return k


The errors:

ValueError: cannot convert float NaN to integer
.
.
.
File "/usr/local/lib/python3.8/site-packages/pandera/schemas.py", line 1789, in coerce_dtype
raise errors.SchemaError(
pandera.errors.SchemaError: Error while coercing 'CopySourceID' to type int64: Could not coerce <class 'pandas.core.series.Series'> data_container into type int64:


I am aware of pandas 'gotcha' with nulls in int columns and I've tried every permutation of using Check's lambda function to solve this. Any help would be appreciated, thanks.

Continue reading...