question Re: calculating median on grouped data in Archives of Support Questions (Read Only)

calculating median on grouped data

ffmm — Fri, 16 Sep 2022 10:15:36 GMT

Hello! I was trying to use spark to calculate median on grouped values in a dataframe, but have not had much success. I have tried using agg(), but median() is not available; tried to apply rank() to window function but the rank was not grouped; also tried to pivot the table to avoid the grouped step but the data frame is huge (8million rows) and it fails multiple times. Calculating median should be something straightforward to do since data analysts use it a lot. Maybe I'm missing something obvious?

Thanks!!

Re: calculating median on grouped data

srowen — Mon, 25 Apr 2016 23:10:22 GMT

Calculating a median or other quantiles is in general much harder than computing a moment like a mean. You want to look for functions like Spark that compute quantiles, rather than look for a median function -- median is the 0.5 quantile. There is an efficient approximate implementation for DataFrames in Spark.

Re: calculating median on grouped data

ffmm — Fri, 29 Apr 2016 13:32:09 GMT

Thanks! Yes percent_rank() and window function together did the trick. A different way is to sort the column and pick the one that is in the middle. The results are close.