search

pandas-dev / pandas

Flexible and powerful data analysis / manipulation library for Python, providing labeled data structures similar to R data.frame objects, statistical functions, and much more
https://pandas.pydata.org
BSD 3-Clause "New" or "Revised" License
43.71k stars 17.92k forks source link

API: Meta-issue for making consistent API's to refer to column names and index names #27652

Open Dr-Irv opened 5 years ago

Dr-Irv commented 5 years ago

I would like to propose that any pandas API that allows specification of a column name also works when specifying an index level name. Today, this works in some places, but not all. Here is a list of places where things work, and where things don't work and could be improved. (The list is most likely incomplete, so additions are welcome). References to existing issues are given when they already exist (and if I knew about them):

Hoping to include this in the roadmap #27478

Things that work:

Things that could be improved:

WillAyd commented 5 years ago

Not sure my thoughts on this but out of curiosity what do you see the distinction being between an index level and a regular column, if any?

Dr-Irv commented 5 years ago

@WillAyd I don't think they should be any different in terms of doing column-wise operations. If you put certain "columns" in an index, then you can do slicing with .loc and pd.IndexSlice more efficiently.

To me, it's a bit analogous to a database table where you have a set of columns, and you then designate a few of them as primary keys. I think pandas could do the same thing, with the added benefit that you can do efficiently slicing on the "keys", i.e, the index.

jorisvandenbossche commented 5 years ago

This is somewhat a duplicate of https://github.com/pandas-dev/pandas/issues/8162 (about allowing to reference an index level like a column in different APIs), a discussion which was also touched upon in https://github.com/pandas-dev/pandas/issues/10000 (but many other cross links in https://github.com/pandas-dev/pandas/issues/8162) and as mentioned already partly implemented in certain APIs (eg merge, see https://github.com/pandas-dev/pandas/issues/14355).

The discussion in https://github.com/pandas-dev/pandas/issues/8162 certainly has some relevant content. I think in general (from that issue and the others), there is already consensus that in methods we want to reduce this distinction (like was already done in groupby and merge).

Personally, for me the bigger question is if we want to go further than that. As @Dr-Irv gives the analogy of a database, for me as well I find the idea of "index as special column" intriguing.

Dr-Irv commented 5 years ago

This is somewhat a duplicate of #8162 (about allowing to reference an index level like a column in different APIs), a discussion which was also touched upon in #10000 (but many other cross links in #8162) and as mentioned already partly implemented in certain APIs (eg merge, see #14355).

I think that my idea of allowing .loc and getitem() to refer to index level names is what #8162 is about.

I also found #20452 which has to do with the merge behavior I listed above,

I think in general (from that issue and the others), there is already consensus that in methods we want to reduce this distinction (like was already done in groupby and merge).

Yes, and I created this meta-issue to track this.

Personally, for me the bigger question is if we want to go further than that. As @Dr-Irv gives the analogy of a database, for me as well I find the idea of "index as special column" intriguing.

Glad to hear it!

shoyer commented 4 years ago

I think this is a worthy goal, and would certainly improve user experience.

My concern is that doing it well would also require an underlying change to pandas's core data model: indexes should be internally represented as a "type of column" rather than as external metadata. Otherwise the codebase will get filled with lots of special case logic, which is hard to maintain.

jorisvandenbossche commented 4 years ago

I also think that we should look into the data model topic of storing the index as a normal column (where the index would just be "a special column" that can be used in eg indexing operations).

However, that's a much bigger change than adding syntax sugar in several APIs, and with possibly much more backwards compatibility concerns (columns and values would then also include the index names/values, while they don't now, etc) But personally, I think this is worthwhile to explore (having the index as optional might be a prerequisite for this).

sangrey commented 4 years ago

As someone who is a heavy user of Multiindexes, I couldn't agree more that this would be very useful to have. I find working in a fairly complex production codebase that whether a particular "column" is a best thought of as an index or a column in its own right is constantly switching back and forth, and so my code ends up being littered with reset_index() calls and set_index() calls.

max-sixty commented 4 years ago

In the category of "syntax sugar" rather than data model changes: In my experience the most inconvenient occurance is set_index not allowing passing index names. For example:

In [10]: pd.DataFrame(np.random.rand(5,3), columns=list('abc')).set_index('a')                                                                                      
Out[10]: 
                 b         c
a                           
0.745626  0.628709  0.219627
0.071207  0.192600  0.643170
0.517568  0.556245  0.079201
0.819596  0.143708  0.368630
0.475074  0.142911  0.837396

If we want to make a multiindex with a & b, we need to do reset_index first, since passing index names to set_index doesn't work:

In [11]: pd.DataFrame(np.random.rand(10,3), columns=list('abc')).set_index('a').set_index(list('ab'))                                                               
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-11-264ec28ddef6> in <module>
----> 1 pd.DataFrame(np.random.rand(10,3), columns=list('abc')).set_index('a').set_index(list('ab'))

~/workspace/corpfin/.venv/lib64/python3.6/site-packages/pandas/core/frame.py in set_index(self, keys, drop, append, inplace, verify_integrity)
   4409 
   4410         if missing:
-> 4411             raise KeyError("None of {} are in the columns".format(missing))
   4412 
   4413         if inplace:

KeyError: "None of ['a'] are in the columns"

So I have a plethora of .reset_index().set_index(), which could be collapsed to set_index if that were possible.

Edit: @sangrey and I raced; I'm leaving this up as a more fleshed out version

Dr-Irv commented 4 years ago

@max-sixty Actually, you can do what you need without using reset_index and using the append argument of set_index:

In [3]: df=pd.DataFrame(np.random.rand(5,3), columns=list('abc')).set_index('a')

In [4]: df
Out[4]:
                 b         c
a
0.369952  0.768527  0.969260
0.587661  0.883011  0.455956
0.944659  0.331729  0.640796
0.528132  0.397122  0.170864
0.959429  0.869463  0.747232

In [5]: df.set_index('b', append=True)
Out[5]:
                          c
a        b
0.369952 0.768527  0.969260
0.587661 0.883011  0.455956
0.944659 0.331729  0.640796
0.528132 0.397122  0.170864
0.959429 0.869463  0.747232
max-sixty commented 4 years ago

Yes, thanks @Dr-Irv . That narrows the gap, though not all the way: being able to specify the indexes without the context of the current indexes would be more declarative and clearer, particularly to a reader of the code.

spaceman1313 commented 1 month ago

I like @Dr-Irv analogy to a database and primary keys. To me an index is just a regular column that has some super powers, it is not a different entity.

One example use case I'm having to write code around. I'm loading a data set from CSV files. I need to validate each column for specific formatting. One of the columns is a natural index. But now to validate that column I either need to set it as the index AFTER validation, or I need to have special logic in the validation process to check "is the column I'm about to validate an index or a regular column"