Bug behavior when checking element inclusion in non-unique MultiIndex

[tipanverella]

I am using pandas.__version__==0.14.0.
This is my first bug report in github so excuse my possibly poorly formatted submission.

This bug report might be related to, or duplicate:

1. multi-index indexing for 3-level index behaving mysteriously. #2646
2. get_loc() returns integer or slice or KeyError nondeterministic in multiindex data frame #6501

It was explained to me by [http://stackoverflow.com/users/1427416/brenbarn] on the following StackOverflow question, http://stackoverflow.com/questions/24683023/having-issue-with-hierarchical-index-set-behavior/24684844#24684844

Essentially, the following behavior is not desirable:

print pd.__version__
WeirdIdx = pd.MultiIndex(levels=[[0], [1]],labels=[[0, 0], [0,0]],names=[u'X', u'Y'])
print WeirdIdx
print (0, 0) in WeirdIdx
print (1, 0) in WeirdIdx
print (100, 0) in WeirdIdx
print (100, 100) in WeirdIdx

since it prints:
0.14.0
X Y
0 1
1
True
True
True
True

despite the fact that (100,0) and (100,100) are unambiguously not part of the index.

[jreback]

.get_loc((100,0)) returns a 0-len slice instead raising here, so this is an untested edge case; will mark it as a bug (its only on a non-unique MultiIndex), pretty odd to do this anyhow

why are you doing the is checking? why are you not simply indexing?

[tipanverella]

I am not sure that I understand what you mean by "the is checking"?

[jreback]

typo, should be in checking. e.g. why are you doing ('foo','bar') in multi_index at all?

[tipanverella]

oh, ok.
I have a dataframe that I am building with batches of data that I get hourly.
So I check whether or not this particular (date,hour) combination was previously procured before fetching it (the fetching is expensive!).

[jreback]

and? so why are you checking if something is in the index?

[tipanverella]

the dataframe is indexed by (date, hour).

[jreback]

why are you not simply using a DatetimeIndex. Then grouping/resampling as needed?

[jreback]

checking like this is very inefficient

[tipanverella]

To be honest, I am not sure I know how to do what you have proposed.

[jreback]

why don't you show somedata, then what you are doing with it

[tipanverella]

So let's say I have a table like the following:

frame = pd.DataFrame(
    [
    (dt.date(2014,1,1),13,'blue',1000),
    (dt.date(2014,1,1),13,'red',1001),
    (dt.date(2014,1,1),13,'green',1001),
    (dt.date(2014,2,1),17,'blue',2000),
    (dt.date(2014,3,1),18,'red',3000),
    (dt.date(2014,7,11),18,'greeb',4000),
    (dt.date(2014,7,11),19,'red',5000),
    (dt.date(2014,7,1),19,'blue',6000)
    ],
    columns = ['day','hour','color','trials']
).set_index(['day','hour'])

I might want to append and concat to it, with a process that essentially grabs a dataframe df:

df = pd.DataFrame(
    [
    (dt.date(2014,1,12),13,'blue',1000),
    (dt.date(2014,1,12),13,'red',1001),
    (dt.date(2014,1,12),13,'green',1001)
    ],
    columns = ['day','hour','color','trials']
).set_index(['day','hour'])

but the process that fetches df is expensive, so I need to check that I am not fetching something I already have.