Confusing results when different field values are used for Integer columns #787

fnikitathom · 2022-03-09T14:55:31Z

fnikitathom
Mar 9, 2022

Hello,
I have crappy data that I am try to validate, and so far everything seems to go well except when I attempt to validate an int column that contains nulls. I've performed the following so far:

    for row in df.itertuples(index=False): # I build a dictionary with metadata from a database query then validate 
        if row.FieldTypeName == "int":
            k.update({row.NameStrNotQuoted:Column(float,coerce=True, nullable=True)})
        elif row.FieldTypeName == 'bit':
            k.update({row.NameStrNotQuoted:Column(pa.Bool, coerce=True)})
        elif row.FieldTypeName == 'varchar':
            k.update({row.NameStrNotQuoted:Column(str,Check(lambda x: x.astype('object')).str_length(max_value=row.SizeStr), coerce=True, nullable=True)})
        elif row.FieldTypeName == 'datetime':
            k.update({row.NameStrNotQuoted:Column(pa.DateTime, coerce=True)})
    sch = DataFrameSchema(k)
    print(sch)
    sch.validate(d)

Here there are 3 int columns in the data, one is all nulls, one partially and one is completely filled with data. The error I get:

pandera.errors.SchemaError: Error while executing check function: TypeError("Invalid comparison between dtype=float64 and str")

If I attempt this:

    for row in df.itertuples(index=False):
        if row.FieldTypeName == "int":
            if row.NameStrNotQuoted in ['CreatedBy','UpdatedBy']:
                k.update({row.NameStrNotQuoted:Column(float, coerce=True, nullable=True)}) # This is what is recommended 
            else:
                k.update({row.NameStrNotQuoted:Column(int,coerce=True, nullable=True)})
        elif row.FieldTypeName == 'bit':
            k.update({row.NameStrNotQuoted:Column(pa.Bool, coerce=True)})
        elif row.FieldTypeName == 'varchar':
            k.update({row.NameStrNotQuoted:Column(str,Check(lambda x: x.astype('object')).str_length(max_value=row.SizeStr), coerce=True, nullable=True)})
        elif row.FieldTypeName == 'datetime':
            k.update({row.NameStrNotQuoted:Column(pa.DateTime, coerce=True)})
    sch = DataFrameSchema(k)
    print(sch)
    sch.validate(d)

I receive the same error... I've also attempted using pandas.Int64Dtype and the results are the same. Could some help me understand the behavior I'm seeing?? Thanks

Answered by fnikitathom

Mar 10, 2022

Well after almost 2 days of going nuts I've figured out what the problem was, my inability to think outside the box, until I looked at the traceback again:

  File "/usr/local/lib/python3.8/site-packages/pandera/checks.py", line 961, in _str_length
    return series.str.len() <= max_value

Which prompted my changing the code to:

    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":
            if row.NameStrNotQuoted in ['CreatedBy','UpdatedBy']:…

View full answer

cosmicBboy · 2022-03-09T15:30:50Z

cosmicBboy
Mar 9, 2022
Maintainer

hi @fnikitathom can you provide a copy-pasteable minimally reproducible example? i.e. the DataFrameSchema definition and DataFrame containing data you want to validate.

2 replies

fnikitathom Mar 9, 2022
Author

Sorry for the late reply, I was in a meeting. Give me a little while and I will provide the information... I will have to dummy up some data since the original has PHI so please be patient

fnikitathom Mar 10, 2022
Author

Hi @cosmicBboy ,
I had tried to find a data masking tool, but finally gave up and just deleted the varchar columns in a csv and of course there were no issues with the int columns as the code is written... I tried running the code against a full dataset, including the varchar data from a csv and there was no issue, however when I attempted to run the data from the downloaded csv in my pipeline I get the same error, the code:

    for row in df.itertuples(index=False):
        if row.FieldTypeName == "int":
            if row.NameStrNotQuoted in ['CreatedBy','UpdatedBy']:
                k.update({row.NameStrNotQuoted:Column(float, coerce=True, nullable=True)})
            else:
                k.update({row.NameStrNotQuoted:Column(int,coerce=True, nullable=True)})
        elif row.FieldTypeName == 'bit':
            k.update({row.NameStrNotQuoted:Column(pa.Bool, coerce=True)})
        elif row.FieldTypeName == 'varchar':
            k.update({row.NameStrNotQuoted:Column(str,Check.str_length(max_value=row.SizeStr), coerce=True, nullable=True)})
        elif row.FieldTypeName == 'datetime':
            k.update({row.NameStrNotQuoted:Column(pa.DateTime, coerce=True, nullable=True)})
    sch = DataFrameSchema(k, coerce=True)
    print(sch)
    sch.validate(d)
    return k

And the traceback:

<Schema DataFrameSchema(
    columns={
        'ContactID': <Schema Column(name=ContactID, type=DataType(int64))>
        'Prefix': <Schema Column(name=Prefix, type=DataType(str))>
        'FirstName': <Schema Column(name=FirstName, type=DataType(str))>
        'MiddleInitial': <Schema Column(name=MiddleInitial, type=DataType(str))>
        'LastName': <Schema Column(name=LastName, type=DataType(str))>
        'Street': <Schema Column(name=Street, type=DataType(str))>
        'City': <Schema Column(name=City, type=DataType(str))>
        'State': <Schema Column(name=State, type=DataType(str))>
        'Zip': <Schema Column(name=Zip, type=DataType(str))>
        'Email': <Schema Column(name=Email, type=DataType(str))>
        'Fax': <Schema Column(name=Fax, type=DataType(str))>
        'Active': <Schema Column(name=Active, type=DataType(bool))>
        'Company': <Schema Column(name=Company, type=DataType(str))>
        'HomePhone': <Schema Column(name=HomePhone, type=DataType(str))>
        'CreatedBy': <Schema Column(name=CreatedBy, type=DataType(float64))>
        'CreatedTimestamp': <Schema Column(name=CreatedTimestamp, type=DataType(datetime64[ns]))>
        'UpdatedBy': <Schema Column(name=UpdatedBy, type=DataType(float64))>
        'UpdatedTimestamp': <Schema Column(name=UpdatedTimestamp, type=DataType(datetime64[ns]))>
    },
    checks=[],
    coerce=True,
    dtype=None,
    index=None,
    strict=False
    name=None,
    ordered=False
)>
[2022-03-10 17:58:28+0000] ERROR - prefect.TaskRunner | Unexpected error: SchemaError('Error while executing check function: TypeError("Invalid comparison between dtype=float64 and str")\nTraceback (most recent call last):\n  File "/usr/local/lib/python3.8/site-packages/pandera/schemas.py", line 1957, in validate\n    _handle_check_results(\n  File "/usr/local/lib/python3.8/site-packages/pandera/schemas.py", line 2333, in _handle_check_results\n    check_result = check(check_obj, *check_args)\n  File "/usr/local/lib/python3.8/site-packages/pandera/checks.py", line 395, in __call__\n    check_output = check_fn(check_obj)\n  File "/usr/local/lib/python3.8/site-packages/pandera/checks.py", line 961, in _str_length\n    return series.str.len() <= max_value\n  File "/usr/local/lib/python3.8/site-packages/pandas/core/ops/common.py", line 64, in new_method\n    return method(self, other)\n  File "/usr/local/lib/python3.8/site-packages/pandas/core/ops/__init__.py", line 529, in wrapper\n    res_values = comparison_op(lvalues, rvalues, op)\n  File "/usr/local/lib/python3.8/site-packages/pandas/core/ops/array_ops.py", line 256, in comparison_op\n    res_values = invalid_comparison(lvalues, rvalues, op)\n  File "/usr/local/lib/python3.8/site-packages/pandas/core/ops/invalid.py", line 34, in invalid_comparison\n    raise TypeError(f"Invalid comparison between dtype={left.dtype} and {typ}")\nTypeError: Invalid comparison between dtype=float64 and str\n')
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/prefect/engine/runner.py", line 48, in inner
    new_state = method(self, state, *args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/prefect/engine/task_runner.py", line 865, in get_task_run_state
    value = prefect.utilities.executors.run_task_with_timeout(
  File "/usr/local/lib/python3.8/site-packages/prefect/utilities/executors.py", line 323, in run_task_with_timeout
    return task.run(*args, **kwargs)  # type: ignore
  File "/workspaces/prefect/ETL/CDNY/PanderaPrefect.py", line 43, in pschema
    sch.validate(d)
  File "/usr/local/lib/python3.8/site-packages/pandera/schemas.py", line 503, in validate
    return self._validate(
  File "/usr/local/lib/python3.8/site-packages/pandera/schemas.py", line 677, in _validate
    error_handler.collect_error("schema_component_check", err)
  File "/usr/local/lib/python3.8/site-packages/pandera/error_handlers.py", line 32, in collect_error
    raise schema_error from original_exc
  File "/usr/local/lib/python3.8/site-packages/pandera/schemas.py", line 669, in _validate
    result = schema_component(
  File "/usr/local/lib/python3.8/site-packages/pandera/schemas.py", line 2004, in __call__
    return self.validate(
  File "/usr/local/lib/python3.8/site-packages/pandera/schema_components.py", line 223, in validate
    validate_column(check_obj, column_name)
  File "/usr/local/lib/python3.8/site-packages/pandera/schema_components.py", line 196, in validate_column
    super(Column, copy(self).set_name(column_name)).validate(
  File "/usr/local/lib/python3.8/site-packages/pandera/schemas.py", line 1972, in validate
    error_handler.collect_error(
  File "/usr/local/lib/python3.8/site-packages/pandera/error_handlers.py", line 32, in collect_error
    raise schema_error from original_exc
pandera.errors.SchemaError: Error while executing check function: TypeError("Invalid comparison between dtype=float64 and str")
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/pandera/schemas.py", line 1957, in validate
    _handle_check_results(
  File "/usr/local/lib/python3.8/site-packages/pandera/schemas.py", line 2333, in _handle_check_results
    check_result = check(check_obj, *check_args)
  File "/usr/local/lib/python3.8/site-packages/pandera/checks.py", line 395, in __call__
    check_output = check_fn(check_obj)
  File "/usr/local/lib/python3.8/site-packages/pandera/checks.py", line 961, in _str_length
    return series.str.len() <= max_value
  File "/usr/local/lib/python3.8/site-packages/pandas/core/ops/common.py", line 64, in new_method
    return method(self, other)
  File "/usr/local/lib/python3.8/site-packages/pandas/core/ops/__init__.py", line 529, in wrapper
    res_values = comparison_op(lvalues, rvalues, op)
  File "/usr/local/lib/python3.8/site-packages/pandas/core/ops/array_ops.py", line 256, in comparison_op
    res_values = invalid_comparison(lvalues, rvalues, op)
  File "/usr/local/lib/python3.8/site-packages/pandas/core/ops/invalid.py", line 34, in invalid_comparison
    raise TypeError(f"Invalid comparison between dtype={left.dtype} and {typ}")
TypeError: Invalid comparison between dtype=float64 and str

Which is the same code I use with the csv file. I see the reference to the str_length Check but what I don't understand why it's only blowing up with the downloaded file? I really wish to use this for validation, do you have any ideas? Thanks again for you help.

fnikitathom · 2022-03-10T21:46:00Z

fnikitathom
Mar 10, 2022
Author

Well after almost 2 days of going nuts I've figured out what the problem was, my inability to think outside the box, until I looked at the traceback again:

  File "/usr/local/lib/python3.8/site-packages/pandera/checks.py", line 961, in _str_length
    return series.str.len() <= max_value

Which prompted my changing the code to:

    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":
            if row.NameStrNotQuoted in ['CreatedBy','UpdatedBy']:
                k.update({row.NameStrNotQuoted:Column(float, coerce=True, nullable=True)})
            else:
                k.update({row.NameStrNotQuoted:Column(int,coerce=True, nullable=True)})
        elif row.FieldTypeName == 'bit':
            k.update({row.NameStrNotQuoted:Column(pa.Bool, coerce=True)})
        elif row.FieldTypeName == 'varchar':
            k.update({row.NameStrNotQuoted:Column(str,pa.Check.str_length(max_value=int(row.SizeStr)), coerce=True, nullable=True)})
        elif row.FieldTypeName == 'text':
            k.update({row.NameStrNotQuoted:Column(str, coerce=True, nullable=True)})
        elif row.FieldTypeName == 'datetime':
            k.update({row.NameStrNotQuoted:Column(pa.DateTime, coerce=True, nullable=True)})
    sch = DataFrameSchema(k, coerce=True)
    print(sch)
    sch.validate(d)
    return k

Note the cast to int for row.SizeStr . Sorry to have been a bother, and thanks

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Confusing results when different field values are used for Integer columns #787

{{title}}

Replies: 2 comments 2 replies

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

Select a reply

Confusing results when different field values are used for Integer columns #787

fnikitathom Mar 9, 2022

Replies: 2 comments · 2 replies

cosmicBboy Mar 9, 2022 Maintainer

fnikitathom Mar 9, 2022 Author

fnikitathom Mar 10, 2022 Author

fnikitathom Mar 10, 2022 Author

fnikitathom
Mar 9, 2022

Replies: 2 comments 2 replies

cosmicBboy
Mar 9, 2022
Maintainer

fnikitathom Mar 9, 2022
Author

fnikitathom Mar 10, 2022
Author

fnikitathom
Mar 10, 2022
Author