ENH: merge set validation (#29649)
State: closed

Added new feature to pandas merge to validate "set-theory" relationship
between left and right dataframes. Updated docs and tests for new
feature.

- [x] closes #xxxx
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [ ] whatsnew entry


Labels:

Comments:
Hello @robertdavidwest! Thanks for updating this PR. We checked the lines you've touched for [PEP 8](https://www.python.org/dev/peps/pep-0008) issues, and found:







There are currently no PEP 8 issues detected in this Pull Request. Cheers! :beers: 

##### Comment last updated at 2019-11-15 22:48:32 UTC
I would have attempted the "whatsnew" entry but I didn't really understand what was being asked [here](https://pandas.pydata.org/pandas-docs/stable/development/contributing.html#documenting-your-code). If anyone is able to describe exactly what should be done here. I'd be happy to add it to the Pull Request. 
@jreback use case is. Suppose you have two dataframes:
```
df1 = pd.DataFrame({"key": [1,2,3,4], "only_1": ["a", "b", "c", "c"]}) 
df2 = pd.DataFrame({"key": [1,2,3], "only_2": ["d", "e", "f"]})
```
Suppose that you want to perform an outer merge on these dataframes on `key` and that you want to validate that there are no records in the second dataframe that do not have a match in the first. You could achieve this using existing features as follows:

```
result = pd.merge(df1, df2, on='key', how='outer', indicator=True)
assert(set(result._merge) == {'left_only', 'both'})
result = result.drop(axis=1, labels="_merge")
```
This is pretty clunky especially if you do it often in your merges. The intended new feature is allow this to be expressed within the call to the merge function as follows:

```
result = pd.merge(df1, df2, on='key', how='outer', indicator_assert_set={'left_only', 'both'})
```

it is analogous to a feature present in the Stata Merge: [see docs](https://www.stata.com/manuals13/dmerge.pdf): `assert(results) specify required match results`). 

I'm guessing Stata was the inspiration when the `validate` parameter was added to the `pd.merge` function, as you can see it is present in these docs.  
> This is pretty clunky especially if you do it often in your merges. The intended new feature is allow this to be expressed within the call to the merge function as follows:

so write a function and call it when you want

every added piece of api adds mental overhead to the user and of course requires upkeep on the project. simpler is always better.



You could have said the exact same thing about the ‘validate’ parameter. I
use to use a wrapper function around ‘pd.merge’ until that feature was
added.

On Fri, Nov 15, 2019 at 7:07 PM Jeff Reback <notifications@github.com>
wrote:

> This is pretty clunky especially if you do it often in your merges. The
> intended new feature is allow this to be expressed within the call to the
> merge function as follows:
>
> so write a function and call it when you want
>
> every added piece of api adds mental overhead to the user and of course
> requires upkeep on the project. simpler is always better.
>
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub
> <https://github.com/pandas-dev/pandas/pull/29651?email_source=notifications&email_token=AAUD6LGCE2B2C34WFDZSUL3QT42VFA5CNFSM4JOBSBU2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEEHC4HY#issuecomment-554577439>,
> or unsubscribe
> <https://github.com/notifications/unsubscribe-auth/AAUD6LEPFUKZ6B2UMTPY4KTQT42VFANCNFSM4JOBSBUQ>
> .
>

indicator could not trivially be computed from the results
where this can 
Yes. I was referencing the ‘validate’ field. That’s pretty trivial right?
Just checking the indices. But that helpful little parameter goes a long
way I think. Especially in terms encouraging people to think more about the
importance of these kinds of things when merging, settings higher standard
for what should be considered when merging. Anyways, just my opinion I
guess. Appreciate your time.

On Fri, Nov 15, 2019 at 7:14 PM Jeff Reback <notifications@github.com>
wrote:

> indicator could not trivially be computed from the results
> where this can
>
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub
> <https://github.com/pandas-dev/pandas/pull/29651?email_source=notifications&email_token=AAUD6LEDPYHADCLVZNSASJDQT43PRA5CNFSM4JOBSBU2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEEHDIJQ#issuecomment-554578982>,
> or unsubscribe
> <https://github.com/notifications/unsubscribe-auth/AAUD6LDTS4DSCDMSGYVLHHLQT43PRANCNFSM4JOBSBUQ>
> .
>

Hi @robertdavidwest and thanks for the PR. I'm also though -1 on this addition. Note that we have a very high bar for adding to the API at this point, so if you have other ideas in the future might be best to open an issue for discussion first, though hope at the very least you learnt more about the pandas internals through this.