Is a Dataframe Just a Table? (2019) [pdf]

https://plateau-workshop.org/assets/papers-2019/10.pdf

by luu • 6 years ago

193 113 6 years ago

chenster• 6 years ago

Yes

SatvikBeri2• 6 years ago

Summary: no, because row order matters in dataframes. That's why matrix operations are a better computational framework for dataframes than relational algebra.

gpderetta5• 6 years ago

That's doesn't seem a very defining characteristic. Row order/index is just an implicit primary key column.

SatvikBeri2• 6 years ago

The point is which operations you optimize for. Most SQL databases aren't well-optimized for matrix multiplication, but are great with search-style queries, and the opposite for dataframes.

collyw3• 6 years ago

It matters in many non-trivial queries as well.

tsbinz• 6 years ago

This doesn't seem to be from 2016 (some identifiers suggest that it is, but it cites papers up to 2018 and it says that a tweet from 2016 is two years old).

MrManatee• 6 years ago

Indeed. "Conference on Very Important Topics 2016" is not a real conference, but placeholder from a template. Maybe it was left behind by accident? The paper is from the PLATEAU Workshop 2019.

dang• 6 years ago

Ok, we've added three years to the title above. Thanks to both of you!

lonelappde5• 6 years ago

Oh I thought "Conference on Very Important Topics" was Twitter

js84• 6 years ago

What should we make of it?

People are idiots! (I am sorry to say that, I don't really mean it, I empathize, everyone sometimes is.)

Yes, dataframe is pretty much just a table. (And yes, GraphQL is a poor reinvention of SQL.) However, to be fair, there are different considerations. Database needs to know things like storage constraints and foreign keys (so you have many different column types), when you're doing just analytics (i.e. pandas), you pretty much only need two types - number and (trimmed) string (and sometimes a datetime, but that's just conveniently formatted number). (I think SAS got that right.)

Anyway, I think the way out of this mess is to have a functional, Haskell-like language for processing tables (not necessarily Turing complete) that would subsume declarativness of SQL, and that could be compiled into different targets. The language would basically specify some (limited, so recursion in that language would not be allowed) processing of tables (of known types), and you could then compose these processing pieces, or integrate them with custom processing in other language.

I understand why people hate SQL, it is on some level hard to write and compose. I think a correctly designed functional language would help here. Or maybe just tear LINQ out of the .NET ecosystem.

CodesInChaos• 6 years ago

In the re-inventing SQL department, I'd take a look at EdgeQL/EdgeDB. It's not perfect, but much closer to a functional language and composes well.

I would not consider GraphQL a poor reinvention of SQL, since its niche of decoupling and simplifying untrusted high latency clients is too different for flexible queries created by a trusted server. It competes with REST and RPC, not SQL.

GraphQL's native operations are limited to following a foreign-key link, which has predictable performance (quasi linear in the number of objects in the response) and selecting a subset of fields (reduce the response size and enable field level deprecation and usage tracking). These limitations prevent both malicious clients and less performance concerned front-end developers from putting excessive load on the database. These limitations also allow it to work with both data stored in a database and data generated by an application, while supporting SQL is pretty much limited to being processed by a database.

js84• 6 years ago

The way I see it, SQL and GraphQL are solving somewhat complementary problems. In SQL, I have a structure (all these tables that possibly have to be joined) in the database and I want to pick something out as a simple result table. In GraphQL, I create the more complex structure on the output.

But I do consider GraphQL somewhat unnecessary, because if those REST APIs composed just like tables do in the database, then you wouldn't need GraphQL, and you could run a normal query. (There is also a problem of externalities, putting the processing costs on the client is cheaper.)

And thanks for pointing out EdgeDB.

virgilp• 6 years ago

You're talking a lot about implementation, when you compare 2 languages. In principle - it's worth inventing a new language when you can express some common patterns clearer/ more naturally. Otherwise - just make a query engine that's very performant for a subset of SQL (SELECT + following foreign-key link), and outright reject all other kinds of SQL.

pdexter• 6 years ago

Sounds like datalog could fit this bill

nikhizzle• 6 years ago

I think you are describing something close to the Dataset API in Spark. Spark is built on the RDD, a novel data structure that creates transparent concurrency and distribution. Additionally, you can access the same data with 4 APIs, one of which is SQL, and another which is a typed functional API.

The RDD paper is one of my favorite papers, and is great bed time reading.

https://www.usenix.org/system/files/conference/nsdi12/nsdi12...

lincpa3• 6 years ago

Everything is RMDB

I advocate: Building a relational data model on top of hash-map to achieve a combination of NoSQL and RMDB advantages. This is actually a reverse implementation of PostgreSQL.

[Clojure is a functional programming language based on relational database theory](https://github.com/linpengcheng/PurefunctionPipelineDataflow...)

[Everything is RMDB](https://github.com/linpengcheng/PurefunctionPipelineDataflow...)

[Implement relational data model and programming based on hash-map (NoSQL)](https://github.com/linpengcheng/PurefunctionPipelineDataflow...)

kumarvvr2• 6 years ago

Can you elaborate on the topic of a "relational data model on top of a hash map"?

Are there any books that cover the concepts?

codr7• 6 years ago

I agree, but prefer a tree map in combination with natural keys where possible to get ranges & sorting without complicating the design. Then I embed additional tree maps in records to model has-many-relations. And I know SQL very well, but this setup often provides the capabilities I need and is much more convenient to deal with.

twelvechairs• 6 years ago

I don't think it needs so much a full blown language - its just an api that allows you to feed data in and pull it out for any arbitrary variety of data (including types/classes and links between data), but then has special commands to tell it how to optimise data storage (for speeding up a particular kind of access need, for minimising data storage, etc.)

gpderetta5• 6 years ago

As a big fan of the relational model (but not so much of SQL) I just wish that pandas didn't tread indices specially but just as a normal columns. Also, multiindices should just be indices on multiple columns. And I should be able to have as many indices as I want.

kthielen• 6 years ago

Anyway, I think the way out of this mess is to have a functional, Haskell-like language for processing tables

I agree, and in a Haskell-like setting we can have any structured types (rather than just primitives).

That’s why I built hobbes: https://github.com/Morgan-Stanley/hobbes

eru• 6 years ago

Have you had a look at Datalog? It's a carefully selected subset of prolog that corresponds to primitive recursive functions.

So: plenty of computational expressivity, but solidly removed from Turing completeness.

Adding types for relational algebra to Haskell is a bit of a slog. Not because it's impossible, but just because the natural way to express those data types is not what comes natural in Haskell.

mumblemumble2• 6 years ago

OK, so, disclaimer, I haven't ever used GraphQL with live ammunition, but I really don't get the impression that it can be dismissed so easily.

TFA does a good job of showing that, while SQL is a cleaner design and theoretically better than the hot mess that is Pandas dataframes in every way, the dataframes API offers a lot of conveniences and ergonomic affordances that can make it more pleasant to program against for certain tasks. As someone who spends a heck of a lot of time using Pandas to prep data for loading into an RDBMS, I agree 1,000%. As someone who, once upon a time, did assembly language programming on both RISC and CISC architectures, and, once upon another time, bounced back and forth between Scheme and Objective-C, this sort of situation doesn't surprise me at all. It's just the standard "worse is better" story that we all know and love to hate.

I suspect it's similar for GraphQL. For example, the JSON basis for the language does make it awful to write by hand, but, if you're talking APIs here, you may not be doing too much much hand-authoring of queries. And it's going to be a lot easier than SQL to manipulate programmatically, since, particularly in a language like JavaScript or Python, its syntactic structure maps trivially to basic data structures that already exist in the language.

TallGuyShort• 6 years ago

> if you're talking APIs here, you may not be doing too much much hand-authoring of queries

No experience with GraphQL myself, but this is a very good point. A lot of the practical problems that come with SQL queries boil down to the code that constructs a string to be used as the query, so it can then be deconstructed by different code to execute the query. There's a lot of mistakes that happen right there.

tveita• 6 years ago

And yes, GraphQL is a poor reinvention of SQL.

No. GraphQL is an RPC specification. There is not a single comparison operator defined it the GraphQL spec. There is no way to join two separate collections. GraphQL was never meant to be an alternative to SQL, and people mainly try to compare them because they both end in "QL".

rand_r• 6 years ago

The main similarity is the whole idea of declaratively saying what you want in a single request. In SQL, you use joins or subqueries, and in GraphQL you use use nested edge/node blocks.

Either way, you define what you want in a nested/tree-like manner and submit one big-ass request to the server.

The difference is that GraphQL is usually way less verbose and tedious to type out, but they’re fundamentally the same idea.

idclip4• 6 years ago

“Now GraphQL has almost 15K GitHub stars and a large and active developer community. What should we make of it?“

don’t know why, made me smile. Didnt know the DB world is so edgy - its cute.

I say bygons about graphql, usability always trumps fad in the end and “nature will decide”, nature the masses and hordes of “US over Time”. Does GraphQL do warehousing well ?

Well im not militant and think its cool.

One could say, based on my experience at uni with random access machine calcs by hand, that everything a computer does reduces to a table. Anyone with examples of things that arnt essentially tables ?

js84• 6 years ago

Anyone with examples of things that arnt essentially tables ?

Well, my definition of table: Fixed number of columns of possibly heterogenous types. Variable number of rows that have all the same type.

So, for starters, a matrix is not a table. Nor is a list, a tree, or a hash table (KV store).

em5002• 6 years ago

Variable number of rows that have all the same type.

Why do they need to have the same type? In sqlite a field can have a different type in every record/row. (https://www.sqlite.org/datatype3.html). Is having a fixed typed fields fundamental to the concept of a table, or just a property of most SQL implementations?

idclip4• 6 years ago

Oh interesting, because i wanted to say “everything is a tensor” before i write the long post

So generics and mixed types is where it hits a wall with tables? Though those mixed structured can just be broken down to collections of tables and enumerated, no? Then call the table of tables a table again?

Also why is a matrix not a table? Because its a super type ?

lonelappde5• 6 years ago

Thanks to GraphQL, the DB world is edgy and nodey.

wodenokoto2• 6 years ago

I liked the article, although it kinda conflates SQL and database and Pandas and dataframe.

It does acknowledge other dataframe implementation (Base R and Spark) but most of the arguments about what defines a dataframe are taken from the Pandas API.

roenxi• 6 years ago

Tables and data frames are both leaves in the far more fundamental flow that beginners don't pick up because it is too simple - the relational model of data. The real problem is the the basic normal forms are so obvious and simple it is difficult to tell if people designed around them on purpose or stumbled onto the right path.

I suspect the distinction between tables and data frames (and arguing about query languages) is from people who have lost sight of the important things when dealing with data - the relational model and a system that supports relational algebra. Beyond that, features and optimisations need to be justified in terms of today's (and tomorrow's) needs which are specific to the data. The only major issue with SQL is a lack of support for something like R's tidyverse gather() and spread() operations which could be considered as missing operations in relational algebra.

js84• 6 years ago

I think one limitation of what you're saying is that you need to process the data before they become relational model. You need to integrate and normalize, and therefore, at some point, you need to support datasets that are not in normal form. Or perhaps you don't even need to normalize.

But I also agree with you. I think we are teaching programming wrong, we start with imperative programming, but perhaps we should start with data modelling. How the data look like? What are the constraints? Can we test the constraints? How many bits I need to store it?

"Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won’t usually need your flowcharts; they’ll be obvious." -- Fred Brooks

ak39• 6 years ago

But that was exactly how programming was taught 30 years ago. Relational data modeling was your starting point. Then object orientation came along and we suffered the dreaded object relational impedance mismatch when we couldn’t translate invoice header to invoice line-items in an object oriented way. Yet somehow the majority of development continued down this design philosophy, abstracting the data model further and further away (it was only heresy for a short while that data models were being generated by tools of process models). Then NoSql. Then graphQL dragging the lamppost of software development further away from understanding data first.

The central design premise for relational data modeling was “if your model could potentially allow inconsistencies, assume the inconsistencies”. Today that premise is easily brushed aside with “you’ll never get anyone manipulating this table without going through this layer” or “we then group by these columns to show unique records to the user”.

perl4ever2• 6 years ago

I always felt like the relational model, SQL, and the extensions and add-ons like PL/SQL come from people with totally different worldviews and make quite a mess together. I couldn't design something better than PL/SQL, but it just seems so, so wrong to me.

On the other hand, the purists* who rant about nulls, I think have missed something as well.

But on the whole, I would like to see something related to SQL, that tries to improve it without grafting things on, that has an overall vision and an approach of simplifying and making it more coherent. Not being committed to the syntax, and wanting to close the loop more between manipulating data and manipulating the language itself.

I don't know, if you say SQL is a functional programming language, ok, and you take some common functional language that has nice syntax and make it consistent with the relational model, what do you get?

*Of which my impression was formed by (IIRC): "Relational Database Writings, 1989-1991 by C. J. Date" (which Amazon seems to want over $1,000 for right now, in paperback)

johnthescott2• 6 years ago

i am confused. R's "gather" just merges a set of columns values into a single column. how is that beyond the scope of sql?

tanilama2• 6 years ago

It is worse than a table.

Dataframe don't really have a clear boundary as what it can do or cant do. I see no problem why you can't shoehorn some control flow logic into it which makes it pretty much just an executor of arbitrary computation graph specified in DSL, which relies on however the developer decides to implement it.

I'd rather take SQL because I have a better understanding what it is doing.

kortex• 6 years ago

Can you drop nans, parse strings to floats, apply arbitrary lambdas over a rolling window, take the cosine of those values, and plot a graph against time, in a single line of sane SQL?

Easy in Pandas.

Dataframes are not tables; tables are not dataframes. It's nearly as apples-to-bananas as comparing Python lists to C arrays.

somurzakov5• 6 years ago

everything you mentioned can be done easily through database schema. window functions work well in SQL. plots are easily done in any BI solution that hooks up to any database.

pandas is just poor man's SQL+BI. pandas stores everything in memory and has many limitations.

in SQL Server I can easily churn through terabyte sized database and get the data I need, because the schema is well designed with partitioned tables, clustered indexes and a well designed SQL takes less than a second to run against >1TB database. It even allows a team of more than 20 people to work with the same SQL and query it simultaneously.

i would love to see how you can analyze 1TB csv file, or a pile of 100 csv files totalling over 10TB where you have yet to discover the schema and how tables join together. and I am doing it with on a simple $700 workstation, not even using any hadoop nonsense

oarabbus_• 6 years ago

Technically all SQL compiles into a single line.

Less pedantically you can quite easily and elegantly drop nans, str->float, perform just about any operation over a rolling window and perform trig functions on it. Generally in a much more sane way than pandas.

In my life as an analyst and data scientist I've found SQL to be far, far superior to pandas for something like 90-95% of tasks.

tanilama2• 6 years ago

Can you drop nans, parse strings to floats, apply arbitrary lambdas over a rolling window, take the cosine of those values, and plot a graph against time, in a single line of sane SQL?

Yes you can, Postgres support Python UDF btw. Though I don't think that is necessary a daily common feature to use.

SQL is pretty powerful if you look deep

kyberias2• 6 years ago

I was unable to learn R because I couldn't understand what a dataframe is. It was irritating that it wasn't defined clearly and there seemed to be no connection to terminology that was familiar to me (relational databases, SQL tables etc.).

jackbrookes• 6 years ago

In R a dataframe is just a named list of vectors, where each vector has equal length.

rankam• 6 years ago

There's no other way to say this without sounding rude, but you weren't unable to learn R because you couldn't understand what a dataframe was - you were unable to learn R because you gave up. Blaming a data structure for the failure seems like a bit of a stretch.

kyberias2• 6 years ago

That is what I said. I didn't understand what a dataframe was. Note that I didn't really blame the data structure. I said it was an irritating factor. Someone else might not have been as irritated. But if R was closer to what I already knew, learning would have been easier. For me.

gowld• 6 years ago

The article is a nice comparison of pros and cons of Pandas and SQL, but the title and ensuing comments are misleading and off the point, akin to favorite arguments like "is Haskell types just C++ objects"? They have similar and differences, in core semantics and in ergonomics.

Pandas has a perlish API designed for abbreviating common tasks, combined with a Pythonic (per common practice, despite being against Putin's stated principles) disregard for informative use of types, instead a simple coherent, principled, layered API. That makes it look like "SQL done wrong" because the semantic differences aren't laid out explicitly.

Iwan-Zotow• 6 years ago

being against Putin's stated principles

walk carefully

playing_colours• 6 years ago

A dataframe may be a table, but it can also be a matrix by supporting some matrix operations.

gpderetta5• 6 years ago

yes, the problem is, it is not a very good table.

leto_ii6• 6 years ago

Having many different ways to express the same logic makes it hard for developers to understand programs of heterogeneous styles. Besides having varying ways to express the same simple logic, the sheer number of APIs (> 200) that are not only overloaded but also have default parameters that may change version to version, making it hard to remember the APIs.

It's a bit tangential to the main point, but I do agree with this remark. I have always found Pandas uncomfortable to work with. I'm never sure if I'm doing things in the most efficient/idiomatic way and I've found it hard to be consistent over time, especially since I've picked up different bits of code from different places.

I've gotten a lot more efficiency out of R, especially the data.table package.

bobbylarrybobby• 6 years ago

Could you give some examples of this issue with pandas?

leto_ii6• 6 years ago

Some of the examples provided in the paper are eloquent:

•df[df.a>3]

•df[df["a"]>3]

•df.loc[df.a>3]

•df.loc[df["a"]>3]

airstrike2• 6 years ago

I mean, perhaps comparing R to panda is too low a bar.

If I read "Having many different ways to express the same logic makes it hard for developers to understand programs of heterogeneous styles" in a vacuum, the very first thing I would think of is R.

leto_ii6• 6 years ago

Indeed, but I was more specifically referring to smth like data.table, which I found simpler to use than Pandas.

javitury• 6 years ago

Agree, data.table is the perfect blend of table and dataframe syntaxis for scientists. It's also performant.

saeranv• 6 years ago

I feel like I'm constantly looking up SO or blog posts that benchmark Pandas methods while I'm coding with Pandas. You have to, since the inefficiency you add with a slower method is nontrivial.

closed• 6 years ago

I just finished a lengthy analysis of why pandas groupby operations ends up harder to use than R's dplyr or data.table.

For example, a grouped filter is very cumbersome in pandas.

Interested to hear if you think it gets at the heart of the problem.

https://mchow.com/posts/2020-02-11-dplyr-in-python/

leto_ii6• 6 years ago

I really think it does.

I also appreciate your idea of porting dplyr to python, keep up the good work :)

This table sums up some of it:

operation | time

apply score + 1 | 30s

apply score.values + 1 | 3s

transform score + 1 | 30s

transform score.values + 1 | 20s

It seems to me that pandas is simply a leakier abstraction than dplyr, data.table etc. As a user of the library in most instances you shouldn't have to profile your code to figure out why things behave the way they do (btw, thanks for pointing out snakeviz - it seems like a useful tool).

This being said, we shouldn't complain too much about pandas - it is in the end a very important and useful tool.

wodenokoto2• 6 years ago

result length dependent operations: to calculate a mean in this case we have to pass the string “mean” to transform. This tells pandas that the result should be the same length as the original data.

    g_students.score.mean()

has the same length as using `g_students.score.transform('mean')` but the result has different values!

I think that is a great point to add to you very interesting article. I wouldn't know which of the two operations is correct to use, and I would not notice anything wrong, or odd with either method in a code review, so this is ripe for adding wrong results in a production environment.

sammycdubs• 6 years ago

Article aside - that LaTeX theme is dope

r-zip• 6 years ago

This guy's writing is really inconsistent. "v." and "vs.", plus singular/plural disagreements all over the place. I think the CS/ML/EE fields need to raise their standards w.r.t. editing. Interesting paper though.

dwenzek• 6 years ago

Reading the sections 2.2.3 (about joins) and 3.5 (about code comprehension) took me aback! I came with a positive a priori for dataframes, but changed my mind. SQL is far to be perfect, but at least there are sound foundations!

commandlinefan• 6 years ago

Man, I wish there were more honest comparisons of approaches like this one rather than the "shut up, stupid" stackoverflow example he references at the start.

eoakes• 6 years ago

she*

http://yifanwu.net/

Is a Dataframe Just a Table? (2019) [pdf]

Related Stories