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; } } }

How to sort on two fields to calculate percentile in Arcade?

RobynSnookCCB

I have a traffic counter dataset (with over 30,000 records) in a related table which has the fields speed and date. I need to calculate the 85th percentile speed for every day of the studies. I've been struggling to this and my code as become a mishmash.

In order to do this I need to sort the data in ascending order by Date AND by Speed and then give the rows unique numbers as sorted. Every day needs to have the unique numbers reset so I can calculate the percentile afterwards. All intensities that I have tried do not number the unique field correctly and the order by function isn't working with 'and' for me.

Somethings I've tried is following these cases: https://community.esri.com/t5/arcgis-pro-questions/arcade-expression-to-calculate-field-based-on-a/td-p/1292737

Open to doing this in python as well

Najděte více příspěvků označených jako

Arcade

Accepted answers

kenbuja

This is an example of what I was thinking of. I'm using the recent earthquakes data to summarize how many earthquakes happened each day and the largest quake of the day. I get the first and last days of the dataset and set the beginning date without the time. Using a While loop, I cycle through the days, adding one day to the existing day, filtering the data by that time span, ordering that filtered data, and reporting the number of quakes and the largest quake for the day. Then I add a day and go through the loop again. The loop breaks when I've gone past the last day of the data.

The one thing I haven't quite worked out is the time zone issue, where it's starting the day at 8 pm local time.

// Fetches features from a public portal item
var fs = FeatureSetByPortalItem(Portal("https://www.arcgis.com"),"9e2f2b544c954fda9cd13b7f3e6eebce", 0, ["*"], false);
var sortedFS = OrderBy(fs, 'eventTime DESC');
var endDate = First(sortedFS)['eventTime'];
var reverseSortedFS = OrderBy(fs, 'eventTime ASC');
var beginDate = First(reverseSortedFS)['eventTime'];
var date1 = Date(Year(beginDate), Month(beginDate), Day(beginDate));
var output;
while (DateDiff(endDate, date1, 'day') > 0) {
  var date2 = Text(DateAdd(date1, 1, 'day'), "MM/DD/YYYY")
  var filteredFS = Filter(fs, `eventTime BETWEEN '${Text(date1, "MM/DD/YYYY")}' and '${date2}'`);
  var sortedFilteredFS = OrderBy(filteredFS, 'mag DESC');
  if (Count(filteredFS) > 0) output += (`${Text(date1, "MM/DD/YYYY")}: ${Count(filteredFS)} quakes. The biggest quake was ${Round(First(sortedFilteredFS)['mag'],1)}
  `);
  date1 = DateAdd(date1, 1, 'day')
}
return output;

All comments

kenbuja

Instead of attempting to sort the records by date and speed, can you loop through each date of the study, filtering the records to get the records for that day, then sorting those filtered records by speed to assign the unique numbers?

RobynSnookCCB

That is an option as well but I'm not sure how to proceed as the date field has date and time in it. I need the dates separated and ordered by speed in order to apply unique numbers and then repeat until the we are out of dates. Unique numbers needs to reset for every day.

This is going to be an every growing database and I need it to function dynamically. I believe my main issue is ordering the for loops correctly an applying the filters correctly to avoid continuous numbering for all dates.

RobynSnookCCB

Here is my code and the results I'm getting. The numbering (rowID) still isn't numbering correctly nor is it sorted by speed.

//related table
var TrafficTable = FeatureSetByName($map, "Traffic Cameras - TrafficCounts", ["DATE", 'GUID', 'LENGTH', 'OBJECTID', 'OUTGOING', 'SPEED'])

//Fields in feature to filter the related table by
var GID = $feature.GlobalID
var ST = DateAdd($feature.StartDateTime, +2.5, 'hours')
var ET = DateADD($feature.EndDateTime, +2.5, 'hours')

//Filter the related table on the Global IDs & start and end times
var fil = Filter(TrafficTable, 'GUID = @GID')
var FilTime = Filter(fil, 'DATE >= <a href="https://community.esri.com/t5/user/viewprofilepage/user-id/70610">@ST</a> and DATE<= <a href="https://community.esri.com/t5/user/viewprofilepage/user-id/255262">@ET</a>')

// Convert datetime to date for table and creates the main feature set to display in pop-up
var new_fs = {
    geometryType: "",
    fields: [
        {name: "DateShort", type: "esriFieldTypeDate"},
        {name: "Speed", type: "esriFieldTypeInteger"}, 
        {name: "LENGTH", type: "esriFieldTypeInteger"},
        {name: "OBJECTID", type: "esriFieldTypeObjectID"},
        {name: "OUTGOING", type: "esriFieldTypeString"},
        {name: "RowID", type: "esriFieldTypeInteger"},
        ],
    features: []
}

//used for unique numbers in RowID
var rowCount = 0
//for loop to add to the new feature set (new_fs)
for(var f in FilTime) {
    var d = f.DATE
    var date_short = Number(Date(Year(d), Month(d), Day(d)))
    var U = Filter(FilTime, "DATE < @d")
    var O = OrderBy(U, "SPEED ASC")
    for (var T in O){
        rowCount += 1
   }
    Push(new_fs.features, {attributes: {DateShort: date_short, Length: f.LENGTH, OBJECTID: f.OBJECTID, OUTGOING:f.OUTGOING, Speed: f.SPEED, RowID: rowCount}})
}

//Data as a feature set
var fs_all = FeatureSet(Text(new_fs))

return OrderBy(fs_all, "RowID ASC")

kenbuja

The one thing I haven't quite worked out is the time zone issue, where it's starting the day at 8 pm local time.

// Fetches features from a public portal item
var fs = FeatureSetByPortalItem(Portal("https://www.arcgis.com"),"9e2f2b544c954fda9cd13b7f3e6eebce", 0, ["*"], false);
var sortedFS = OrderBy(fs, 'eventTime DESC');
var endDate = First(sortedFS)['eventTime'];
var reverseSortedFS = OrderBy(fs, 'eventTime ASC');
var beginDate = First(reverseSortedFS)['eventTime'];
var date1 = Date(Year(beginDate), Month(beginDate), Day(beginDate));
var output;
while (DateDiff(endDate, date1, 'day') > 0) {
  var date2 = Text(DateAdd(date1, 1, 'day'), "MM/DD/YYYY")
  var filteredFS = Filter(fs, `eventTime BETWEEN '${Text(date1, "MM/DD/YYYY")}' and '${date2}'`);
  var sortedFilteredFS = OrderBy(filteredFS, 'mag DESC');
  if (Count(filteredFS) > 0) output += (`${Text(date1, "MM/DD/YYYY")}: ${Count(filteredFS)} quakes. The biggest quake was ${Round(First(sortedFilteredFS)['mag'],1)}
  `);
  date1 = DateAdd(date1, 1, 'day')
}
return output;

RobynSnookCCB

Thank you this was very helpful. I'm after getting more done on my code. However, when I attempt to calculate the 85th percentile I need to isolate a specific value which is why I needed to give the data unique numbers by date. I'm stumped on a filter solution and the if statement doesn't seem to be working correctly as it keeps giving back a random row from the data instead of the row related to the ID I assigned.

// Fetches related table and fields
var fs = FeatureSetByName($map, "Traffic Cameras - TrafficCounts", ["*"], false);
var Class = $feature.RdClass

//Fields in feature to filter the related table by
var ST = DateAdd($feature.StartDateTime, +2.5, 'hours')
var ET = DateADD($feature.EndDateTime, +2.5, 'hours')

//Filter the related table on start and end times
var FilTime = OrderBy(Filter(fs, 'DATE >= <a href="https://community.esri.com/t5/user/viewprofilepage/user-id/70610">@ST</a> and DATE<= <a href="https://community.esri.com/t5/user/viewprofilepage/user-id/255262">@ET</a>'), 'DATE DESC')

//var sortedFS = OrderBy(FilTime, 'DATE DESC');
var endDate = First(FilTime)['DATE']
var reverseSortedFS = OrderBy(FilTime, 'DATE ASC');
var beginDate = First(reverseSortedFS)['DATE'];
var date1 = Date(Year(beginDate), Month(beginDate), Day(beginDate));

// variables to return in the popup

var rowCount = 0
var output;

//while loop to run through and filter out the data by date
while (DateDiff(endDate, date1, 'day') > 0) {
  var date2 = Text(DateAdd(date1, 1, 'day'), "MM/DD/YYYY")
  var sortedFilteredFS = OrderBy(Filter(FilTime, `DATE BETWEEN '${Text(date1, "MM/DD/YYYY")}' and '${date2}'`), 'SPEED ASC');

  //loops through the data that is separated (filtered) by  date and sorted by speed
    for (var f in sortedFilteredFS){
      var S = f.SPEED
      var G = f.GlobalID
      var L = f.LENGTH
      //applies unique numbering by date and sorted by speed
      if(S > 0){
        rowCount += 1
        }
      //Total count of vehciles by day
      var TCount = Count(sortedfilteredFS)
      //the 85th ID number per day
      var F85_ID = Round((TCount*0.85)+0.5,0)   
      if (rowCount == F85_ID){
        var v85 = f.SPEED
      }
    }
  
   //text info
  if (TCount > 0) 
  output += Text(date1, 'ddd. MMMM DD, Y') + TextFormatting.NewLine +
  'Total Vehicals: ' + TCount + TextFormatting.NewLine +
  'F85: ' + F85_ID + TextFormatting.NewLine + 
  'V85: ' + v85 + ' km/h' + TextFormatting.NewLine +
  'GID: ' + G + TextFormatting.NewLine +
  'length ' + L + TextFormatting.NewLine +
  'Exceed 1000 VPD (Local): ' + IIf(Class == 0 && TCount >= 1000, 'Yes', IIF(Class == 1, 'N/A','No')) + TextFormatting.NewLine +
  'Exceed 8000 VPD (Collector): ' + IIf(TCount >= 8000 && Class == 1, 'Yes', IIF(Class == 0, 'N/A', 'No')) + TextFormatting.NewLine + TextFormatting.NewLine
   
  date1 = DateAdd(date1, 1, 'day')
  
}

return output

kenbuja

You're calculating F85_ID for the filtered records for each day, but you never reset rowCount to zero for each day.

Připojte se ke konverzaci

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Přihlásit se
Registrovat

Rychlé odkazy

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