如何将Spark DataFrame中的计数列按Action拆分至多列

Got it, you want to turn those action values (Action1, Action2, Action3) into their own columns with the corresponding count values. Spark's built-in pivot() method is exactly what you need here—let's walk through how to use it step by step.

Step 1: Recreate Your Sample DataFrame (for reference) #

First, let's set up the sample data you provided in PySpark so you can follow along:

from pyspark.sql import SparkSession
from pyspark.sql import Row

spark = SparkSession.builder.appName("ActionPivotDemo").getOrCreate()

# Your sample data
data = [
    Row(user="Albert", dt="2018-03-24", action="Action1", count=19),
    Row(user="Albert", dt="2018-03-25", action="Action1", count=1),
    Row(user="Albert", dt="2018-03-26", action="Action1", count=6),
    Row(user="Barack", dt="2018-03-26", action="Action2", count=3),
    Row(user="Barack", dt="2018-03-26", action="Action3", count=1),
    Row(user="Donald", dt="2018-03-26", action="Action3", count=29),
    Row(user="Hillary", dt="2018-03-24", action="Action1", count=4),
    Row(user="Hillary", dt="2018-03-26", action="Action2", count=2)
]

df = spark.createDataFrame(data)
df.show()

Step 2: Pivot the DataFrame #

We'll group by the columns we want to keep as rows (user and dt), pivot on the action column to turn its values into columns, and aggregate the count values using sum() (since each (user, dt, action) combo is unique here, sum just returns the original count):

# Pivot the action column
pivoted_df = df.groupBy("user", "dt").pivot("action").sum("count")

# Replace nulls with 0 (optional but cleaner if you want to show no action as 0)
pivoted_df = pivoted_df.fillna(0)

pivoted_df.show()

Final Result #

This will give you the exact output you're looking for:

+-------+----------+-------+-------+-------+
|   user|        dt|Action1|Action2|Action3|
+-------+----------+-------+-------+-------+
| Albert|2018-03-24|     19|      0|      0|
| Albert|2018-03-25|      1|      0|      0|
| Albert|2018-03-26|      6|      0|      0|
| Barack|2018-03-26|      0|      3|      1|
| Donald|2018-03-26|      0|      0|     29|
|Hillary|2018-03-24|      4|      0|      0|
|Hillary|2018-03-26|      0|      2|      0|
+-------+----------+-------+-------+-------+

Quick Tips #

• If you know all possible action values upfront, pass them to pivot() (like pivot("action", ["Action1", "Action2", "Action3"]))—this speeds up the operation, especially for large datasets.
• For Scala users, the syntax is nearly identical:

import org.apache.spark.sql.SparkSession

val spark = SparkSession.builder.appName("ActionPivotDemo").getOrCreate()
import spark.implicits._

val data = Seq(
    ("Albert", "2018-03-24", "Action1", 19),
    ("Albert", "2018-03-25", "Action1", 1),
    ("Albert", "2018-03-26", "Action1", 6),
    ("Barack", "2018-03-26", "Action2", 3),
    ("Barack", "2018-03-26", "Action3", 1),
    ("Donald", "2018-03-26", "Action3", 29),
    ("Hillary", "2018-03-24", "Action1", 4),
    ("Hillary", "2018-03-26", "Action2", 2)
).toDF("user", "dt", "action", "count")

val pivotedDF = data.groupBy("user", "dt").pivot("action").sum("count").na.fill(0)
pivotedDF.show()

内容的提问来源于stack exchange，提问作者Vasiliy Galkin