Always been interested to know why pandas implemented index the way it did. I generally find myself doing .reset_index on everything by default because it's just one less thing to think about, but it's clear that pandas devs are very fond of it based on the API. Where it still feels weird is when e.g. groupby/pivot by default return everything with a custom index, when I've given no indication that it needs to be treated differently to a column, but then e.g. merge doesn't do this? It's also written out by default in .to_csv, like just... why? Not useful for any csv that is to be used outside pandas. God help you if you end up needing to use a multi-index for something - deeply unpleasant.
Was this just a high level (possibly misguided) paradigm that the pandas devs fell in love with - or is there a good, performance related reason to embed it so deeply in the API?
I have no special insight in pandas development, so this is just my guess.
I believe this was because Pandas' initial primary use case was manipulating time series (datetime-indexed numerical vectors), which are used extensively in financial institutions such as hedge funds and trading firms (Pandas was initiated as a skunkwork project in AQR Capoital Management). You can see the lineage in its very extensive collection of convenience methods for manipulating time series (pd.Series where the index is some datetime type). A pd.Series is a numpy array with a meaningful index, and a pd.DataFrame is a collection of pd.Series with a shared index. If you use Dataframe to store and manipulate multivariate time series, the api is quite sensible. So pd.Series and pd.DataFrame are probably the datatype that you'd design to store time series of (a portfolio of) stock returns. (Old foggies who'd use something like Matlab before know that just being sure your vector/matrix calculations are using correctly aligned dates was not a given.)
Dplyr and its R data.frame heritage are what statisticians would probably use to record measurements / experimental outcomes on individuals. There is usually no meaningful index/natural primary key, and the order usually doesn't matter. It's much closer to a relational database table (unordered collection of tuples), but for analytics rather than transactions so column- rather than row-oriented.
For data tables without a meaningful natural index, the Pandas api is much more confusing and cumbersome than needed. It happens that a lot of ML applications fall in that category, but during the early 2010s when Panndas took of it had very little competition.
I concur wholeheartedly. The use of indexes in Pandas makes working with timerseries data so much nicer and that's why indexing is an important part of the API.
Really interesting. I would argue that a lot of datapoints (maybe all) in fact do have a natural index. It is the fact if you are talking about Relational databases (every tables has a primary key). In a lot of scenarios it helped me a lot to think as the index as an associated pk of some database table.
In my own experience my struggles come from not taking the time to properly understand indexes. When starting out many things are intuitive so it feels documentation isn’t that necessary. But then you hit a wall and things don’t work correctly. At this point I would just try everything brute force or search stack overflow until I got it to work so I could move on with other things. But it was just a quick fix. Once I took a few hours reading and trying examples to understand how the index works things made sense.
When you start with simple data frames it feels dict-like and very pythonic —- zero learning curve. But then multi-indexes are lists of tuples and things get tricky. It’s also important that data frames always have an index whether you realize it or not, the default being a RangeIndex.
This is why the “to_csv” bites people, there’s a numerical index. Once an index is set you no longer have to say “index=False”.
With the default RangeIndex “df.loc[0]” and “df.iloc[0]” will give the same result because the first record has both the position 0 and index value of 0. Once a “meaningful” index is set you need to start referring to it by value rather than position. This makes it much easier to manipulate data, for me at least.
I have been using indexes more and more if you do essentially series based operations but you need to re-associate the results back. It enables you to avoid using dataframes as inputs to your data transformations.
I am experienced with pandas and I understand its uses - but it definitely makes learning it much more confusing for new people - like the first time you see a multi-index you're just like 'OMG what'. It also feels like it silghtly breaks the mental model of a dataframe for me - like why am I treating these columns as special all of a sudden? Sometimes that can make things slicker, but 90% of the time for me it just necessitates the need for .reset_index or index=False or equivalent. If I want to use index to optimise something - that should be a deliberate act, not something pushed on me by the API.
yea I know what you mean. The main use I have found is something like. Say you have a price per company_id and per transaction_id. Now you have some function, which for the sake of it, takes it to an exponent.
i used to write a lot of functions which went like this:
and i came back to these functions, and i was always like. hmm what needs to be in the price frame. which columns etc. Also it mutates the state of the data frame
However what is good as well you can treat the series as a single dimensional array and do operations on it. Its not a perfect example since im not using the ids haha but you might see what i mean :D
I think it helps coming from another direction where dataframes are fancy 2D numpy arrays or a fancy 'dict-of-dicts'. I do like being able to query from index with .loc. Nonetheless I basically agree with you.
One reason is that lots of operations automatically join on index (instead of, say, using rows’ integer indices) which makes them more convenient than manually doing the join yourself every time. You only need to set the indices correctly one and then things just work.
Apart from grouping, custom indexes are great as default columns for joins.
I just wish Pandas treated named index columns as columns. When I write df["blah"], I don't want to have to remember whether I just loaded the data and it's a normal column or if I just grouped on "blah" and it's an index column.
Currently, in the latter case, subscripting doesn't work and you either have to do a reset_index() or look up the correct incantation -- something like df.index.get_level("blah").
I have a feeling that the root of the problem is that Pandas ended up the same concept of "index" both for optimized lookup and for the UI of grouping / joining. My guess is that get_level is less efficient than it should be, and thus Pandas discourages using it by making it obscure.
Happy to hear I was not the only one wondering what was going on with (multi)indexing for pandas data frames.
I am not sure if I am just older now but I am more and more set in my dplyr ways and it’s hard for me to adopt the python way of wrangling data frames.
Comments
Always been interested to know why pandas implemented index the way it did. I generally find myself doing .reset_index on everything by default because it's just one less thing to think about, but it's clear that pandas devs are very fond of it based on the API. Where it still feels weird is when e.g. groupby/pivot by default return everything with a custom index, when I've given no indication that it needs to be treated differently to a column, but then e.g. merge doesn't do this? It's also written out by default in .to_csv, like just... why? Not useful for any csv that is to be used outside pandas. God help you if you end up needing to use a multi-index for something - deeply unpleasant.
Was this just a high level (possibly misguided) paradigm that the pandas devs fell in love with - or is there a good, performance related reason to embed it so deeply in the API?
I have no special insight in pandas development, so this is just my guess.
I believe this was because Pandas' initial primary use case was manipulating time series (datetime-indexed numerical vectors), which are used extensively in financial institutions such as hedge funds and trading firms (Pandas was initiated as a skunkwork project in AQR Capoital Management). You can see the lineage in its very extensive collection of convenience methods for manipulating time series (pd.Series where the index is some datetime type). A pd.Series is a numpy array with a meaningful index, and a pd.DataFrame is a collection of pd.Series with a shared index. If you use Dataframe to store and manipulate multivariate time series, the api is quite sensible. So pd.Series and pd.DataFrame are probably the datatype that you'd design to store time series of (a portfolio of) stock returns. (Old foggies who'd use something like Matlab before know that just being sure your vector/matrix calculations are using correctly aligned dates was not a given.)
Dplyr and its R data.frame heritage are what statisticians would probably use to record measurements / experimental outcomes on individuals. There is usually no meaningful index/natural primary key, and the order usually doesn't matter. It's much closer to a relational database table (unordered collection of tuples), but for analytics rather than transactions so column- rather than row-oriented.
For data tables without a meaningful natural index, the Pandas api is much more confusing and cumbersome than needed. It happens that a lot of ML applications fall in that category, but during the early 2010s when Panndas took of it had very little competition.
The name pandas was derived from panel data (data sets with observations over multiple time periods).
https://www.dlr.de/sc/Portaldata/15/Resources/dokumente/pyhp...
I concur wholeheartedly. The use of indexes in Pandas makes working with timerseries data so much nicer and that's why indexing is an important part of the API.
Really interesting. I would argue that a lot of datapoints (maybe all) in fact do have a natural index. It is the fact if you are talking about Relational databases (every tables has a primary key). In a lot of scenarios it helped me a lot to think as the index as an associated pk of some database table.
In my own experience my struggles come from not taking the time to properly understand indexes. When starting out many things are intuitive so it feels documentation isn’t that necessary. But then you hit a wall and things don’t work correctly. At this point I would just try everything brute force or search stack overflow until I got it to work so I could move on with other things. But it was just a quick fix. Once I took a few hours reading and trying examples to understand how the index works things made sense.
When you start with simple data frames it feels dict-like and very pythonic —- zero learning curve. But then multi-indexes are lists of tuples and things get tricky. It’s also important that data frames always have an index whether you realize it or not, the default being a RangeIndex.
This is why the “to_csv” bites people, there’s a numerical index. Once an index is set you no longer have to say “index=False”.
With the default RangeIndex “df.loc[0]” and “df.iloc[0]” will give the same result because the first record has both the position 0 and index value of 0. Once a “meaningful” index is set you need to start referring to it by value rather than position. This makes it much easier to manipulate data, for me at least.
I have been using indexes more and more if you do essentially series based operations but you need to re-associate the results back. It enables you to avoid using dataframes as inputs to your data transformations.
I am experienced with pandas and I understand its uses - but it definitely makes learning it much more confusing for new people - like the first time you see a multi-index you're just like 'OMG what'. It also feels like it silghtly breaks the mental model of a dataframe for me - like why am I treating these columns as special all of a sudden? Sometimes that can make things slicker, but 90% of the time for me it just necessitates the need for .reset_index or index=False or equivalent. If I want to use index to optimise something - that should be a deliberate act, not something pushed on me by the API.
As someone with years of working experience with dplyr who had to learn pandas, 100% agree. And thanks for that post, I thought it was just me.
yea I know what you mean. The main use I have found is something like. Say you have a price per company_id and per transaction_id. Now you have some function, which for the sake of it, takes it to an exponent.
i used to write a lot of functions which went like this:
and i came back to these functions, and i was always like. hmm what needs to be in the price frame. which columns etc. Also it mutates the state of the data framewhile i now write functions like
However what is good as well you can treat the series as a single dimensional array and do operations on it. Its not a perfect example since im not using the ids haha but you might see what i mean :DI think it helps coming from another direction where dataframes are fancy 2D numpy arrays or a fancy 'dict-of-dicts'. I do like being able to query from index with .loc. Nonetheless I basically agree with you.
One reason is that lots of operations automatically join on index (instead of, say, using rows’ integer indices) which makes them more convenient than manually doing the join yourself every time. You only need to set the indices correctly one and then things just work.
This is the biggest thing. Indexes are like an extension of array broadcasting to relational data.
Not least in that both broadcasting and pandas indicies seem surprising and magical if one doesn’t understand the details.
Apart from grouping, custom indexes are great as default columns for joins.
I just wish Pandas treated named index columns as columns. When I write df["blah"], I don't want to have to remember whether I just loaded the data and it's a normal column or if I just grouped on "blah" and it's an index column.
Currently, in the latter case, subscripting doesn't work and you either have to do a reset_index() or look up the correct incantation -- something like df.index.get_level("blah").
I have a feeling that the root of the problem is that Pandas ended up the same concept of "index" both for optimized lookup and for the UI of grouping / joining. My guess is that get_level is less efficient than it should be, and thus Pandas discourages using it by making it obscure.
I imagine this is mirroring R's `write.csv()` behaviour.
But I agree, if you're designing something sane you probably shouldn't copy R.
Interesting - I haven't used R for years, I forgot that it also did that.
Row names don't really make any difference in R though, which is very unlike pandas.
Happy to hear I was not the only one wondering what was going on with (multi)indexing for pandas data frames.
I am not sure if I am just older now but I am more and more set in my dplyr ways and it’s hard for me to adopt the python way of wrangling data frames.