queries.compare

View Source

  1import warnings
  2
  3import pandas as pd
  4
  5
  6def _prepare_comparison_data(left: pd.DataFrame, right: pd.DataFrame) \
  7        -> tuple[pd.DataFrame, pd.DataFrame]:
  8    """
  9    Prepare data for comparison.
 10    Sort data by columns and reset index.
 11
 12    Args:
 13        left (pd.DataFrame): Left dataframe.
 14        right (pd.DataFrame): Right dataframe.
 15
 16    Returns:
 17        tuple[pd.DataFrame, pd.DataFrame]: Tuple with sorted dataframes.
 18    """
 19    # copy data
 20    left = left.copy()
 21    right = right.copy()
 22
 23    # sort data
 24    left = left.sort_values(
 25        # sort by all columns
 26        by=left.columns.tolist(),
 27        # sort by rows
 28        axis=0,
 29        # ascending order
 30        ascending=True,
 31        # do not modify original data
 32        inplace=False,
 33        # reset index
 34        ignore_index=True
 35    )
 36    right = right.sort_values(
 37        by=right.columns.tolist(),
 38        axis=0,
 39        ascending=True,
 40        inplace=False,
 41        ignore_index=True
 42    )
 43
 44    # return data
 45    return left, right
 46
 47
 48def _find_differences(left: pd.DataFrame,
 49                      right: pd.DataFrame,
 50                      left_source: str = 'left',
 51                      right_source: str = 'right') -> pd.DataFrame:
 52    """
 53    Find differences between two dataframes.
 54    Return differences in a new dataframe.
 55
 56    Resulting dataframe will have additional column 'source'
 57    which will indicate from which dataframe the row comes from.
 58    To set the source, use `left_source` and `right_source` arguments.
 59
 60    The number and naming of columns depends on the input dataframes.
 61    The resulting dataframe will have all columns from input dataframes (
 62    union of them) and additional column 'source'.
 63
 64    If there is no difference between two dataframes an empty DataFrame will be
 65    returned (containing union of columns from original dataframes and
 66    'source' column).
 67
 68    Args:
 69        left (pd.DataFrame): Left dataframe.
 70        right (pd.DataFrame): Right dataframe.
 71        left_source (str): Source name for left dataframe.
 72        right_source (str): Source name for right dataframe.
 73
 74    Returns:
 75        pd.DataFrame: Dataframe with differences.
 76    """
 77    # align columns
 78    all_columns = left.columns.union(right.columns)
 79    left = left.reindex(columns=all_columns)
 80    right = right.reindex(columns=all_columns)
 81
 82    # transform to tuples
 83    left = left.to_records(index=False).tolist()
 84    right = right.to_records(index=False).tolist()
 85
 86    # create dictionaries
 87    # hash(row) - hash of a row
 88    # row - row data
 89    left_dict = {hash(row): row for row in left}
 90    right_dict = {hash(row): row for row in right}
 91
 92    # get keys of dictionaries
 93    # set of unique keys for fast comparison
 94    left_set = set(left_dict.keys())
 95    right_set = set(right_dict.keys())
 96
 97    # get left differences
 98    left_diff = left_set - right_set
 99    left_diff = [(*left_dict[key], left_source) for key in left_diff]
100
101    # get right differences
102    right_diff = right_set - left_set
103    right_diff = [(*right_dict[key], right_source) for key in right_diff]
104
105    # combine differences
106    total_diff = []
107    total_diff.extend(left_diff)
108    total_diff.extend(right_diff)
109
110    # create dataframe
111    all_columns = all_columns.tolist()
112    all_columns.append('source')
113
114    # return differences
115    return pd.DataFrame(
116        data=total_diff,
117        columns=all_columns
118    )
119
120
121def get_differences(left: pd.DataFrame,
122                    right: pd.DataFrame,
123                    left_source: str = 'left',
124                    right_source: str = 'right') -> pd.DataFrame:
125    """
126    Compare two dataframes.
127    This method returns **rows that are different between two dataframes**,
128    and also **it returns rows that are in one dataframe but not in the
129    other**.
130    To distinguish between dataframes, use `left_source` and `right_source`
131    arguments, which will be added to the resulting dataframe as a new column
132    'source'.
133
134    > It is recommended to standardize input data, meaning to round floats,
135    > convert all columns to the same data type (be careful with datetime).
136    > This method will to some degree ignore data type differences, but **it is
137    > recommended to standardize data before comparison.**
138
139    ## Example 1:
140    ```python
141    import pandas as pd
142    from queries.compare import get_differences
143
144    a = pd.DataFrame({'a': [1, 2, 3, 4], 'b': [5, 6, 7, 8]})
145    b = pd.DataFrame({'a': [1, 2, 3, 5], 'b': [5, 6, 7, 8]})
146
147    print(get_differences(a, b))
148    ```
149
150    Results:
151    ```text
152       a  b source
153    0  4  8   left
154    1  5  8  right
155    ```
156
157    ## Example 2:
158    ```python
159    import pandas as pd
160    from queries.compare import get_differences
161
162    a = pd.DataFrame({'a': [1, 2, 3, 4, 5], 'b': [5, 6, 7, 8, 9]})
163    b = pd.DataFrame({'a': [1, 2, 3, 5, 6], 'b': [5, 6, 7, 8, 1]})
164
165    print(get_differences(a, b))
166    ```
167
168    Results:
169    ```text
170       a  b source
171    0  5  9   left
172    1  4  8   left
173    2  5  8  right
174    3  6  1  right
175    ```
176
177    The number and naming of columns depends on the input dataframes.
178    The resulting dataframe will have all columns from input dataframes (
179    union of them) and additional column 'source'.
180
181    If there is no difference between two dataframes an empty DataFrame will be
182    returned (containing union of columns from original dataframes and
183    'source' column).
184
185    **This method raises `UserWarning` if two dataframes don't have same column
186    names!**
187
188    Args:
189        left (pd.DataFrame): Left dataframe.
190        right (pd.DataFrame): Right dataframe.
191        left_source (str): Source name for left dataframe.
192        right_source (str): Source name for right dataframe.
193
194    Returns:
195        pd.DataFrame: Dataframe with differences.
196    """
197    # prepare data
198    left, right = _prepare_comparison_data(left, right)
199    try:
200        # compare dataframes
201        # check if dataframes are equal
202        # if not, then raise AssertionError
203        pd.testing.assert_frame_equal(
204            left=left,
205            right=right,
206            # check exact data types for each column ELEMENT
207            # for example 3.0 = 3, but 3.0 != '3'
208            check_dtype=False,
209            # check index types
210            check_index_type=True,
211            # check column name and index type
212            check_column_type=True,
213            # check name of columns and index
214            check_names=True,
215            # check exact difference in numbers
216            # when True then: 3.000000000001 != 3.0
217            check_exact=False,
218            # rtol - relative tolerance
219            # abs((a - b) / max(a, b)) <= rtol
220            rtol=1e-5,
221            # default
222            # atol - absolute tolerance
223            # abs(a - b) <= atol
224            atol=1e-8,
225            # default
226
227            # combination od atol and rtol
228            # abs(a - b) <= (atol + rtol * abs(b))
229
230            # check datetime dtypes compatiblity
231            check_datetimelike_compat=True,
232            # check categorical columns exactly
233            check_categorical=True,
234            # ignore order
235            # (Note: index labels must match their respective rows
236            # (same as in columns) - same labels must be with the same data.)
237            check_like=True,
238        )
239        return pd.DataFrame([], columns=list(left.columns) + ['source'])
240    except AssertionError as e:
241        # find differences
242        diff = _find_differences(
243            left=left,
244            right=right,
245            left_source=left_source,
246            right_source=right_source
247        )
248
249        if len(set(left.columns).difference(set(right.columns))) != 0:
250            warnings.warn(f'Columns are not the same in both '
251                          f'dataframes.\n'
252                          f'["{left_source}"]: {list(left.columns)}\n'
253                          f'["{right_source}"]: {list(right.columns)}\n'
254                          f'Difference: '
255                          f'{set(left.columns).difference(set(right.columns))}',
256                          UserWarning
257                          )
258
259        return diff

def get_differences( left: pandas.core.frame.DataFrame, right: pandas.core.frame.DataFrame, left_source: str = 'left', right_source: str = 'right') -> pandas.core.frame.DataFrame: View Source

122def get_differences(left: pd.DataFrame,
123                    right: pd.DataFrame,
124                    left_source: str = 'left',
125                    right_source: str = 'right') -> pd.DataFrame:
126    """
127    Compare two dataframes.
128    This method returns **rows that are different between two dataframes**,
129    and also **it returns rows that are in one dataframe but not in the
130    other**.
131    To distinguish between dataframes, use `left_source` and `right_source`
132    arguments, which will be added to the resulting dataframe as a new column
133    'source'.
134
135    > It is recommended to standardize input data, meaning to round floats,
136    > convert all columns to the same data type (be careful with datetime).
137    > This method will to some degree ignore data type differences, but **it is
138    > recommended to standardize data before comparison.**
139
140    ## Example 1:
141    ```python
142    import pandas as pd
143    from queries.compare import get_differences
144
145    a = pd.DataFrame({'a': [1, 2, 3, 4], 'b': [5, 6, 7, 8]})
146    b = pd.DataFrame({'a': [1, 2, 3, 5], 'b': [5, 6, 7, 8]})
147
148    print(get_differences(a, b))
149    ```
150
151    Results:
152    ```text
153       a  b source
154    0  4  8   left
155    1  5  8  right
156    ```
157
158    ## Example 2:
159    ```python
160    import pandas as pd
161    from queries.compare import get_differences
162
163    a = pd.DataFrame({'a': [1, 2, 3, 4, 5], 'b': [5, 6, 7, 8, 9]})
164    b = pd.DataFrame({'a': [1, 2, 3, 5, 6], 'b': [5, 6, 7, 8, 1]})
165
166    print(get_differences(a, b))
167    ```
168
169    Results:
170    ```text
171       a  b source
172    0  5  9   left
173    1  4  8   left
174    2  5  8  right
175    3  6  1  right
176    ```
177
178    The number and naming of columns depends on the input dataframes.
179    The resulting dataframe will have all columns from input dataframes (
180    union of them) and additional column 'source'.
181
182    If there is no difference between two dataframes an empty DataFrame will be
183    returned (containing union of columns from original dataframes and
184    'source' column).
185
186    **This method raises `UserWarning` if two dataframes don't have same column
187    names!**
188
189    Args:
190        left (pd.DataFrame): Left dataframe.
191        right (pd.DataFrame): Right dataframe.
192        left_source (str): Source name for left dataframe.
193        right_source (str): Source name for right dataframe.
194
195    Returns:
196        pd.DataFrame: Dataframe with differences.
197    """
198    # prepare data
199    left, right = _prepare_comparison_data(left, right)
200    try:
201        # compare dataframes
202        # check if dataframes are equal
203        # if not, then raise AssertionError
204        pd.testing.assert_frame_equal(
205            left=left,
206            right=right,
207            # check exact data types for each column ELEMENT
208            # for example 3.0 = 3, but 3.0 != '3'
209            check_dtype=False,
210            # check index types
211            check_index_type=True,
212            # check column name and index type
213            check_column_type=True,
214            # check name of columns and index
215            check_names=True,
216            # check exact difference in numbers
217            # when True then: 3.000000000001 != 3.0
218            check_exact=False,
219            # rtol - relative tolerance
220            # abs((a - b) / max(a, b)) <= rtol
221            rtol=1e-5,
222            # default
223            # atol - absolute tolerance
224            # abs(a - b) <= atol
225            atol=1e-8,
226            # default
227
228            # combination od atol and rtol
229            # abs(a - b) <= (atol + rtol * abs(b))
230
231            # check datetime dtypes compatiblity
232            check_datetimelike_compat=True,
233            # check categorical columns exactly
234            check_categorical=True,
235            # ignore order
236            # (Note: index labels must match their respective rows
237            # (same as in columns) - same labels must be with the same data.)
238            check_like=True,
239        )
240        return pd.DataFrame([], columns=list(left.columns) + ['source'])
241    except AssertionError as e:
242        # find differences
243        diff = _find_differences(
244            left=left,
245            right=right,
246            left_source=left_source,
247            right_source=right_source
248        )
249
250        if len(set(left.columns).difference(set(right.columns))) != 0:
251            warnings.warn(f'Columns are not the same in both '
252                          f'dataframes.\n'
253                          f'["{left_source}"]: {list(left.columns)}\n'
254                          f'["{right_source}"]: {list(right.columns)}\n'
255                          f'Difference: '
256                          f'{set(left.columns).difference(set(right.columns))}',
257                          UserWarning
258                          )
259
260        return diff

Compare two dataframes. This method returns rows that are different between two dataframes, and also it returns rows that are in one dataframe but not in the other. To distinguish between dataframes, use left_source and right_source arguments, which will be added to the resulting dataframe as a new column 'source'.

It is recommended to standardize input data, meaning to round floats, convert all columns to the same data type (be careful with datetime). This method will to some degree ignore data type differences, but it is recommended to standardize data before comparison.

Example 1:

import pandas as pd
from queries.compare import get_differences

a = pd.DataFrame({'a': [1, 2, 3, 4], 'b': [5, 6, 7, 8]})
b = pd.DataFrame({'a': [1, 2, 3, 5], 'b': [5, 6, 7, 8]})

print(get_differences(a, b))

Results:

   a  b source
0  4  8   left
1  5  8  right

Example 2:

import pandas as pd
from queries.compare import get_differences

a = pd.DataFrame({'a': [1, 2, 3, 4, 5], 'b': [5, 6, 7, 8, 9]})
b = pd.DataFrame({'a': [1, 2, 3, 5, 6], 'b': [5, 6, 7, 8, 1]})

print(get_differences(a, b))

Results:

   a  b source
0  5  9   left
1  4  8   left
2  5  8  right
3  6  1  right

The number and naming of columns depends on the input dataframes. The resulting dataframe will have all columns from input dataframes ( union of them) and additional column 'source'.

If there is no difference between two dataframes an empty DataFrame will be returned (containing union of columns from original dataframes and 'source' column).

This method raises UserWarning if two dataframes don't have same column names!

Arguments:

left (pd.DataFrame): Left dataframe.
right (pd.DataFrame): Right dataframe.
left_source (str): Source name for left dataframe.
right_source (str): Source name for right dataframe.

Returns:

pd.DataFrame: Dataframe with differences.