Pivot a dataframe directly using Spark SQL

demo.ipynb

query.sql

SELECT
    *
FROM
    (
        SELECT
            date_format (tpep_pickup_datetime, 'EEEE') AS day_of_week,
            fare_amount,
            passenger_count
        FROM
            data
    ) PIVOT (
        {agg_func}(fare_amount) FOR day_of_week IN (
            'Monday',
            'Tuesday',
            'Wednesday',
            'Thursday',
            'Friday',
            'Saturday',
            'Sunday'
        )
    )
ORDER BY
    passenger_count;