Practical SQL for Data Analysis • Hacker News Board

Re: Practical SQL for Data Analysis

#101

Post by shankr » Mon, May 03, 2021, 6:24 PM UTC

EForEndeavour wrote: post #68

disgruntledphd2 wrote: post #60

Earlier quoted context omitted.

Pandas is just a bad API, to be honest. I know base-R very, very well (which was one of the inspirations, I believe) and I still spend most of my time looking stuff up. It's such a shame that python doesn't have a better DF library.

I've used pandas regularly for the past ~5 years and find its API intuitive enough not to complain. I can write and read decently long pandas chained expressions fluently, but I barely know any R. Am I unwittingly a hostage of an inferior API and don't know what I'm missing?

This might not seem like a big issue but in the beginning, these were my issues

1. Not so easy way to rename columns during aggregation

2. The group by generates its own grouped by data and hence you almost always need `reset_index`

3. Sometimes group by can convert a dataframe to series

4. Now `.loc` has provided bit consistent indexing/slicing, but earlier you had `.ix` `.iloc` and what not

These are something I can remember from top of my head. Of course all of these have solutions, but it makes pandas much more verbose. In R, these are just much more succinct.

Re: Practical SQL for Data Analysis

#102

Post by EForEndeavour » Mon, May 03, 2021, 6:29 PM UTC

disgruntledphd2 wrote: post #63

nerdponx wrote: post #2

SQL syntax sucks for doing non-trivial data analysis. I've tried it. Verbose, no composability or code reuse, not really portable across different databases, no easy interoperability with other tools, limited editor/IDE support, etc. I guess if you have huge amounts of data (10m+ rows) already loaded into a database then sure, do your basic summary stats in SQL. For everything else, I'll continue using SQL to get the…

> (i.e. not relying on data engineers to do basic ETL for you). Is this actually a thing? Surely it can't be a thing.

In a sufficiently large org, why not?

Re: Practical SQL for Data Analysis

#103

Post by slver » Mon, May 03, 2021, 6:33 PM UTC

spamalot159 wrote: post #9

I think for a lot of people, SQL is a skill that doesn't stick. You learn enough to do the queries you need for your project, they work then you forget about them as you work on the rest of your project. These skills are perishable. Left outer join? Yeah, I knew what that was some time ago, but not anymore The days of dedicated SQL programers are mostly gone.

We have a lot more clueless developers on average, which makes it seem like no one uses SQL anymore beyond basics. But we're still here, believe me. No one today is a dedicated X programmer, but doesn't mean we're all clueless about X.

Re: Practical SQL for Data Analysis

#104

Post by tomrod » Mon, May 03, 2021, 6:39 PM UTC

protomyth wrote: post #86

solaxun wrote: post #84

Earlier quoted context omitted.

I understand your point generally but I don't understand this example. If you need data from 15 tables, you need to do those joins, regardless of prefiltering or subgrouping, right?

Well, yes but. Why do you need 15 tables for analysis queries and why isn't someone rolling those tables into something a bit easier with some backend process.

> why isn't someone rolling those tables into something a bit easier with some backend process.

You'd identified why we're all going to have jobs in 100 years.

Automation sounds great. It's exponentially augmenting to some users in a defined user space.

Until you get to someone like me, who looks at the production structure and goes: "this is wholly insufficient for what I need to build, but it has good bones, so I'm going to strip it out and rebuild it for my use case, and only my use case, because to wait for a team to prioritize it according to an arcane schedule will push my team deadlines exceedingly far."

This is why you don't roll everything into backend processes. Companies set up for production (high automation value ROI) and analytics (high labor value ROI) and has a hard time serving the mid tail. EVERYTHING on either direction works against the mid-tail -- security policies, data access policies, software approvals, you name it.

People, policy, and technology. These are the three pillars. If your org isn't performing the way it should, then by golly work at one of these and remember that technology is only one of three.

Re: Practical SQL for Data Analysis

#105

Post by tomrod » Mon, May 03, 2021, 6:42 PM UTC

tomnipotent wrote: post #37

michelpp wrote: post #26

This is an excellent example of what I call the Copy-Object-Copy effect. It's particularly apparent in frameworks with ORMs like Django. In Pandas case, devs will do 'SELECT *' and use pandas as a sort of pseudo ORM. You run a query to get your data as a bunch of objects, but you're copying the data over the db connection from the postgres wire protocol into Python objects in memory, which are typically then garbage…

> it's that people way overuse it for SQL tasks as this article points out I'm very confused by this. I've used pandas for ever a decade, and in most cases it's a massive time saver. I can do a single query to bring data into local memory in a Jupyter notebook, and from there re-use that memory across hundreds or more executions of pandas functions to further refine an analysis or whatever task I'm up to. Your "copy-…

I know a team that would read in multi-GB CSVs from an FTP site into an orchestrator server using Pandas, write to locsl CSV in the orchestator, validate data after write, reload into pandas, painfully and manually check every data type, then use a pandas connector to a database.

Every step along the way here is wrong. The database came with SFTP connectors. The orchestrator should have simply told the database server to pull from the SFTP site into a landing table. Even if not, Pandas has datatypes you can specify on ingestion, which is heaven if your CSV or other files don't record filetypes yet are consistent in structure. Further, if you have custom validation you're applying (not a bad thing!) you likely rarely even need pandas; the native CSV library or (XLRD/openpyxl) for Excel are fine.

Ultimately, it is a training issue. The toolspace is too complex, with too many buzzwords to describe simple things, that people get lost in the whirlwind.

Re: Practical SQL for Data Analysis

#106

Post by nojito » Mon, May 03, 2021, 6:42 PM UTC

ziml77 wrote: post #55

isoprophlex wrote: post #45

Earlier quoted context omitted.

This really grinds my gears too. There's something about the pandas API that makes it impossible for me to do basic ops without tedious manual browsing to get inplace or index arguments right... assignments and conditionals are needlessly verbose too. Pyspark on the other hand just sticks in my brain, somehow. Chained pyspark method calls looks much neater.

Setting inplace=True isn't too bad, but I definitely have had many issues with working with indexes in Pandas. I don't understand why they didn't design it so that the index can be referenced like any other columns. It overcomplicates things like having to know the subtle difference between join() and merge().

Setting Inplace=True is not recommended and should be used with caution

https://github.com/pandas-dev/pandas/issues/16529

Re: Practical SQL for Data Analysis

#107

Post by racl101 » Mon, May 03, 2021, 6:52 PM UTC

I think I do a healthy mixture of both.

Re: Practical SQL for Data Analysis

#108

Post by thamer » Mon, May 03, 2021, 6:55 PM UTC

wswope wrote: post #20

jonas_b wrote: post #7

Question, 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()

SQLite is what you're looking for. If you want to use the CLI, .format csv .import Alternatively, read your data into pandas and there's extremely easy interop between a DBAPI connection from the python standard lib Sqlite3 module and Pandas (to_sql, read_sql_query, etc.).

I would second this suggestion. I was querying CSV data imported into SQLite this weekend, and it was extremely easy to get started. SQLite is pretty snappy even for tables with millions of rows.

SQLite supports defining columns without a type and will use TEXT by default, so you can take the first line of your CSV that lists the document's dimensions, put those in the brackets of a CREATE TABLE statement, and then run the .import described above (so just CREATE TABLE foo(x,y,z); if x,y,z are your column names).

After importing the data don't forget to create indexes for the queries you'll be using most often, and you're good to go.

Another suggestion for once your data is imported, have SQLite report it in table format:

    .mode column
    .headers on

Re: Practical SQL for Data Analysis

#109

Post by denimnerd42 » Mon, May 03, 2021, 7:01 PM UTC

disgruntledphd2 wrote: post #63

nerdponx wrote: post #2

SQL syntax sucks for doing non-trivial data analysis. I've tried it. Verbose, no composability or code reuse, not really portable across different databases, no easy interoperability with other tools, limited editor/IDE support, etc. I guess if you have huge amounts of data (10m+ rows) already loaded into a database then sure, do your basic summary stats in SQL. For everything else, I'll continue using SQL to get the…

> (i.e. not relying on data engineers to do basic ETL for you). Is this actually a thing? Surely it can't be a thing.

of course, that's my job... we do basic ETL on hundreds of data sources to provide data to the analysts and quants

Re: Practical SQL for Data Analysis

#110

Post by hantusk » Mon, May 03, 2021, 7:05 PM UTC

m16ghost wrote: post #29

>Pandas is a very popular tool for data analysis. It comes built-in with many useful features, it's battle tested and widely accepted. However, pandas is not always the best tool for the job. SQL is very useful, but there are some data manipulations which are much easier to perform in pandas/dplyr/data.table than in SQL. For example, the article discusses how to perform a pivot table, which takes data in a "long" for…

Agreed. https://ibis-project.org/ and https://dbplyr.tidyverse.org/ can compile dataframe-like input to SQL, which might bridge the gap in tooling (although there still are small differences to the pure dataframe syntax)