33 ms·
SQL is easier and more natural to work with than Pandas.
by benjaminwootton 4y ago
SQL is easier and more natural to work with than Pandas.
- nojito 4y agoHow would you do df.T in sql?
- wenc 4y agodf.T is a special Pandas dataframe transpose on the dataframe index and the columns. DuckDB produces Pandas dataframes, so you would just do df.T. No need to choose between one the other. But to answer your original question, the SQL analogue to a transpose are PIVOT/UNPIVOT operations which are mathematically rotation operations on invariants (your dimensions). This makes them much more general than a transpose -- which are just rotation operations on the rows/cols. PIVOT/UNPIVOT work on non-square data and allow you to specify different types of aggregations. PIVOT/UNPIVOT keywords are not yet implemented in DuckDB but are on the roadmap if I'm not mistaken.
- nojito 4y ago>DuckDB produces Pandas dataframes, so you would just do df.T. No need to choose between one the other. Transforming into a pandas df isn't zero copy. UNPIVOT and PIVOT are quite verbose compared to df.T.
- brahbrah 4y agoSometimes yes and sometimes no. For example if you had price data and you wanted to bump prices up a dollar in 2020 how would you do that in sql? In pandas it’s: prices_df.loc['2020'] += 1 If you had a temperature forecast and you wanted to add the expected temperature miss to them, how would you do that on sql? In pandas it’s: temps_df + expected_miss_df