groupby.apply is often the thing that is already way too slow though based on use-case - because I think it pretty much does what you wrote rather than something that makes stronger optimisation assumptions.
Performance aside, I think one of the main criticism of `groupby` in pandas is that they are not composable and become verbose. I'd argue that's the case with your example too.
Author here. My concern in the article isn't that you can't do groupby fast, but that the approach above is not composable.
* If f() converts grouped data to something ungrouped, then you can't use a similar function f2(f(group))
* If f() returns a grouped object, then you can't do basic operations like f(grouped) + 1, because DataFrameGroupBy, SeriesGroupBy do not define basic operators like addition. Let alone operations against other grouped data.
A lot of this is worked out in siuba now, and this doc explains a bit more:
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 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.
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.
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
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.
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.
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.
That's what I do at $dayjob whenever I have to do windowing &c. Figuring out this stuff in Pandas is a waste of time. Before I discovered DuckDB, I would re-learn the API every damn time.
I came up with a little utility function, which you can implement yourself :)
Years of unpicking others use of Rs sqldf (which by default used to copy the entire data frame to a SQLite db, run the query, the copy the result set back) when they complained their R code was to slow has taught me a visceral, negative to the name and pattern.
Glad to to see duckDB delivering, finally, on the promise of running SQL against in-memory dataframes
I like interface-only packages in the Julia ecosystem e.g. Tables.jl enables the development of several packages for querying tabular data that work across many concrete implementations; Plots.jl separates the high-level plotting interface from the plotting backend.
It's true. I've spent a small but nontrivial amount of time learning and using Polars, but it's just a nonstarter for most work projects. Not only does no one else know it exists, let alone how to use it, but it doesn't integrate with (to my knowledge) any ETL or ML Python library. You have to convert to pandas or NumPy, which is costly and to some extent defeats the purpose.
The to numpy conversion is free if you don't have missing data. Which is most of the cases if you send it over to a ML library.
If its not zero copy. It is still not a big deal. Pandas make a lot more copies internally. I truly wouldn't worry about that single copy if you have a order of magnitude speedup overall.
I stand corrected. The conversion felt relatively slow to me, but it was a large dataset and there were definitely missing values. Overall the benefits to speed and API cleanliness might be worth it, though it feels a bit gross to convert Spark to pandas to Polars to NumPy to DMatrix.
That said, it’s so much better than pandas for data manip that I’ll probably still try to use it.
Are you the author? If so, thanks for being so responsive on GitHub. You fixed basically every issue I had almost immediately back when I was learning Polars. It was awesome.
Yep, Thats me. Glad to help. :) There still room for parallelization when converting to a matrix. I will take a look. Haven't given that conversion any effort yet because that's often a one time conversion at the end of a pipeline.
I haven't touched pandas in months, but I also found quite tiring to deal with pandas.
Does your setup allow for an end-to-end solution? I mean, can I sink time into that setup and feel like I have everything I need to for regular data-wrangling?
I'm sure Pandas is amazing, but as a newbie I found myself doing many transformation logic with python data structures because it's just so much easier.
Maybe I'm dumb but going around the docs sometimes was like :/
Author of the post and siuba here. I'm pretty interested in exploring supporting polars as a backend, and if it works well supporting versions of the SQL backends that translate to SQL based on the polars method API :).
(I haven't really used it, but it looks promising)
Hey, I love siuba. Haven't had a chance to use it much but it scratches an itch for me. For years I've grumbled about how Python isn't flexible enough to accommodate tidyverse style libraries, as it lacks pipes and lazy evaluation (or macros), but siuba has managed to be very nice to use.
I don't know DuckDB but polars could dethrone pandas. We're planning on using it to create our pipeline. Ibis-project is another solution if anyone wants to check it out.
Nice, but honestly, Polars offers a nice enough API that I don't miss dplyr as much when using it. I probably wouldn't bother with another API on top of Polars unless it were particularly feature-rich.
Dplyr (and its spin-off dbplyr) is to me a fantastic practical example of the power of lispy metaprogramming. While R gets knocked a lot for not being a "real" programming language, or being weird, or just generally being different than e.g. Python, I don't know that I've seen a cooler example of high-level programming language ideas expressed as cleanly.
R’s waxing popularity is a testament to that power. dplyr and friends effectively reject much of the R-the-language and substitute their own friendlier, more popular syntax without rejecting R-the-platform.
Using DataFramesMeta.jl [1], the code from the first example turns out pretty similar:
using DataFramesMeta, Statistics
@chain mtcars begin
@select :Cyl :HP
groupby(:Cyl)
@transform _ begin
:dumb_result = myarbitraryfunc.(:HP)
:demeaned = :HP .- mean(:HP)
end
end
I am distracted with the example provided in the post. I am pretty sure that most pandas users will just put course_ids on the columns. I mean the shape of the user_courses dataframe is not suitable for the task.
Meanwhile, every time I use an external library (including pandas) I still think numpy does everything you need, and does it well, and people just haven't bothered to learn it properly and keep reinventing the wheel.
(no disrespect to to the package in the article or OP who I know is active in this thread. just a general motif that I keep coming across in python).
I used numpy a lot for my PhD research, and think you're right, in the sense that things could have been much more numpy array centric (just like how in R the dataframe is a tiny structure around arrays).
There are a lot of problems you encounter when using arrays for data analysis, like some of their funky behavior with strings [0], but it seems like extending arrays, or building new types of numpy arrays would have been better than new data structures like the pandas Series.
(pandas folks thought a lot about these problems so I could be very wrong).
59 comments
[ 3.0 ms ] story [ 119 ms ] threadPort the functionality of the R package but try to keep it python. Run flake8.
* If f() converts grouped data to something ungrouped, then you can't use a similar function f2(f(group))
* If f() returns a grouped object, then you can't do basic operations like f(grouped) + 1, because DataFrameGroupBy, SeriesGroupBy do not define basic operators like addition. Let alone operations against other grouped data.
A lot of this is worked out in siuba now, and this doc explains a bit more:
https://siuba.readthedocs.io/en/latest/developer/pandas-grou...
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 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 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.
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.
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.
https://www.dlr.de/sc/Portaldata/15/Resources/dokumente/pyhp...
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.
Not least in that both broadcasting and pandas indicies seem surprising and magical if one doesn’t understand the details.
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.
[1] https://duckdb.org/2021/05/14/sql-on-pandas.html
``` def sqldf(df: DataFrame, query: str) -> DataFrame: ... ```
Glad to to see duckDB delivering, finally, on the promise of running SQL against in-memory dataframes
If its not zero copy. It is still not a big deal. Pandas make a lot more copies internally. I truly wouldn't worry about that single copy if you have a order of magnitude speedup overall.
That said, it’s so much better than pandas for data manip that I’ll probably still try to use it.
Are you the author? If so, thanks for being so responsive on GitHub. You fixed basically every issue I had almost immediately back when I was learning Polars. It was awesome.
But I will improve it. ;)
Does your setup allow for an end-to-end solution? I mean, can I sink time into that setup and feel like I have everything I need to for regular data-wrangling?
I'm sure Pandas is amazing, but as a newbie I found myself doing many transformation logic with python data structures because it's just so much easier.
Maybe I'm dumb but going around the docs sometimes was like :/
(I haven't really used it, but it looks promising)
Maybe someday Python'll get a macro system ...
Ditto the rest of the tidyverse.
Siuba has come a long way since I wrote this, and now can optimize for fast grouped operations!:
* https://github.com/machow/siuba
* https://siuba.readthedocs.io/en/latest/developer/pandas-grou...
Comparison to dplyr, ...: https://dataframes.juliadata.org/stable/man/comparisons/#Com...
Comparison to Pandas: https://dataframes.juliadata.org/stable/man/comparisons/#Com...
[1] https://juliadata.github.io/DataFramesMeta.jl/stable/dplyr/
As other have said, escaping pandas is hard. Many visualization and data manipulation, validation and analysis libraries expect pandas input.
Siuba is really cool in that it offers a convenient syntax on top of pandas (and SQL databases) without requiring its own data format.
(no disrespect to to the package in the article or OP who I know is active in this thread. just a general motif that I keep coming across in python).
There are a lot of problems you encounter when using arrays for data analysis, like some of their funky behavior with strings [0], but it seems like extending arrays, or building new types of numpy arrays would have been better than new data structures like the pandas Series.
(pandas folks thought a lot about these problems so I could be very wrong).
[0]: https://mchow.com/posts/pandas-has-a-hard-job/