BUG/API: .merge() and .join() on category dtype columns will now preserve category dtype
State: closed

closes #10409

```
    before     after       ratio
  [6d2293f7] [c352573f]
-  410.11ms   260.46ms      0.64  join_merge.MergeCategoricals.time_merge_cat

SOME BENCHMARKS HAVE CHANGED SIGNIFICANTLY.
```

Labels:
name: Bug
name: Categorical
description: Categorical Data Type

Comments:
cc @amelio-vazquez-reina  
cc @psychemedia
cc @watercrossing 

as you have posted issues about this in the past.
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/15321?src=pr&el=h1) Report
> Merging [#15321](https://codecov.io/gh/pandas-dev/pandas/pull/15321?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/5dee1f18ac2a06d38e4bb3800eee11424ec25ca1?src=pr&el=desc) will **increase** coverage by `<.01%`.
> The diff coverage is `100%`.


```diff
@@            Coverage Diff             @@
##           master   #15321      +/-   ##
==========================================
+ Coverage   91.01%   91.02%   +<.01%     
==========================================
  Files         143      143              
  Lines       49376    49338      -38     
==========================================
- Hits        44941    44909      -32     
+ Misses       4435     4429       -6
```


| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/15321?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/tools/merge.py](https://codecov.io/gh/pandas-dev/pandas/compare/5dee1f18ac2a06d38e4bb3800eee11424ec25ca1...16348bea981fbc62cca330c9c1995f1c53ce7bd7?src=pr&el=tree#diff-cGFuZGFzL3Rvb2xzL21lcmdlLnB5) | `93.61% <100%> (+1.5%)` | :white_check_mark: |
| [pandas/core/internals.py](https://codecov.io/gh/pandas-dev/pandas/compare/5dee1f18ac2a06d38e4bb3800eee11424ec25ca1...16348bea981fbc62cca330c9c1995f1c53ce7bd7?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvaW50ZXJuYWxzLnB5) | `93.64% <100%> (ø)` | :white_check_mark: |
| [pandas/io/gbq.py](https://codecov.io/gh/pandas-dev/pandas/compare/5dee1f18ac2a06d38e4bb3800eee11424ec25ca1...16348bea981fbc62cca330c9c1995f1c53ce7bd7?src=pr&el=tree#diff-cGFuZGFzL2lvL2dicS5weQ==) | `25% <0%> (-58.34%)` | :x: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/compare/5dee1f18ac2a06d38e4bb3800eee11424ec25ca1...16348bea981fbc62cca330c9c1995f1c53ce7bd7?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.87% <0%> (-0.1%)` | :x: |
| [pandas/sparse/array.py](https://codecov.io/gh/pandas-dev/pandas/compare/5dee1f18ac2a06d38e4bb3800eee11424ec25ca1...16348bea981fbc62cca330c9c1995f1c53ce7bd7?src=pr&el=tree#diff-cGFuZGFzL3NwYXJzZS9hcnJheS5weQ==) | `91.42% <0%> (-0.05%)` | :x: |
| [pandas/indexes/base.py](https://codecov.io/gh/pandas-dev/pandas/compare/5dee1f18ac2a06d38e4bb3800eee11424ec25ca1...16348bea981fbc62cca330c9c1995f1c53ce7bd7?src=pr&el=tree#diff-cGFuZGFzL2luZGV4ZXMvYmFzZS5weQ==) | `96.1% <0%> (-0.03%)` | :x: |
| [pandas/sparse/frame.py](https://codecov.io/gh/pandas-dev/pandas/compare/5dee1f18ac2a06d38e4bb3800eee11424ec25ca1...16348bea981fbc62cca330c9c1995f1c53ce7bd7?src=pr&el=tree#diff-cGFuZGFzL3NwYXJzZS9mcmFtZS5weQ==) | `96.69% <0%> (ø)` | :white_check_mark: |
| [pandas/core/generic.py](https://codecov.io/gh/pandas-dev/pandas/compare/5dee1f18ac2a06d38e4bb3800eee11424ec25ca1...16348bea981fbc62cca330c9c1995f1c53ce7bd7?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ2VuZXJpYy5weQ==) | `96.25% <0%> (ø)` | :white_check_mark: |
| [pandas/util/testing.py](https://codecov.io/gh/pandas-dev/pandas/compare/5dee1f18ac2a06d38e4bb3800eee11424ec25ca1...16348bea981fbc62cca330c9c1995f1c53ce7bd7?src=pr&el=tree#diff-cGFuZGFzL3V0aWwvdGVzdGluZy5weQ==) | `81.11% <0%> (+0.18%)` | :white_check_mark: |
| ... and [2 more](https://codecov.io/gh/pandas-dev/pandas/pull/15321?src=pr&el=tree-more) | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/15321?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/15321?src=pr&el=footer). Last update [5dee1f1...3671dad](https://codecov.io/gh/pandas-dev/pandas/compare/5dee1f18ac2a06d38e4bb3800eee11424ec25ca1...3671dad6b2596ceb07f1c512d0551930d1005182?src=pr&el=footer&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).
@jorisvandenbossche if you'd have a look (mainly at the tests).
Do you want to add an asv for this?

Probably a separate issue, but should we be warning about the implicit conversion to `object` when the categories don't match?
@chris-b1 yes I should add an asv. that's easy.

> Probably a separate issue, but should we be warning about the implicit conversion to object when the categories don't match?

hmm, we don't do this generally on merges, though maybe we should. Can you open another issue (with some examples), even things like string/int merging should be warn/raised I think. Maybe need a ``errors='warn|raise'`` generally in merge.
@chris-b1 added benchmark.
so after looking at this there are some cases that we ought to consider.

assume we are joining with ``left`` and ``right`` with a single ``on`` (it can be the index or not, but ultimately these come down to a left and right on a column (single) for now.

- ``how='left'``: will be the dtype of the left, regardless of right
- ``how='right'``: will be the dtype of the right, regardless of left
- ``how='inner'``: if left and right are match on dtype (IOW if categorical then categories & ordered must match), OR right is ``object``, then can return ``categorical`` of left flavor, otherwise must be ``object``
- ``how='outer'``: I think same as inner, these must match exactly (or be ``object`` that has categories that are fully contained within ``left``).

So I think these follow our rules exactly from [here, on the bottom](http://pandas-docs.github.io/pandas-docs-travis/categorical.html#concatenation)

Ah, yes, I didn't consider the different join operations. 

> how='inner': if left and right are match on dtype (IOW if categorical then categories & ordered must match), OR right is object, then can return categorical of left flavor, otherwise must be object

For inner and outer, I would follow the same rules as `concat`: this means only retaining category dtype if categories and orderedness match exactly, so for both left and right frame. 
(the "be object that has categories that are fully contained within the other" rule is something we removed from `concat`, so I wouldn't introduce that again?)
@jorisvandenbossche ok revised. If you'd play with this when you have a chance.
@jorisvandenbossche if you have a chance
@jorisvandenbossche if you'd have a look. going to merge soon.
Some feedback. I had this -- 1.5 year old -- function that I have used to compensate for the bug

```python
# NOTE: This is a temporary fix due to bug
# https://github.com/pydata/pandas/issues/10409
# Remove when that bug is fixed
import pandas.api.types as pdtypes

def preserve_categories(ref, other):
    for col in ref.columns & other.columns:
        if pdtypes.is_categorical_dtype(ref[col]):
            other[col] = other[col].astype(
                'category', categories=ref[col].cat.categories)
```

I have removed it and my tests pass.