For what it's worth, I maintain a library called siuba that lets you generate SQL code from pandas methods.
It's crazy to me how people use SELECT * -> pandas, but also how people in SQL type a ton of code over and over.
Practical SQL for Data Analysis
161–170 of 198 posts
Re: Practical SQL for Data Analysis
#162Earlier quoted context omitted.
If you know SQL well, then doing exploratory analysis in SQL is perfectly reasonable. Especially if you are using a high-performance analytic database. Disclosure: I develop high-performance analytic database systems with SQL frontends.
What do you mean by exploratory analysis? Lets assume that my dataset contains features which are not normally distributed, and I want to check for coskewness and cokurtosis matrices before and after I remove outliers with a method specific to my field of research, found in a academic paper. Am I supposed to code all of this in SQL? What is your definition of exporatory analysis, and which metrics do you have in mind…
> Am I supposed to use... excel to do that?
This is gratuitous. You have a clear bias, granted, because it seems your domain is so specific, only a procedural language will do. But it seems you are unfamiliar with modern SQL tools. Some of the obvious ones that come to mind: Metabase[0] for visualisation or Apache MADlib[1] for in-database statistics and machine learning.
[0] https://github.com/metabase/metabase [1] http://madlib.apache.org/
Re: Practical SQL for Data Analysis
#163Nice article. Pandas gets the job done but it's such a step backwards in terms of useability, API consistency and code feel. You can do anything that you can possibly need with it but you regularly have to look up things that you've looked up before because the different parts of the library are patched up together and don't work consistenly in an intuitive way. And then you end up with long lines of().chained().['ex…
This is exactly my experience. I've found becoming proficient with Pandas to take much more time than other data manipulation libraries like dplyr or Pyspark dataframes. The Pandas way of doing things is just not memorable or intuitive.
Re: Practical SQL for Data Analysis
#164Earlier quoted context omitted.
I've never seen anything about inplace being a poor idea to use. Is that documented anywhere or is that ticket the only info about it?
It's not particularly front-and-centre, but it's all over various discussion boards if you go looking for it directly. I'd like the debate to be more visible, personally, particularly whenever the argument gets deprecated for a particular method. Essentially, `inplace=True` rarely actually saves memory, and causes problems if you like chaining things together. The people who maintain the library/populate the discussi…
I wish it wasn't something you had to go looking for. I never would have thought that it would be an issue. If you do Google searches for inplace, you get no results on the first page discouraging its use.
The documentation should clearly say if the inplace argument causes an internal copy, because the availability of it implies that it doesn't. I've used inplace many times with Pandas because I've had code that I know is working with large amounts of data and I've thought "well I probably shouldn't chain these and cause tons of unnecessary allocations just to have prettier code".
Re: Practical SQL for Data Analysis
#165> This benchmark does not mention the memory consumed by the database itself - this is intentional. [...] Whether you decide to use the database or not, the memory is already paid for, so you might as well use it! This sentence is a big red flag for me. An analysis of a stategy that pushes work towards a subsystem, and then purposedly ignores the perforance implications on that subsystem is methodologically unsound.…
oddly, I was having this conversation with a consulting client very recently. The client asked for an "in-memory datastore" .. personally, I am fluent in PostgreSQL and had already done some early versions of a solution using Postgres. Speaking, I was trying to get across the idea that Postgres can be configured specifically to a RAM balance such that, it starts to act like an "in-memory datastore". You can think of the server instance as a "wrapper" around the action of tables and access. The client was/is skeptical and also knows no Postgres personally. Perhaps there are some architectural parts missing in his mind that blur multi-node, cloud'y datastores, versus single node.
Postgres certainly comes from the days of single-box, many workloads, many users.. Currently, there are more varied scenarios, and that common case is a lot less common. As this article shows very well, Postgres is actually a very capable tool.
Re: Practical SQL for Data Analysis
#166I think one of the less discussed advantages of SQL and reasons why it might be so prevalent in the analytics world is that users don't really need to spend much time to set up their environment. I can send a query to a less technical person with my analysis for them to run. They can put the processed data into a spreadsheet and do whatever they want.
Re: Practical SQL for Data Analysis
#167Earlier quoted context omitted.
parent post says "devs do SELECT *" and ... relational databases can have a lot more data in one table, or related tables, than one query needs. It is often very wasteful of RAM to get ALL and filter again
If your query does " * " it gets all the columns in all the tables. Often, the optimizer when all the columns you need are on the index, never visits the actual table (I remember the term covered index). " * " basically screws this up. "Select *" should never be used in anything in an actual production environment.
Covering index is right.
Select * has a few valid use cases.
If you're selecting from a cte or subquery then writing the same column list twice is redundant and increases complexity/risk of mistakes.
* is also harmless for count and exists.
Re: Practical SQL for Data Analysis
#168Question, if I have a CSV file that I'd like to do some quick SQL queries on before moving the results into Pandas. What would be good resource to do this? Preferably compatible with the rest of the Python-dataframe ecosystem and as simple as pd.read_csv()
Try https://bit.io/. You can drop a CSV file and query the data with SQL.
Re: Practical SQL for Data Analysis
#169Earlier quoted context omitted.
I much prefer writing actual functions in a real programming language to the verbose non-reusable non-composable relic that is SQL syntax. Give me a better query language and I will gladly drop Pandas and Data.Table.
Yes, SQL does have the problem of bad composeability and some things being less explicit than they are when working with dataframes. Dplyr is probably the most sane API out of all of them.
I agree, the degree of expressiveness that dplyr achieves is impressive. The library has had a great impact on my work. The syntax is easy to remember so you don't have to constantly look up minor variations in usage. Also, the pipelining aspect makes it dead-simple to debug. It's especially powerful when combined with the purrr library: https://purrr.tidyverse.org/
Re: Practical SQL for Data Analysis
#170Earlier quoted context omitted.
How many TBs of data are we really talking here, if it's just 6M rows? Surely this processing could easily be done on a single machine.
It doesn't say it is just 6m rows. It is 6m patient, which only hints that one of the dimension tables is 6m. Facts gathered in patients might be significantly larger. Also, my experience is saying, if you have hundreds of queries running simultaneously, it is not the volume of data that can be a bottleneck. Depending on the system it can be anything, starting from acquiring lock on a record or initiating a transacti…
True, the number of events / records is probably significantly larger than 6M. I still have a hard time believing any reasonably modern datawarehouse would struggle with queries on this dataset.
If you've got 1400 analysts, I hope you've exported your data from an OLTP database to an OLAP database. Those generally are much easier to scale to many users.