Select to view content in your preferred language

Groupby

765
4
05-18-2023 06:08 PM
SRSSpatial
New Contributor II

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

0 Kudos
4 Replies
JohannesLindner
MVP Frequent Contributor

 

// 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))

 


Have a great day!
Johannes
SRSSpatial
New Contributor II

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? 

SRSSpatial_0-1684708600972.png

Thanks 

Calvin

0 Kudos
JohannesLindner
MVP Frequent Contributor

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")

Have a great day!
Johannes
SRSSpatial
New Contributor II

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

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

SRSSpatial_0-1685409235726.png

 

Cheers 

0 Kudos