5 ms·

I use this in Pandas all the time. (Just got done teaching a class about it.) Very useful once you grok the syntax. Smart folks like duckdb realize the utility

I use this in Pandas all the time. (Just got done teaching a class about it.) Very useful once you grok the syntax. Smart folks like duckdb realize the utility (and the pain of doing this in normal SQL) and have added PIVOT to their implementation. Super useful.

ideamotor 3y ago
I like R’s implementation in dplyr, which is pivot_longer and pivot_wider. I’d really like to see those in Postgres.
- bafe 3y ago
  Weren't those functions moved to tidyr? Anyway they should be taken as a model for how to implement a sane interface for data reshaping in other languages. You can say what you want about R/Wickham, but he's not afraid to thinker with his modules until they find the perfect user experience
  - ideamotor 3y ago
    true. and it used to be spread() and gather() and those were bad
    - bafe 3y ago
      Yes, the newer "pivot_wider" and "pivot_longer" are just much cleaner and nicer than the 25 different half-broken way you can use in pandas. Luckily I don't have to do a lot of data engineering anymore, because R is just not fun for large projects while pandas is very unergonomic
      - ideamotor 3y ago
        It sounds like in the past you’ve used pandas/python for this. Any opinions on the best option for pivoting with those tools?
        bafe 3y ago
        I did and honestly both ways I tried (pivot and melt) looked disappointing compared to tidyr. I just don't like the reliance on indices (and the stateful API that comes with it) in many operations, like in pivot
tda 3y ago
Back in the days I recall there was some plug-in to query Pivot tables in postgres, but it was such a pain to use that a two step approach (query the column names, and then generate a second query based on these column names) with Python/SQLAlchemy glue code was easier and possibly even more performant. I hope the situation has improved, as indeed pivot tables are so useful
phkahler 3y ago
>> Smart folks like duckdb realize the utility (and the pain of doing this in normal SQL) I used to love the Microsoft Access visual query tool. Super intuitive but maybe a little too abstract for normal people. It would also produce SQL which was how I learned a little bit of that.
bafe 3y ago
As much as I don't like working in R, the pivoting/reshaping functions in tidyr are hard to beat. It's much more ergonomic than the similar functions in Python
- airstrike 3y ago
  I really wish I could throw money at Hadley Wickham (along with a sufficiently inspiring speech) and have him work on Python for a while...
  - __mharrison__ 3y ago
    I know a book that might help you feel more comfortable with Pandas... (Wink)
hackandthink 3y ago
Oracle has PIVOT as well. (the last time I used it you had to specify the columns manually)
- stvltvs 3y ago
  Very similar with the MS SQL implementation of PIVOT.

ideamotor 3y ago

I like R’s implementation in dplyr, which is pivot_longer and pivot_wider. I’d really like to see those in Postgres.

bafe 3y ago
Weren't those functions moved to tidyr? Anyway they should be taken as a model for how to implement a sane interface for data reshaping in other languages. You can say what you want about R/Wickham, but he's not afraid to thinker with his modules until they find the perfect user experience
- ideamotor 3y ago
  true. and it used to be spread() and gather() and those were bad
  - bafe 3y ago
    Yes, the newer "pivot_wider" and "pivot_longer" are just much cleaner and nicer than the 25 different half-broken way you can use in pandas. Luckily I don't have to do a lot of data engineering anymore, because R is just not fun for large projects while pandas is very unergonomic
    - ideamotor 3y ago
      It sounds like in the past you’ve used pandas/python for this. Any opinions on the best option for pivoting with those tools?
      - bafe 3y ago
        I did and honestly both ways I tried (pivot and melt) looked disappointing compared to tidyr. I just don't like the reliance on indices (and the stateful API that comes with it) in many operations, like in pivot

tda 3y ago

Back in the days I recall there was some plug-in to query Pivot tables in postgres, but it was such a pain to use that a two step approach (query the column names, and then generate a second query based on these column names) with Python/SQLAlchemy glue code was easier and possibly even more performant. I hope the situation has improved, as indeed pivot tables are so useful

phkahler 3y ago

>> Smart folks like duckdb realize the utility (and the pain of doing this in normal SQL) I used to love the Microsoft Access visual query tool. Super intuitive but maybe a little too abstract for normal people. It would also produce SQL which was how I learned a little bit of that.

bafe 3y ago

As much as I don't like working in R, the pivoting/reshaping functions in tidyr are hard to beat. It's much more ergonomic than the similar functions in Python

airstrike 3y ago
I really wish I could throw money at Hadley Wickham (along with a sufficiently inspiring speech) and have him work on Python for a while...
- __mharrison__ 3y ago
  I know a book that might help you feel more comfortable with Pandas... (Wink)

hackandthink 3y ago

Oracle has PIVOT as well. (the last time I used it you had to specify the columns manually)

stvltvs 3y ago
Very similar with the MS SQL implementation of PIVOT.