<commit_before>datacompare
------------------

Background
---------------

We work in Data Warehousing with SSIS for ETL and the options for easy-to-build and interact with data-comparison for testing and exploration purposes are limited.
The SQL unit test with C# does seem promising, but only for automated testing, not exploratory comparing.

Features
---------------
    - Compare data from SQL (via pyodbc), txt or DataFrame
    - Get rows in one set not in the other and vice versa and view in session or dump to text
    - Show values different in every column for matched rows and view in session or dump to text

Caution
---------------
I am increasing my skills in python by leaps and bounds (thanks Talk Python to Me!) but am no expert. So I can't promise this will work in anything besides
latest version of python and with MS SQL, texts or sqlite.

License
---------------
MIT

Usage
---------------

    - PIP install from GitHub > pip install git+git://github.com/joshmorel/datacompare.git@master
    - Open an interactive python interpreter and set working directory to something which contains these files https://github.com/joshmorel/datacompare/tree/master/docs/example
    - Run the following code (assume sqlite 3 ODBC driver is installed)

.. code-block:: python

    import datacompare as dc
    sql_texts = dc.get_sql_texts()
    connection_string = dc.get_connection_info('connection_file.ini', 'salesdb')
    left = dc.CompareDataFrame.from_sql(sql_texts['example_sales_new'],
                                    connection_string,params=['2015-04-01', '2016-04-02'])
    right = dc.CompareDataFrame.from_sql(sql_texts['example_sales_old'],
                                     connection_string,params=['2015-04-01', '2016-04-02'])
    in_left_not_right, in_right_not_left = left.get_member_difference(right, limit=2, to_file=False)
    value_differences = left.get_value_difference(right, to_file=False, limit=2, value_precision=2)
    print('Rows in left not in right\n {}\
    \n\nRows in right not in left\n{}\
    \n\nValue differences\n {}'.format(in_left_not_right,in_right_not_left,value_differences))


Output
---------------

    Rows in left not in right
                                 date_product  sales_quantity  sales_amount
    date_product
    2015-05-05 - Helmet  2015-05-05 - Helmet             4.0           1.0

    Rows in right not in left
                                  date_product  sales_quantity  sales_amount
    date_product
    2015-04-04 - Bicycle  2015-04-04 - Bicycle               4       1001.64
    2015-04-04 - Helmet    2015-04-04 - Helmet               8        216.00

    Value differences
                           values_different   sales_amount_2 sales_quantity_1
    date_product
    2015-04-01 - Bicycle                 1  2504.1 | 2408.0             10.0
    2015-04-02 - Helmet                  2       nan | 27.0        nan | 1.0




Future Direction
------------------
    - Automated ETL testing functionality
    - Interactive command line session with iter function to generate chunks of rows<commit_msg>Update readme for output<commit_after>datacompare
------------------

Background
---------------

We work in Data Warehousing with SSIS for ETL and the options for easy-to-build and interact with data-comparison for testing and exploration purposes are limited.
The SQL unit test with C# does seem promising, but only for automated testing, not exploratory comparing.

Features
---------------
    - Compare data from SQL (via pyodbc), txt or DataFrame
    - Get rows in one set not in the other and vice versa and view in session or dump to text
    - Show values different in every column for matched rows and view in session or dump to text

Caution
---------------
I am increasing my skills in python by leaps and bounds (thanks Talk Python to Me!) but am no expert. So I can't promise this will work in anything besides
latest version of python and with MS SQL, texts or sqlite.

License
---------------
MIT

Usage
---------------

    - PIP install from GitHub > pip install git+git://github.com/joshmorel/datacompare.git@master
    - Open an interactive python interpreter and set working directory to something which contains these files https://github.com/joshmorel/datacompare/tree/master/docs/example
    - Run the following code (assume sqlite 3 ODBC driver is installed)

.. code-block:: python

    import datacompare as dc
    sql_texts = dc.get_sql_texts()
    connection_string = dc.get_connection_info('connection_file.ini', 'salesdb')

    left = dc.CompareDataFrame.from_sql(sql_texts['example_sales_new'],
                                    connection_string,params=['2015-04-01', '2016-04-02'])
    right = dc.CompareDataFrame.from_sql(sql_texts['example_sales_old'],
                                     connection_string,params=['2015-04-01', '2016-04-02'])

    value_differences = left.get_value_difference(right, to_file=False, limit=2, value_precision=2)

    print('Rows in left not in right\n {}\
    \n\nRows in right not in left\n{}\
    \n\nValue differences\n {}'.format(in_left_not_right,in_right_not_left,value_differences))

Will show:

.. code-block:: python

    Rows in left not in right
                                 date_product  sales_quantity  sales_amount
    date_product
    2015-05-05 - Helmet  2015-05-05 - Helmet             4.0           1.0

    Rows in right not in left
                                  date_product  sales_quantity  sales_amount
    date_product
    2015-04-04 - Bicycle  2015-04-04 - Bicycle               4       1001.64
    2015-04-04 - Helmet    2015-04-04 - Helmet               8        216.00

    Value differences
                           values_different   sales_amount_2 sales_quantity_1
    date_product
    2015-04-01 - Bicycle                 1  2504.1 | 2408.0             10.0
    2015-04-02 - Helmet                  2       nan | 27.0        nan | 1.0

Future Direction
------------------
    - Automated ETL testing functionality
    - Interactive command line session with iter function to generate chunks of rows
{'fraction_non_alphanumeric': 0.10227831503032289, 'fraction_numerical': 0.04540239305031962, 'mean_word_length': 2.145360824742268, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 5, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '553983', 'n_tokens_mistral': 1885, 'n_tokens_neox': 1646, 'n_words': 580}