Description
Code Sample, a copy-pastable example if possible
import pandas as pd
from pandas.api.types import CategoricalDtype
# Setup CategoricalDtype causing issue
cat_type1 = CategoricalDtype(categories=['A', 'B', 'C'], ordered=False)
cat_type2 = CategoricalDtype(categories=['C', 'B', 'A'], ordered=False)
print('Check dtypes are equivalent:', cat_type1 == cat_type2)
print()
# Test Data
df1 = pd.DataFrame({
'Foo': pd.Series(['A', 'B', 'C']).astype(cat_type1),
'Left': ['A0', 'B0', 'C0'],
})
df2 = pd.DataFrame({
'Foo': pd.Series(['C', 'B', 'A']).astype(cat_type2),
'Right': ['C1', 'B1', 'A1'],
})
print('df1:\n', df1)
print()
print('df2:\n', df2)
print()
# Issue happens here. Merges on codes instead of value.
# Notice, data from df2 isn't merged correctly.
df_merge = df1.merge(df2, on=['Foo'])
print('df_merge:\n', df_merge)
print()
results = """
Check dtypes are equivalent: True
df1:
Foo Left
0 A A0
1 B B0
2 C C0
df2:
Foo Right
0 C C1
1 B B1
2 A A1
df_merge:
Foo Left Right
0 A A0 C1
1 B B0 B1
2 C C0 A1
"""Problem description
Since upgrading from v0.20.3 to v0.22.0 I noticed data missing on my datasets. After a few hours debugging I narrowed it down to an issue involving merges that involve Categoricals. I downgraded to v0.20.3 to test my original code and didn't have the issue. I then tested on v.0.21.0 and noticed the issue was first introduced on that version.
While the example provided doesn't run in v0.20.3, it does highlight the bug and I think shows why its happening. Notice that the merge should give a result of
Foo Left Right
0 A A0 A1
1 B B0 B1
2 C C0 C1
instead of
Foo Left Right
0 A A0 C1 # Wrong
1 B B0 B1
2 C C0 A1 # Wrong
Expected Output
Foo Left Right
0 A A0 A1
1 B B0 B1
2 C C0 C1
Output of pd.show_versions()
[paste the output of pd.show_versions() here below this line]
INSTALLED VERSIONS
commit: None
python: 3.6.3.final.0
python-bits: 64
OS: Windows
OS-release: 10
machine: AMD64
processor: Intel64 Family 6 Model 79 Stepping 1, GenuineIntel
byteorder: little
LC_ALL: None
LANG: None
LOCALE: None.None
pandas: 0.22.0
pytest: 3.2.1
pip: 9.0.1
setuptools: 36.5.0.post20170921
Cython: 0.26.1
numpy: 1.12.1
scipy: 0.19.1
pyarrow: 0.7.1
xarray: None
IPython: 6.1.0
sphinx: 1.6.3
patsy: 0.4.1
dateutil: 2.6.1
pytz: 2017.2
blosc: None
bottleneck: 1.2.1
tables: 3.4.2
numexpr: 2.6.2
feather: None
matplotlib: 2.1.0
openpyxl: 2.4.8
xlrd: 1.1.0
xlwt: 1.3.0
xlsxwriter: 1.0.2
lxml: 4.1.0
bs4: 4.6.0
html5lib: 0.999999999
sqlalchemy: 1.1.13
pymysql: None
psycopg2: None
jinja2: 2.9.6
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None