Data Expression with Groupby & Intersection

JerrySneary — Wed, 19 Jul 2023 22:56:02 GMT

I originally created a data expression that intersected a point layer with a polygon layer and returned a count of points per polygon and a sum of a field.

Now I've been asked to split each of the counts and sums for each district by Survey_Year. I lost count of how many iterations I did on the above code but could never figure it out. I am now trying to use this code to accomplish my goal but I can't seem to figure it out, if I'm even going in the right direction.

Any help would be appreciated.

Jerry

Re: Data Expression with Groupby & Intersection

JerrySneary — Thu, 20 Jul 2023 18:19:24 GMT

I figured it out. Just in case it could help someone else I'll paste the code below.

// Create FeatureSet for polygons var boundaries = FeatureSetByPortalItem( Portal('https://arcgis.com/'), 'a1795cc14f9744d68787f649a7865715', 12, ['Type', 'DISTRICT'], true ); // Create Featureset for points var nno = FeatureSetByPortalItem( Portal('https://arcgis.com/'), '8de09d32c583450584fe901ecaed1f6f', 0, ['Survey_Year', 'Expected_Attendance'], true ); var outputDict = { 'fields': [ { 'name': 'Survey_Year', 'type': 'esriFieldTypeString'}, { 'name': 'Type', 'type': 'esriFieldTypeString'}, { 'name': 'DISTRICT', 'type': 'esriFieldTypeString'}, { 'name': 'NNO_Count', 'type': 'esriFieldTypeInteger'}, { 'name': 'NNO_Attendance', 'type': 'esriFieldTypeInteger'} ], 'geometryType': 'esriGeometryPolygon', 'features': [] }; var index = 0; for (var boundary in boundaries) { var district = boundary['DISTRICT']; var nnoint = Intersects(boundary, nno); // Create an object to store counts and sums by Survey_Year var surveyYearData = Dictionary(); for (var nnoi in nnoint) { var surveyYear = nnoi.Survey_Year; var expectedAttendance = nnoi.Expected_Attendance; if (!HasKey(surveyYearData, surveyYear)) { var dictVars = Dictionary("count", 0, "sum", 0); surveyYearData[surveyYear] = dictVars; } surveyYearData[surveyYear].count++; surveyYearData[surveyYear].sum += expectedAttendance; } // Add aggregated data to the output feature set for (var currYear in surveyYearData) { var currCount = surveyYearData[currYear].count; var currSum = surveyYearData[currYear].sum; outputDict.features[index++] = { 'attributes': { 'Survey_Year': currYear, 'Type': boundary['Type'], 'DISTRICT': district, 'NNO_Count': currCount, 'NNO_Attendance': currSum }, 'geometry': Geometry(boundary) }; } } //return null; return FeatureSet(Text(outputDict));

topic Data Expression with Groupby & Intersection in ArcGIS Online Questions

Data Expression with Groupby & Intersection

Re: Data Expression with Groupby & Intersection