<jupyter_start><jupyter_text>This is a quick note showing how to compare two data frames usingthe [data algebra](https://github.com/WinVector/data_algebra).  The question is: which rowsare in one data frame and not the otherFirst let's set up our example<jupyter_code># import packages
import string
import numpy
import numpy.random
import pandas

from data_algebra.data_ops import *
from data_algebra.cdata import *
import data_algebra.BigQuery
import data_algebra.SQLite


# build synthetic example data

# seed the pseudo-random generator for repeatability
numpy.random.seed(1999)

# choose our simulated number of observations
n_obs = 100
symbols = list(string.ascii_lowercase)

d1 = pandas.DataFrame({
    'group': numpy.random.choice(symbols, size=n_obs, replace=True),
})

d2 = pandas.DataFrame({
    'group': numpy.random.choice(symbols, size=n_obs, replace=True),
})<jupyter_output><empty_output><jupyter_text>Our example question is: which rows are unique to `d1` and which are unique to `d2`.Let's define our grouping columns and proceed.<jupyter_code># which columns we consider to be row keys
# can be more than one column
grouping_columns = ['group']<jupyter_output><empty_output><jupyter_text>Our plan is simple, we count how many rows each table hasfor a given key and then join the results together for comparison.The data algebra notations we will use include:  * `descr()`. `descr(name=value)` is a notation that builds a description     of a the Pandas data frame "`value`" and refers to this table by the name "`name`".  * `.project()`. The project an aggregation that produces one row per distinct combination     of values in the grouping columns. The grouping columns are copied into the result,     and we can calculate additional columns, such as the row count `(1).sum()`.  * `.natural_join()` joins two tables on the keys specified by "`by`".  * `.extend()` allows us to calculate new columns. In this case we are using `coalece()`    to replace missing values produced by the join with zeros. The missing values are    exactly the key combinations where one table has rows and the other does not.<jupyter_code>summary_ops = (
    descr(d1=d1)
        .project(
            {'d1_count': '(1).sum()'},
            group_by=grouping_columns)
        .natural_join(
            b=descr(d2=d2)
                .project(
                    {'d2_count': '(1).sum()'},
                    group_by=grouping_columns),
            by=grouping_columns,
            jointype='full')
        .extend({
            'd1_count': 'd1_count.coalesce(0)',
            'd2_count': 'd2_count.coalesce(0)',
            })
)<jupyter_output><empty_output><jupyter_text>Once we have our intended set of operations we can execute them against our tables bysupplying data for each named table using the `.eval()` method.<jupyter_code>summary_table = summary_ops.eval({'d1': d1, 'd2': d2})

summary_table<jupyter_output><empty_output><jupyter_text>(Note: data algebra can run the exact same command in many databases by automatic translation to SQLby the `.to_sql()` method.) From the resulting summary it is easy to see which columns are unique to one table or another.We can zero in on these columns by selecting the rows where one of the counts is zero. Newcommands we use in this example include:  * `data()`. `data()` is a notation that captures a description of a Pandas data frame *and*    a copy of the data. Notice data described in this way doesn't need a name. This    is because as we have the data, we don't need a name to later look up data with.  * `ex()`. `ex()` is a wrapper that takes a data algebra pipeline and executes it with the    captured data.  * `.select_rows()` picks rows matching the logical conditions we specify on the columns.  * `.order_rows()` sorts the Pandas data frame by the values in the named columns.<jupyter_code>ex(
    data(summary_table)
        .select_rows('(d1_count <= 0) | (d2_count <= 0)')
        .order_rows(grouping_columns)
)<jupyter_output><empty_output><jupyter_text>Notice throughout we used the variable `grouping_columns` instead of explicitly naming thecolumns. That means this code is re-usable and could easily be converted into a utility function.And that is it. We have worked through how to easily compare two Pandas data frames using the data algebra.  Appendix, the same query work in BigQueryFor this demo we show the same operations operatingin the Google BigQuery database. Note: we didn't useSQLite (as inour [previous example](https://win-vector.com/2021/10/03/how-to-compare-two-tables-using-the-data-algebra/))both for variety of examples and because SQLite doesn't currently support fulljoins ([ref](https://www.sqlitetutorial.net/sqlite-full-outer-join/), we have an example ofhow to simulate a full join [here](https://github.com/WinVector/data_algebra/blob/main/Examples/GettingStarted/simulating_full_join.ipynb)).<jupyter_code>db_handle = data_algebra.BigQuery.example_handle()

# inserting just for the example, usually for databases the
# data is already in the database
db_handle.insert_table(d1, table_name='d1', allow_overwrite=True)
db_handle.insert_table(d2, table_name='d2', allow_overwrite=True)

all_ops = (
    summary_ops
        .select_rows('(d1_count <= 0) | (d2_count <= 0)')
        # move order to read-back request, as it isn't needed here
)

db_handle.drop_table('compare_result')
db_handle.execute(
    f'CREATE TABLE {db_handle.db_model.table_prefix}.compare_result AS {db_handle.to_sql(all_ops)}')

read_ops = (
    db_handle.describe_table(f'compare_result')
        .order_rows(grouping_columns)
)
db_handle.read_query(read_ops)
# clean up
db_handle.close()<jupyter_output><empty_output><jupyter_text>SQLite example<jupyter_code>sqlite_handle = data_algebra.SQLite.example_handle()

print(sqlite_handle.to_sql(summary_ops))
# clean up
sqlite_handle.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09296013570822731, 'fraction_numerical': 0.008481764206955046, 'mean_word_length': 4.3893967093235835, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 29, 'https://': 4, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '421086', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 1793, 'n_tokens_neox': 1718, 'n_words': 629, 'path': 'Examples/GettingStarted/comparing_two_dataframes.ipynb', 'repo_name': 'WinVector/data_algebra', 'size': '5895'}