Python / PiSpark: Pivot on multiple rows

first…

Let's create some data that needs pivoting. What is special about this data is that it has two columns (Product and Categories) in its granularity and that we want to pivot two values out (Amount and Quantity).

# Make a dictionary list containing inventory data:
data = [{"Product": 'Carrots',  "Category": "Vegetable", "Quantity":  8, "Amount": 270},
        {"Product": 'Broccoli', "Category": "Vegetable", "Quantity":  9, "Amount": 239},
        {"Product": 'Banana',   "Category": "Fruit",     "Quantity":  4, "Amount": 384},
        {"Product": 'Beans',    "Category": "Vegetable", "Quantity":  5, "Amount": 626},
        {"Product": 'Orange',   "Category": "Fruit",     "Quantity":  9, "Amount": 610}
]

# Import and create a SparkContext:
from pyspark import SparkContext, SparkConf
conf = SparkConf().setAppName("projectName").setMaster("local[*]")
sc = SparkContext.getOrCreate(conf)

# Generate an RDD from the created data. Check the type to confirm the object is an RDD:
rdd = sc.parallelize(data)
type(rdd)

# Call the toDF() method on the RDD to create the DataFrame. Test the object type to confirm:
df = rdd.toDF()
type(df)

df.show()

df.createOrReplaceGlobalTempView('Inventory')

%sql
WITH cte AS (
SELECT
  Category,
  Product,
  MAP_FROM_ENTRIES(COLLECT_LIST(
    STRUCT(
      CONCAT(Category,"_",Product),
      named_struct("Amount", Amount,"Quantity", Quantity)
  ))) AS group_results
FROM global_temp.Inventory
GROUP BY Category, Product
)
SELECT Category,
       Product ,
       group_results.Vegetable_Carrots.Amount Vegetable_Carrots_Amount,
       group_results.Vegetable_Carrots.Quantity Vegetable_Carrots_Quantity, 
       group_results.Vegetable_Beans.Amount Vegetable_Beans_Amount,
       group_results.Vegetable_Beans.Quantity Vegetable_Beans_Quantity, 
       group_results.Vegetable_Broccoli.Amount Vegetable_Broccoli_Amount,
       group_results.Vegetable_Broccoli.Quantity Vegetable_Broccoli_Quantity, 
       group_results.Fruit_Banana.Amount Fruit_Banana_Amount,
       group_results.Fruit_Banana.Quantity Fruit_Banana_Quantity, 
       group_results.Fruit_Orange.Amount Fruit_Orange_Amount,
       group_results.Fruit_Orange.Quantity Fruit_Orange_Quantity 
FROM cte