Formula to find Mode of Daily values

evasonl

Hi,

I have a datatable which has multiple samples tracking different parameters for a collection of different systems within those samples (1 sample will have 1 system, but another sample may also have the same system). Each sample has a different state as it approaches it's end. These states are what I'm trying to predict. I have created a decision tree algorithm using some different samples in a seperate datatable which I then have added to this datatable. What this decision tree is trying to do is predict the state of the system at different points in time. I will then measure these predicted states against the known states of the system at these points in time (the algorithm was created with different samples so this is "fresh" data)
Now as you can imagine during the course of a day this formula can be sending hundreds or thousands of different predictions and not all of them are correct so I want a column formula that can look at all of these predictions, by sample number and by day, and output what the most commonly predicted state of the equipment (Mode)

 

In other words I'd like a column that calculates the mode Alert rating for each day of each sample of the data and output it's predicted Alert rating to each of these rows

 

In the example datatable I've attached I would like a formula that can automate the column titled "Alert Prediction for the day" and I don't mind it using the LogTime column or the time since start up column to create it's daily predictions. 

Thanks for your help

Example Datatable for Summarising Alert Ratings.jmp

ian_jmp

Bearing in mind that's it's rather specific to your case, this should do it. You can use 'Help > Scripting Index' and the editor tooltips to help understand how it works. 

NamesDefaultToHere(1);

// Starting table
dt = DataTable("Example Datatable for Summarising Alert Ratings.jmp");

// Add required columns
dt << NewColumn("Date", Character, Formula(Short Date( :Logtime )));
dt << NewColumn("Group", Character, Formula((:System || " ") || :Date));

// Find the mode of :Decision Tree Prediction for each level in :Group, and fill in 'modeVals'
vals = Column(dt, "Decision Tree Prediction") << getValues;
gVals = Column(dt, "Group") << getValues;
levs = AssociativeArray(gVals) << getKeys;
modes = J(NItems(levs), 1, .);
modeVals = J(NRow(dt), 1, .);
for(i=1, i<=NItems(levs), i++,
	rows = Loc(AsList(gVals), levels[i]);
	modes[i] = Mode(vals[rows]);
	modeVals[rows] = modes[i];
	);

// Build the final column in dt using 'modeVals'
vl = Column(dt, "Decision Tree Prediction") << getProperty("Value Labels");
nc = dt << NewColumn("Modes for Decision Tree Prediction", Numeric, Nominal, Values(modeVals));
nc << valueLabels(Arg(vl, 1));