对星火数据中的多列执行算术运算

Question

我有一个名为spark-dataframe的输入df

+---------------+---+---+---+-----------+
|Main_CustomerID| P1| P2| P3|Total_Count|
+---------------+---+---+---+-----------+
|         725153|  1|  0|  2|          3|
|         873008|  0|  0|  3|          3|
|         625109|  1|  1|  0|          2|
+---------------+---+---+---+-----------+

这里，Total_Count是P1,P2,P3和P1,P2,P3是product names的总和。我需要通过将产品的值除以它的frequency来找到每个产品的Total_Count。我需要创建一个名为spark-dataframe的新frequencyTable，如下所示，

+---------------+------------------+---+------------------+-----------+
|Main_CustomerID|                P1| P2|                P3|Total_Count|
+---------------+------------------+---+------------------+-----------+
|         725153|0.3333333333333333|0.0|0.6666666666666666|          3|
|         873008|               0.0|0.0|               1.0|          3|
|         625109|               0.5|0.5|               0.0|          2|
+---------------+------------------+---+------------------+-----------+

我是用Scala做的，

val df_columns = df.columns.toSeq
var frequencyTable = df
for (index <- df_columns) {
  if (index != "Main_CustomerID" && index != "Total_Count") {
  frequencyTable = frequencyTable.withColumn(index, df.col(index) / df.col("Total_Count"))
}
}

但是我不喜欢这个for循环，因为我的df比较大。什么是优化的解决方案？

Answer 1

如果你有数据

val df = Seq(
  ("725153", 1, 0, 2, 3),
  ("873008", 0, 0, 3, 3),
  ("625109", 1, 1, 0, 2)
).toDF("Main_CustomerID", "P1", "P2", "P3", "Total_Count")

+---------------+---+---+---+-----------+
|Main_CustomerID|P1 |P2 |P3 |Total_Count|
+---------------+---+---+---+-----------+
|725153         |1  |0  |2  |3          |
|873008         |0  |0  |3  |3          |
|625109         |1  |1  |0  |2          |
+---------------+---+---+---+-----------+

您只需在列上使用foldLeft，Main_CustomerID、Total_Count除外，即P1 P2和P3。

val df_columns = df.columns.toSet - "Main_CustomerID" - "Total_Count" toList

df_columns.foldLeft(df){(tempdf, colName) => tempdf.withColumn(colName, df.col(colName) / df.col("Total_Count"))}.show(false)

这应该会给你

+---------------+------------------+---+------------------+-----------+
|Main_CustomerID|P1                |P2 |P3                |Total_Count|
+---------------+------------------+---+------------------+-----------+
|725153         |0.3333333333333333|0.0|0.6666666666666666|3          |
|873008         |0.0               |0.0|1.0               |3          |
|625109         |0.5               |0.5|0.0               |2          |
+---------------+------------------+---+------------------+-----------+

我希望答案是有帮助的。