BUG: merge with categoricals does not preserve categories dtype

[amelio-vazquez-reina]

xref #14351

None of the following merge operations retain the category types. Is this expected? How can I keep them?

Merging on a category type:

Consider the following:

A = pd.DataFrame({'X': np.random.choice(['foo', 'bar'],size=(10,)), 
                  'Y': np.random.choice(['one', 'two', 'three'], size=(10,))})
A['X'] = A['X'].astype('category')

B = pd.DataFrame({'X': np.random.choice(['foo', 'bar'],size=(10,)), 
                  'Z': np.random.choice(['jjj', 'kkk', 'sss'], size=(10,))})
B['X'] = B['X'].astype('category')

if I do the merge, we end up with:

> pd.merge(A, B, on='X').dtypes 
X    object
Y    object
Z    object
dtype: object

Merging on a non-category type:

A = pd.DataFrame({'X': np.random.choice(['foo', 'bar'],size=(10,)), 
                  'Y': np.random.choice(['one', 'two', 'three'], size=(10,))})
A['Y'] = A['Y'].astype('category')

B = pd.DataFrame({'X': np.random.choice(['foo', 'bar'],size=(10,)), 
                  'Z': np.random.choice(['jjj', 'kkk', 'sss'], size=(10,))})
B['Z'] = B['Z'].astype('category')

if I do the merge, we end up with:

pd.merge(A, B, on='X').dtypes
X    object
Y    object
Z    object
dtype: object

[jreback]

ok, this works for a simple concat, but merging will not preserve the categoricals. Needs some post processing logic. Keep in mind this will ONLY work for identically categorized blocks. I think we originally left this out and didn't automagically recategorize, but let me see what I can do.

[wikiped]

@jreback

Keep in mind this will ONLY work for identically categorized blocks.

Do you mean that attempting to merge on Categorical with one of categories missing should normally fail like this:

A = pd.DataFrame({'X': np.random.choice(['foo', 'bar'],size=(5,)), 
                  'Y': np.random.choice(['one', 'two', 'three'], size=(5,))})
A['X'] = A['X'].astype('category')

B = pd.DataFrame({'X': np.random.choice(['foo', 'baz'],size=(5,)),   # baz instead of bar
                  'Z': np.random.choice(['jjj', 'kkk', 'sss'], size=(5,))})
B['X'] = B['X'].astype('category')

pd.merge(A, B, on='X', how='outer')

Returning error:

d:\Anaconda\envs\py2k\lib\site-packages\pandas\core\common.pyc in take_nd(arr, indexer, axis, out, fill_value, mask_info, allow_fill)
    829         out_shape[axis] = len(indexer)
    830         out_shape = tuple(out_shape)
--> 831         if arr.flags.f_contiguous and axis == arr.ndim - 1:
    832             # minor tweak that can make an order-of-magnitude difference
    833             # for dataframes initialized directly from 2-d ndarrays

AttributeError: 'Categorical' object has no attribute 'flags'

Perhaps it is linked to issue #10374...

Ideally this would return a new Categorical with inserted NaNs.

The above was tried on pandas 0.16.2

[jreback]

well aside from the bug in #10324

• An ordered Categorical merge should raise as the new ordering would be impossible to determine.
• A merge on a Categorical and an object column would be ok with nan for any non-represented categories.
• A merge with 2 different Categoricals I suppose could succeed with a union of the categoricals.

[joshlk]

Additionally the performance benefits of the underlying int representation of the categories are lost when doing the merge. For example:

# Here we are joining on a int column and appending two string columns
A = pd.DataFrame({'X': np.random.choice(range(0, 10), size=(100000,)), 
                                'Y': np.random.choice(['one', 'two', 'three'], size=(100000,))})

B = pd.DataFrame({'X': np.random.choice(range(0, 10), size=(100000,)), 
                                'Z': np.random.choice(['jjj', 'kkk', 'sss'], size=(100000,))})

%time r = pd.merge(A, B, on='X')
Wall time: 57.3 s

# Lets do the same but convert the appending columns 'Y' and 'Z' to a category
A['Y'] = A['Y'].astype('category')
B['Z'] = B['Z'].astype('category')

%time r = pd.merge(A, B, on='X')
Wall time: 54.9 s          # Slightly less

# Now lets use the int representation of the categorical columns and merge
A['Y'] = A['Y'].cat.codes
B['Z'] = B['Z'].cat.codes

%time r = pd.merge(A, B, on='X')
Wall time: 41.7 s           # 72% of the original time taken

[jreback]

@joshlk all true

if u would like to try an impl would be great
I will get to this but prob not for a little bit