Convert Text to Number Using Arcade and Get Average in ArcGIS Dashboards

GeodataSystems

Hi! I have a training evaluation survey form that gets feedback and returns 5,4,3,2,1. For every item that they need to rate, Survey123 returns strings (5,4,3,2,1 in text format). I need to convert this to numeric so that I can get the average for every item and then get the average for each category. Thank you!

JohannesLindner

To get the average of a featureset column , we can use the Average() function.

 

Let's load some test data:

 

 

// create a test fs for a scoring survey.
// 3 text fields with scores between 1 and 5
var survey_dict = {
    fields:[
        {name: "Score1", type: "esriFieldTypeString"},
        {name: "Score2", type: "esriFieldTypeString"},
        {name: "Score3", type: "esriFieldTypeString"},
        ],
    geometryType: "",
    features: []
}
for(var i = 0; i < 200; i++) {
    var rating = {attributes: {
        Score1: Text(Round(4 * Random() + 1)),
        Score2: Text(Round(4 * Random() + 1)),
        Score3: Text(Round(4 * Random() + 1)),
    }}
    Push(survey_dict.features, rating)
}
var survey = Featureset(Text(survey_dict))
// return survey

 

 

 

Now, if we take the Average() of column Score1, we get this result:

 

This is obviously incorrect. It seems like the function doesn't convert to Number(), but instead somehow uses the bytes of the string values as number (or something else...).

So we need to covert that column to a proper integer column first. I haven't found a way to do that inside the Average() function, so we should probably just convert the featureset before calling Average():

var score_fields = ["Score1", "Score2", "Score3"]
// create an empty featureset dict
var numeric_survey_dict = {
    fields: [],
    geometryType: "",
    features: []
}
// add the score fields as integer fields
for(var i in score_fields) {
    var field = {name: score_fields[i], type: "esriFieldTypeInteger"}
    Push(numeric_survey_dict.fields, field)
}
// go thorugh the original featureset and convert each value to Number()
for(var f in survey) {
    var converted_f = Dictionary()
    for(var i in score_fields) {
        var field = score_fields[i]
        converted_f[field] = Number(f[field])
    }
    Push(numeric_survey_dict.features, {attributes: converted_f})
}
// convert the dictionary to a featureset
var numeric_survey = Featureset(Text(numeric_survey_dict))

// get the Average()
return Average(numeric_survey, "Score1")