Combine and sum fields in data expression

1032
6
Jump to solution
11-30-2022 06:46 PM
ChrisRoberts2
Occasional Contributor III

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:

 

SiteSpecies1Species1ObsSpecies2Species2ObsSpecies3Species3Obs
Little SwampRed Kangaroo5Wallaby3Eastern Grey10
Big SwampFox5Red Kangaroo10Eastern Grey5

 

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

SpeciesTotal
Red Kangaroo15
Wallaby3
Fox5
Eastern Grey15

 

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

Cheers, Chris

0 Kudos
1 Solution

Accepted Solutions
JohannesLindner
MVP Frequent Contributor

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:

JohannesLindner_0-1669970502979.png

 


Have a great day!
Johannes

View solution in original post

6 Replies
JohannesLindner
MVP Frequent Contributor
// 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"}])

JohannesLindner_1-1669879689299.png

 

 


Have a great day!
Johannes
0 Kudos
ChrisRoberts2
Occasional Contributor III

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

0 Kudos
JohannesLindner
MVP Frequent Contributor

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:

JohannesLindner_0-1669970502979.png

 


Have a great day!
Johannes
ChrisRoberts2
Occasional Contributor III

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}

0 Kudos
ChrisRoberts2
Occasional Contributor III

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

0 Kudos
ChrisRoberts2
Occasional Contributor III

Awesome!

Thanks so much!!

0 Kudos