Allowing the index to be referenced by name, like a column

[makmanalp]

• Idea: use df.index/df.columns names to automatically choose axis along which to broadcast #13243 use df.index/df.columns names to automatically choose axis along which to broadcast
• Request for some kind of named arguments loc #11373 Request for some kind of named arguments loc
• Partial Selection on MultiIndex: The need for empty slice support & dict indexing #4036 Partial Selection on MultiIndex: The need for empty slice support & dict indexing

What if we allowed the index of a dataframe to be referred to in the usual ways?

data = pd.read_table("...", index_col="id")
data.id  # breaks
data["id"]  # breaks

I find myself setting and resetting indices very often to join to a different dataframe or to pull in the values of the index to a subselection of the dataframe, etc. I figure this is because of how the data is stored under the hood, but wouldn't this be convenient?

[jreback]

I recall another issue about this - can u have a look for it?

further this is not difficult

want to try a pr?

[makmanalp]

Yeah, I'd love to take a shot at implementing this. I spent a few minutes looking for the old issue but couldn't find anything other than the tangentially relevant #8082 . Do you remember any other details?

[jreback]

I think I am remembering implementing (then reverting) this

you will need to change __getattr__ and _get_item_cached in core/generic.py

need good tests!

[shoyer]

I think this is a great idea. I did something similar in xray.

A few things to consider for a full-fledged implementation:

1. What should the type of data['id'] be? I think it should be a Series (i.e., data.index.to_series() or pd.Series(data.index, data.index)) rather than an Index (data.index), to follow the rule that the items in a DataFrame are always Series objects.
2. This should work with a MultiIndex. In this case, you should get a Series where the values are only from the named level (i.e., pd.Series(data.index.get_level_values('id'), data.index)).
3. Don't forget indexing columns with lists. This should also work, returning a DataFrame: data[['id', 'other_col']]

[makmanalp]

@shoyer - thank you so much! I was pondering the first myself - great point about the type, I wonder if Index follows the Series interface exactly. If so, shouldn't be a problem. Second and third hadn't even occured to me.

It looks like Index and Series inherits IndexOpsMixin (https://github.com/pydata/pandas/blob/master/pandas/core/base.py#L283)

https://github.com/pydata/pandas/blob/master/pandas/core/index.py#L74 and https://github.com/pydata/pandas/blob/master/pandas/core/series.py#L80

@jreback thoughts?

[jreback]

this is very simple

just change the methods I showed above
and wrap with _constructor

[TomAugspurger]

Regarding @shoyer's #3, with

In [7]: df = pd.DataFrame({"A": [1, 2, 3], "B": [4, 5, 6]}, index=['a', 'b', 'c'])
In [8]: df.index.name = 'idx'

Does df[['idx', 'A', 'B']] return

     A  B
idx      
a    1  4
b    2  5
c    3  6

with idx in the index still, or

  idx  A  B
0   a  1  4
1   b  2  5
2   c  3  6

with idx as a column? It should be the second one IMO.

[shoyer]

@TomAugspurger actually, I think it should either be your first example, or something like:

     idx  A  B
idx
a      a  1  4
b      b  2  5
c      c  3  6

This has the disadvantage of now having a redundant column/index with the same name. But I don't like changing the index based on indexing particular columns -- if you want that, you can use reset_index().

[makmanalp]

I think the first one is simpler too. We're not hiding that it's the index, and we're not promoting it to be a column, we're just allowing it to be referred to and used as a column.

[jorisvandenbossche]

But is should be consistent I think. If df['idx] returns the index wrapped in a Series, then df[['idx', 'A', 'B']] should also return it as a Series, and thus a DataFrame with 3 columns I think (so the example how @shoyer showed it). df[['idx', 'A', 'B']] and df[['A', 'B']] should not be the same I think.

[shoyer]

I agree with @jorisvandenbossche. Columns are never going to be fully interchangeable with indexes (even after this change), and if you're explicitly indexing the index as a column you presumably want it as a series, not an index.

Another edge case to test for: let's make sure df.groupby('idx') works. Right now you need to write df.groupby(level='idx').

[TomAugspurger]

+1 for @shoyer's example. I should have explained why I think that including idx in the slice should return it as a column. First of all there's the mental model that df[<list>] always returns a DataFrame whose columns are in the list. Second this would be the only way to do things like df[['idx', 'A', 'B']].sum(1) without resorting to the ugly old way of restet_index()ing.

I had an issue and PR about the @shoyer's groupby that I never finished off. We can handle groupby separately, but If this goes into 0.15, I'll finish up that PR.

[makmanalp]

@shoyer didn't know about the level=idx! The groupby was on my list because it's such a pain in the butt.

One question, is wrapping the index in a series and adding it onto the dataframe essentially a no-op, or is it going to be horribly inefficient for larger dataframes?