Repository: pandas-dev/pandas
Pull request: ENH: Merge DataFrame and Series using `on` (GH21220)
- [x] closes #21220 
- [x] tests added / passed
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry


Problem statement:
Merge DataFrame and Series using `on`
Now that we support merging `on` combination of column names and index levels, this should work

```python
In [40]: a = pd.DataFrame({"A": [1, 2, 3, 4]}, index=pd.MultiIndex.from_product([['a', 'b'], [0, 1]], names=['outer', 'inner']))

In [41]: b = pd.Series([1, 2, 3, 4], index=pd.MultiIndex.from_product([['a', 'b'], [1, 2]], names=['outer', 'inner']), name='B')

In [42]: pd.merge(a, b, on=['outer', 'inner'])
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-42-337c5a9e9f8f> in <module>()
----> 1 pd.merge(a, b, on=['outer', 'inner'])

~/Envs/dask-dev/lib/python3.6/site-packages/pandas/core/reshape/merge.py in merge(left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)
     58                          right_index=right_index, sort=sort, suffixes=suffixes,
     59                          copy=copy, indicator=indicator,
---> 60                          validate=validate)
     61     return op.get_result()
     62

~/Envs/dask-dev/lib/python3.6/site-packages/pandas/core/reshape/merge.py in __init__(self, left, right, how, on, left_on, right_on, axis, left_index, right_index, sort, suffixes, copy, indicator, validate)
    524         if not isinstance(right, DataFrame):
    525             raise ValueError('can not merge DataFrame with instance of '
--> 526                              'type {right}'.format(right=type(right)))
    527
    528         if not is_bool(left_index):

ValueError: can not merge DataFrame with instance of type <class 'pandas.core.series.Series'>
```

Should be the same as

```python
In [39]: pd.merge(a, b.to_frame(), on=['outer', 'inner'])
Out[39]:
             A  B
outer inner
a     1      2  1
b     1      4  3
```

Hints from issue discussion:

Commit message: Updated comments in test

GH21220

Patch to review:
diff --git a/pandas/core/reshape/merge.py b/pandas/core/reshape/merge.py
index 4d8897fb7c811..ed1e184d9915a 100644
--- a/pandas/core/reshape/merge.py
+++ b/pandas/core/reshape/merge.py
@@ -11,7 +11,7 @@
 import pandas.compat as compat
 
 from pandas import (Categorical, DataFrame,
-                    Index, MultiIndex, Timedelta)
+                    Index, MultiIndex, Timedelta, Series)
 from pandas.core.arrays.categorical import _recode_for_categories
 from pandas.core.frame import _merge_doc
 from pandas.core.dtypes.common import (
@@ -492,6 +492,10 @@ def __init__(self, left, right, how='inner', on=None,
                  left_index=False, right_index=False, sort=True,
                  suffixes=('_x', '_y'), copy=True, indicator=False,
                  validate=None):
+        if isinstance(left, Series):
+            left = left.to_frame()
+        if isinstance(right, Series):
+            right = right.to_frame()
         self.left = self.orig_left = left
         self.right = self.orig_right = right
         self.how = how
diff --git a/pandas/tests/reshape/merge/test_join.py b/pandas/tests/reshape/merge/test_join.py
index 1b8f3632d381c..b2ba93aa4813f 100644
--- a/pandas/tests/reshape/merge/test_join.py
+++ b/pandas/tests/reshape/merge/test_join.py
@@ -230,7 +230,10 @@ def test_join_on_fails_with_different_column_counts(self):
 
     def test_join_on_fails_with_wrong_object_type(self):
         # GH12081
-        wrongly_typed = [Series([0, 1]), 2, 'str', None, np.array([0, 1])]
+        """ GH21220 - merging of Series and DataFrame is now allowed
+            Edited the test to remove the Series object from 'wrongly_typed'
+        """
+        wrongly_typed = [2, 'str', None, np.array([0, 1])]
         df = DataFrame({'a': [1, 1]})
 
         for obj in wrongly_typed:
diff --git a/pandas/tests/reshape/merge/test_merge.py b/pandas/tests/reshape/merge/test_merge.py
index 8e639edd34b18..2040934b5cc2c 100644
--- a/pandas/tests/reshape/merge/test_merge.py
+++ b/pandas/tests/reshape/merge/test_merge.py
@@ -1864,3 +1864,24 @@ def test_merge_index_types(index):
         OrderedDict([('left_data', [1, 2]), ('right_data', [1.0, 2.0])]),
         index=index)
     assert_frame_equal(result, expected)
+
+
+def test_merge_series():
+    # GH 21220
+    a = pd.DataFrame({"A": [1, 2, 3, 4]},
+                     index=pd.MultiIndex.from_product([['a', 'b'], [0, 1]],
+                     names=['outer', 'inner']))
+    b = pd.Series([1, 2, 3, 4],
+                  index=pd.MultiIndex.from_product([['a', 'b'], [1, 2]],
+                  names=['outer', 'inner']), name='B')
+    expected = pd.DataFrame({"A": [2, 4], "B": [1, 3]},
+                            index=pd.MultiIndex.from_product([['a', 'b'], [1]],
+                            names=['outer', 'inner']))
+
+    # Test merge with a DataFrame and a Series 'converted-to-DataFrame' object
+    result = pd.merge(a, b.to_frame(), on=['outer', 'inner'])
+    tm.assert_frame_equal(result, expected)
+
+    # Test merge with a DataFrame and a Series object
+    result = pd.merge(a, b, on=['outer', 'inner'])
+    tm.assert_frame_equal(result, expected)


Code review comments:
diff_hunk: @@ -492,6 +492,10 @@ def __init__(self, left, right, how='inner', on=None,
                  left_index=False, right_index=False, sort=True,
                  suffixes=('_x', '_y'), copy=True, indicator=False,
                  validate=None):
+        if isinstance(left, Series):
+            left = left.to_frame()
+        if isinstance(right, Series):
+            right = right.to_frame()
path: pandas/core/reshape/merge.py
text: @user1:
need to validate that the input is a Series or Dataframe and nothing else 

@author:
Thanks - Had initially addeded these validations at 525 and 528 and also before 541 (if memory serves right) on my local machine - but then it fails at 571...

Thus after trying to fix the same issue (checking if input is Series or DataFrame) at multiple places, the current change seemed like the most logical fix as we are changing the type of incoming Series object to DataFrame and then avoiding any susequent changes to the code....

However open to suggestions...

@user1:
this is ok, but what I mean is validation that you have a Series/DataFrame and nothing else. (and a test to go with that), something like

```
left = validate_operand(left)
right = validate_operand(right)


......

def validate_operand(obj):
     if isinstance(obj, DataFrame): 
         return obj
     elif isinstance(obj, Series):
        return obj.to_frame()
     raise ValueError(.....)
```

@user1:
also need to test this with left_index=True, right_index=True (which should be fine). And a test that a Series w/o a name raises (it will convert just fine, but then you would have to specify the on in a funny way)

@author:
Thanks for the review - done.
also raises a ValueError for Series w/o a name