12 ms·
Is a Dataframe Just a Table? (2019) [pdf]
- deleted 7y ago[deleted]
- chenster 7y agoYes
- SatvikBeri 7y agoSummary: no, because row order matters in dataframes. That's why matrix operations are a better computational framework for dataframes than relational algebra.
- deleted 7y ago[deleted]
- gpderetta 7y agoThat's doesn't seem a very defining characteristic. Row order/index is just an implicit primary key column.
- SatvikBeri 7y agoThe 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.
- collyw 7y agoIt matters in many non-trivial queries as well.
- tsbinz 7y agoThis 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 7y agoIndeed. "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 7y agoOk, we've added three years to the title above. Thanks to both of you!
- lonelappde 7y agoOh I thought "Conference on Very Important Topics" was Twitter
- js8 7y 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 7y agoIn 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.
- js8 7y agoThe 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.
- idclip 7y 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 ?
- js8 7y 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).
- em500 7y 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 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?
- nnq 7y agoHow do you aggregate without field types? You'd end up writing custom code for untyped map-reduce... How can you code know what data to expect without typed field? Your code size can grow up even 10x if you need to assume that any record can have any shape... You could have more complex and user defined types in an ideal super-SQL, like "int or map:string->bool" etc., but you WANT types. They reduce complexity at all levels! You want them in you code, you want them in your data! Sure, go ahead, just use MongoDB with no constraints on collections structure... You'll end up with 5x more code in you application and 5x more bugs. SQL and the relational model help you think more, code less (and slower - but this is an advantage) and have less bugs with less effort! If you're lazy and prefer thinking deeper to "working harder", you'll always prefer properly typed SQL :P (Unless you're working with stuff that's naturally shaped as graphs or tensors.)
- wodenokoto 7y agoI 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 7y agoTables 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.
- js8 7y agoI 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 7y agoBut 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”.
- tanilama 7y agoIt 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 7y agoCan 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.
- somurzakov 7y agoeverything 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
- ezzzzz 7y agoHow about working with poorly designed schemas? I work with SQL-Server as well, dealing with legacy data designed around imperative t-sql programming. Our 'BI-Solution', SSRS, crawls on pretty simple queries, where 'hacks' need to be done, joining on same table, all kinds of dirty tricks... I don't know... I honestly feel like 'BI-Solutions' are a poor-persons Python if you are doing anything more than simple dashboards. Something that can be done in 2 lines of code in a Notebook requires endless fiddling in an IDE, to produce something not easily reproducible. Aside, I've no experience with Tableau or Power-BI, just know that Crystal Reports and SSRS which are pretty painful.
- kyberias 7y agoI 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 7y agoIn R a dataframe is just a named list of vectors, where each vector has equal length.
- rankam 7y agoThere'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.
- kyberias 7y agoThat 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 7y agoThe 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 7y ago> being against Putin's stated principles walk carefully
- playing_colours 7y agoA dataframe may be a table, but it can also be a matrix by supporting some matrix operations.
- gpderetta 7y agoyes, the problem is, it is not a very good table.
- leto_ii 7y 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 7y agoCould you give some examples of this issue with pandas?
- leto_ii 7y agoSome 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]
- rcar 7y agoNot sure that I'd consider those all that eloquent since it's just the product of 2 different pieces of syntactic sugar (df.a being shorthand for df["a"] and df[<index filter>] shorthand for df.loc[<index filter>]).
- leto_ii 7y agoHere's another example: https://stackoverflow.com/questions/49936557/pandas-dataframe-loc-vs-query-performance https://stackoverflow.com/questions/49936557/pandas-datafram... What's the difference between query() and loc()? Do they evaluate to the same thing under the hood? Is one better than the other? In what cases? These are questions that don't have obvious answers at first sight.
- sammycdubs 7y agoArticle aside - that LaTeX theme is dope
- r-zip 7y agoThis 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 7y agoReading 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 7y agoMan, 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 7y agoshe* http://yifanwu.net/ http://yifanwu.net/