在AWS Glue的PySpark环境中实现表格列转置（SQL/PySpark）

Hey there! The problem with your current SQL join approach is that using a.loop <> b.loop creates a cross-product of all distinct loop values for each year/month pair—resulting in those duplicate rows you’re seeing. What you actually need is to pivot your data (convert rows into columns) based on the loop field. Here are two straightforward ways to achieve your desired output in your AWS Glue Python 3.6 PySpark environment:

1. Use PySpark DataFrame API (Recommended) #

This is the most idiomatic approach for PySpark. We’ll group by year and month, pivot the loop values into separate columns, then aggregate to get the corresponding total values:

# Assume your source DataFrame is named `df`
from pyspark.sql import functions as F

# Pivot the data and rename columns to match your desired output
pivoted_df = df.groupBy("year", "month") \
               .pivot("loop") \
               .agg(F.sum("total")) \
               .withColumnRenamed("loop1", "total_loop1") \
               .withColumnRenamed("loop2", "total_loop2") \
               .withColumnRenamed("loop3", "total_loop3")

# View the result
pivoted_df.show()

Explanation: #

• groupBy("year", "month"): Groups the data so we process each unique year/month pair together.
• pivot("loop"): Converts each distinct value in the loop column into a new column.
• agg(F.sum("total")): Aggregates the total values for each pivoted column. Since each year/month/loop combination has exactly one row, sum() works the same as first() here.
• withColumnRenamed: Adjusts the column names to match your total_loopX format.

2. Use Spark SQL #

If you prefer working with SQL, you can use either the PIVOT syntax or a CASE WHEN approach to reshape the data:

Option A: Spark SQL PIVOT #

First, register your DataFrame as a temporary view:

df.createOrReplaceTempView("test")

Then run this query:

SELECT 
    year, 
    month, 
    loop1 AS total_loop1, 
    loop2 AS total_loop2, 
    loop3 AS total_loop3
FROM (
    SELECT year, month, loop, total FROM test
)
PIVOT (
    SUM(total) FOR loop IN ('loop1', 'loop2', 'loop3')
)

Option B: CASE WHEN with GROUP BY #

This is a more explicit approach if you’re less familiar with the PIVOT syntax:

SELECT 
    year,
    month,
    SUM(CASE WHEN loop = 'loop1' THEN total ELSE 0 END) AS total_loop1,
    SUM(CASE WHEN loop = 'loop2' THEN total ELSE 0 END) AS total_loop2,
    SUM(CASE WHEN loop = 'loop3' THEN total ELSE 0 END) AS total_loop3
FROM test
GROUP BY year, month

Expected Output #

Both methods will produce exactly the format you need:

+----+-----+-----------+-----------+-----------+
|year|month|total_loop1|total_loop2|total_loop3|
+----+-----+-----------+-----------+-----------+
|2012|    1|         20|         10|         50|
|2012|    2|         30|          5|         60|
+----+-----+-----------+-----------+-----------+

内容的提问来源于stack exchange，提问作者Andres Urrego Angel