What category theory teaches us about dataframes
mchav.github.io
mchav.github.io
The more interesting nugget for me is about this project they mention: https://modin.readthedocs.io/en/latest/index.html called Modin, which apparently went to the effort of analysing common pandas uses and compressed the API into a mere handful of operations. Which sounds great!
Sadly for me the purpose seems to have been rather to then recreate the full pandas API, only running much faster, backed by things like Ray and Dask. So it's the same API, just much faster.
To me it's a shame. Pandas is clearly quite ergonomic for various exploratory interactive analyses, but the API is, imo, awful. The speed is usually not a concern for me - slow operations often seem to be avoidable, and my data tends to fit in (a lot of) RAM.
I can't see that their more condensed API is public facing and usable.
In a world where AI is writing the code, I guess I shouldn't complain, but when I am discovering something the ai of choice yet again missed, both pandas and polars still feel verbose and lacking sugar.
I hate to be the "you're holding it wrong" guy but 90% of "Pandas bad!" posts I find are either outright misinformed or mischaracterizing one person's particular opinion as some kind of common truth. This one is both!
> That comes from the assumption that there is almost always a meaningful index (timestamps)
The index can be literally any unique row label or ID. It's idiosyncratic among "data frames" (SQL has no equivalent concept, and the R community has disowned theirs), but it's really not such a crazy thing to have row labels built into your data table. Excel supports this in several different ways (frozen columns, VLOOKUP) and users expect it in just about any table-oriented GUI tool.
> having to write index=False every single time you write to disk
If you're actually using the index as it's meant to be used, you'd see why this isn't the default setting.
> functions seemingly randomly returning dataframes with column data as the index
I assume you're talking about the behavior of .groupby() and .rolling()? It's never been random. Under-documented and hard to reason about group_keys= and related options, yes. But not random.
> appending the index to the Series numpy data leading to incredibly confusing bugs
I've been using Pandas professionally almost daily since 2015 and I have no idea what this means.
Sometimes you need data in a certain order. Sometimes there is no primary key. And it is nuts how janky the pandas API is if you just want the index to mean the current order of the dataframe and nothing else. Oh you did a pivot? I'm just going to make those pivot columns a row label now if that's alright with you. I don't do that for all functions though, you're going to have to remember which ones. Oh you want to sort a dataframe? You better make damn sure you reindex if you're planning to use that with data from another dataframe (e.g. x + y on data from separate dataframes), otherwise I'm going to align the data on indices, and you can't stop me. Also - want to call pyplot.plot(df['column'])? Yeah I'm giving it the data in index order obviously I don't care about that sort you just did. Oh you want to port this data to excel? Well if your row labels aren't meaningful and you don't want "Unnamed: 0" you're going to have to tell me not to. You need to manipulate a multi-index? You're so cute. Have fun with that buddy.
There is a reason no other dataframe library does this - because it's confusing and cognitive overhead that doesn't need to exist. I've used pandas since ~2013, had this chat with colleagues and many recommend just giving in and maintaining an index throughout. Except I've read their pandas and it sucks because now _you_ need to reason about what is currently the index - because it actually needs to change a lot to do normal things with data. I just use .reset_index copiously and try to make it behave like a normal dataframe library because it's just easier to understand later. Pandas has not earned the right to redefine what a dataframe means.
At the absolute least, index behaviour should be opt-in, not something imposed on the user.
Having previously inherited (and now dispossessed) an un-disentangleable pile of Python, pandas, and SQL hacks reminiscent of a spreadsheet rammed with inscrutable Excel formulae, I have no idea how data scientists collaborate on anything with this technology. It's like when bioinformatics was full of write-only Perl code that was maybe executed successfully once for the purposes of a study or paper, and was kept around for future archaeologists to hopefully one day resuscitate when the need may arise again.
If programmers are expected to just throw garbage like this at the next asshole with the misfortune to have to maintain code that was never designed to be maintained, it's not a surprise that the industry is once again moving towards write-only code, this time produced at scale by LLMs.
It's like we're back to Visual Studio Ultimate slopping out 10k lines of XAML in response to your dragging and dropping in the WYSIWYG. There is a reason nobody does this any more.
For example filter can be expressed as:
is_even = lambda x: x % 2 == 0
mapped = map(lambda x: [x] if is_even(x) else [], data)
filtered = reduce(lambda x, y: x + y, mapped, [])
But you are also allowing arbitrary code expressions. So it is less lego-like.
Modern graph reducers have inherent confluence and aren't reliant on explicit commutation. They can do everything parallel and out of order (until they have to talk to some extrinsic thing like getting input or spitting out output), including arbitrary side-effectual mutation. We really live in the future.
The point of map/reduce was that it could easily be parallelized across large numbers of machines, for processing very large amounts of data. Hadoop implemented the first open-source example of this.
The limitations on what it could do were well-known from the start. No-one who knew what they were doing proposed that programs should be rewritten that way unless you were processing enough data to need to run them distributed on a cluster, in which case that was often your best option.
Many of the limitations of pure map/reduce were overcome by adding steps to the basic map/reduce parallel pipelines. Apache Spark is one example. It still has map and reduce operations in its pipeline, but it has several other operations as well. Nothing better than map and reduce has been found for the purpose it serves in such pipelines.
Granted, it's got more than 15 functions, but its simplicity seems to me very similar to what the author presented in the end.
- Row select: From N rows, produce 0-N rows.
- Column select: From N columns, produce 0-N columns.
- Table add: From MxN and OxP tables, produce max M+OxN+P table.
- Table subtract: From MxN and OxP tables, produce min 0x0 table.
This line of thinking reveals some normally hard-to-see similarities, such as `groupby` and `dedupe` sharing the same underlying mechanism. (i.e., both are "collapsing" row selects.)
The pandas API feels like someone desperately needed a wheel and had never heard of a wheel, so they made a heptagon, and now millions of people are riding on heptagon wheels. Because it's locked in now, everyone uses heptagon wheels, what can you do? And now a category theorist comes along, studies the heptagon, and says hey look, you could get by on a hexagon. Maybe even a square or a triangle. That would be simpler!
No. Stop. Data frames are not fundamentally different from database tables [1]. There's no reason to invent a completely new API for them. You'll get within 10% of optimal just by porting SQL to your language. Which dplyr does, and then closes most of the remaining optimality gap by going beyond SQL's limitations.
You found a small core of operations that generates everything? Great. Also, did you know Brainfuck is Turing-complete? Nobody cares. Not all "complete" systems are created equal. A great DSL is not just about getting down to a small number of operations. It's about getting down to meaningful operations that are grammatically composable. The relational algebra that inspired SQL already nailed this. Build on SQL. Don't make up your own thing.
Like, what is "drop duplicates"? What are duplicates? Why would anyone need to drop them? That's a pandas-brained operation. You want the distinct keys defined by a select set of key columns, like SQL and dplyr provide.
Who needs a separate select and rename? Select is already using names, so why not do your name management there? One flexible select function can do it all. Again, like both SQL and dplyr.
Who needs a separate difference operation? There's already a type of join, the anti-join, that gets that done more concisely and flexibly, and without adding a new primitive, just a variation on the concept of a join. Again, like both SQL and dplyr.
Props to pandas for helping so many people who have no choice but to do tabular data analysis in Python, but the pandas API is not the right foundation for anything, not even a better version of pandas.
[1] No, row labels and transposition are not a good enough reason to regard them as different. They are both just structures that support pivoting, which is vastly more useful, and again, implemented by both R and many popular dialects of SQL.
The author takes the 4 operations below and discusses some 3-operation thing from category theory. Not worth it, and not as clear as dplyr.
> But I kept looking at the relational operators in that table (PROJECTION, RENAME, GROUPBY, JOIN) and thinking: these feel related. They all change the schema of the dataframe. Is there a deeper relationship?
And the API is vastly superior to SQL is some respects from a user perspective despite being all over the place in others. Dataframe select/filtering e.g. df = df[df.duplicated(keep='last')] is simple, expressive, obvious, and doesn't result in bleeding fingers. The main problem is the rest of the language around it with all the indentations, newlines, loops, functions and so on can be too terse or too dense and much hard to read than SQL.
And FWIW I personally hate writing raw SQL. But the problem with the API is not the data operations available, it's the syntax and lack of composability. It's English rather than ALGOL/C-style. Variables and functions, to the extent they exist at all, are second-class, making abstraction high-friction.
But seriously I'm not in always in control of upstream data, I get stuff thrown over to my side of the fence by an organization who just needs data jiggled around for one-off ops purposes. They are communicating to me via CSV file scraped from Excel files in their Shared Drive, kind of thing.
Of course, a lot of one-off data analysis has no rules but get a quick answer that no one will complain about!
Nope. Duplicates in source data(INPUT) is natural, correct and MUST be supported or almost all data become impossible.
What is the actual problem is the OUTPUT. Duplicates on the OUTPUT need to be controlled and explicit. In general, we need in the OUTPUT a unique rowby a N-key, but probably not need it to be unique for the rest, so, in the relational model, you need unique for a combination of columns (rarely, by ALL of them).
I always warn people (particularly junior people) though that blindly dropping duplicates is a dangerous habit because it helps you and others in your organization ignore the causes of bad data quickly without getting them fixed at the source. Over time, that breeds a lot of complexity and inefficiency. And it can easily mask flaws in one's own logic or understanding of the data and its properties.
I stand by my warnings against using pandas as a foundation for thinking about tabular data manipulation APIs, but maybe the work has value regardless.
You make it sound like writing an SQL parser and query engine is a trivial task. Have you ever looked at the implementation of a query engine to see what’s actually involved? You can’t just ‘build on SQL’, you have to build a substantial library of functions to build SQL on top of.
"Porting" SQL to your language usually means inventing a new API for relational and/or tabular data access that feels ergonomic in the host language, and then either compiling it to SQL or executing it in some kind of array processing backend, or DataFusion if you're fancy like that.
Yes there is: SQL is one of many possible ways to interact with tabular data, why should it be the only one? R data frames literally pioneered an alternative API. Dplyr is fantastic for many reasons, one of those being that people like the verb-based approach
Furthermore I argue that dplyr is not particularly similar to SQL in the way you actually use it and how it's actually interpreted/executed.
As for the rest I feel like you're just stating your preferences as fact.
What is 'a vector of column domains D'? A description of how the data A maps to columns?
To your point, I wonder if the 73 distinct transforms were just different defaults/usability wrappers over these. And you may also get into situations where kernels can be fused together or other batching constraints enable optimizations that nice algebraic primitives don't capture. But that's just systems---theory is useful in helping rethink API bloats and keeping us all honest.
It is a balance. Too few transforms and they become to low level for my users. Too many and you struggle to find the transform you want.
He says himself the ideas are more important than the software package
As long as your core primitives are well designed (easier said than done!), this accomplishes two things: it makes your implementation simpler, and it helps guide and constrain your user-facing design. This latter aspect is a bit unintuitive (why would you want more constraints to work around?), but I've seen it lead to much better interface designs in multiple projects. By forcing yourself to express user-level affordances in terms of a small conceptual core, you end up with a user design that is more internally consistent and composable.