在PySpark中将Spark DataFrame从行转置到列，并将其附加到另一个DataFrame

from pyspark.sql import SparkSession
from pyspark.sql.functions import col

spark = SparkSession.builder.getOrCreate()

data = [("Alice", 25, "F"), ("Bob", 30, "M"), ("Charlie", 35, "M")]
df1 = spark.createDataFrame(data, ["name", "age", "gender"])
df1.show()

+-------+---+------+
|   name|age|gender|
+-------+---+------+
|  Alice| 25|     F|
|    Bob| 30|     M|
|Charlie| 35|     M|
+-------+---+------+

df2 = df1.select("name", "age", "gender") \
    .groupBy("name") \
    .pivot("age") \
    .agg({"age": "first", "gender": "first"}) \
    .select("name", *[col("age").alias(f"age_{c}") for c in df1.select("age").distinct().collect()[0]]) \
    .select("name", *[col(c).alias(f"gender_{c}") for c in df1.select("age").distinct().collect()[0]])

df2.show()

+-------+------+------+------+
|   name|age_25|age_30|age_35|
+-------+------+------+------+
|Charlie|  null|  null|     M|
|    Bob|  null|     M|  null|
|  Alice|     F|  null|  null|
+-------+------+------+------+

data2 = [("Alice", "USA"), ("Bob", "UK"), ("Charlie", "Canada")]
df3 = spark.createDataFrame(data2, ["name", "country"])
df3.show()

+-------+-------+
|   name|country|
+-------+-------+
|  Alice|    USA|
|    Bob|     UK|
|Charlie| Canada|
+-------+-------+

result = df3.join(df2, "name", "left")
result.show()

+-------+-------+------+------+------+
|   name|country|age_25|age_30|age_35|
+-------+-------+------+------+------+
|  Alice|    USA|     F|  null|  null|
|    Bob|     UK|  null|     M|  null|
|Charlie| Canada|  null|  null|     M|
+-------+-------+------+------+------+