What would it take to recreate dplyr in Python? (2020)(mchow.com)
mchow.com
What would it take to recreate dplyr in Python? (2020)
https://mchow.com/posts/2020-02-11-dplyr-in-python/
59 comments
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 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...
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.
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.
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:
while i now write functions like
i used to write a lot of functions which went like this:
def convert_price(price_frame:pd.DataFrame) -> pd.DataFrame:
price_frame["new_price"] = np.exp(price_frame)
return price_frame
while i now write functions like
def convert_price(price:pd.Series) -> pd.Series:
return np.exp(price)
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.
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 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.
> It's even written out by default in .to_csv, like just... why? Do they not know how csvs are used outside pandas?
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 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.
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.
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.
Ditto the rest of the tidyverse.
Ditto the rest of the tidyverse.
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.
Agree 100%!
As a comparison to another language: for Julia there is DataFrames.jl: https://dataframes.juliadata.org/stable/
Comparison to dplyr, ...: https://dataframes.juliadata.org/stable/man/comparisons/#Com...
Comparison to Pandas: https://dataframes.juliadata.org/stable/man/comparisons/#Com...
Comparison to dplyr, ...: https://dataframes.juliadata.org/stable/man/comparisons/#Com...
Comparison to Pandas: https://dataframes.juliadata.org/stable/man/comparisons/#Com...
Using DataFramesMeta.jl [1], the code from the first example turns out pretty similar:
[1] https://juliadata.github.io/DataFramesMeta.jl/stable/dplyr/
using DataFramesMeta, Statistics
@chain mtcars begin
@select :Cyl :HP
groupby(:Cyl)
@transform _ begin
:dumb_result = myarbitraryfunc.(:HP)
:demeaned = :HP .- mean(:HP)
end
end
[1] https://juliadata.github.io/DataFramesMeta.jl/stable/dplyr/
DuckDB and Polars are my bets in the Python data-wrangling space. I grew tired of Pandas' weird-ass API.
I would love to switch to something else, but it feels like pandas is lingua-franca in data science now, to switch puts a burden on everyone else.
You can use DuckDB as a processing engine on top of Pandas [1], while continuing to use Pandas as a data storage/data interchange format.
[1] https://duckdb.org/2021/05/14/sql-on-pandas.html
[1] https://duckdb.org/2021/05/14/sql-on-pandas.html
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 :)
``` def sqldf(df: DataFrame, query: str) -> DataFrame: ... ```
``` def sqldf(df: DataFrame, query: str) -> DataFrame: ... ```
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
Glad to to see duckDB delivering, finally, on the promise of running SQL against in-memory dataframes
TIL there's an actual 'botched' library with the same name; I actually came up with it independently on a lazy office afternoon :^)
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.
Hah - I'm saying switching libraries is a headache - switching languages is absolutely not an option...
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.
It says here: https://github.com/pola-rs/polars/issues/580#issuecomment-82... , that Polars has zero copy for arrow and numpy.
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.
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.
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.
But I will improve it. ;)
But I will improve it. ;)
Yes I used it for the first time in ages recently and I have to say I found the whole thing a mess. There are about 5 ways to do everything.
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.
Huh, even though I would prefer a universal SQL layer, ibis looks quite nice.
I mean I haven't heard about DuckDB.
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 :/
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)
(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.
Maybe someday Python'll get a macro system ...
Maybe someday Python'll get a macro system ...
Author here--happy to answer questions :)
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...
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...
This article compares dplyr syntax with pandas, siuba, polars, ibis and duckdb: https://simmering.dev/blog/dataframes/
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.
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.
Hey regarding groupby operations in pandas. I have been moving more and more (for too complicated groupbys) to running them something like:
out_rec = []
for id, group in data_frame.groupby("id"):
ladidida....
result = f(group)
out_rec.append(result)
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:
https://siuba.readthedocs.io/en/latest/developer/pandas-grou...
* 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...
Yea makes sense. I actually come from R and the deplyr world. Was really happy to read your article :). I might give siuba a try.
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.
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.
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.
(user_courses
.set_index(["student_id",
"course_id"])
.unstack()
.apply(lambda x: x+1))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).
(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).
[0]: https://mchow.com/posts/pandas-has-a-hard-job/
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/
There is a project called Datar (https://github.com/pwwang/datar), which mimics dplyr in Python.
Thanks for posting it here. I was going to look for this exact package to post here but I forgot its name!
That looks neat and was very interesting. Pandas is sometimes a dark art to know how to do something fast enough.
Port the functionality of the R package but try to keep it python. Run flake8.
Port the functionality of the R package but try to keep it python. Run flake8.
Check out Tidypolars
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.
Same. I'd keep an eye on tidypolars but polars is good for now.
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?