ENH: Merge DataFrame and Series using `on` (GH21220)
State: closed

- [x] closes #21220 
- [x] tests added / passed
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry


Labels:
name: Enhancement
name: Reshaping
description: Concat, Merge/Join, Stack/Unstack, Explode

Comments:
Not sure if any another changes / functionality changes are required

Also, not sure whether this PR title  should go under 'CLN' or 'ENH'?
Looks like we had some tests that asserted this did not work: https://travis-ci.org/pandas-dev/pandas/jobs/384393959#L2444. Can you go through those and see what the original motivation was?

Will also need a release note in 0.24.0
cc @jmmease
On thing to nail down: what's the type of `merge(Series, Series)`? Do we want `merge` to *always* return a DataFrame?
Thanks @TomAugspurger 

> Looks like we had some tests that asserted this did not work: ... Can you go through those and see what the original motivation was?

`test_join_on_fails_with_wrong_object_type` in`pandas/tests/reshape/merge/test_join.py` was failing as it was checking that a ValueError is raised on merging Series with DataFrame - have now edited out the portion for Series from that test

> Will also need a release note in 0.24.0

will make changes to release note once the tests run successfully and also there is consensus on whether merge should _always_ return a DataFrame
 
>  On thing to nail down: what's the type of merge(Series, Series)? Do we want merge to always return a DataFrame?

That would only be a question when merging on the values and not the index I think? 
But personally I would go for consistent output of always DataFrame.

We will need to add tests for this as well.
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/21223?src=pr&el=h1) Report
> Merging [#21223](https://codecov.io/gh/pandas-dev/pandas/pull/21223?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/1c2844aca8e3dad7373576d5de40ee6810f7e5ad?src=pr&el=desc) will **decrease** coverage by `<.01%`.
> The diff coverage is `75%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/21223/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/21223?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #21223      +/-   ##
==========================================
- Coverage   91.84%   91.83%   -0.01%     
==========================================
  Files         153      153              
  Lines       49505    49509       +4     
==========================================
+ Hits        45466    45469       +3     
- Misses       4039     4040       +1
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.23% <75%> (-0.01%)` | :arrow_down: |
| #single | `41.87% <0%> (-0.01%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/21223?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/reshape/merge.py](https://codecov.io/gh/pandas-dev/pandas/pull/21223/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvcmVzaGFwZS9tZXJnZS5weQ==) | `94.14% <75%> (-0.12%)` | :arrow_down: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/21223?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/21223?src=pr&el=footer). Last update [1c2844a...b09ffb7](https://codecov.io/gh/pandas-dev/pandas/pull/21223?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/21223?src=pr&el=h1) Report
> :exclamation: No coverage uploaded for pull request base (`master@27ebb3e`). [Click here to learn what that means](https://docs.codecov.io/docs/error-reference#section-missing-base-commit).
> The diff coverage is `100%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/21223/graphs/tree.svg?token=eZ4WkYLtcO&width=650&src=pr&height=150)](https://codecov.io/gh/pandas-dev/pandas/pull/21223?src=pr&el=tree)

```diff
@@            Coverage Diff            @@
##             master   #21223   +/-   ##
=========================================
  Coverage          ?      92%           
=========================================
  Files             ?      168           
  Lines             ?    50603           
  Branches          ?        0           
=========================================
  Hits              ?    46555           
  Misses            ?     4048           
  Partials          ?        0
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.4% <100%> (?)` | |
| #single | `42.19% <10%> (?)` | |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/21223?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/21223/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.19% <ø> (ø)` | |
| [pandas/core/reshape/merge.py](https://codecov.io/gh/pandas-dev/pandas/pull/21223/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvcmVzaGFwZS9tZXJnZS5weQ==) | `94.16% <100%> (ø)` | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/21223?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/21223?src=pr&el=footer). Last update [27ebb3e...d099fd6](https://codecov.io/gh/pandas-dev/pandas/pull/21223?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

also need a whatsnew note
Thanks @TomAugspurger 
Done - moved the whatsnew note to 0.24.0
As this PR was removed from 0.24.0 milestone had earlier added whatsnew note to 0.23.1 
Are any additional changes needed on this PR? Thanks
@jreback please let me know if any other edits are needed on this one. Thanks.
Could you update the docstring `_merge_doc` in `pandas/core/frame.py` to reflect the ability to merge DataFrames and named series? `merging.rst` also has a few references to joining DataFrame objects, that should be updated to DataFrame or named series.
Looks good otherwise.
@TomAugspurger Thanks - made the updates. Any other edits needed?
Waiting for CI to finish.

On Wed, Jun 20, 2018 at 10:29 AM, Kalyan Gokhale <notifications@github.com>
wrote:

> @TomAugspurger <https://github.com/TomAugspurger> Thanks - made the
> updates. Any other edits needed?
>
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub
> <https://github.com/pandas-dev/pandas/pull/21223#issuecomment-398793132>,
> or mute the thread
> <https://github.com/notifications/unsubscribe-auth/ABQHIq9_4MXB4Hm-4Ppp7n5zLUmuNx_Jks5t-mpFgaJpZM4UPUDU>
> .
>

Any additional work needed on this? Thanks
There's a merge conflict in `core/frame.py` now. Can you merge in master and resolve that, and let us know if you see that all the CI pass?

If you can't get to fixing the merge conflict, I'll push a fix when I get a chance.
@TomAugspurger resolved the merge conflict - all CI tests passed. Any other changes needed? Thanks
@jorisvandenbossche made the suggested change - all CI tests passed. Thanks
@KalyanGokhale Thanks a lot for this PR!