Replacing Pandas with Polars
confessionsofadataguy.com
confessionsofadataguy.com
I am hopeful about duckdb mostly because how friendly the people behind the project is. But honestly they really need to improve their csv reader operation. The data type recognition for auto read csv needs work. And duckdb people knows about CSV read is the reason polars has an edge on them and I know they are working on it.
But at the end of all that, I will just wait for Pandas to get faster and better.
There is also dask to increase parallelism and performance which I’ve used on some massive datasets 200GB+
Beyond this, a dataframe api/syntax, compared to SQL syntax, is to me way easier to follow and to debug.
The way I see the speed improvements is this: fast processes run faster, moderate-to-long processes (that are still awkward and large in Python, but not enough to justify the shift to Spark) now run in noticeably faster time, and the threshold for “what I need to use Spark for, shifts a long way up the scale. That is, you can now do more, with the same amount of compute.
There is work being done on a dataframe API frontend for DuckDB, if you prefer that interface.
We hesitated a lot before mentioning ETL in the OSS project windmill [1] but since we support any python including polars, passing data between steps through shared folders and since polars is blazingly fast, it is a suprisingly good system for ETL waiting for the need of using spark/materialize directly.
What used to be 'big data' fits on an instance, you only need shards/clusters for 'ludicrous data'.
- pandas
- polars
- modin with various backends
- DuckDB and Postgres SQL from Jupyter/python with the usual SQL dataframe magic
- R
- on medium and 'pretty big data' i.e. 10GB dataframes with millions of rows.
- maybe on consumer Intel, M2, and Epyc with lots of cores
I feel like I'm witnessing the start of that here.
The big thing pandas has going for it is that it's already been through this field testing. All the bugs have been ironed out by the hundreds of thousands of users. And the compute/memory efficiency is "good enough" for most use-cases, that those who need better efficiency can spend some time to optimize it. Sure the API is confusing and bad, but most people just kind of muddle through using stackoverflow answers from 2017 and it works well enough.
I've stanned for data.table (the R package) on HN before and I'll do it again now. It's a great example of a package that has been thoroughly field tested as well as having a simple, elegant API that allows you to do basically anything. So we know it's possible!
At this very moment pandas github repo has 1563 open issues labeled with a bug tag [0]. So much for "all the bugs have been ironed out".
[0] https://github.com/pandas-dev/pandas/issues?q=is%3Aopen+is%3...
I my previous project, I use pandas' read_table to avoid for loop to parse molecular dynamics trajectories LAMMPS. The trajectories are pure text file with size of a few Gigs. Compared to a pure Python implementation ~3-5 min, pandas reduce it to 20 secs. I wonder how Polars works.
To be honest, compared to parsing, data manipulation such as the benchmark showcased, is just not so costly.
We control all our parsers and read directly into arrow memory. This differs from pandas, which utilizes pyarrow for reading parquet and then finally has to copy the arrow memory over to pandas memory.
Nonetheless, would reading a parquet file with polars be faster than reading a csv?
Also thanks for polars! Great contribution data science!
No worries. :) Most blogs on the topic I encounter in the wild make incorrect claims, I understand the confusion.
> Nonetheless, would reading a parquet file with polars be faster than reading a csv?
Yes, much faster. Please don't use the csv format for anything of a reasonable size. It is a terrible format to process and very ambiguous.
``` # polars
We should not rechunk as that is not what pyarrow does. Rechunking is a different operation and is also optimized away in many lazy operations.
%%time pl.read_csv("csv-benchmark/yellow_tripdata_2010-01.csv", rechunk=False, ignore_errors=True)
CPU times: user 17.4 s, sys: 2.42 s, total: 19.8 s Wall time: 1.89 s
%%time pa.csv.read_csv("csv-benchmark/yellow_tripdata_2010-01.csv")
CPU times: user 17.1 s, sys: 3.07 s, total: 20.1 s Wall time: 1.99 s
# pyarrow embedded new lines (e.g. valid csv files)
Polars by default allows embedded new line characters. Pyarrow does not, if we tell it to do so, polars is faster on my laptop.
%%time pa.csv.read_csv("csv-benchmark/yellow_tripdata_2010-01.csv", parse_options=pa.csv.ParseOptions(newlines_in_values=True))
CPU times: user 17.8 s, sys: 3.09 s, total: 20.9 s Wall time: 2.72 s
# pandas (pyarrow engine)
And pandas itself has to pay for the copy from pyarrow to pandas:
%%time pd.read_csv("csv-benchmark/yellow_tripdata_2010-01.csv", engine="pyarrow")
CPU times: user 18.5 s, sys: 5.48 s, total: 24 s Wall time: 2.81 s
# pandas (default engine)
I also tried with pandas default csv parser, but I got an OOM after 20 seconds or so. I have 16GB of RAM. ```
How many cores you got? Arrow has one thread serially chunking the data, and uses the rest of the cores to process the chunks in parallel. On large machines (and files, with fast io) the chunking becomes the bottleneck. Is this the same for polars?
Compare:
pd_df.groupby(['date'])['failure'].count() # pandas
pl_df.groupby(pl.col('date')).agg(pl.count('failure')) # polars
dt[, .N, date] # R data.table
e.g.
> iris.groupby('species').count()
sepal_length sepal_width petal_length petal_width
species
setosa 50 50 50 50
versicolor 50 50 50 50
virginica 50 50 50 50
> iris.groupby('species')['sepal_length'].count()
species
setosa 50
versicolor 50
virginica 50R is not a replacement for pandas.
R is it’s own special little painful ecosystem, loved by people who don’t have to maintain the code they write.
You can complain all you like about pandas, but at the end of the day, it’s python. Python tooling works with it. The python ecosystem works with it.
It’s not without faults, but at least you’ll have a community of people to help when things go wrong.
R, not so much.
(Spoken as jaded developer who had to support r on databricks, which is deep in the hell of “well, it’s not really a tier one language” even from their support team)
Sounds like you worked with (or wrote) really bad R code.
…the point I’m making is that when you already have bad r code, it’s a massive pain in the ass.
Bad python is terrible too, but lots of people know python, and it’s easy to find help to unduck bad python code and turn it into maintainable code.
That has not been my experience with r. Ever. At any organisation.
Your experience may vary. (:
Do you think it’s because R code tends to be written by statisticians and stats-adjacent domain experts who don’t necessarily know how to write clean code while Python code has at least some input from actual programmers? Or is this really down to the language itself?
The R community is categorically smaller than the python community. The support on community forums is harder to get, or non-existent (eg. with databricks).
Are you saying you've worked in places where its easier to find people that are familiar with R to help work on a project than it is to find people are familiar with python?
That you've found its easier to hire people who are familiar with R than it is to hire people who are familiar with python?
I... all I can say is that has not been my experience.
The places I've worked, of all the developers a small handful of people use R, and a small subset of those are good at it.
I don't hate R. I don't think it's a bad language. I'm saying: It's harder to support, because it's obscure, rarely used by most developers, and the people who use it and know it well are rare and expensive.
As a data engineer, expected to support workflows in production: Don't use obscure crap and expect other people to support it. Not R. Not rust. Not pony.
Using R on databricks, specifically, is a) unsupported^, and b) obscure and c) buggy. Don't do it.
(^ sorry, it's a 'tier 2 language' if you speak to DB representative, which means bugs don't count and new features don't get support)
All I can say, is that my experience has been that supporting python has been less painful; it's a simple known quantity, and its easy to scale up a team to fix projects if you need to.
At least in my experience we’ve never had issues with people learning it on the job and far fewer software issues from eg versioning, dependencies, regression bugs. It just works, there’s rarely even a need for a venv.
I’d never expect it applied as a general purpose language like python though, typical projects are <1k lines of some specific data task, perhaps our use cases are just different
Maybe I'm just jaded because I've inherited a 100K+ line R codebase at my job written by a single person with no unit tests and about 3 lines of comments, and it's a completely miserable experience.
dplyr syntax IMO is the best balance between clarity and nonredundancy as evident in pandas/polar code.
R is also impossible to understand whta is actually going on.
``` pd_df['date'].value_counts() # pandas pl_df.select(pl.col('date').value_counts()) # polars ```
Note: we could also do it the Pandas way in Polars but square bracket indexing in Polars is not recommended
Wait — did I miss the memo? What's replacing Airflow?
But you're right, Airflow is still the de facto standard, there's just increasing awareness of pain points with it.
Granted, the real correct solution for manipulating Actual Big Data (tm) is at the data warehouse level itself before it even hits Python but that's a topic for another day.
I rarely do one thing to a dataset. Show me what real world code looks like.
Pandas and Polars are both awesome. Pandas is more entrenched and is a solid choice these days (and getting better for big data).
Polars is an awesome up and coming library. Competition is good for the Python Data ecosystem.
(Disclaimer: I've authored multiple Pandas books and have taught courses on both Pandas and Polars.)
That said, using Ray places limits on that, since it can distribute data across machines, breaking the Arrow model of in-memory data sharing between tools. I know that Ray Data is starting to use Polars internally, but I wish I could use the Polars API over distributed Ray Data sets so I could write code once and not worry about whether Ray has split it across machines.
I thought we were going to get some interesting zoo logistics or something.
But, the one universal complaint about pandas was always its API! Moving on from it, I'd say, is a substantial part of the motivation behind something like polars. So we're in some unpleasant "worse is better" territory here, and to me, it feels like python really missed the mark by too much. The pandas API is "fine", but really, we could do with better.
One option is to just treat the pandas DataFrame as the translation layer. If you need to dump tabular data from your library into a datavis library, then `plot(my_df.to_pandas())`.
Hopefully that conversion overhead never becomes too much of a headache. If pandas gets full Arrow compatibility (last I checked, they were behind R and tidyverse on this??), then it shouldn't really be an issue anymore and Wes Mckinney can hopefully stop getting complaints about the pandas API!.
Columns have names, except when they don't. Things (rows? individual cells?) have types which is sometimes enforced and sometimes not. If you don't follow a recipe exactly, your dataframe will be subtly different and might not work. A lot of code mixes mutation in place and creating new dataframes, and so on. And Pandas lends itself to a special chained method syntax which is almost it's own DSL in Python.
They are not a fit for every problem. Please don't try to use them for that. For normalized tabular data, it is a good fit.
> have types which is sometimes enforced and sometimes not. If you don't follow a recipe exactly, your dataframe will be subtly different and might not work.
Polars is really strict on types. A type is always determined by the input datatype and the operation applied. You can check the schema before you run the query. Polars will produce the same output datatype as defined by its logical plan.
> A lot of code mixes mutation in place and creating new dataframes, and so on
All polars methods are pure, e.g. they don't mutate the original.
There are like 10 3rd party solutions for this but ffs it should be built in.
https://github.com/fugue-project/fugue
A unified interface for distributed computing. Fugue executes SQL, Python, and Pandas code on Spark, Dask and Ray without any rewrites.
Almost everything required reading to grasp and creation of several dummy tasks, nothing was intuitive and in the end while some of the functions helped I am not entirely sure that the overhead was worth it compared to researching alternative solutions.
I'm hoping that PRQL [0] will one day become the universal API for tabular/relational data. We're targeting SQL as the backend in the first iteration given it's universality but there are early plans to support other backends.
You can already use PRQL with Pandas, the tidyverse, shell and pretty much any database. See my presentation [1]. PRQL reads very similarly to dplyr, and in my (biased) opinion, actually a bit better than dplyr because it can do away with some of the punctuation due to being its own language.
For questions see our Discord [2] and if you would like to see PRQL in more places, file an issue on Github [3].
Some examples below:
## Pandas
```python
#!pip install pyprql
import pandas as pd
import pyprql.pandas_accessor
df = pd.read_csv("data/customers.csv")
df.prql.query('filter country=="Germany"')
```
```sh
mkdir -p ~/.local/R_libs
R -q -e 'install.packages("prqlr", repos = "https://eitsupi.r-universe.dev", lib="~/.local/R_libs/")'
```
```R
library(prqlr, lib.loc="~/.local/R_libs/")
library("tidyquery")
"
from mtcars
filter cyl > 6
sort [-mpg]
select [cyl, mpg]
" |> prql_to_sql() |> query()
```
```prql
from employees
filter start_date > @2021-01-01
derive [
gross_salary = salary + (tax ?? 0),
gross_cost = gross_salary + benefits_cost,
]
filter gross_cost > 0
group [title, country] (
aggregate [
average gross_salary,
sum_gross_cost = sum gross_cost,
]
)
filter sum_gross_cost > 100_000
derive id = f"{title}_{country}"
derive country_code = s"LEFT(country, 2)"
sort [sum_gross_cost, -country]
take 1..20
```
[1]: https://github.com/snth/normconf2022/blob/main/notebooks/nor...
If you need recursive/iterative queries then watch this space. That's ok the roadmap!
It suffers from the same problem as so many other python libraries which were clearly designed by scientists rather than software engineers. The design philosophy seems to be that having 100s of ways to do every single thing is "convenient". In reality, it makes almost every single function in the pandas library waaaaaaay too complicated. Nearly all of them have different "modes", and sometimes choosing one thing over another will absolutely cripple the performance of your application.
I hadn't heard about polars, but I'm currently using pandas in a part of our stack, and since it's not a lot of code yet, I might just be able to replace pandas. Thanks!
https://stackoverflow.com/questions/20625582/how-to-deal-wit...
“SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame.”
df.loc['2023-01-01': '2023-12-31'] *= 2
In polars there’s no mutating operations, everything is pure, so you won’t see any warnings of that nature, but what you will see instead is having to do something like this to solve the same problem:
df.with_column([
pl.when(pl.col('date').is_between(
datetime(2023, 1, 1),
datetime(2023, 12, 31),
)).then(pl.col(x) * 2)
.otherwise(pl.col(x))
.alias(x)
for x in df.columns if x != 'date'
])
[1] https://spark.apache.org/docs/latest/api/python/user_guide/p...