5 ms·

> 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'

> 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-object-copy" is not relevant in data analysis use cases, and exists in pretty much any system that pulls data from an external service be it SQL or not.

mistrial9 6y ago
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
- tomnipotent 6y ago
  Not for data analysis, it's a pointless constraint unless you're having issues. Most data analysis isn't against datasets larger than memory, and I'd rather have more data than I need to spend time waiting to bring it all local again because I forgot a few columns that turn out to be useful later on.
  - disgruntledphd2 6y ago
    True, but in that kind of exploratory environment, you'd normally reduce data load and speed up iteration by using sampling, which is super easy in SQL. > Not for data analysis, it's a pointless constraint unless you're having issues. This will always happen, as time spent on the project scales up (unless you use some kind of autoscaling magic I suppose).
  - EForEndeavour 6y ago
    I totally get what you're saying because most of my datasets also used to be smaller than my laptop's memory. Used to. I'm in the process of moving more and more processing "upstream" from local pandas to the DBMS and it's already proving to be a bit of a superpower. I regret not learning it earlier, but the second-best time is now.
- protomyth 6y ago
  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.
  - tomnipotent 6y ago
    Data analysis workloads more often run into problems solved by partitioning rather than indexes.
    - mistrial9 6y ago
      yes and no - I trained on "larger than RAM datasets" intentionally, and subsequently took on projects that require "larger than RAM datasets". Two things happened on my way, companies led by Google invented and deployed datasets previously impossible e.g. BigTable, and secondly the equipment I worked on went from modest to mid-sized RAM (hard to define that). Granted that lots of very useful (and common?) tasks are not "larger than RAM datasets", and then have very different characteristics.. which is starting to sound like "Excel problems look like this, they always fit in RAM, they need partitions not indexes" and the like.. There is a blind-man-and-the-Elephant drift here, which is not terrible, but might need calling out to improve..
      - tomnipotent 6y ago
        > companies led by Google invented and deployed datasets previously impossible e.g. BigTable BigTable, Redshift, Snowflake and every other "big data" storage system rely heavily on partitioning to achieve the scales they're able to achieve. Some of these systems don't even support indexes.
  - Hjfrf 6y ago
    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.
qsort 6y ago
Pandas is great when you're working on the data manually because it lets you interleave python code and "queries", but it's strictly worse than a plain SQL statement if you're writing, say, a REST service that needs to output some data. SQL executed by the database is orders of magnitude more efficient, way more expressive, and doesn't require you to memorize pandas' absurd API. And I say this as someone who is by no means a SQL wizard, and fully acknowledging all of SQL's blemishes.
- tomnipotent 6y ago
  > a REST service This is a post about data analysis, and everyone wants to point out that pandas isn't good at real-time service requests. > SQL executed by the database is orders of magnitude more efficient Compared to pandas? No, it's not. Once I have all the data I need locally, it's MUCH faster to use pandas locally then to re-issue queries to a remote database.
  - qsort 6y ago
    > Compared to pandas? No, it's not. I'm not saying you shouldn't use pandas, it depends on the size of the data. I'm working right now on a project where a SELECT * of the entire fact table would be a couple hundred gigabytes. The flow is SQL -> pandas -> manipulation, and as always in pipelines like those, the most work you can do at the earliest stage, the better.
    - disgruntledphd2 6y ago
      Yeah, speaking as a data person, the SQL argument is correct. Python/R are much, much, much slower for this kind of work. OTOH, SQL is super limiting for a lot of data analysis tasks and you'll inevitably need the data in weird forms that require lots of munging. Personally, I'm a big fan of using SQL/Airflow/whatever to generate whatever data I'll need all the time at a high level of granularity (user/action etc), and then just run a (very quick) SQL query to get whatever you need into your analytics environment. Gives you the best of both worlds, IME.
tarsinge 6y ago
The point is that if you only need to join and aggregate data it's easier and more efficient to do it directly in SQL. Also in production or when your database doesn't fit in memory the idea is to first use SQL to generate an optimized view of your data from the database before further analysis and transformation.
- jstrong 6y ago
  > The point is that if you only need to join and aggregate data it's easier and more efficient to do it directly in SQL citation needed? I've seen plenty of cases where it would have taken the db ages to do something that pandas does fast, and I don't consider pandas to be particularly fast.
  - tarsinge 6y ago
    TFA
tomrod 6y ago
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.
- nojito 6y ago
  Uses pandas to infer datatypes leads to nasty coercions. Also, pandas --> objects --> chunk based reading yields the same performance as any other library you mentioned.
  - tomrod 6y ago
    Absolutely. On top of that, the Pandas object type (most people's default for strings) can house any scalar type, including ints and floats. This has hit a LOT of immature adoption of pyspark, for example, where the datatypes get screwy.

mistrial9 6y ago

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

tomnipotent 6y ago
Not for data analysis, it's a pointless constraint unless you're having issues. Most data analysis isn't against datasets larger than memory, and I'd rather have more data than I need to spend time waiting to bring it all local again because I forgot a few columns that turn out to be useful later on.
- disgruntledphd2 6y ago
  True, but in that kind of exploratory environment, you'd normally reduce data load and speed up iteration by using sampling, which is super easy in SQL. > Not for data analysis, it's a pointless constraint unless you're having issues. This will always happen, as time spent on the project scales up (unless you use some kind of autoscaling magic I suppose).
- EForEndeavour 6y ago
  I totally get what you're saying because most of my datasets also used to be smaller than my laptop's memory. Used to. I'm in the process of moving more and more processing "upstream" from local pandas to the DBMS and it's already proving to be a bit of a superpower. I regret not learning it earlier, but the second-best time is now.
protomyth 6y ago
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.
- tomnipotent 6y ago
  Data analysis workloads more often run into problems solved by partitioning rather than indexes.
  - mistrial9 6y ago
    yes and no - I trained on "larger than RAM datasets" intentionally, and subsequently took on projects that require "larger than RAM datasets". Two things happened on my way, companies led by Google invented and deployed datasets previously impossible e.g. BigTable, and secondly the equipment I worked on went from modest to mid-sized RAM (hard to define that). Granted that lots of very useful (and common?) tasks are not "larger than RAM datasets", and then have very different characteristics.. which is starting to sound like "Excel problems look like this, they always fit in RAM, they need partitions not indexes" and the like.. There is a blind-man-and-the-Elephant drift here, which is not terrible, but might need calling out to improve..
    - tomnipotent 6y ago
      > companies led by Google invented and deployed datasets previously impossible e.g. BigTable BigTable, Redshift, Snowflake and every other "big data" storage system rely heavily on partitioning to achieve the scales they're able to achieve. Some of these systems don't even support indexes.
- Hjfrf 6y ago
  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.

qsort 6y ago

Pandas is great when you're working on the data manually because it lets you interleave python code and "queries", but it's strictly worse than a plain SQL statement if you're writing, say, a REST service that needs to output some data. SQL executed by the database is orders of magnitude more efficient, way more expressive, and doesn't require you to memorize pandas' absurd API. And I say this as someone who is by no means a SQL wizard, and fully acknowledging all of SQL's blemishes.

tomnipotent 6y ago
> a REST service This is a post about data analysis, and everyone wants to point out that pandas isn't good at real-time service requests. > SQL executed by the database is orders of magnitude more efficient Compared to pandas? No, it's not. Once I have all the data I need locally, it's MUCH faster to use pandas locally then to re-issue queries to a remote database.
- qsort 6y ago
  > Compared to pandas? No, it's not. I'm not saying you shouldn't use pandas, it depends on the size of the data. I'm working right now on a project where a SELECT * of the entire fact table would be a couple hundred gigabytes. The flow is SQL -> pandas -> manipulation, and as always in pipelines like those, the most work you can do at the earliest stage, the better.
  - disgruntledphd2 6y ago
    Yeah, speaking as a data person, the SQL argument is correct. Python/R are much, much, much slower for this kind of work. OTOH, SQL is super limiting for a lot of data analysis tasks and you'll inevitably need the data in weird forms that require lots of munging. Personally, I'm a big fan of using SQL/Airflow/whatever to generate whatever data I'll need all the time at a high level of granularity (user/action etc), and then just run a (very quick) SQL query to get whatever you need into your analytics environment. Gives you the best of both worlds, IME.

tarsinge 6y ago

The point is that if you only need to join and aggregate data it's easier and more efficient to do it directly in SQL. Also in production or when your database doesn't fit in memory the idea is to first use SQL to generate an optimized view of your data from the database before further analysis and transformation.

jstrong 6y ago
> The point is that if you only need to join and aggregate data it's easier and more efficient to do it directly in SQL citation needed? I've seen plenty of cases where it would have taken the db ages to do something that pandas does fast, and I don't consider pandas to be particularly fast.
- tarsinge 6y ago
  TFA

tomrod 6y ago

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.

nojito 6y ago
Uses pandas to infer datatypes leads to nasty coercions. Also, pandas --> objects --> chunk based reading yields the same performance as any other library you mentioned.
- tomrod 6y ago
  Absolutely. On top of that, the Pandas object type (most people's default for strings) can house any scalar type, including ints and floats. This has hit a LOT of immature adoption of pyspark, for example, where the datatypes get screwy.