topic Re: Use Arcade Data Expressions to split Ranked Choice Fields into Separate Columns in ArcGIS Dashboards Questions

Use Arcade Data Expressions to split Ranked Choice Fields into Separate Columns

MatthewBrandt — Mon, 24 Jul 2023 23:38:57 GMT

Hello, I am trying to display some survey results in a dashboard. The question is ranked choice and I'm trying to replicate the chart shown in Survey123 Analyze -- where it gets the average "score" for each of the ranked choices. I have not been able to embed the analyze results in a way that I like, so now I'm trying to replicate using data expressions. The end result I'm looking for is a dictionary/array with a row for each of the unique choices, a column for each of the ranks, and a count in each field representing how many times that choice was ranked that number, as well as a final column representing the average. Below is an example of the input data

FeatureID	RankChoiceList
1	Apple,Banana,Orange
2	Apple,Orange,Banana
3	Orange,Apple,Banana
4	Banana,Apple,Orange
5	Apple,Banana,Orange

Now an example of the desired result, the average is calculated by: [(1st Choice Count * 3) + (2nd Choice Count * 2) + (3rd Choice Count * 1)] / [# of Features]

Choice	1st Choice	2nd Choice	3rd Choice	Average
Apple	3	2	0	2.6
Banana	1	2	2	1.8
Orange	1	1	3	1.6

Now for the Arcade, this is my thought process followed by what I have so far:

First I'd like to split the RankChoiceList Column into three columns (1st, 2nd and 3rd) where the order of the comma-separated values matters -- first value in the 1st choice, second value in the 2nd choice, etc..

FeatureID	1st Choice	2nd Choice	3rd Choice
1	Apple	Banana	Orange
2	Apple	Orange	Banana
3	Orange	Apple	Banana
4	Banana	Apple	Orange
5	Apple	Banana	Orange

Then I would like to get counts of each of the unique choices for every rank (a total of 3 tables for each of the three choices)

Choice	1st Choice
Apple	3
Banana	1
Orange	1

Then I'd like to combine these tables and calculate the average -- resulting in the desired result table from above.

I have laid out my whole thought process in case there are suggestions of ways to do it better, however I am stuck on the first step. My code below returns a dictionary with the appropriate number of features and appropriate fields, but all of them return the same feature (objectId 19 and the corresponding ranks)

// Reference layer using the FeatureSetByPortalItem() function. var fs = FeatureSetByPortalItem(Portal('https://www.arcgis.com'), '2284118eba59466e9169133c458ef9d4' , 0, ['nodes','character_qualities','development_priorities','objectid'], false); // Empty dictionary to capture each hazard reported as separate rows. var choicesDict = {'fields': [{'name':'oid','type':'esriFieldTypeString'},{ 'name': 'split_choices', 'type': 'esriFieldTypeString'},{ 'name': 'first_choice', 'type': 'esriFieldTypeString'},{ 'name': 'second_choice', 'type': 'esriFieldTypeString'},{ 'name': 'third_choice', 'type': 'esriFieldTypeString'},{ 'name': 'fourth_choice', 'type': 'esriFieldTypeString'},{ 'name': 'fifth_choice', 'type': 'esriFieldTypeString'},{ 'name': 'sixth_choice', 'type': 'esriFieldTypeString'}], 'geometryType': '', 'features': []}; var index = 0; // Split comma separated hazard types and store in dictionary. for (var feature in fs) { var split_array = Split(feature["development_priorities"], ',') var count_arr = Count(fs) for(var i = 0; i < count_arr; i++ ){ choicesDict.features[i] = { 'attributes': { //'split_choices': Trim(split_array[i]), 'first_choice':split_array[0],'oid':feature.objectid,'second_choice':split_array[1],'third_choice':split_array[2],'fourth_choice':split_array[3],'fifth_choice':split_array[4],'sixth_choice':split_array[5]}} }} return choicesDict

My question is: how can I modify my code to successfully translate the choices into the appropriate columns and/or what would be a better way to do this?

Re: Use Arcade Data Expressions to split Ranked Choice Fields into Separate Columns

KenBuja — Tue, 25 Jul 2023 14:19:50 GMT

What's happening is that as you loop through each feature, in line 14 you are rewriting all the records in the dictionary for the current feature's attributes. Remove that loop and each dictionary record will have the correct attributes. I removed that loop and used the index variable as a counter instead of i. Also, don't use "feature" as a variable, since it is a reserved word.

// Reference layer using the FeatureSetByPortalItem() function. var fs = FeatureSetByPortalItem(Portal('https://www.arcgis.com'), '2284118eba59466e9169133c458ef9d4' , 0, ['nodes','character_qualities','development_priorities','objectid'], false); // Empty dictionary to capture each hazard reported as separate rows. var choicesDict = {'fields': [{'name':'oid','type':'esriFieldTypeString'},{ 'name': 'split_choices', 'type': 'esriFieldTypeString'},{ 'name': 'first_choice', 'type': 'esriFieldTypeString'},{ 'name': 'second_choice', 'type': 'esriFieldTypeString'},{ 'name': 'third_choice', 'type': 'esriFieldTypeString'},{ 'name': 'fourth_choice', 'type': 'esriFieldTypeString'},{ 'name': 'fifth_choice', 'type': 'esriFieldTypeString'},{ 'name': 'sixth_choice', 'type': 'esriFieldTypeString'}], 'geometryType': '', 'features': []}; var index = 0; // Split comma separated hazard types and store in dictionary. for (var f in fs) { console(f.objectid, f["development_priorities"]) var split_array = Split(f["development_priorities"], ',') var count_arr = Count(fs) //for(var i = 0; i < count_arr; i++ ){ choicesDict.features[index] = { 'attributes': { //'split_choices': Trim(split_array[i]), 'first_choice':split_array[0],'oid':f.objectid,'second_choice':split_array[1],'third_choice':split_array[2],'fourth_choice':split_array[3],'fifth_choice':split_array[4],'sixth_choice':split_array[5]}} //} index++; } return choicesDict

Re: Use Arcade Data Expressions to split Ranked Choice Fields into Separate Columns

MatthewBrandt — Tue, 25 Jul 2023 16:39:34 GMT

@KenBuja Thank you for your help on this! I was wondering if you had any insights in the second part of the code I am working on to get the desired result table from my initial post. My code below returns a dictionary counting the occurrences of the respective values across the whole dataset, I would instead like a table with the unique counts for occurrences within the specific fields. I imagine I will have to change the value_counts dictionary to include a sub-dictionary or list within each of the values, and then somehow populate that within the loop for each i in fields?

//create dictionary of possible values setting default count at 0 var value_counts = { "transportation": 0, "jobs": 0, "housing":0, "sustainability":0, "historical":0, "parks":0 } //list of unique fields to get counts for each of the values var fields = ["first_choice", "second_choice", "third_choice","fourth_choice","fifth_choice","sixth_choice"] //loop through rows in feature that contain the text 1st, 2nd, 3rd choice etc. for(var a in fs_dict) { //loop through each field that needs to be counted within that feature for(var i in fields) { var value = a[fields[i]] //add 1 to value counts for each time the feature exists?? if(IsEmpty(value)) { continue } if(!HasKey(value_counts, value)) { value_counts[value] = 0 } value_counts[value] = value_counts[value] + 1 } } var out_fs = { geometryType: "", fields: [ {name: "Choice", type: "esriFieldTypeString"}, {name: "Count", type: "esriFieldTypeInteger"}, ], features: [] } //return a dictionary of each possible value with their respective count for(var v in value_counts) { var c = value_counts[v] var m = {attributes: {Choice: v, Count: c}} Push(out_fs.features, m) } return out_fs return Featureset(Text(out_fs))

Re: Use Arcade Data Expressions to split Ranked Choice Fields into Separate Columns

KenBuja — Tue, 25 Jul 2023 19:28:05 GMT

If I'm understanding your objective correctly, you want to get a table of each item and the number of times it's been selected for each of the six rankings. Since you only working with the attributes of the development priorities field for the second part of your code, I've rewritten most of your code from the first example to return just an array of arrays. Each array is a separate response, split into the six choices. You don't need a dictionary here containing the geometry information. It makes it easier to get the final table, which is a dictionary of dictionaries.

Each item in the dictionary of choices (transportation, jobs, etc) has a dictionary of how many times it was chosen at each rank (First choice, second choice, etc).

Is this what you're looking for?

Re: Use Arcade Data Expressions to split Ranked Choice Fields into Separate Columns

KenBuja — Tue, 25 Jul 2023 20:14:09 GMT

And here's a slightly more efficient way of doing that. There's no need to create the choicesArray array once then loop through it again.

Re: Use Arcade Data Expressions to split Ranked Choice Fields into Separate Columns

MatthewBrandt — Tue, 25 Jul 2023 20:52:04 GMT

Yes! Thank you. This is exactly what I was looking for. In case you were curious, I also was looking for a weighted "average" to compare the ranks which I was able to successfully do by appending this last bit of code. This returns the same results as the Analyze tab in Survey123 which is what I was trying to replicate.

// Reference layer using the FeatureSetByPortalItem() function. var fs = FeatureSetByPortalItem(Portal('https://www.arcgis.com'), '2284118eba59466e9169133c458ef9d4' , 0, ['development_priorities'], false); var choicesArray = [] //return fs // Split comma separated hazard types and store in array. for (var f in fs) { var split_array = Split(f["development_priorities"], ','); var choiceArray = [] for (var index in split_array) push(choiceArray, split_array[index]); push(choicesArray, choiceArray); //add this to final array } //return choicesArray //Dictionary of rank counts for each choice // NEW Add Average to each array var value_counts = { "transportation": {'First Choice': 0,'Second Choice' : 0,'Third Choice' : 0,'Fourth Choice' : 0,'Fifth Choice' : 0,'Sixth Choice' : 0,'Average' : 0}, "jobs": {'First Choice': 0,'Second Choice' : 0,'Third Choice' : 0,'Fourth Choice' : 0,'Fifth Choice' : 0,'Sixth Choice' : 0,'Average': 0}, "housing": {'First Choice': 0,'Second Choice' : 0,'Third Choice' : 0,'Fourth Choice' : 0,'Fifth Choice' : 0,'Sixth Choice' : 0,'Average':0}, "sustainability": {'First Choice': 0,'Second Choice' : 0,'Third Choice' : 0,'Fourth Choice' : 0,'Fifth Choice' : 0,'Sixth Choice' : 0,'Average':0}, "historical": {'First Choice': 0,'Second Choice' : 0,'Third Choice' : 0,'Fourth Choice' : 0,'Fifth Choice' : 0,'Sixth Choice' : 0,'Average':0}, "parks": {'First Choice': 0,'Second Choice' : 0,'Third Choice' : 0,'Fourth Choice' : 0,'Fifth Choice' : 0,'Sixth Choice' : 0,'Average':0} }; for (var a in choicesArray) { var choices = choicesArray[a] for(var i in choices) { var value = choices[i] //add 1 to value counts for each time the feature exists?? if(IsEmpty(value)) { continue } //if(!HasKey(value_counts, value)) { value_counts[value][i] = 0 } ignoring non-present values for now if (i == 0) value_counts[value]['First Choice'] = value_counts[value]['First Choice'] + 1 if (i == 1) value_counts[value]['Second Choice'] = value_counts[value]['Second Choice'] + 1 if (i == 2) value_counts[value]['Third Choice'] = value_counts[value]['Third Choice'] + 1 if (i == 3) value_counts[value]['Fourth Choice'] = value_counts[value]['Fourth Choice'] + 1 if (i == 4) value_counts[value]['Fifth Choice'] = value_counts[value]['Fifth Choice'] + 1 if (i == 5) value_counts[value]['Sixth Choice'] = value_counts[value]['Sixth Choice'] + 1 } } // NEW Get average value and append for (var a in value_counts) { value_counts[a]['Average']=(value_counts[a]['First Choice']*6+value_counts[a]['Second Choice']*5+value_counts[a]['Third Choice']*4+value_counts[a]['Fourth Choice']*3+value_counts[a]['Fifth Choice']*2+value_counts[a]['Sixth Choice'])/Count(fs) } return value_counts

Re: Use Arcade Data Expressions to split Ranked Choice Fields into Separate Columns

MatthewBrandt — Wed, 02 Aug 2023 00:21:05 GMT

@KenBuja So this works to create a dictionary representing the data I need, but I am not able to successfully convert into a featureSet for use within dashboards. I believe I need to add the relevant fields that would be in the JSON, and add the dictionary essentially as features -- but I'm having trouble doing this. I'll attach my code, but it is not successfully replicating the dictionary nor is it able to be converted to a featureset using the FeatureSet() function.

//SECOND ATTEMPT: this time trying to add directly to a fs compatible dicitonary var fs1 = FeatureSetByPortalItem(Portal('https://www.arcgis.com'), '2284118eba59466e9169133c458ef9d4' , 0, ['development_priorities'], false); var choicesArray1 = [] // return fs // Split comma separated hazard types and store in array. for (var f in fs1) { var split_array1 = Split(f["development_priorities"], ','); var choiceArray1 = [] for (var index in split_array1) push(choiceArray1, split_array1[index]); push(choicesArray1, choiceArray1); //add this to final array } //return(choicesArray1) var valuecounts1={ 'fields':[ {'name':'First Choice','type':'esriFieldTypeInteger'}, {'name':'Second Choice','type':'esriFieldTypeInteger'}, {'name':'Third Choice','type':'esriFieldTypeInteger'}, {'name':'Fourth Choice','type':'esriFieldTypeInteger'}, {'name':'Fifth Choice','type':'esriFieldTypeInteger'}, {'name':'Sixth Choice','type':'esriFieldTypeInteger'}, {'name':'Quality','type':'esriFieldTypeString'}, {'name':'Average','type':'esriFieldTypeFloat'} ], 'geometryType':'', 'features':[] }; //return(valuecounts1) valuecounts1.features[0]= {'Quality':"transportation",'First Choice': 0,'Second Choice' : 0,'Third Choice' : 0,'Fourth Choice' : 0,'Fifth Choice' : 0,'Sixth Choice' : 0,'Average' : 0} valuecounts1.features[1]= {'Quality':"jobs",'First Choice': 0,'Second Choice' : 0,'Third Choice' : 0,'Fourth Choice' : 0,'Fifth Choice' : 0,'Sixth Choice' : 0,'Average': 0} valuecounts1.features[2]= {'Quality':"housing",'First Choice': 0,'Second Choice' : 0,'Third Choice' : 0,'Fourth Choice' : 0,'Fifth Choice' : 0,'Sixth Choice' : 0,'Average':0} valuecounts1.features[3]= {'Quality':"sustainability",'First Choice': 0,'Second Choice' : 0,'Third Choice' : 0,'Fourth Choice' : 0,'Fifth Choice' : 0,'Sixth Choice' : 0,'Average':0} valuecounts1.features[4]= {'Quality':"historical",'First Choice': 0,'Second Choice' : 0,'Third Choice' : 0,'Fourth Choice' : 0,'Fifth Choice' : 0,'Sixth Choice' : 0,'Average':0} valuecounts1.features[5]= {'Quality':"parks",'First Choice': 0,'Second Choice' : 0,'Third Choice' : 0,'Fourth Choice' : 0,'Fifth Choice' : 0,'Sixth Choice' : 0,'Average':0} //return(valuecounts1) for (var a in choicesArray1) { //for each feature within the dataset var choices = choicesArray1[a] //set choices as a variable representing the current feature which has 6 fields, a field for each choice for(var i in choices) { //for each field within the current feature, rank 1, rank 2, etc.. var value = choices[i] //set value as the field within the feature //add 1 to value counts for each time the feature exists?? var vcf = valuecounts1.features[i] var vcfq = vcf['Quality'] //return(vcf['Quality']) if(IsEmpty(value)) { continue } if(vcfq == value) { //if(!HasKey(value_counts, value)) { value_counts[value][i] = 0 } ignoring non-present values for now if (i == 0) vcf['First Choice'] = vcf['First Choice'] + 1 if (i == 1) vcf['Second Choice'] = vcf['Second Choice'] + 1 if (i == 2) vcf['Third Choice'] = vcf['Third Choice'] + 1 if (i == 3) vcf['Fourth Choice'] = vcf['Fourth Choice'] + 1 if (i == 4) vcf['Fifth Choice'] = vcf['Fifth Choice'] + 1 if (i == 5) vcf['Sixth Choice'] = vcf['Sixth Choice'] + 1 } } } return(valuecounts1) //Console(valuecounts1) // var fs2 = FeatureSet(Text(valuecounts1)) // return fs2 // NEW Get average value and append for (var a in valuecounts1) { vcf=valuecounts1.features[a] vcf['Average']=(valuecounts1.features[a]['First Choice']*6+valuecounts1.features[a]['Second Choice']*5+valuecounts1.features[a]['Third Choice']*4+valuecounts1.features[a]['Fourth Choice']*3+valuecounts1.features[a]['Fifth Choice']*2+valuecounts1.features[a]['Sixth Choice'])/Count(fs1) } return(valuecounts1) Console(valuecounts1) var fs2 = FeatureSet(Text(valuecounts1)) return fs2

Re: Use Arcade Data Expressions to split Ranked Choice Fields into Separate Columns

KenBuja — Fri, 04 Aug 2023 16:33:35 GMT

This code works