BUG: `assert_frame_equal` behaves differently when comparing a mostly-numeric column from CSV if that column has a non-numeric entry

[blakeNaccarato]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd
from pandas.testing import assert_frame_equal

orig_df1 = pd.DataFrame(
    {
        "Column": [
            "Units",
            1.0000000000005,
        ],
    }
)

orig_df2 = pd.DataFrame(
    {
        "Column": [
            "Units",
            1.0000000000007,
        ],
    }
)

orig_df1.to_csv("df1.csv", index=False)
orig_df2.to_csv("df2.csv", index=False)


def test_dfs_directly():
    """This test passes."""
    assert_frame_equal(orig_df1, orig_df2)


def test_csv_roundtrip():
    """This test fails."""
    df1 = pd.read_csv("df1.csv")
    df2 = pd.read_csv("df2.csv")
    assert_frame_equal(df1, df2)


def test_csv_roundtrip_omit_nonnumeric_row():
    """This test passes."""
    df1 = pd.read_csv("df1.csv", skiprows=[1])
    df2 = pd.read_csv("df2.csv", skiprows=[1])
    assert_frame_equal(df1, df2)

Issue Description

Tests pass on the original dataframes or when the dataframes loaded from CSV have their non-numeric row omitted. The test fails if the non-numeric row is included. This behavior difference could be classified as a bug, or if is working as intended, then maybe it could be documented that using assert_frame_equal behaves strangely when loading mixed-type columns from CSV.

The minimal example supposes that the user has a "Units" row, where they store, say "m/s" or "kg". Of course this may be a "data smell", that care should be taken to load uniform data into dataframes, and keep track of metadata like units separately.

Expected Behavior

All three tests should pass.

Installed Versions

For some reason, `pd.show_versions()` is raising the following `AssertionError`:

AssertionError: C:\Users\Blake\AppData\Local\Programs\Python\Python310\lib\distutils\core.py

Hopefully the output of pip show pandas should suffice:

Name: pandas
Version: 1.4.2
...

[simonjayhawkins]

Thanks @blakeNaccarato for the report

The test fails if the non-numeric row is included. This behavior difference could be classified as a bug, or if is working as intended, then maybe it could be documented that using assert_frame_equal behaves strangely when loading mixed-type columns from CSV.

the first test correctly passes because the DataFrames are object dtype with the numeric values stored as Python float objects that are within the default tolerance of assert_frame_equal

orig_df1 = pd.DataFrame({"Column": ["Units", 1.0000000000005]})
print(orig_df1.dtypes["Column"])
object_scalar1 = orig_df1.loc[1, "Column"]
print(object_scalar1, type(object_scalar1))
orig_df2 = pd.DataFrame({"Column": ["Units", 1.0000000000007]})
object_scalar2 = orig_df2.loc[1, "Column"]
print(object_scalar2, type(object_scalar2))
assert_frame_equal(orig_df1, orig_df2)

object
1.0000000000005 <class 'float'>
1.0000000000007 <class 'float'>

the second case correctly fails since again the DataFrames are object dtype but the numeric values are parsed as Python strings which are different.

df1 = pd.read_csv(StringIO(orig_df1.to_csv(index=False)))
print(df1.dtypes)
object_scalar1 = df1.loc[1, "Column"]
print(object_scalar1, type(object_scalar1))
df2 = pd.read_csv(StringIO(orig_df2.to_csv(index=False)))
print(df2.dtypes)
object_scalar2 = df2.loc[1, "Column"]
print(object_scalar2, type(object_scalar2))
assert_frame_equal(df1, df2)

Column    object
dtype: object
1.0000000000005 <class 'str'>
Column    object
dtype: object
1.0000000000007 <class 'str'>
---------------------------------------------------------------------------
AssertionError                            Traceback (most recent call last)
....

The third case correctly passes since the numeric values as parsed as numeric with float64 dtype and within default tolerance.

df1 = pd.read_csv(StringIO(orig_df1.to_csv(index=False)), skiprows=[1])
print(df1.dtypes)
scalar1 = df1.loc[0, "Column"]
print(scalar1, type(scalar1))
df2 = pd.read_csv(StringIO(orig_df2.to_csv(index=False)), skiprows=[1])
print(df2.dtypes)
scalar2 = df2.loc[0, "Column"]
print(scalar2, type(scalar2))
assert_frame_equal(df1, df2)

Column    float64
dtype: object
1.0000000000005 <class 'numpy.float64'>
Column    float64
dtype: object
1.0000000000007 <class 'numpy.float64'>

and correctly raises AssertionError with

assert_frame_equal(df1, df2, check_exact=True)

or

assert_frame_equal(df1, df2, check_exact=False, atol=1e-20, rtol=1e-20)

Where there looks like there may be an issue though is with the first cast where changing the tolerances seems to work but using check_exact=True does not.

As that issue unrelated to the issues raised in the OP regarding CSV parsing, i'll label this issue as a closing candidate for now.

[blakeNaccarato]

Thanks for the explanation! I dug deeply enough to write tests, but should've checked dtypes as you have pointed out. Rookie mistake 😅

Am I free to close this myself? The "Closing Candidate" label makes it seem like I should leave it up to internal processes, but this clearly "works as intended" and I just misunderstood. I'm going to go ahead and close it...

Again I appreciate the thorough response!

[gordonkoehn]

This is good to know. I thought pandas would automatically parse the right type here, so I made the same mistake. @simonjayhawkins Thank you for the through explanation!