Pandas and Spark DataFrame comparison for humans

Row indexes are not right while trying to find out unique rows which are missing in respective dataFrames. compare.df1_unq_rows and compare.df2_unq_rows returns the unique rows which are missing in respective dataFrames but their index are not returned correctly. Please have a look at this issue. I would be really great if the right indexes are returned on using mentioned functions.

This PR adds the following three parameters to all_mismatched():

        """All rows with any columns that have a mismatch. Returns all df1 and df2 versions of the columns and join
        columns.

        Parameters
        ----------
        exclude_matched_columns : bool, optional
            When set to True, columns where all rows are matched are excluded.
        simplify_matched : bool, optional
            When set to True, only a single column will be included for
            columns that had no changes between the two dataframes.
        include_match_col : bool, optional
            Whether to include a `xxx_match` column that indicactes whether
            the column data has changed for the row.

        Returns
        -------
        Pandas.DataFrame
            All rows of the intersection dataframe, containing any columns, that don't match.
        """

Please let me know if you'd like me to make any changes before it gets merged and thanks for creating this great library.

Join columns can have whitespaces as well,so ideally below section should be enhanced to check for ignore_spaces and strip() if it is true for string columns This is causing complete mismatch of 2 dataframes here in case 1 of join column has space:

if on_index and join_columns is not None: raise Exception("Only provide on_index or join_columns") elif on_index: self.on_index = True self.join_columns = [] elif isinstance(join_columns, str): self.join_columns = [join_columns.lower()] self.on_index = False else: self.join_columns = [col.lower() for col in join_columns] self.on_index = False

How to get more rows shown in the report like for rows available in source but not in target, if I want to see may be 100 rows. How do I do that?

I tried using print(compare.df1_unq_rows(sample_count=50),file=tgtuniqrows) but it gave me error as print(compare.df1_unq_rows(sample_count=50),file=tgtuniqrows) TypeError: 'DataFrame' object is not callable

Can you please help here?

@ryanshiroma Can you please sign the project CLA for datacompy project? If you plan to submit a PR in the near future, CLA-Assistant bot will prompt to to sign. If you aren't planning to submit any new PRs to the project, pls use this link to sign: https://docs.google.com/forms/d/e/1FAIpQLSfwtl1s6KmpLhCY6CjiY8nFZshDwf_wrmNYx1ahpsNFXXmHKw/viewform

We appreciate your contributions to the project and your understanding on the contribution requirement for CLA.

'Sample Rows with Unequal Values' shows just 10 rows which are different. I want to see what changed in all the rows in the 2 dataframes, not just 10 rows.

Is there a way I can see all the unequal rows?

Please advise.

Currently the csv that's being written out has all the columns written out on a failed comparison making it harder to read with a larger data-set. This is more of a feature request but the ability to only have columns that didn't match written to the mismatch csv would really help and speed up the QA process on our end.

Changes

We use datacompy as a library in the project using poetry to lock dependencies. And using the strict upper bounds for minor versions makes it hard to update our dependencies to include bug and security fixes. This PR changes the version constraints to be more permissive.

@fdosani @KrishanBhasin

Fixes #81. Iteration on #107. This aims to maintain the original order when printing out the set of common columns as well as unique columns, both which are based on set operations.

If I am trying to compare the intersect rows for the following two dataframes:

df1 = pd.DataFrame({'key': ['foo', 'bar', 'baz', 'foo'],
                   'value': [1, 2, 3, 5]              
                   })
df2 = pd.DataFrame({'key': ['foo', 'bar', 'baz'],
                    'value': [5, 6, 7]})

compare = datacompy.Compare(
            df1,
            df2,
            join_columns=['key'],  #You can also specify a list of columns
            abs_tol=0,
            rel_tol=0,
            df1_name='df1',
            df2_name='df2')

compare.intersect_rows only return the following rows:

key | value_df1 | value_df2 | _merge | value_match -- | -- | -- | -- | -- foo | 1 | 5 | both | False bar | 2 | 6 | both | False baz | 3 | 7 | both | False

While I am expecting an additional row:

key | value_df1 | value_df2 | _merge | value_match -- | -- | -- | -- | -- foo | 5 | 5 | both | True

In fact, when you use pandas merge on df1 and df2, you do see the last row in df1:

df1.merge(df2, how="outer", suffixes=("_df1", "_df2"), indicator=True, on='key')

key | value_df1 | value_df2 | _merge -- | -- | -- | -- foo | 1 | 5 | both foo | 5 | 5 | both bar | 2 | 6 | both baz | 3 | 7 | both

Why is the last row from df1 missing from intersect_row dataframe?

Since we target Python 3.5+ in our CI testing, we can make this explicit and commit to only supporting that version and higher. This will enable us to use type hinting in our code, which would have a few benefits:

• IDE support for types while developing datacompy
• IDE support for types for users using datacompy
• Ability to do static type checking in our CI pipeline
• Automatic type inference during documentation generation

This should probably best be done in concert with something like #13 as long as we're messing with the code, but since this will involve committing to supporting certain versions of Python, it should be a conscious choice. Python 3.4 reached EOL in March 2019 so I don't see a problem with explicitly targeting 3.5+.

called_with is an incorrect method that just returns a Mock object that evaluates to True and doesn't perform appropriate assertion. This method should be replaced with assert_called_with . Related CPython issue : https://github.com/python/cpython/issues/100690 . Changes cause below test failures.

pytest tests/test_core.py           
==================================================================== test session starts =====================================================================
platform linux -- Python 3.11.1, pytest-7.2.0, pluggy-1.0.0
rootdir: /root/checked_repos_clone_3900_4000/datacompy, configfile: pytest.ini
plugins: cov-2.12.1
collected 81 items                                                                                                                                           

tests/test_core.py .............................FF..................................................                                                   [100%]

========================================================================== FAILURES ==========================================================================
________________________________________________________________________ test_subset _________________________________________________________________________

mock_debug = <MagicMock name='debug' id='140448511508688'>

    @mock.patch("datacompy.logging.debug")
    def test_subset(mock_debug):
        df1 = pd.DataFrame([{"a": 1, "b": 2, "c": "hi"}, {"a": 2, "b": 2, "c": "yo"}])
        df2 = pd.DataFrame([{"a": 1, "c": "hi"}])
        comp = datacompy.Compare(df1, df2, ["a"])
        assert comp.subset()
>       mock_debug.assert_called_with("Checking equality")

tests/test_core.py:529: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <MagicMock name='debug' id='140448511508688'>, args = ('Checking equality',), kwargs = {}, expected = "debug('Checking equality')"
actual = 'not called.', error_message = "expected call not found.\nExpected: debug('Checking equality')\nActual: not called."

    def assert_called_with(self, /, *args, **kwargs):
        """assert that the last call was made with the specified arguments.
    
        Raises an AssertionError if the args and keyword args passed in are
        different to the last call to the mock."""
        if self.call_args is None:
            expected = self._format_mock_call_signature(args, kwargs)
            actual = 'not called.'
            error_message = ('expected call not found.\nExpected: %s\nActual: %s'
                    % (expected, actual))
>           raise AssertionError(error_message)
E           AssertionError: expected call not found.
E           Expected: debug('Checking equality')
E           Actual: not called.

/usr/lib/python3.11/unittest/mock.py:924: AssertionError
______________________________________________________________________ test_not_subset _______________________________________________________________________

mock_info = <MagicMock name='info' id='140448491660176'>

    @mock.patch("datacompy.logging.info")
    def test_not_subset(mock_info):
        df1 = pd.DataFrame([{"a": 1, "b": 2, "c": "hi"}, {"a": 2, "b": 2, "c": "yo"}])
        df2 = pd.DataFrame([{"a": 1, "b": 2, "c": "hi"}, {"a": 2, "b": 2, "c": "great"}])
        comp = datacompy.Compare(df1, df2, ["a"])
        assert not comp.subset()
>       mock_info.assert_called_with("Sample c mismatch: a: 2, df1: yo, df2: great")

tests/test_core.py:538: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <MagicMock name='info' id='140448491660176'>, args = ('Sample c mismatch: a: 2, df1: yo, df2: great',), kwargs = {}
expected = "info('Sample c mismatch: a: 2, df1: yo, df2: great')", actual = 'not called.'
error_message = "expected call not found.\nExpected: info('Sample c mismatch: a: 2, df1: yo, df2: great')\nActual: not called."

    def assert_called_with(self, /, *args, **kwargs):
        """assert that the last call was made with the specified arguments.
    
        Raises an AssertionError if the args and keyword args passed in are
        different to the last call to the mock."""
        if self.call_args is None:
            expected = self._format_mock_call_signature(args, kwargs)
            actual = 'not called.'
            error_message = ('expected call not found.\nExpected: %s\nActual: %s'
                    % (expected, actual))
>           raise AssertionError(error_message)
E           AssertionError: expected call not found.
E           Expected: info('Sample c mismatch: a: 2, df1: yo, df2: great')
E           Actual: not called.

/usr/lib/python3.11/unittest/mock.py:924: AssertionError
====================================================================== warnings summary ======================================================================
../../checked_repos/taskcat/.env/lib/python3.11/site-packages/_pytest/config/__init__.py:1294
  /root/checked_repos/taskcat/.env/lib/python3.11/site-packages/_pytest/config/__init__.py:1294: PytestConfigWarning: Unknown config option: spark_options
  
    self._warn_or_fail_if_strict(f"Unknown config option: {key}\n")

tests/test_core.py:29
  /root/checked_repos_clone_3900_4000/datacompy/tests/test_core.py:29: FutureWarning: pandas.util.testing is deprecated. Use the functions in the public API at pandas.testing instead.
    from pandas.util.testing import assert_series_equal

-- Docs: https://docs.pytest.org/en/stable/how-to/capture-warnings.html
================================================================== short test summary info ===================================================================
FAILED tests/test_core.py::test_subset - AssertionError: expected call not found.
FAILED tests/test_core.py::test_not_subset - AssertionError: expected call not found.
========================================================== 2 failed, 79 passed, 2 warnings in 1.69s ==========================================================

There are some security vulns which keep coming up with Python 3.7 support. In the next release (0.9.x) we should drop Python 3.7 support. Should be an easy change and fix.

• Following: https://github.com/capitalone/datacompy/blob/develop/setup.cfg#L14 should become >=3.8.0
• Doc / README updates would be needed also

Any idea when support for datetime64[ns, UTC] will be implemented ? I get this error Cannot interpret 'datetime64[ns, UTC]' as a data type when using compare function

Summary report shows there are records in base and compare dataset that did not have corresponding matches

****** Row Summary ****** Number of rows in common: 2971140 Number of rows in base but not compare: 41536 Number of rows in compare but not base: 41536** Number of duplicate rows found in base: 3721 Number of duplicate rows found in compare: 3721

but when i try to get the records in base and/or compare that do not have matches.. it returns 0...

Using latest Spark version to do comparison...

Any thoughts/suggestions on what might be the issue. I was hoping to see 41536 records for compare.rows_only_compare