我使用取自另一篇SO post的公式来计算pyspark中列的中位数:
columns = ['id', 'dogs', 'cats']
vals = [(1, 2, 0),(2, 0, 1)]
df = sqlContext.createDataFrame(vals, columns)
df.approxQuantile(list(c for c in df.columns), [0.5], 0)当df中有奇数行时,该公式有效,但如果行数为偶数,则不取中心元素之间的平均值(我的意思当然是排序后),而只取第一个元素的平均值。
例如,上述代码的结果如下:
[[1.0], [0.0], [0.0]] 而它本应该是:
[[1.5], [1.0], [0.5]] 如何正确计算pyspark的中位数?
发布于 2019-01-30 01:34:30
下面是一种获得中位数的简单方法
import numpy as np
np.random.seed(2019)
def get_median(df, col):
# if # df rows are even get the "middle" values and average
num_rows = df.count()
if num_rows%2==0:
mid_pt = num_rows/2
q1 = df.approxQuantile( col, [0.5], 0)
q2 = df.approxQuantile( col, [(mid_pt+1)*1./num_rows], 0)
return(np.mean(q1+q2).tolist())
return(df.approxQuantile( col, [0.5], 0))
# now test it out
df = pd.DataFrame(data = np.random.rand(20,2), columns=['dogs', 'cats'])
df['id'] = list(range(20))
df = sqlContext.createDataFrame(df)
df.approxQuantile( "cats", [0.5], 0)
# [0.5783015865898744]发布于 2020-08-12 18:44:44
我按组计算了精确的中位数(不使用numpy)。您可以通过删除Windows部件轻松地调整方法。
如果首先按组为每个值分配一个行数(排序后),并计算每个组的行数。
<>F29优先保留的行数的平均值
import pyspark.sql.functions as F
from pyspark.sql.window import Window
columns = ['group1_col', 'group2_col', 'value_col']
vals = [['a', 'aa', 1],
['a', 'aa', 2],
['b', 'aa', 2],
['b', 'aa', 0],
['c', 'aa', 0],
['c', 'bb', 1],
['d', 'bb', 10],
['d', 'bb', 20],
['d', 'bb', 30],
]
df = spark.createDataFrame(vals, columns)
def compute_median(self, col, median_name, by_col=None):
""" Method to be added to spark native DataFrame class """
df_without_null = self.filter(F.col(col).isNotNull())
window_spec = Window.partitionBy()
if by_col is not None:
window_spec = Window.partitionBy(by_col)
window_spec_order = window_spec.orderBy(col)
df = (df_without_null
.withColumn('row_number', F.row_number().over(window_spec_order))
.withColumn('total_rows', F.count(F.lit(1)).over(window_spec))
)
row_to_keep = (
df
.filter((F.col('row_number') == F.ceil(F.col('total_rows') / 2))
| ((F.col('total_rows') % 2 == 0)
& (F.col('row_number') == F.floor(F.col('total_rows') / 2) + 1)))
)
if by_col is None:
return row_to_keep.select(F.mean(F.col(col)).alias(median_name))
return row_to_keep.groupBy(by_col).agg(F.mean(F.col(col)).alias(median_name))
# Add method to DataFrame class
DataFrame.compute_median = compute_median
# med = df.compute_median("value_col", "global_median")
# med.collect()
# global_median
# 2.0
# med_group1 = df.compute_median("value_col", "median", 'group1_col')
# med_group1.collect()
# group1_col | median
# a | 1.5
# b | 1.0
# c | 0.5
# d | 20.0
# med_group2 = df.compute_median("value_col", "median", 'group2_col')
# med_group1.collect()
# group1_col | median
# aa | 1.5
# bb | 15.0您可以检查我的过滤条件是否等同于这个条件(更长,但可能更容易理解)
res = (df_rank_and_number_or_row
.filter(
((F.col('total_rows') % 2 == 0)
& ((F.col('row_number') == F.ceil(F.col('total_rows') / 2))
| (F.col('row_number') == F.floor(F.col('total_rows') / 2) + 1))
) |
(F.col('total_rows') % 2 != 0)
& ((F.col('row_number') == F.ceil(F.col('total_rows') / 2))
)
)发布于 2021-08-29 17:47:24
尝尝这个。它取中间数字的平均值。它也适用于奇数个观察值。
(np.array(a1.approxQuantile(['id', 'dogs', 'cats'], [0.5], 0)) + np.array(a1.approxQuantile(['id', 'dogs', 'cats'], [0.51], 0)))/2
输出:数组([1.5,1.,0.5])
这也是可行的:
a1.select(((percentile_approx(a1.id, 0.5,100000) + percentile_approx(a1.id, 0.51,100000))*.5)
https://stackoverflow.com/questions/54401568
复制相似问题