PySpark 帧中差异列上的多个透视

Question

我有一个py-spark数据帧，有8列。

数据帧包含列：

---

Columns  L ,  W , PN , PV , CP , CH , RP , RH 
Row1     L1,  W1, PN1, PV1, CP1, CH1, RP1, RH1  
Row2     L2,  W2, PN2, PV2, CP2, CH2, RP2, RH2 

---

列 L、W 构成数据集的键。

我想将数据集透视为以下形式：

---

Columns L ,  W , PN1 , PN2 , CP1 , CP2 , RP1 , RP2
Row1    L1,  W1, PV1 ,  -  , CH1 ,  -  , RH1 , - 
Row2    L2,  W2,  -  , PV2 ,  -  , CH2 ,  -  , RH2

---

从根本上说，我必须透视 3 个差异列，其中包含 3 个不同的统计数据(例如：最小值、最大值、平均值(。

在 pyspark 中透视此数据集的最佳方法是什么？

谢谢P Ved

Answer 1

一次只能透视一列，但可以按多个列分组并计算多个聚合：

让我们从一个示例数据帧开始：

import numpy as np
df = spark.createDataFrame([np.random.randint(0, 10, 8).tolist() for _ in range(10)], ["L", "W", "PN", "PV", "CP", "CH", "RP", "RH"])
df.show()
    +---+---+---+---+---+---+---+---+
    |  L|  W| PN| PV| CP| CH| RP| RH|
    +---+---+---+---+---+---+---+---+
    |  9|  2|  9|  7|  1|  5|  2|  7|
    |  4|  1|  1|  7|  5|  0|  2|  3|
    |  6|  2|  0|  3|  3|  6|  0|  0|
    |  9|  8|  9|  8|  8|  5|  5|  1|
    |  8|  2|  2|  3|  9|  1|  1|  7|
    |  2|  7|  7|  3|  8|  6|  1|  4|
    |  9|  7|  4|  8|  1|  7|  6|  1|
    |  8|  1|  9|  2|  2|  2|  9|  9|
    |  8|  9|  9|  0|  4|  4|  9|  7|
    |  4|  4|  2|  2|  0|  6|  1|  0|
    +---+---+---+---+---+---+---+---+

• 让我们按列L分组，透视第 W 列并计算所有其他列的min, max, mean：

  import pyspark.sql.functions as psf
  from itertools import chain
  df.groupBy("L").pivot("W").agg(*list(chain(*[[psf.min(c), psf.max(c), psf.mean(c)] for c in df.columns if c not in ["L", "W"]]))).show()
      +---+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
      |  L|1_min(PN)|1_max(PN)|1_avg(PN)|1_min(PV)|1_max(PV)|1_avg(PV)|1_min(CP)|1_max(CP)|1_avg(CP)|1_min(CH)|1_max(CH)|1_avg(CH)|1_min(RP)|1_max(RP)|1_avg(RP)|1_min(RH)|1_max(RH)|1_avg(RH)|2_min(PN)|2_max(PN)|2_avg(PN)|2_min(PV)|2_max(PV)|2_avg(PV)|2_min(CP)|2_max(CP)|2_avg(CP)|2_min(CH)|2_max(CH)|2_avg(CH)|2_min(RP)|2_max(RP)|2_avg(RP)|2_min(RH)|2_max(RH)|2_avg(RH)|4_min(PN)|4_max(PN)|4_avg(PN)|4_min(PV)|4_max(PV)|4_avg(PV)|4_min(CP)|4_max(CP)|4_avg(CP)|4_min(CH)|4_max(CH)|4_avg(CH)|4_min(RP)|4_max(RP)|4_avg(RP)|4_min(RH)|4_max(RH)|4_avg(RH)|7_min(PN)|7_max(PN)|7_avg(PN)|7_min(PV)|7_max(PV)|7_avg(PV)|7_min(CP)|7_max(CP)|7_avg(CP)|7_min(CH)|7_max(CH)|7_avg(CH)|7_min(RP)|7_max(RP)|7_avg(RP)|7_min(RH)|7_max(RH)|7_avg(RH)|8_min(PN)|8_max(PN)|8_avg(PN)|8_min(PV)|8_max(PV)|8_avg(PV)|8_min(CP)|8_max(CP)|8_avg(CP)|8_min(CH)|8_max(CH)|8_avg(CH)|8_min(RP)|8_max(RP)|8_avg(RP)|8_min(RH)|8_max(RH)|8_avg(RH)|9_min(PN)|9_max(PN)|9_avg(PN)|9_min(PV)|9_max(PV)|9_avg(PV)|9_min(CP)|9_max(CP)|9_avg(CP)|9_min(CH)|9_max(CH)|9_avg(CH)|9_min(RP)|9_max(RP)|9_avg(RP)|9_min(RH)|9_max(RH)|9_avg(RH)|
      +---+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
      |  6|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|        0|        0|      0.0|        3|        3|      3.0|        3|        3|      3.0|        6|        6|      6.0|        0|        0|      0.0|        0|        0|      0.0|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|
      |  9|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|        9|        9|      9.0|        7|        7|      7.0|        1|        1|      1.0|        5|        5|      5.0|        2|        2|      2.0|        7|        7|      7.0|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|        4|        4|      4.0|        8|        8|      8.0|        1|        1|      1.0|        7|        7|      7.0|        6|        6|      6.0|        1|        1|      1.0|        9|        9|      9.0|        8|        8|      8.0|        8|        8|      8.0|        5|        5|      5.0|        5|        5|      5.0|        1|        1|      1.0|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|
      |  8|        9|        9|      9.0|        2|        2|      2.0|        2|        2|      2.0|        2|        2|      2.0|        9|        9|      9.0|        9|        9|      9.0|        2|        2|      2.0|        3|        3|      3.0|        9|        9|      9.0|        1|        1|      1.0|        1|        1|      1.0|        7|        7|      7.0|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|        9|        9|      9.0|        0|        0|      0.0|        4|        4|      4.0|        4|        4|      4.0|        9|        9|      9.0|        7|        7|      7.0|
      |  2|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|        7|        7|      7.0|        3|        3|      3.0|        8|        8|      8.0|        6|        6|      6.0|        1|        1|      1.0|        4|        4|      4.0|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|
      |  4|        1|        1|      1.0|        7|        7|      7.0|        5|        5|      5.0|        0|        0|      0.0|        2|        2|      2.0|        3|        3|      3.0|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|        2|        2|      2.0|        2|        2|      2.0|        0|        0|      0.0|        6|        6|      6.0|        1|        1|      1.0|        0|        0|      0.0|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|     null|
      +---+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
  

• 如果有三列聚合列，您可以将其中 2 列放在groupBy中：

  df.groupBy("L", "W").pivot("PN").agg(psf.max("PV")).show()
      +---+---+----+----+----+----+----+----+
      |  L|  W|   0|   1|   2|   4|   7|   9|
      +---+---+----+----+----+----+----+----+
      |  8|  9|null|null|null|null|null|   0|
      |  8|  1|null|null|null|null|null|   2|
      |  4|  4|null|null|   2|null|null|null|
      |  9|  8|null|null|null|null|null|   8|
      |  2|  7|null|null|null|null|   3|null|
      |  4|  1|null|   7|null|null|null|null|
      |  8|  2|null|null|   3|null|null|null|
      |  6|  2|   3|null|null|null|null|null|
      |  9|  2|null|null|null|null|null|   7|
      |  9|  7|null|null|null|   8|null|null|
      +---+---+----+----+----+----+----+----+