Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Is a Dataframe Just a Table? (2019) [pdf]

plateau-workshop.org

Post reply on HN
Print view View original

81–90 of 120 posts

Re: Is a Dataframe Just a Table? (2019) [pdf]

#81

Post by jstrong »

somurzakov wrote: post #74
kortex wrote: post #55

Earlier quoted context omitted.

Can you drop nans, parse strings to floats, apply arbitrary lambdas over a rolling window, take the cosine of those values, and plot a graph against time, in a single line of sane SQL? Easy in Pandas. Dataframes are not tables; tables are not dataframes. It's nearly as apples-to-bananas as comparing Python lists to C arrays.

everything you mentioned can be done easily through database schema. window functions work well in SQL. plots are easily done in any BI solution that hooks up to any database. pandas is just poor man's SQL+BI. pandas stores everything in memory and has many limitations. in SQL Server I can easily churn through terabyte sized database and get the data I need, because the schema is well designed with partitioned tables…

is the database server running on a $700 workstation? how many rows? what types of queries? what is a typical query execution time? interested in your response because I generally find RDBMS performance quite poor, although I've never used SQL server. Pandas gets to be fairly painful after the data size hits 10GB, in my experience. I do think you are missing how pandas fits into a data exploration pipeline for someone who knows how to code - there's plenty of tasks for which SQL+BI is not going to cut it.

Re: Is a Dataframe Just a Table? (2019) [pdf]

#82

Post by javitury »

leto_ii wrote: post #54

> Having many different ways to express the same logic makes it hard for developers to understand programs of heterogeneous styles. Besides having varying ways to express the same simple logic, the sheer number of APIs (> 200) that are not only overloaded but also have default parameters that may change version to version, making it hard to remember the APIs. It's a bit tangential to the main point, but I do agree wi…

Agree, data.table is the perfect blend of table and dataframe syntaxis for scientists. It's also performant.

Re: Is a Dataframe Just a Table? (2019) [pdf]

#83

Post by rankam »

kyberias wrote: post #31

I was unable to learn R because I couldn't understand what a dataframe is. It was irritating that it wasn't defined clearly and there seemed to be no connection to terminology that was familiar to me (relational databases, SQL tables etc.).

There's no other way to say this without sounding rude, but you weren't unable to learn R because you couldn't understand what a dataframe was - you were unable to learn R because you gave up. Blaming a data structure for the failure seems like a bit of a stretch.

Re: Is a Dataframe Just a Table? (2019) [pdf]

#84

Post by somurzakov »

jstrong wrote: post #81
somurzakov wrote: post #74

Earlier quoted context omitted.

everything you mentioned can be done easily through database schema. window functions work well in SQL. plots are easily done in any BI solution that hooks up to any database. pandas is just poor man's SQL+BI. pandas stores everything in memory and has many limitations. in SQL Server I can easily churn through terabyte sized database and get the data I need, because the schema is well designed with partitioned tables…

is the database server running on a $700 workstation? how many rows? what types of queries? what is a typical query execution time? interested in your response because I generally find RDBMS performance quite poor, although I've never used SQL server. Pandas gets to be fairly painful after the data size hits 10GB, in my experience. I do think you are missing how pandas fits into a data exploration pipeline for someon…

Commodity virtualized server with 4 cores and 8GB of RAM, storage is on NAS. We have hundreds of these typical SQL instances.

db has lot of rows, around 20k rows logged per minute and the events are logged 24/7 three years.

Again, because the schema is well designed, I use clustered index on Date to filter and analyze and the engine actually never reads the whole db all the time. It actually read only the pages I need, and that's the benefit from millions of man-hours MSFT invested in optimizing its SQL engine.

typical response time depends on date period, I try to write queries that dont take more than a 5-10 secs to run.

if you have RDBMS performance problems - just hire an expensive DBA for a brief period and learn-learn-learn from her how to work with large data effectively. DBAs can optimize your db to run as fast as a your hardware I/O speed.

Re: Is a Dataframe Just a Table? (2019) [pdf]

#85

Post by somurzakov »

ezzzzz wrote: post #78
somurzakov wrote: post #74

Earlier quoted context omitted.

everything you mentioned can be done easily through database schema. window functions work well in SQL. plots are easily done in any BI solution that hooks up to any database. pandas is just poor man's SQL+BI. pandas stores everything in memory and has many limitations. in SQL Server I can easily churn through terabyte sized database and get the data I need, because the schema is well designed with partitioned tables…

How about working with poorly designed schemas? I work with SQL-Server as well, dealing with legacy data designed around imperative t-sql programming. Our 'BI-Solution', SSRS, crawls on pretty simple queries, where 'hacks' need to be done, joining on same table, all kinds of dirty tricks... I don't know... I honestly feel like 'BI-Solutions' are a poor-persons Python if you are doing anything more than simple dashboa…

it's hard dealing with legacy stuff. One alternative I can propose - pitch your management and go get yourself a separate and latest SQL instance just for analytics. Easiest solution you can do is to install SQL Server Developer version which is free.

cherry pick what your need and ETL your data out of legacy systems into your warehouse and run something like tableau/looker/powerbi on top and you will be amazed how effective you can be

Re: Is a Dataframe Just a Table? (2019) [pdf]

#86

Post by leto_ii »

rcar wrote: post #76
leto_ii wrote: post #63

Earlier quoted context omitted.

Some of the examples provided in the paper are eloquent: •df[df.a>3] •df[df["a"]>3] •df.loc[df.a>3] •df.loc[df["a"]>3]

Not sure that I'd consider those all that eloquent since it's just the product of 2 different pieces of syntactic sugar (df.a being shorthand for df["a"] and df[ ] shorthand for df.loc[ ]).

Well, that's kind of the point. What's the purpose of the syntactic sugar? Is it just that, or is there some hidden performance difference? This is not clear at first sight.

Re: Is a Dataframe Just a Table? (2019) [pdf]

#87

Post by leto_ii »

rcar wrote: post #76
leto_ii wrote: post #63

Earlier quoted context omitted.

Some of the examples provided in the paper are eloquent: •df[df.a>3] •df[df["a"]>3] •df.loc[df.a>3] •df.loc[df["a"]>3]

Not sure that I'd consider those all that eloquent since it's just the product of 2 different pieces of syntactic sugar (df.a being shorthand for df["a"] and df[ ] shorthand for df.loc[ ]).

Here's another example: https://stackoverflow.com/questions/49936557/pandas-datafram...

What's the difference between query() and loc()? Do they evaluate to the same thing under the hood? Is one better than the other? In what cases?

These are questions that don't have obvious answers at first sight.

Re: Is a Dataframe Just a Table? (2019) [pdf]

#88

Post by oarabbus_ »

kortex wrote: post #55
tanilama wrote: post #21

It is worse than a table. Dataframe don't really have a clear boundary as what it can do or cant do. I see no problem why you can't shoehorn some control flow logic into it which makes it pretty much just an executor of arbitrary computation graph specified in DSL, which relies on however the developer decides to implement it. I'd rather take SQL because I have a better understanding what it is doing.

Can you drop nans, parse strings to floats, apply arbitrary lambdas over a rolling window, take the cosine of those values, and plot a graph against time, in a single line of sane SQL? Easy in Pandas. Dataframes are not tables; tables are not dataframes. It's nearly as apples-to-bananas as comparing Python lists to C arrays.

Technically all SQL compiles into a single line.

Less pedantically you can quite easily and elegantly drop nans, str->float, perform just about any operation over a rolling window and perform trig functions on it. Generally in a much more sane way than pandas.

In my life as an analyst and data scientist I've found SQL to be far, far superior to pandas for something like 90-95% of tasks.

Re: Is a Dataframe Just a Table? (2019) [pdf]

#89

Post by ezzzzz »

somurzakov wrote: post #85
ezzzzz wrote: post #78

Earlier quoted context omitted.

How about working with poorly designed schemas? I work with SQL-Server as well, dealing with legacy data designed around imperative t-sql programming. Our 'BI-Solution', SSRS, crawls on pretty simple queries, where 'hacks' need to be done, joining on same table, all kinds of dirty tricks... I don't know... I honestly feel like 'BI-Solutions' are a poor-persons Python if you are doing anything more than simple dashboa…

it's hard dealing with legacy stuff. One alternative I can propose - pitch your management and go get yourself a separate and latest SQL instance just for analytics. Easiest solution you can do is to install SQL Server Developer version which is free. cherry pick what your need and ETL your data out of legacy systems into your warehouse and run something like tableau/looker/powerbi on top and you will be amazed how e…

Agreed, but again, I ETL my data into a warehouse, as a developer (not a BI person), I'm reaching for spark, flink, or whatever to roll my analytics, and python/flask/d3 for building web dashboards.

Then, once you have 'insight' into your Data, you can easily 'do' something with it without the limitations of a tiered product.

Re: Is a Dataframe Just a Table? (2019) [pdf]

#90

Post by SatvikBeri »

gpderetta wrote: post #25
SatvikBeri wrote: post #4

Summary: no, because row order matters in dataframes. That's why matrix operations are a better computational framework for dataframes than relational algebra.

That's doesn't seem a very defining characteristic. Row order/index is just an implicit primary key column.

The point is which operations you optimize for. Most SQL databases aren't well-optimized for matrix multiplication, but are great with search-style queries, and the opposite for dataframes.
Post reply on HN