topic Groupby in ArcGIS Online Questions

Groupby

SRSSpatial — Fri, 19 May 2023 03:03:47 GMT

Hi guys,

I have a table that I want to use the groupby function in 2 different ways, then join it back to calculate the percentages to display on ArcGIS dashboard. I am very new to ArcGIS Arcade so this has been a major struggle. Can someone please provide some guidance?

What I have got so far:

//Grab data

var portal = Portal('https://www.arcgis.com/');

var fs = FeatureSetByPortalItem(portal,'id',0,['LGA','Activity']);

//Table 1

var LGA_Activity_Count = GroupBy(fs,['LGA','Activity'],[

{name: 'Local_Activity_Count', expression: 'Activity', statistic: 'Count'},]);

//Table 2

var LGA_Activity_Count = GroupBy(fs,['Activity'],[

{name: 'State_Activity_Count', expression: 'Activity', statistic: 'Count'},]);

//Join Table

//(Table 1 divide by table 2) * 100 to work out the percentages

Re: Groupby

JohannesLindner — Fri, 19 May 2023 18:01:18 GMT

// function to load Featureset into RAM (for performance) // community.esri.com/t5/arcgis-online-blog/improving-expression-performance-a-custom-function/ba-p/1288785 function Memorize(fs) {var temp_dict = {fields: Schema(fs)['fields'], geometryType: '', features: []}; for (var f in fs) {var attrs = {}; for (var attr in f) {attrs[attr] = Iif(TypeOf(f[attr]) == 'Date', Number(f[attr]), f[attr]);} Push(temp_dict['features'], {attributes: attrs});} return FeatureSet(Text(temp_dict))} // load Featureset var fs = Memorize(FeaturesetByPortalItem(Portal("https://www.arcgis.com"), "b0d335151aad48a5883326b9aed69cdd", 0, ["LGA", "Activity"], false)) // GroupBy county and activity fields var local_activities = GroupBy(fs, ["LGA", "Activity"], [{name: "LocalActivityCount", expression: "1", statistic: "COUNT"}]) // GroupBy activity field var state_activities = GroupBy(fs, ["Activity"], [{name: "StateActivityCount", expression: "1", statistic: "COUNT"}]) // create empty output featureset ( add percentage field to local_activities) var out_fs = { geometryType: "", fields: Splice( Schema(local_activities).fields, [{name:"PercentageOfTotal", type: "esriFieldTypeDouble"}] ), features: [] } // iterate through the local activities for(var f_local in local_activities) { // find the corresponding feature in the state activities var a = f_local.Activity var f_state = First(Filter(state_activities, "Activity = @a")) // copy the local activity attributes var att = Dictionary(Text(f_local)).attributes // calculate and append the percentage att.PercentageOfTotal = f_local.LocalActivityCount / f_state.StateActivityCount * 100 // add the new feature to the output fs Push(out_fs.features, {attributes: att}) } return Featureset(Text(out_fs))

Re: Groupby

SRSSpatial — Sun, 21 May 2023 22:54:40 GMT

Hi Johannes,

Thank you so much for taking the time to answer my question!

Unfortunately I've received an error message (image below). I am thinking this might have something to do with the "Activity" column that's sitting in the back-end as there are few records showing null/blank value. Is there any way to fix this?

Thanks

Calvin

Re: Groupby

JohannesLindner — Fri, 26 May 2023 06:21:29 GMT

Yes, it is because of null values. To fix it, you can insert this after line 7 to ignore features with null values in either field:

fs = Filter(fs, "LGA IS NOT NULL AND Activity IS NOT NULL")

Re: Groupby

SRSSpatial — Tue, 30 May 2023 01:14:04 GMT

Thank you so much Johannes. This is exactly what I am chasing after :).

Any chance you know why the "StateActivityCount" column isn't displaying?

Cheers