121–130 of 198 posts
Earlier 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.
SQL is a real programming language.
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…
>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
-You still need to enumerate and label each new column and their types. This particular problem is fixed by crosstabN().
-You need to know upfront how many columns are created before performing the pivot. In the context of data analysis, this is often dynamic or unknown.
-The input to the function is not a dataframe, but a text string that generates the pre-pivot results. This means your analysis up to this point needs to be converted into a string. Not only does this disrupt the flow of an analysis, you also have to worry about escape characters in your string.
-It is not standard across SQL dialects. This function is specific to Postgres, and other dialects have their own version of this function with their own limitations.
The article contains several examples like this where SQL is much more verbose and brittle than the equivalent pandas code.
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…
Is it possible to do any kind of version control with PostgREST? I have no doubt that raw SQL offers huge performance advantages over my ORM, but I'm pretty fond of the ability to roll back to a previous state when I push a bad change to production. Performance is just one of a number of tradeoffs that I consider when I'm choosing tools.
Earlier quoted context omitted.
SQL is a real programming language.
It's not turing complete which is I think why it doesn't feel like a "real" programming language.
More usefully, most databases allow you to write user-defined functions in other languages, including imperative SQL dialects.
Earlier quoted context omitted.
SQL is a real programming language.
It's not turing complete which is I think why it doesn't feel like a "real" programming language.
Earlier quoted context omitted.
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
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…
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.
Earlier quoted context omitted.
What is your problem with this example, doesn't it get the job done? WITH temperatures AS ( /* ... */ ) SELECT *, MAX(c) OVER ( ORDER BY t ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS hottest_temperature_last_three_days FROM temperatures; t │ c │ hottest_temperature_last_three_days ────────────┼────┼───────────────────────────────────── 2021-01-01 │ 10 │ 10 2021-01-02 │ 12 │ 12 2021-01-03 │ 13 │ 13 2021-01-04 │ 14 │…
I re-read my comment, and I think that I expressed myself too harshly. If you like SQL and it get the job done for you, no problem. I would see myself using that SQL query. However in order to get there, you need to know why you need the ''hottest_temparature_last_three_days''. Why not 2 or 4 days? Why not using heating degree day? What about serial correlation with other regions, or metering disfunction? What if you…
Disclosure: I develop high-performance analytic database systems with SQL frontends.
Following similar observations I was wondering if one can actually execute SQL queries inside of a Python process with the access to native Python functions and Numpy as UDFs. Thanks to Apache Arrow one can essentially combine DataFrame API with SQL within data analysis workflows, without the need to copy the data and write operators in a mix of C++ and Python, all within the confines of the same Python process. So I…