ERR: better error message on invalid on with multi-index columns

[amelio-vazquez-reina]

Consider the following:

> a_df

   object_uid       item_uid
0  0FlIRitxKX     0D1dPxep2C
1  0FLPRriacw     0DoYxRkSdr
2  0FUh6FQ4mQ     0DsOBGsYl9
3  0FH1hIfNCL     0DoYxRkSdr
4  0FZVBzi2DX     0DoYxRkSdr
5  0FAJ5vfLQs     0DoYxRkSdr
6  0F7lEk65qa     0DoYxRkSdr
7  0Fqe3TIN7Q     0DoYxRkSdr
8  0FrSgt4Vut     0DsOBGsYl9
9  0FByA7F19x     0DoYxRkSdr

and

> b_df

   object_uid         after        before change_rate
                micro_spend   micro_spend            
0  0F0092Ntoi  2.940774e+09  2.932917e+09    0.267909
1  0F01SZnTfs  3.000654e+07  2.887034e+07    3.935523
2  0F027Rm6rd  6.729106e+07  6.912947e+07   -2.659376
3  0F02BZeTr6  2.072908e+09  2.048737e+09    1.179802
4  0F02Vwd6Ou  1.619934e+08  1.682784e+08   -3.734909

I would like to do an inner join in object_uid. When I do:

pd.merge(a_df, b_df, on='object_uid')

I get:
AttributeError: 'numpy.ndarray' object has no attribute 'start'

This is with:

> pd.pandas.__version__
'0.15.2'

The full error trace is below:

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-131-5e11878c8b10> in <module>()
----> 1 pd.merge(exp_setup_df.head(10).iloc[:, [0,4]].copy(), spend_diff.reset_index().head(10).copy(), on='flight_uid')

/Users/avazquez/anaconda3/envs/py34/lib/python3.4/site-packages/pandas/tools/merge.py in merge(left, right, how, on, left_on, right_on, left_index, right_index, sort, s
uffixes, copy)                                                                                                                                                         
     36                          right_on=right_on, left_index=left_index,
     37                          right_index=right_index, sort=sort, suffixes=suffixes,
---> 38                          copy=copy)
     39     return op.get_result()
     40 if __debug__:

/Users/avazquez/anaconda3/envs/py34/lib/python3.4/site-packages/pandas/tools/merge.py in __init__(self, left, right, how, on, left_on, right_on, axis, left_index, right
_index, sort, suffixes, copy)                                                                                                                                          
    182         (self.left_join_keys,
    183          self.right_join_keys,
--> 184          self.join_names) = self._get_merge_keys()
    185 
    186     def get_result(self):

/Users/avazquez/anaconda3/envs/py34/lib/python3.4/site-packages/pandas/tools/merge.py in _get_merge_keys(self)
    386 
    387         if right_drop:
--> 388             self.right = self.right.drop(right_drop, axis=1)
    389 
    390         return left_keys, right_keys, join_names

/Users/avazquez/anaconda3/envs/py34/lib/python3.4/site-packages/pandas/core/generic.py in drop(self, labels, axis, level, inplace, **kwargs)
   1559                 new_axis = axis.drop(labels, level=level)
   1560             else:
-> 1561                 new_axis = axis.drop(labels)
   1562             dropped = self.reindex(**{axis_name: new_axis})
   1563             try:

/Users/avazquez/anaconda3/envs/py34/lib/python3.4/site-packages/pandas/core/index.py in drop(self, labels, level)
   3734                 inds.append(loc)
   3735             else:
-> 3736                 inds.extend(lrange(loc.start, loc.stop))
   3737 
   3738         return self.delete(inds)

AttributeError: 'numpy.ndarray' object has no attribute 'start'

Also, the following works:

> result_df = pd.concat([a_df.set_index('object_uid'), b_df.set_index('object_uid'], axis=1).columns

but it flattens my columns:

> result_df.columns
Index(['object_uid', ('after','micro_spend'), ('before', 'micro_spend'), 'item_id'], dtype='object']

[jreback]

show .info() on both frames

[amelio-vazquez-reina]

Thanks @jreback

> a_df.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 10 entries, 0 to 9
Data columns (total 2 columns):
object_uid      10 non-null object
item_uid        10 non-null object
dtypes: object(2)
memory usage: 240.0+ bytes

and

> b_df.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 10 entries, 0 to 9
Data columns (total 4 columns):
(object_uid, )           10 non-null object
(after, micro_spend)     10 non-null float64
(before, micro_spend)    10 non-null float64
(change_rate, )          10 non-null float64
dtypes: float64(3), object(1)
memory usage: 400.0+ bytes

[jreback]

how did you create the columns ?

[jreback]

the columns index mean

[amelio-vazquez-reina]

I created the multi-index one (b_df as follows):

b_df            = pd.concat([left_df, right_df], keys=['after', 'before'], axis=1)
b_df.index.name = 'object_uid'
b_df            = b_df.reset_index()

Other than that, a_df and the data in left_df and right_df comes from SQL queries and perhaps a few other joins before that.

[amelio-vazquez-reina]

That said @jreback I have tried using .copy() on both dataframes with the hope that that may perhaps recreate the dataframes with no luck.

[amelio-vazquez-reina]

Also, the following (flattening columns for b_df) works:

b_df.columns = ['object_uid', 'after', 'before', 'change_rate']
pd.merge(a_df, b_df, on='object_uid')

[jreback]

When you have a multi-index column, you have to specify the entire column depth in the on, IOW, a tuple. The error message could be better I think though. Want to submit a pull-request to improve that?

[amelio-vazquez-reina]

Thanks @jreback I wonder if the following problem is related to this:

Consider a dataframe that we populate as follows:

df = pd.DataFrame()
df['col1'] = ...
df['col2'] = ...

idx = pd.IndexSlice
df[idx['higher_col_1', 'col3']] = ...
df[idx['higher_col_2', 'col4']] = ...

I end up with

list(df.columns)
['col1',
'col2',
('higher_col_1', 'col3')
('higher_col_1', 'col4')
]

and when I do:

df.sort_index(axis=1

I get:

TypeError: unorderable types: str() > tuple()