Repository: capitalone/datacompy

Issue:
Would be useful to have a parameter to strip spaces for comparison
As probably expected, the following code will return a mismatch since 'B'<>'B ':
```
import pandas as pd
import datacompy

df1 = pd.DataFrame([
        {'id': 1234, 'column_value': 'A'},
        {'id': 2345, 'column_value': 'B'}])

df2 = pd.DataFrame([
        {'id': 1234, 'column_value': 'A'},
        {'id': 2345, 'column_value': 'B '}])

compare = datacompy.Compare(
    df1,
    df2,
    join_columns='id',
    abs_tol=0,
    rel_tol=0,
    )
compare.matches(ignore_extra_columns=False)
# False

# This method prints out a human-readable report summarizing and sampling differences
print(compare.report())
```
What I propose is an optional parameter to ignore differences where the only difference is leading or trailing spaces. In this example it is obvious that there is a trailing space. However, when we are dealing with extracts from different databases/source files, without real control over the ETL of these, sometimes we can't prevent these discrepancies. We may wish to ignore these types of mismatches to identify 'worse' mismatches more effectively.

Another candidate could be ignoring case sensitivity differences.
Of course these could both be easily handled with preprocessing the dataframes, but still could be some convenient enhancements!

Discussion:

Patch:
diff --git a/datacompy/core.py b/datacompy/core.py
index 7fc296e..e03d75e 100644
--- a/datacompy/core.py
+++ b/datacompy/core.py
@@ -59,6 +59,8 @@ class Compare(object):
         more easily track the dataframes.
     df2_name : str, optional
         A string name for the second dataframe
+    ignore_spaces : bool, optional
+        Flag to strip whitespace (including newlines) from string columns
 
     Attributes
     ----------
@@ -70,7 +72,7 @@ class Compare(object):
 
     def __init__(
         self, df1, df2, join_columns=None, on_index=False, abs_tol=0,
-        rel_tol=0, df1_name='df1', df2_name='df2'):
+        rel_tol=0, df1_name='df1', df2_name='df2', ignore_spaces=False):
 
         if on_index and join_columns is not None:
             raise Exception('Only provide on_index or join_columns')
@@ -93,7 +95,7 @@ class Compare(object):
         self.rel_tol = rel_tol
         self.df1_unq_rows = self.df2_unq_rows = self.intersect_rows = None
         self.column_stats = []
-        self._compare()
+        self._compare(ignore_spaces)
 
     @property
     def df1(self):
@@ -143,7 +145,7 @@ class Compare(object):
             if len(dataframe.drop_duplicates(subset=self.join_columns)) < len(dataframe):
                 self._any_dupes = True
 
-    def _compare(self):
+    def _compare(self, ignore_spaces):
         """Actually run the comparison.  This tries to run df1.equals(df2)
         first so that if they're truly equal we can tell.
 
@@ -167,8 +169,8 @@ class Compare(object):
         LOG.info('Number of columns in df2 and not in df1: {}'.format(
             len(self.df2_unq_columns())))
         LOG.debug('Merging dataframes')
-        self._dataframe_merge()
-        self._intersect_compare()
+        self._dataframe_merge(ignore_spaces)
+        self._intersect_compare(ignore_spaces)
         if self.matches():
             LOG.info('df1 matches df2')
         else:
@@ -186,7 +188,7 @@ class Compare(object):
         """Get columns that are shared between the two dataframes"""
         return set(self.df1.columns) & set(self.df2.columns)
 
-    def _dataframe_merge(self):
+    def _dataframe_merge(self, ignore_spaces):
         """Merge df1 to df2 on the join columns, to get df1 - df2, df2 - df1
         and df1 & df2
 
@@ -262,7 +264,7 @@ class Compare(object):
             'Number of rows in df1 and df2 (not necessarily equal): {}'.format(
                 len(self.intersect_rows)))
 
-    def _intersect_compare(self):
+    def _intersect_compare(self, ignore_spaces):
         """Run the comparison on the intersect dataframe
 
         This loops through all columns that are shared between df1 and df2, and
@@ -285,7 +287,8 @@ class Compare(object):
                     self.intersect_rows[col_1],
                     self.intersect_rows[col_2],
                     self.rel_tol,
-                    self.abs_tol)
+                    self.abs_tol,
+                    ignore_spaces)
                 match_cnt = self.intersect_rows[col_match].sum()
 
                 try:
@@ -570,7 +573,7 @@ def render(filename, *fields):
         return file_open.read().format(*fields)
 
 
-def columns_equal(col_1, col_2, rel_tol=0, abs_tol=0):
+def columns_equal(col_1, col_2, rel_tol=0, abs_tol=0, ignore_spaces=False):
     """Compares two columns from a dataframe, returning a True/False series,
     with the same index as column 1.
 
@@ -592,6 +595,8 @@ def columns_equal(col_1, col_2, rel_tol=0, abs_tol=0):
         Relative tolerance
     abs_tol : float, optional
         Absolute tolerance
+    ignore_spaces : bool, optional
+        Flag to strip whitespace (including newlines) from string columns
 
     Returns
     -------
@@ -616,6 +621,12 @@ def columns_equal(col_1, col_2, rel_tol=0, abs_tol=0):
                 equal_nan=True))
         except (ValueError, TypeError):
             try:
+                if ignore_spaces:
+                    if col_1.dtype.kind == 'O':
+                        col_1 = col_1.str.strip()
+                    if col_2.dtype.kind == 'O':
+                        col_2 = col_2.str.strip()
+                        
                 if set([col_1.dtype.kind, col_2.dtype.kind]) == set(['M','O']):
                     compare = compare_string_and_date_columns(col_1, col_2)
                 else:


Test patch:
diff --git a/tests/test_core.py b/tests/test_core.py
index d236427..f3e8437 100644
--- a/tests/test_core.py
+++ b/tests/test_core.py
@@ -85,6 +85,28 @@ something||False
     assert_series_equal(expect_out, actual_out, check_names=False)
 
 
+def test_string_columns_equal_with_ignore_spaces():
+    data = '''a|b|expected
+Hi|Hi|True
+Yo|Yo|True
+Hey|Hey |True
+résumé|resume|False
+résumé|résumé|True
+💩|💩|True
+💩|🤔|False
+ | |True
+  |       |True
+datacompy|DataComPy|False
+something||False
+|something|False
+||True'''
+    df = pd.read_csv(six.StringIO(data), sep='|')
+    actual_out = datacompy.columns_equal(
+        df.a, df.b, rel_tol=0.2, ignore_spaces=True)
+    expect_out = df['expected']
+    assert_series_equal(expect_out, actual_out, check_names=False)
+
+
 def test_date_columns_equal():
     data = '''a|b|expected
 2017-01-01|2017-01-01|True
@@ -110,6 +132,34 @@ def test_date_columns_equal():
     assert_series_equal(expect_out, actual_out_rev, check_names=False)
 
 
+def test_date_columns_equal_with_ignore_spaces():
+    data = '''a|b|expected
+2017-01-01|2017-01-01   |True
+2017-01-02  |2017-01-02|True
+2017-10-01  |2017-10-10   |False
+2017-01-01||False
+|2017-01-01|False
+||True'''
+    df = pd.read_csv(six.StringIO(data), sep='|')
+    #First compare just the strings
+    actual_out = datacompy.columns_equal(
+        df.a, df.b, rel_tol=0.2, ignore_spaces=True)
+    expect_out = df['expected']
+    assert_series_equal(expect_out, actual_out, check_names=False)
+
+    #Then compare converted to datetime objects
+    df['a'] = pd.to_datetime(df['a'])
+    df['b'] = pd.to_datetime(df['b'])
+    actual_out = datacompy.columns_equal(
+        df.a, df.b, rel_tol=0.2, ignore_spaces=True)
+    expect_out = df['expected']
+    assert_series_equal(expect_out, actual_out, check_names=False)
+    #and reverse
+    actual_out_rev = datacompy.columns_equal(
+        df.b, df.a, rel_tol=0.2, ignore_spaces=True)
+    assert_series_equal(expect_out, actual_out_rev, check_names=False)
+
+
 
 def test_date_columns_unequal():
     """I want datetime fields to match with dates stored as strings
@@ -250,6 +300,20 @@ def test_mixed_column():
     assert_series_equal(expect_out, actual_out, check_names=False)
 
 
+def test_mixed_column_with_ignore_spaces():
+    df = pd.DataFrame([
+        {'a': 'hi', 'b': 'hi ', 'expected': True},
+        {'a': 1, 'b': 1, 'expected': True},
+        {'a': np.inf, 'b': np.inf, 'expected': True},
+        {'a': Decimal('1'), 'b': Decimal('1'), 'expected': True},
+        {'a': 1, 'b': '1 ', 'expected': False},
+        {'a': 1, 'b': 'yo ', 'expected': False}
+        ])
+    actual_out = datacompy.columns_equal(df.a, df.b, ignore_spaces=True)
+    expect_out = df['expected']
+    assert_series_equal(expect_out, actual_out, check_names=False)
+
+
 def test_compare_df_setter_bad():
     df = pd.DataFrame([{'a': 1, 'A': 2}, {'a': 2, 'A': 2}])
     with raises(TypeError, message='df1 must be a pandas DataFrame'):
@@ -565,4 +629,52 @@ def test_dupes_from_real_data():
     assert compare_unq.matches()
     #Just render the report to make sure it renders.
     t = compare_acct.report()
-    r = compare_unq.report()
\ No newline at end of file
+    r = compare_unq.report()
+
+
+def test_strings_with_joins_with_ignore_spaces():
+    df1 = pd.DataFrame([{'a': 'hi', 'b': ' A'}, {'a': 'bye', 'b': 'A'}])
+    df2 = pd.DataFrame([{'a': 'hi', 'b': 'A'}, {'a': 'bye', 'b': 'A '}])
+    compare = datacompy.Compare(df1, df2, 'a', ignore_spaces=False)
+    assert not compare.matches()
+    assert compare.all_columns_match()
+    assert compare.all_rows_overlap()
+    assert not compare.intersect_rows_match()
+
+    compare = datacompy.Compare(df1, df2, 'a', ignore_spaces=True)
+    assert compare.matches()
+    assert compare.all_columns_match()
+    assert compare.all_rows_overlap()
+    assert compare.intersect_rows_match()
+
+
+def test_decimal_with_joins_with_ignore_spaces():
+    df1 = pd.DataFrame([{'a': 1, 'b': ' A'}, {'a': 2, 'b': 'A'}])
+    df2 = pd.DataFrame([{'a': 1, 'b': 'A'}, {'a': 2, 'b': 'A '}])
+    compare = datacompy.Compare(df1, df2, 'a', ignore_spaces=False)
+    assert not compare.matches()
+    assert compare.all_columns_match()
+    assert compare.all_rows_overlap()
+    assert not compare.intersect_rows_match()
+
+    compare = datacompy.Compare(df1, df2, 'a', ignore_spaces=True)
+    assert compare.matches()
+    assert compare.all_columns_match()
+    assert compare.all_rows_overlap()
+    assert compare.intersect_rows_match()
+
+
+def test_index_with_joins_with_ignore_spaces():
+    df1 = pd.DataFrame([{'a': 1, 'b': ' A'}, {'a': 2, 'b': 'A'}])
+    df2 = pd.DataFrame([{'a': 1, 'b': 'A'}, {'a': 2, 'b': 'A '}])
+    compare = datacompy.Compare(df1, df2, on_index=True, ignore_spaces=False)
+    assert not compare.matches()
+    assert compare.all_columns_match()
+    assert compare.all_rows_overlap()
+    assert not compare.intersect_rows_match()
+
+    compare = datacompy.Compare(df1, df2, 'a', ignore_spaces=True)
+    assert compare.matches()
+    assert compare.all_columns_match()
+    assert compare.all_rows_overlap()
+    assert compare.intersect_rows_match()