3 ms·

I really love pandas and dplyr, but honestly both of them are inferior to modern SQL. In my workflows, I’ve almost exclusively replaced them with Postgres and i

I really love pandas and dplyr, but honestly both of them are inferior to modern SQL. In my workflows, I’ve almost exclusively replaced them with Postgres and it’s foreign data wrappers, spit out the results to a text file and then load into R or Python. It’s a more complicated environment for sure, but still more efficient.

udkl 9y ago
Are there any specific examples that will illustrate your point ?
- darksaints 9y ago
  The biggest painpoints IMO involve: * anything with data that doesn't fit in memory * anything that could trivially be written in an SQL window function * anything involving table joins, especially multi-column and/or non-inner joins. * anything involving subtotal-like summaries (easily implemented using GROUPING SETS, CUBE, and/or ROLLUP)
sevensor 9y ago
I've found pandas great for interactive sessions, for the most part, but I found doing joins was way too fiddly and I'd much rather do it in SQL. Could be I missed an important pandas concept in there somewhere that would have made it make more sense, or maybe the API has improved since the last time I tried. Generally I've found my SQL ends up being clearer and more readable after the fact.
- chrisgd 9y ago
  https://github.com/yhat/pandasql https://github.com/yhat/pandasql
arafa 9y ago
This is so true for me as well. I actually use more SQL, Bash, and even MS Access DBs and Excel pivots connecting to text files to get the data in the right, aggregated shape first. Then I load into R or Python as you say so I can work with the data in memory.
tomnipotent 9y ago
Have you tried Apache Spark + pyspark + Jupyter? Spark allows SQL against all data frames bound to the current context: df1.createOrReplaceTempView("table1") df2.createOrReplaceTempView("table2") spark.sql("SELECT * FROM table1 t1, table2 t2 WHERE t1.id=t2.fid")
- darksaints 9y ago
  I haven't tried it but there is a package for R that I find similarly useful, called sqldf. The back end is sqlite, which is quite fast for anything that fits on disk. I find both hobbled, however, by their rather simple SQL dialects. No window functions, common table expressions, grouping sets or similar, etc. An improvement, no doubt, but Postgres still has it beat. I honestly wish the vast corpus of effort put into statistics and machine learning in R and Python were more easily portable as Postgres foreign function packages. I've tried the ones I've seen mentioned and they were all horrible, mostly due to embedding an entire language's runtime and memory model on top of the SQL inner workings. A project on my backburner, waiting for the time to work on it :)
cwyers 9y ago
I've only touched enough Pandas to get a feel for it, so I'm talking more here about dplyr, but I think most of this applies to Pandas as well. Yes, SQL is incredibly battle-tested, and has a ton of features that newer systems need to catch up on, and there's a ton of work sunk into performance on too-big-for-RAM datasets that standalone Pandas and dplyr doesn't have. (But see dbplyr, for example.) But actually programming in SQL sucks. There's no such thing as code reuse, as parameterization, as composability, etc. Everything about DRY goes right out the window when writing SQL and copy-pasta is the law of the land. Writing dplyr is so much more comfortable for me with just a little bit of learning about it. And I think it's going to be so much easier to extend dplyr to cover the gaps between it an SQL than it would be to try and fix the ergonomics of SQL.

udkl 9y ago

Are there any specific examples that will illustrate your point ?

darksaints 9y ago
The biggest painpoints IMO involve: * anything with data that doesn't fit in memory * anything that could trivially be written in an SQL window function * anything involving table joins, especially multi-column and/or non-inner joins. * anything involving subtotal-like summaries (easily implemented using GROUPING SETS, CUBE, and/or ROLLUP)

sevensor 9y ago

I've found pandas great for interactive sessions, for the most part, but I found doing joins was way too fiddly and I'd much rather do it in SQL. Could be I missed an important pandas concept in there somewhere that would have made it make more sense, or maybe the API has improved since the last time I tried. Generally I've found my SQL ends up being clearer and more readable after the fact.

chrisgd 9y ago
https://github.com/yhat/pandasql https://github.com/yhat/pandasql

arafa 9y ago

This is so true for me as well. I actually use more SQL, Bash, and even MS Access DBs and Excel pivots connecting to text files to get the data in the right, aggregated shape first. Then I load into R or Python as you say so I can work with the data in memory.

tomnipotent 9y ago

Have you tried Apache Spark + pyspark + Jupyter? Spark allows SQL against all data frames bound to the current context: df1.createOrReplaceTempView("table1") df2.createOrReplaceTempView("table2") spark.sql("SELECT * FROM table1 t1, table2 t2 WHERE t1.id=t2.fid")

darksaints 9y ago
I haven't tried it but there is a package for R that I find similarly useful, called sqldf. The back end is sqlite, which is quite fast for anything that fits on disk. I find both hobbled, however, by their rather simple SQL dialects. No window functions, common table expressions, grouping sets or similar, etc. An improvement, no doubt, but Postgres still has it beat. I honestly wish the vast corpus of effort put into statistics and machine learning in R and Python were more easily portable as Postgres foreign function packages. I've tried the ones I've seen mentioned and they were all horrible, mostly due to embedding an entire language's runtime and memory model on top of the SQL inner workings. A project on my backburner, waiting for the time to work on it :)

cwyers 9y ago

I've only touched enough Pandas to get a feel for it, so I'm talking more here about dplyr, but I think most of this applies to Pandas as well. Yes, SQL is incredibly battle-tested, and has a ton of features that newer systems need to catch up on, and there's a ton of work sunk into performance on too-big-for-RAM datasets that standalone Pandas and dplyr doesn't have. (But see dbplyr, for example.) But actually programming in SQL sucks. There's no such thing as code reuse, as parameterization, as composability, etc. Everything about DRY goes right out the window when writing SQL and copy-pasta is the law of the land. Writing dplyr is so much more comfortable for me with just a little bit of learning about it. And I think it's going to be so much easier to extend dplyr to cover the gaps between it an SQL than it would be to try and fix the ergonomics of SQL.