feature request: pivot command

[fgregg]

pivoting long-format table to wide-format tables is pretty common and kind of pain. would love to see this feature in sqlite-utils!

[simonw]

Really interesting idea! Can you flesh out what this might look like, maybe with a before and after example schema?

[fgregg]

for example, i have data on votes that look like this:

ballot_id	option_id	choice
1	1	0
1	2	1
1	3	0
1	4	1
2	1	1
2	2	0
2	3	1
2	4	0

and i want to reshape from this long form to this wide form:

ballot_id	option_id_1	option_id_2	option_id_3	optionid 4
1	0	1	0	1
2	1	0	1	0

i could do such a think like this.

select ballot_id, 
sum(choice) filter (where option_id = 1) as option_id_1,
sum(choice) filter (where option_id = 2) as option_id_2,
sum(choice) filter (where option_id = 3) as option_id_3,
sum(choice) filter (where option_id = 4) as option_id_4
from vote
group by ballot_id

[fgregg]

hadley wickham's melt and reshape could be good inspo: http://had.co.nz/reshape/introduction.pdf

[jcmkk3]

hadley wickham's melt and reshape could be good inspo: http://had.co.nz/reshape/introduction.pdf

Note that Hadley has since implemented pivot_longer and pivot_wider instead of the previous verbs/functions that he used. Those can be found in the tidyr package and are probably the best reference which includes all of the learnings from years of user feedback. https://tidyr.tidyverse.org/articles/pivot.html

[simonw]

Wow https://tidyr.tidyverse.org/articles/pivot.html is fascinating.