如何在pyspark中的groupBy之后获得每个计数的总数百分比? [英] How get the percentage of totals for each count after a groupBy in pyspark?

问题描述

给定以下数据帧:

import findspark
findspark.init()
from pyspark.sql import SparkSession

spark = SparkSession.builder.master("local").appName("test").getOrCreate()
df = spark.createDataFrame([['a',1],['b', 2],['a', 3]], ['category', 'value'])
df.show()


+--------+-----+
|category|value|
+--------+-----+
|       a|    1|
|       b|    2|
|       a|    3|
+--------+-----+

我想计算每个类别中的项目数量，并为每个计数提供一个百分比，就像这样

I want to count the number of items in each category and provide a percentage of total for each count, like so

+--------+-----+----------+
|category|count|percentage|
+--------+-----+----------+
|       b|    1|     0.333|
|       a|    2|     0.667|
+--------+-----+----------+

推荐答案

你可以groupby和agg聚合:

import pyspark.sql.functions as F

df.groupby('category').agg(F.count('value') / df.count()).show()

输出:

+--------+------------------+
|category|(count(value) / 3)|
+--------+------------------+
|       b|0.3333333333333333|
|       a|0.6666666666666666|
+--------+------------------+

为了让它更好，你可以使用:

To make it nicer you can use:

df.groupby('category').agg(
    (
        F.round(F.count('value') / df.count(), 2)
    ).alias('ratio')
).show()

输出:

+--------+-----+
|category|ratio|
+--------+-----+
|       b| 0.33|
|       a| 0.67|
+--------+-----+

您也可以使用 SQL:

You can also use SQL:

df.createOrReplaceTempView('df')

spark.sql(
    """
    SELECT category, COUNT(*) / (SELECT COUNT(*) FROM df) AS ratio
    FROM df
    GROUP BY category
    """
).show()

这篇关于如何在pyspark中的groupBy之后获得每个计数的总数百分比?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！