Add pivot & unpivot (melt) to DataFrame

[ion-elgreco]

Is your feature request related to a problem or challenge?

Pivoting and unpivoting is a common use case for data scientists, this is currently missing in the DF api.

Describe the solution you'd like

Add two methods to DataFrame struct:

• pivot
  • possibility to add distinct values in advance (would be optimized into groupby.agg() directly)
  • let DataFusion calculate distinct values automatically
• unpivot

Describe alternatives you've considered

No response

Additional context

No response

[Omega359]

referenced in apache/datafusion-python#875

[jonmmease]

Out of curiosity, would it be possible to implement pivot using DataFusion's current extension mechanisms? I had hoped it would be possible as a UDTF, but it doesn't look like UDTFs can input a table.

[riziles]

@jonmmease , not sure if it's helpful, but Ibis has a pure Python implementation that works on DataFusion dataframes:

https://github.com/ibis-project/ibis/blob/20bec137463d0dba71e741247624119b7a7b3452/ibis/expr/types/relations.py#L4005