Combine and sum fields in data expression

[ChrisRoberts2]

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

[JohannesLindner]

// 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"}])

[ChrisRoberts2]

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

[JohannesLindner · Accepted solution]

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:

[ChrisRoberts2]

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}

[ChrisRoberts2]

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

[ChrisRoberts2]

Awesome!

Thanks so much!!