4 ms·

DataFrames are easy to use, everyone knows how to use them, you can move fast, and it's easy to iterate and test differences between things, and reviewing the c

DataFrames are easy to use, everyone knows how to use them, you can move fast, and it's easy to iterate and test differences between things, and reviewing the code is a breeze. That said, my team moved to polars about a year ago and we haven't looked back.

_boffin_ 2y ago
Why not sql?
- bmer 2y ago
  Editor completion. Programmability "out-of-the-box" (rather than having to generate SQL using another programming language).
  - RadiozRadioz 2y ago
    Why would you generate SQL using another programming language? To me that sounds like something you'd only do if you're deep in an ORM with no escape hatch. For data analysis tasks, that's extremely unergonomic and you should definitely just write normal SQL. Use the ORM for CRUD. I've never seen an ORM that won't let you drop down to regular SQL for ad-hoc queries. Editor completion is an extremely low ranking aspect for choosing technologies for data analysis. If SQL is the better tool but you're not using it because it doesn't have editor completion, then you need a better editor. It pains me when people prioritise "developer experience in VS Code" over "actually the correct technological choice".
    - halfcat 2y ago
      Can I ask what you do for version control of SQL? I ask this sincerely, as I’ve seen many scenarios over the years where a tool like Django is used to manage a Postgres database, solely that the schema migrations are captured in version control. A .sql file can be in version control, but rolling back a git commit is not the same as being able to roll back a schema migration in the actual database.
      - bcoates 2y ago
        You roll back a schema migration on a dev/test database by nuking it and regenerating it, probably with the same tools you use to manage the rest of your environment. You don't rollback a schema migration to a production database, because that is impossible. What has been done cannot be undone.
      - RadiozRadioz 2y ago
        Sure, happy to go into it. Firstly we need to distinguish between version control of DQL and DDL. In the context of comparing SQL and Pandas, we're mostly talking about DQL, so version control for this is exactly the same as any other query code. For the DDL side that you're asking about, indeed just version controlling SQL files won't work, you need to use a proper migration tool. There are many available that do pure SQL migrations in the same way Django works (a sequence of up/down operations to perform or revert a migration). Personally I use migrate[1]. So you really can achieve the same thing. Personally I like this approach because it can do anything Django can, but it can also do more complicated migrations that can't be expressed by an ORM's DB-agnostic abstract layer. Pure SQL migrations also decouple your database schema from your ORM/framework, which I think is sensible anyway; databases & data tend to be much stickier than apps & frameworks. A downside here is that you need to do some extra work to keep models in sync with the schema. But the point I was making with my original post is that you can totally use ORMs for CRUD and auto-generate away all that boilerplate, _in addition to_ using raw SQL for complicated data analysis. My point is it's not binary, you can mix them, and there's nothing forcing you to generate _all_ your queries if you use an ORM for most of them. Use the ORM to manage schema, then SQL for a one-off query - still works. [1]: https://github.com/golang-migrate/migrate https://github.com/golang-migrate/migrate
    - bmer 2y ago
      What's the "right editor" for SQL? "Correct technological choice": I think relational algebra style APIs (a la Polars) are the "correct technological choice" here. SQL is just a tool to express relational algebra, and I'm not sure it's a good one.
      - _boffin_ 2y ago
        I personally love any of the IDEA platform products, such as IntelliJ Ultimate, DataGrip, and also DataSpell. For strictly analysis, I’d recommend DataSpell as it’s more oriented towards that. Additionally, it has built in functionality to do sql queries in notebook cells and will save to a variable which is a data frame.
RadiozRadioz 2y ago
> reviewing the code is a breeze I have the opposite opinion. In a previous codebase I fought hard to use dataclasses & type hinting where possible over dictionaries, because with dictionaries you'd never know what type anything was, or what keys were present. That worked nicely and it was much easier to understand the codebase. Now I've been put on a Pandas project and it's full of mysterious df = df[df["thing"] == "value"] I just feel like we've gone back to the unreadability of dictionaries. Everything's just called "df", you never know what type anything is without going in and checking, the structure of the frames is completely opaque, they change the structure of the dataframe halfway through the program. Type hinting these things is much harder than TypedDict/dataclass, at least doing it correctly & unambiguously is. It's practically a requirement to shove this stuff in a debugger/REPL because you'd have no chance otherwise. Sure, the argument is that I'm just in a bad Pandas codebase, and it can be done much better. However what I take issue with is that this seems to be the overwhelming "culture" of Pandas. All Pandas code I've ever read is like this. If you look at tutorials, examples online, you see the same stuff. They all call everything the same name and program in the most dynamic & opaque fashion possible. Sure it's quick to write, and if you love Pandas you're used to it, but personally I wince every time I look in a method and see this stuff instead of normal code. Personally I only use Pandas if I absolutely need it for performance, as a last resort.
- halfcat 2y ago
  Is it just the generic, non-descriptive naming, or what do you think is the root of your distaste for pandas? Like if we have a dataclass: obj.thing == value Or SQL: SELECT * FROM table WHERE thing = ‘value’ We don’t know what the types are, either, without looking it up. The fact the dataframe often changes halfway through the program is, I think, more to do with the task at hand, that often pandas is being used to perform data transformation (the T in ETL), where some raw data is read in, and the goal is literally to change the structure to clean it up and normalize it, so the data can be ingested into a SQL table in a consistent form with other data points. But if transformation is not what you are doing, then yes, that might not be the right use of dataframes.
  - RadiozRadioz 2y ago

_boffin_ 2y ago

Why not sql?

bmer 2y ago
Editor completion. Programmability "out-of-the-box" (rather than having to generate SQL using another programming language).
- RadiozRadioz 2y ago
  Why would you generate SQL using another programming language? To me that sounds like something you'd only do if you're deep in an ORM with no escape hatch. For data analysis tasks, that's extremely unergonomic and you should definitely just write normal SQL. Use the ORM for CRUD. I've never seen an ORM that won't let you drop down to regular SQL for ad-hoc queries. Editor completion is an extremely low ranking aspect for choosing technologies for data analysis. If SQL is the better tool but you're not using it because it doesn't have editor completion, then you need a better editor. It pains me when people prioritise "developer experience in VS Code" over "actually the correct technological choice".
  - halfcat 2y ago
    Can I ask what you do for version control of SQL? I ask this sincerely, as I’ve seen many scenarios over the years where a tool like Django is used to manage a Postgres database, solely that the schema migrations are captured in version control. A .sql file can be in version control, but rolling back a git commit is not the same as being able to roll back a schema migration in the actual database.
    - bcoates 2y ago
      You roll back a schema migration on a dev/test database by nuking it and regenerating it, probably with the same tools you use to manage the rest of your environment. You don't rollback a schema migration to a production database, because that is impossible. What has been done cannot be undone.
    - RadiozRadioz 2y ago
      Sure, happy to go into it. Firstly we need to distinguish between version control of DQL and DDL. In the context of comparing SQL and Pandas, we're mostly talking about DQL, so version control for this is exactly the same as any other query code. For the DDL side that you're asking about, indeed just version controlling SQL files won't work, you need to use a proper migration tool. There are many available that do pure SQL migrations in the same way Django works (a sequence of up/down operations to perform or revert a migration). Personally I use migrate[1]. So you really can achieve the same thing. Personally I like this approach because it can do anything Django can, but it can also do more complicated migrations that can't be expressed by an ORM's DB-agnostic abstract layer. Pure SQL migrations also decouple your database schema from your ORM/framework, which I think is sensible anyway; databases & data tend to be much stickier than apps & frameworks. A downside here is that you need to do some extra work to keep models in sync with the schema. But the point I was making with my original post is that you can totally use ORMs for CRUD and auto-generate away all that boilerplate, _in addition to_ using raw SQL for complicated data analysis. My point is it's not binary, you can mix them, and there's nothing forcing you to generate _all_ your queries if you use an ORM for most of them. Use the ORM to manage schema, then SQL for a one-off query - still works. [1]: https://github.com/golang-migrate/migrate https://github.com/golang-migrate/migrate
  - bmer 2y ago
    What's the "right editor" for SQL? "Correct technological choice": I think relational algebra style APIs (a la Polars) are the "correct technological choice" here. SQL is just a tool to express relational algebra, and I'm not sure it's a good one.
    - _boffin_ 2y ago
      I personally love any of the IDEA platform products, such as IntelliJ Ultimate, DataGrip, and also DataSpell. For strictly analysis, I’d recommend DataSpell as it’s more oriented towards that. Additionally, it has built in functionality to do sql queries in notebook cells and will save to a variable which is a data frame.

RadiozRadioz 2y ago

> reviewing the code is a breeze I have the opposite opinion. In a previous codebase I fought hard to use dataclasses & type hinting where possible over dictionaries, because with dictionaries you'd never know what type anything was, or what keys were present. That worked nicely and it was much easier to understand the codebase. Now I've been put on a Pandas project and it's full of mysterious df = df[df["thing"] == "value"] I just feel like we've gone back to the unreadability of dictionaries. Everything's just called "df", you never know what type anything is without going in and checking, the structure of the frames is completely opaque, they change the structure of the dataframe halfway through the program. Type hinting these things is much harder than TypedDict/dataclass, at least doing it correctly & unambiguously is. It's practically a requirement to shove this stuff in a debugger/REPL because you'd have no chance otherwise. Sure, the argument is that I'm just in a bad Pandas codebase, and it can be done much better. However what I take issue with is that this seems to be the overwhelming "culture" of Pandas. All Pandas code I've ever read is like this. If you look at tutorials, examples online, you see the same stuff. They all call everything the same name and program in the most dynamic & opaque fashion possible. Sure it's quick to write, and if you love Pandas you're used to it, but personally I wince every time I look in a method and see this stuff instead of normal code. Personally I only use Pandas if I absolutely need it for performance, as a last resort.

halfcat 2y ago
Is it just the generic, non-descriptive naming, or what do you think is the root of your distaste for pandas? Like if we have a dataclass: obj.thing == value Or SQL: SELECT * FROM table WHERE thing = ‘value’ We don’t know what the types are, either, without looking it up. The fact the dataframe often changes halfway through the program is, I think, more to do with the task at hand, that often pandas is being used to perform data transformation (the T in ETL), where some raw data is read in, and the goal is literally to change the structure to clean it up and normalize it, so the data can be ingested into a SQL table in a consistent form with other data points. But if transformation is not what you are doing, then yes, that might not be the right use of dataframes.
- RadiozRadioz 2y ago