21–30 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()
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.
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.
Nice 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…
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.
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…
You're entitled to your opinion and tooling choices of course, but the problem is you don't know SQL.
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 collected at the end of the transaction, then copy the result to a JSON buffer that is also garbage collected, and then send the final result to send to the browser which has been waiting this whole time.
I regularly see Django apps require several gigs of RAM per worker and when you look at the queries, it's just a bunch of poorly rendered SELECTs. It's grotesque.
Contrast PostgREST: 100 megabytes of RAM per worker. I've seen Django DRF workers require 50 to 1 memory vs PostgREST for the same REST interface and result with PostgREST being much faster. Since the database is generating the json, it can do so immediately upon generating the first result row which is then streamed to the browser. No double buffering.
Unlike Django, it's not that I don't like Pandas, it's that people way overuse it for SQL tasks as this article points out. I've seen pandas scripts that could be a simpler psql script. If psql can't do what you want, resist the temptation to 'SELECT *' into a data frame and break the problem up into stages where you get the database to do the maximum work before it gets to the data frame.
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…
> 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. You're entitled to your opinion and tooling choices of course, but the problem is you don't know SQL.
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…
> 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. You're entitled to your opinion and tooling choices of course, but the problem is you don't know SQL.
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" format, and makes it "wider".
In the article, the pandas version is:
>pd.pivot_table(df, values='name', index='role', columns='department', aggfunc='count')
Compared to the SQL version:
>SELECT role, SUM(CASE department WHEN 'R&D' THEN 1 ELSE 0 END) as "R&D", SUM(CASE department WHEN 'Sales' THEN 1 ELSE 0 END) as "Sales" FROM emp GROUP BY role;
Not only does the SQL code require you to know up front how many distinct columns you are creating, it requires you to write a line out for each new column. This is okay in simple cases, but is untenable when you are pivoting on a column with hundreds or more distinct values, such as dates or zip codes.
There are some SQL dialects which provide pivot functions like in pandas, but they are not universal.
There are other examples in the article where the SQL code is much longer and less flexible, such as binning, where the bins are hardcoded into the query.
Nice 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…
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.