获取一个空的Int列:Pandas和Pandera

Question

我使用pandas和pandra进行模式验证，但由于数据中有一个空整数列，我遇到了一个问题。

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

错误：

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:

我知道pandas‘gotcha’在int列中有null，我已经尝试过使用Check的lambda函数的每一种排列来解决这个问题。任何帮助都将不胜感激，谢谢。

Answer 1

我刚刚遇到了这个问题，它可以通过Pandas可为null的整数("Int64"/https://pandas.pydata.org/docs/user_guide/integer_na.html)和胁迫=真。

例如：

pa.Column("Int64",coerce=True)