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: SiteSpecies1Species1ObsSpecies2Species2ObsSpecies3Species3ObsLittle SwampRed Kangaroo5Wallaby3Eastern Grey10Big SwampFox5Red Kangaroo10Eastern Grey5 What I want is to use a data expression so I can transform the data to use indicator(s) eg: SpeciesTotalRed Kangaroo15Wallaby3Fox5Eastern Grey15 It seems like it would be possible in a data expression, I just need some help with a starting point. Cheers, Chris

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Find more posts tagged with

Accepted answers

JohannesLindner

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:

All comments

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

Awesome!

Thanks so much!!

JohannesLindner

Sorry, forgot to mention...

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

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

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.