java - 如何使用 partitionedBy 计算按列分组的百分位数?
问题描述
我正在使用 spark-sql-2.4.1v,我正在尝试在给定数据的每一列上查找分位数,即百分位数 0、百分位数 25 等。当我做多个百分位数时,如何从结果中检索每个计算的百分位数?
我的数据框df
:
+----+---------+-------------+----------+-----------+-------+
| id| date| revenue|con_dist_1| con_dist_2| zone |
+----+---------+-------------+----------+-----------+-------+
| 10|1/15/2018| 0.010680705| 10|0.019875458| east |
| 10|1/15/2018| 0.006628853| 4|0.816039063| west |
| 10|1/15/2018| 0.01378215| 20|0.082049528| east |
| 10|1/15/2018| 0.010680705| 6|0.019875458| west |
| 10|1/15/2018| 0.006628853| 30|0.816039063| east |
+----+---------+-------------+----------+-----------+-------+
最终的数据框应该如下所示,即每个区域:
+---+---------+-----------+-------+-------------+-----------+-----------+
| id| date| revenue| zone | perctile_col| quantile_0|quantile_10|
+---+---------+-----------+-------+-------------+-----------+-----------+
| 10|1/15/2018|0.010680705| east | con_dist_1 | 10.0| 30.0|
| 10|1/15/2018|0.010680705| east | con_dist_2 |0.019875458|0.816039063|
| 10|1/15/2018|0.010680705| west | con_dist_1 | 4.0| 6.0|
| 10|1/15/2018|0.010680705| west | con_dist_2 |0.019875458|0.816039063|
+---+---------+-----------+-------+-------------+-----------+-----------+
有什么使用方法partitionBy
和approxQuantile
功能吗?是否会使用 进行处理repartition("zone")
,即不收集每个区域的数据集?
解决方案
approxQuantile
在这里不太适合,因为它不允许分组。然而,这个问题可以使用percentile_approx
和 Spark 窗口函数来解决(groupBy
这里也可以,使用哪一个取决于所需的数据帧格式)。首先我们做一些设置:
val df = Seq(
(10, "1/15/2018", 0.010680705, 10,0.019875458, "east"),
(10, "1/15/2018", 0.006628853, 4,0.816039063, "west"),
(10, "1/15/2018", 0.01378215, 20,0.082049528, "east"),
(10, "1/15/2018", 0.010680705, 6,0.019875458, "west"),
(10, "1/15/2018", 0.006628853, 30,0.816039063, "east"))
.toDF("id", "date", "revenue", "con_dist_1", "con_dist_2", "zone")
val percentiles = Seq(0.1, 1.0) // Which percentiles to calculate
val cols = Seq("con_dist_1", "con_dist_2") // The columns to use
要计算每个区域组的百分位数,可以按如下方式完成:
val window = Window.partitionBy("zone")
val percentile_func = (col: String) => expr(s"percentile_approx(${col}, array(${percentiles.mkString(",")}))")
val df2 = cols.foldLeft(df){case (df, c) => df.withColumn(c, percentile_func(c).over(window))}
结果将是这样的:
+---+---------+-----------+----------+--------------------------+----+
|id |date |revenue |con_dist_1|con_dist_2 |zone|
+---+---------+-----------+----------+--------------------------+----+
|10 |1/15/2018|0.006628853|[4, 6] |[0.019875458, 0.816039063]|west|
|10 |1/15/2018|0.010680705|[4, 6] |[0.019875458, 0.816039063]|west|
|10 |1/15/2018|0.010680705|[10, 30] |[0.019875458, 0.816039063]|east|
|10 |1/15/2018|0.01378215 |[10, 30] |[0.019875458, 0.816039063]|east|
|10 |1/15/2018|0.006628853|[10, 30] |[0.019875458, 0.816039063]|east|
+---+---------+-----------+----------+--------------------------+----+
接下来,我们要将数据帧转换为正确的格式。这是对这里答案的轻微改编:如何将计算的百分位数包含/映射到结果数据框?.
cols.map{ case c =>
percentiles
.zipWithIndex
.foldLeft(df2.withColumn("perctile_col", lit(c))){ case (df2, (perc, index)) =>
df2.withColumn(s"qunantile_${perc}", col(c).getItem(index))
}
}
.reduce(_.union(_))
.drop(cols: _*) // these are not needed anymore
最终数据框:
+---+---------+-----------+----+------------+-------------+-------------+
| id| date| revenue|zone|perctile_col|qunantile_0.1|qunantile_1.0|
+---+---------+-----------+----+------------+-------------+-------------+
| 10|1/15/2018|0.006628853|west| con_dist_1| 4.0| 6.0|
| 10|1/15/2018|0.010680705|west| con_dist_1| 4.0| 6.0|
| 10|1/15/2018|0.010680705|east| con_dist_1| 10.0| 30.0|
| 10|1/15/2018| 0.01378215|east| con_dist_1| 10.0| 30.0|
| 10|1/15/2018|0.006628853|east| con_dist_1| 10.0| 30.0|
| 10|1/15/2018|0.006628853|west| con_dist_2| 0.019875458| 0.816039063|
| 10|1/15/2018|0.010680705|west| con_dist_2| 0.019875458| 0.816039063|
| 10|1/15/2018|0.010680705|east| con_dist_2| 0.019875458| 0.816039063|
| 10|1/15/2018| 0.01378215|east| con_dist_2| 0.019875458| 0.816039063|
| 10|1/15/2018|0.006628853|east| con_dist_2| 0.019875458| 0.816039063|
+---+---------+-----------+----+------------+-------------+-------------+
推荐阅读
- node.js - Docusign - 如何为多个用户集成 docusign,每个用户都有不同的 docusign 凭据和多个客户
- javascript - 如何在基于反应js的id中获取api中的数据
- r - 从 FTP 到 R 损坏的文件
- flutter - 在小部件定义中插入 if-else 语句
- python - 如何在命令提示符下输入变量并将输入变量与其他变量进行比较
- regex - Bash 脚本:检测电子邮件地址后的分隔符,并进行分支
- google-cloud-platform - 如何查找从 Google Cloud Platform 下载数据的用户:服务:“云存储”、SKU“下载全球目的地”
- python-3.x - 如何在 Selenium python 中找到页面的特定元素,因为我找不到它们
- javascript - JS 适用于 CodePen 但不适用于本地
- python - 尽管存在自定义语言,但 Tesseract 未能加载自定义语言