Hacker News Reader

Comment by __mharrison__ | Hacker News Reader

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.

View on HN

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

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

I know a book that might help you feel more comfortable with Pandas... (Wink)

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

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

true. and it used to be spread() and gather() and those were bad

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

It sounds like in the past you’ve used pandas/python for this. Any opinions on the best option for pivoting with those tools?

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

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

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

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

Very similar with the MS SQL implementation of PIVOT.

Reply on news.ycombinator.com