topic Re: Combine and sum fields in data expression in ArcGIS Dashboards Questions

Combine and sum fields in data expression

ChrisRoberts2 — Thu, 01 Dec 2022 02:46:28 GMT

Hi All

I'm sure this can be done but I'm new to data expressions and just cant seem to get started.

I have a dataset that has fields Species1, Species1Obs, Species2, Species2Obs, Species3, Species3Obs and so on

The species are defined via a dropdown list so what I want to do is to be able to sum the total number of observations per species across all fields. For example the data table might look something like this:

Site	Species1	Species1Obs	Species2	Species2Obs	Species3	Species3Obs
Little Swamp	Red Kangaroo	5	Wallaby	3	Eastern Grey	10
Big Swamp	Fox	5	Red Kangaroo	10	Eastern Grey	5

What I want is to use a data expression so I can transform the data to use indicator(s) eg:

Species	Total
Red Kangaroo	15
Wallaby	3
Fox	5
Eastern Grey	15

It seems like it would be possible in a data expression, I just need some help with a starting point.

Cheers, Chris

Re: Combine and sum fields in data expression

JohannesLindner — Thu, 01 Dec 2022 07:28:18 GMT

// load data var input = {fields: [{name: "Site", type: "esriFieldTypeString"}, {name: "Species1", type: "esriFieldTypeString"}, {name: "Species1Obs", type: "esriFieldTypeInteger"}, {name: "Species2", type: "esriFieldTypeString"}, {name: "Species2Obs", type: "esriFieldTypeInteger"}, {name: "Species3", type: "esriFieldTypeString"}, {name: "Species3Obs", type: "esriFieldTypeInteger"},], features: [{attributes: {Site: "Little Swamp", Species1: "Red Kangaroo", Species1Obs: 5, Species2: "Wallaby", Species2Obs: 3, Species3: "Eastern Grey", Species3Obs: 10}}, {attributes: {Site: "Big Swamp", Species1: "Fox", Species1Obs: 5, Species2: "Red Kangaroo", Species2Obs: 10, Species3: "Eastern Grey", Species3Obs: 5}},], geometryType: ""} var fs = Featureset(Text(input)) // flatten the table var flat_dict = { fields: [ {name: "Species", type: "esriFieldTypeString"}, {name: "Obs", type: "esriFieldTypeInteger"}, ], features: [], geometryType: "" } var max_species_number = 3 for(var f in fs) { for(var i = 1; i <= max_species_number; i++) { var new_f = {attributes: {Species: f[`Species${i}`], Obs: f[`Species${i}Obs`]}} Push(flat_dict.features, new_f) } } var flat_fs = Featureset(Text(flat_dict)) // group by species and return return GroupBy(flat_fs, ["Species"], [{name: "Obs", expression: "Obs", statistic: "SUM"}])

Re: Combine and sum fields in data expression

ChrisRoberts2 — Thu, 01 Dec 2022 23:25:31 GMT

Thanks for the prompt response Johannes

The code you gave me just seems to take a table of data. What I need to do is interegate the dataset in the portal, combine all the species and sum the observation

Re: Combine and sum fields in data expression

JohannesLindner — Fri, 02 Dec 2022 08:42:40 GMT

Sorry, forgot to mention...

I took your sample data for the expression. In your real expression, you should of course load the featureset from your portal. So instead of lines 2&3 in my code above, you would do something like this:

var fs = FeaturesetByPortalItem( Portal("https://my-server.de/portal"), // your Portal's url "bb11a82308a5439291c17a477755d27a", // the service id 0, // the sub-layer id ["*"], // the fields you want to load false // do you want to load geometries )

You can get all the neccessary arguments from your table's url:

Re: Combine and sum fields in data expression

ChrisRoberts2 — Sun, 04 Dec 2022 21:26:13 GMT

Awesome!

Thanks so much!!

Re: Combine and sum fields in data expression

ChrisRoberts2 — Mon, 05 Dec 2022 21:56:31 GMT

Hi Johannes

I hate to push my luck because you have been super helpful.....but is it possible to further group/split by Observation Date {obs_date}

Re: Combine and sum fields in data expression

ChrisRoberts2 — Tue, 06 Dec 2022 05:58:28 GMT

I actually think I managed to work it out myself 🙂

var fs = FeatureSetByPortalItem(Portal('https://xxxxx'), 'a81b87605d0346599ea5cffbebab66e6', 0, ["*"], false);
// load data

// flatten the table
var flat_dict = {
fields: [
{name: "Species", type: "esriFieldTypeString"},
{name: "Obs", type: "esriFieldTypeInteger"},
{name: "Obs_date", type: "esriFieldTypeDate"},
],
features: [{attributes: {Obs_date: Number(`obs_date`)}}],
geometryType: ""
}
var max_species_number = 7
for(var f in fs) {
for(var i = 1; i <= max_species_number; i++) {
var new_f = {attributes: {Obs_date: Number(f[`obs_date`]), Species: f[`Species${i}`], Obs: f[`Species${i}Obs`]}}
Push(flat_dict.features, new_f)
}
}
var flat_fs = Featureset(Text(flat_dict))

// group by species and return
return GroupBy(flat_fs, ["Species", "obs_date"], [{name: "Obs", expression: "Obs", statistic: "SUM"}])