BUG: Indexes still include values that have been deleted

[darindillon]

Using pandas 0.10. If we create a Dataframe with a multi-index, then delete all the rows with value X, we'd expect the index to no longer show value X. But it does.
Note the apparent inconsistency between "index" and "index.levels" -- one shows the values have been deleted but the other doesn't.

import pandas

x = pandas.DataFrame([['deleteMe',1, 9],['keepMe',2, 9],['keepMeToo',3, 9]], columns=['first','second', 'third'])
x = x.set_index(['first','second'], drop=False)

x = x[x['first'] != 'deleteMe'] #Chop off all the 'deleteMe' rows

print x.index #Good: Index no longer has any rows with 'deleteMe'. But....

print x.index.levels #Bad: index still shows the "deleteMe" values are there. But why? We deleted them.

x.groupby(level='first').sum() #Bad: it's creating a dummy row for the rows we deleted!

We don't want the deleted values to show up in that groupby. Can we eliminate them?

related #2655 (maybe)

[wesm]

This is kind of a tricky problem, e.g. when should you "recompute" the levels? Have to table this until I have a chance to look a bit more deeply. Another solution is to exclude levels with no observations in such a groupby

[darindillon]

Well, is there any easy workaround I can use? Like if I know I have this problem, can I manually call a .rebuild_index() or something? I've played around with all the obvious possibilities (short of creating a brand new dataframe) and can't find any workaround.
It's the last line (the .groupby(...).sum() ) that I care about -- that's the one I need to make the bad data go away.

EDIT: better clarification. I have one function that builds the dataset and drops the rows. At that point, I know I'm in the situation described in this issue, and I'd like to do my workaround there. But then the .groupby().sum() happens much much later in a different function. I could easily hack that second function as you say (exclude levels with no observations) but it makes more sense to keep my workaround code in the first function. Any ideas?

[michaelaye]

How about the workaround that I proposed for #2655 ? In your case maybe

x.groupby(x.index.get_level_values(1)).sum() 

should do the correct thing, if I'm not wrong? I don't know why, but the result of this function delivers updated values.

[darindillon]

Yes that works; but the code that does .groupby().sum() is in one function and the code that removes the value from the table is in another fxn. It would be much much clear to use a workaround that cleans up the problem with the dataframe in the fxn that creates it -- that way any other fxn could use the dataframe without having to do your trick.

[michaelaye]

Ehm, can you confirm that this problem still exists with 0.10.1?
I just tried your example, and I don't see a dummy row with index "deleteMe" ?

In [9]: print x.index.levels
[Index([deleteMe, keepMe, keepMeToo], dtype=object), Int64Index([1, 2, 3], dtype=int64)]

In [10]: x.groupby(level='first').sum()
Out[10]: 
           second  third
first                   
keepMe          2      9
keepMeToo       3      9

[jreback]

is this closable? @tavistmorph does this exist in 0.11-dev?

[darindillon]

It's still an issue. Still happens for me in 10.1 and 0.11 (as of the last time I pulled, at least). Just run the code snippet in my orig post and you can see it.

Michael -- the deleted row appears in your output above on step #9 ("deleteMe" should not be there since we deleted it) and then it appears in the output for step 10 ("first" should not appear since all the rows with the "first" value were deleted).

[wesm]

This isn't really a bug. Perhaps an option should be added to return an array of observed values in a particular level in the index (which is what you're after)?

[michaelaye]

Can you precise what you mean by observed? Do you mean, that the object is a view into the original object (I don't know if it is), and that's why it still contains the 'deleteMe' index?

[wesm]

The levels are not computed from the actual observed values. For example, in R you can have a factor (categorical variable) in which some distinct values are not observed:

> d
[1] b c b c
Levels: a b c

[michaelaye]

Version: '0.12.0-1184-gc73b957'
The MultiIndex still shows all previously existing index values and therefore still is confusing to the user who looks at it, after chopping off the 'deleteMe' rows:

In [10]: x.index
Out[10]:
MultiIndex(levels=[[u'deleteMe', u'keepMe', u'keepMeToo'], [1, 2, 3]],
           labels=[[1, 2], [1, 2]],
           names=[u'first', u'second'])

but at least the groupby does not create an empty row anymore for previously existing indices:

In [12]: x.groupby(level='first').sum()
Out[12]:
           second  third
first
keepMe          2      9
keepMeToo       3      9

[2 rows x 2 columns]

so the discussion now boils down to the confusion of looking at df.index. I would argue, as I am looking often at the index to see what I am working with, that I would still be very puzzled by the index showing old values and from that point on I would not trust the results anymore.

[jtratner]

If you print that MultiIndex, it looks like what you want:

In [7]: mi
Out[7]:
MultiIndex(levels=[[u'deleteMe', u'keepMe', u'keepMeToo'], [1, 2, 3]],
           labels=[[1, 2], [1, 2]],
           names=[u'first', u'second'])

In [8]: print mi
first      second
keepMe     2
keepMeToo  3

Thus, a simple way to handle this is to examine your indices with print rather than the repr that IPython shows you.

The MultiIndex repr isn't really intuitive in any case, unless you understand that it's a categorical and that labels represent the integer positions of the levels at each location. You shouldn't need to care about that as a consumer of a MultiIndex. And if you understand the internal representation, you can then also understand why it doesn't matter whether there are extra levels.

The issue becomes clearer with a more complicated MI:

In [2]: ind = pd.MultiIndex.from_arrays([['a', 'a', 'b', 'b', 'c'], ['d', 'e', 'f', 'g', 'h']])

In [3]: ind
Out[3]:
MultiIndex(levels=[[u'a', u'b', u'c'], [u'd', u'e', u'f', u'g', u'h']],
           labels=[[0, 0, 1, 1, 2], [0, 1, 2, 3, 4]])

In [4]: print ind

a  d
   e
b  f
   g
c  h