81–90 of 198 posts
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()
q "SELECT COUNT(*) FROM ./clicks_file.csv WHERE c3 > 32.3"
It uses sqlite under the hood.
>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…
Does `tablefunc.crosstab()` do what you want? https://www.postgresql.org/docs/13/tablefunc.html
Although the SQL syntax is weird and so dated, its portability across tools trumps everything else. You finished the EDA and decided to port the insights to a dashboard? With SQL it's trivial. With Python... well, probably you'll have to port it to SQL unless you have Netflix-like, Jupyter-backed dashboard infrastructure in place. For many of us who only have much-more-prevalent SQL-based dashboard platform, why not starting from SQL? Copy-n-paste is your friend!
I still hate the SQL as a programmer, but as a non-expert data analyst I now have accepted it.
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…
Within the VA hospital system, the data admins often got onto their "soapboxes" to dress down the 1400+ data analysts for writing inefficient SQL queries. If you need 6 million patients, joining data from 15 tables, gathering 250 variables, a beginning SQL user has the potential to take 15-20 hours where they could be pulling for 1-2 if they do some up-front filtering and sub-grouping within SQL. If you already know…
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…
Within the VA hospital system, the data admins often got onto their "soapboxes" to dress down the 1400+ data analysts for writing inefficient SQL queries. If you need 6 million patients, joining data from 15 tables, gathering 250 variables, a beginning SQL user has the potential to take 15-20 hours where they could be pulling for 1-2 if they do some up-front filtering and sub-grouping within SQL. If you already know…
This is one of those things I just don't get about folks setting up their databases. If you have a rather large dataset that keeps building via daily transactions, then its time to recognize you really have some basic distinct scenarios and to plan for them.
The most common is adding or querying data about a single entity. Most application developers really only deal with this scenario since that is what most applications care about and how you get your transactional data. Basic database knowledge gets most people to do this ok with proper primary and secondary keys.
Next up is a simple rule, "if you put a state on an entity, expect someone to need to know all the entities with this state." This is a killer for application developers for some reason. It actually requires some database knowledge to setup correctly to be performant. If the data analysts have problems with those queries, then its to to get the DBA to fix the damn schema and write some stored procedures for the app team.
At some point, you will need to do actual reporting, excuse me, business intelligence. You really should have some process that takes the transactional data and puts it into a form where the queries of data analysts can take place. In the old days that would be something to load up Red Brick or some equivalent. Transactional systems make horrid reporting systems. Running those types of queries on the same database as the transactional system is currently trying to work is just a bad idea.
Of course, if you are buying something like IBM DB2 EEE spreading queries against a room of 40 POWER servers, then ignore the above. IBM will fix it for you.
Earlier quoted context omitted.
Within the VA hospital system, the data admins often got onto their "soapboxes" to dress down the 1400+ data analysts for writing inefficient SQL queries. If you need 6 million patients, joining data from 15 tables, gathering 250 variables, a beginning SQL user has the potential to take 15-20 hours where they could be pulling for 1-2 if they do some up-front filtering and sub-grouping within SQL. If you already know…
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?
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…
This is not huge. This is quite small. Pandas can't handle data that runs into billions of rows or sub-second response on arbitrary queries. Both are common requirements in many analytic applications.
I like Pandas. It's flexible and powerful if you have experience with it. But there's no question that SQL databases (especially data warehouses) handle large datasets and low latency response far better than anything in the Python ecosystem.
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…
Interesting. I'm guessing the extra layer may be needed for some manipulations of data at the application layer, but perhaps that is almost always avoidable? Have you ever tried Hasura? I'm thinking of giving it a go in a future project.
Earlier quoted context omitted.
> 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-…
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
The more I learn SQL, the less I write Python. Although the SQL syntax is weird and so dated, its portability across tools trumps everything else. You finished the EDA and decided to port the insights to a dashboard? With SQL it's trivial. With Python... well, probably you'll have to port it to SQL unless you have Netflix-like, Jupyter-backed dashboard infrastructure in place. For many of us who only have much-more-p…