Select to view content in your preferred language

Summary Statistic Tool - Weighted average

1695
5
05-27-2014 11:21 PM
Status: Under Consideration
Labels (1)
CarstenSchuermann
Occasional Contributor

Give us the possibility in the summary statistics tool to calculate weighted averages, where the weights are taken from another field. Currently the tool only allows to calculate simple averages (among other statistics like min, max, mean etc.). The corresponding statistics tool in ArcInfo Workstation allows calculation of weighted averages since the beginning, so I am wondering why the summary statistics tool is lacking such option.

Tags (1)
5 Comments
DavidJames

Well, here's a good idea that has seen no activity. Yes, a weighted average calculator would be great...and specifically an area-weighted average would be a nice touch. so how about...

- weighted average

- area-weighted average

- most frequent occurrence

Please make summary statistics as expansive and useful as possible.

WalkerKosmidou-Bradley

Add the option to use sample or population weights in Summarize Within, Summary Statistics, Multivariate Clustering, and other statistical tools.

Context:

My team has been tasked with producing a number of accessibility metrics for client governments. We've done that using custom friction surfaces, and we've extracted those to population and survey points.  The intent is to aggregate these to an admin level because that's where decisions will be made, and budgets allocated. The statistics toolbox (Summarize within and summary stats) and spatial statistics tools do not include the option to apply population or sample weights.  This means that we'll have to do spatial joins/intersect to tack on the admin boundary data to the points, then export to CSVs, ship it to economists to collapse in STATA, then they ship it back to us to link up with the Admin boundaries to display and merge with other variables at the ADM level to continue analysis or display.  ArcGIS is first and foremost a spatial software, but it does include many straight statistics functions, hence all the lovely options in the Summary Stats and summarize within tools.  The use of weights in statistics is a critical function thereof and it would be great to be able to do that directly in ArcPro so we can better leverage ArcPy and ArcNotebooks.  I can see a number of business clients also seeking to use weights in their analytics (Business Analyst does this a little bit) and the lack of weighted statistical function is a pretty sizeable hole in the statistical modernization/census context.  If this functionality were available, I can certainly see the need (and in fact we're bouncing around it) for the use of both spatial and population weights simultaneously.  We just have to do those things separately at the moment in a very clunky manner

MargaretCrawford
Status changed to: Under Consideration
 
DennisSwaney

Has there been any progress on providing a tool for area-weighting (or other weighting options).  It is really ridiculous that such a tool is apparently not yet available in ArcGIS PRO.  If such functionality has been added, could you point me to it.

Thanks!

Bud
by

FYI, as a plan B, you can calculate a weighted average in a database view or a query layer using SQL:

https://dba.stackexchange.com/a/189236/100880

select
    road_id,
    to_char(trunc( insp_date), 'YYYY-MM-DD') as insp_day,
    avg(condition) condition_avg,
    sum(insp_length) insp_length_tot,
    round(sum(condition * insp_length) / sum(insp_length), 2) as CONDITION_WEIGHTED_AVG 
from
    road_insp
group by
    road_id,
    trunc(insp_date)
order by
    road_id,
    trunc( insp_date);

   ROAD_ID INSP_DAY   CONDITION_AVG INSP_LENGTH_TOT CONDITION_WEIGHTED_AVG
---------- ---------- ------------- --------------- ----------------------
       100 2017-01-01             5             100                      5
       101 2017-02-01          5.75            1500                   5.83
       102 2018-04-01             7            6000                   7.08
       103 2018-07-01          8.25            6500                   8.27
       103 2019-09-01          9.25            8500                   9.26

As always, be very careful with nulls: Tips about nulls in SQL


And I have a different idea here that might interest people who use Summary Statistics: Summary Statistics — Tie fields to a primary field