将行值转换为列，其值来自 spark scala 中另一列的值 [英] Convert row values into columns with its value from another column in spark scala

问题描述

我正在尝试将行中的值转换为不同列中的值及其来自另一列的值.例如 -

I'm trying to convert values from row into different columns with its value from another column. For example -

输入数据框就像 -

+-----------+
| X | Y | Z |
+-----------+
| 1 | A | a |
| 2 | A | b |
| 3 | A | c |
| 1 | B | d |
| 3 | B | e |
| 2 | C | f |
+-----------+

输出数据帧应该是这样的 -

And the output dataframe should be like this -

+------------------------+
| Y | 1    | 2    | 3    |
+------------------------+
| A | a    | b    | c    |
| B | d    | null | e    |
| C | null | f    | null |
+------------------------+

我尝试对基于 Y 的值和 X 和 Z 上的 collect_list 进行分组，然后压缩 X &Z 在一起以获得某种键值对.但是对于 Y 的某些值，某些 X 可能会丢失，因此为了用空值填充它们，我交叉加入了 X 的所有可能值和 Y 的所有可能值，然后将其加入了我的原始数据帧.这种方法效率极低.

I've tried to groupBy the values based on Y and collect_list on X and Z and then zipped X & Z together to get some sort of key-value pairs. But some Xs may be missing for some values of Y so in order to fill them with null values, I cross joined all possible values of X and all possible values of Y and then joined it my original dataframe. This is approach is highly inefficient.

有什么有效的方法可以解决这个问题吗?提前致谢.

Is there any efficient method to approach this problem ? Thanks in advance.

推荐答案

你可以简单地使用 groupBy 和 pivot 和 first 作为聚合函数作为

You can simply use groupBy with pivot and first as aggregate function as

import org.apache.spark.sql.functions._
df.groupBy("Y").pivot("X").agg(first("z")) 

输出:

+---+----+----+----+
|Y  |1   |2   |3   |
+---+----+----+----+
|B  |d   |null|e   |
|C  |null|f   |null|
|A  |a   |b   |c   |
+---+----+----+----+

这篇关于将行值转换为列，其值来自 spark scala 中另一列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！