Finding the median of a column in a global table

Question

question

Noah Z asked Aug 25, '16 Adrian Haws edited Sep 1, '16

Finding the median of a column in a global table

A couple of months ago I asked a question and got great answers regarding finding certain basic descriptive stats for global table columns (https://answers.flexsim.com/questions/22530/way-to-pull-global-table-column-statistics-for-exp.html). Along a similar line of questioning, is there a way to pull out the median value of a numeric column in a global table? I've been copying over the data to Excel and then calculating it there but if there was a way to pull that out in FlexSim it would save a few post-processing steps.

Software Version:

Choose One

global table getdatastat

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Answer 1 · 2016-08-25T16:27:28Z

Sam Stubbs answered Aug 25, '16 Phil BoBo edited Aug 26, '16

You can do this with FlexScript, but you'd have to sort your data first. If this is a problem with your data, what you'd want to do, is to create a temporary table, copy the data into it, sort it, and then find the median.

The code to sort and then get the median would look something like this:

treenode table = reftable("GlobalTable1");
int column = 1;
double median = 0;


sorttable(table, column);
int numrows = gettablerows(table);
if (numrowsget-median.fsm % 2 == 0) // even number of rows
	median = (gettablenum(table, numrows/2, column) + gettablenum(table, numrows/2+1, column)) / 2;
else
	median = gettablenum(table, numrows/2+1, column);


return median;

Where it will grab the middle value for an odd number of rows in a table, or the average of the two middle values of an even numbered rows in a table.

I'm attaching a simple example that has two scripts. One script that creates random values for two tables, and the above script that will sort whichever of the two tables you tell it to, and return the median.

get-median.fsm (13.0 KiB)

· 2

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Noah Z commented · Aug 25, 2016 at 06:36 PM

Thank you! This is exactly what I was looking for. You mentioned that if you want to keep the original table ordered correctly you might need to make a temporary table and copy your data into it before sorting and pulling out the median. What is most painless way to do this copying?

0 ·

Phil BoBo ♦♦ Noah Z commented · Aug 26, 2016 at 04:39 PM

Add these lines to the code:

treenode table = reftable("GlobalTable2");
int column = 1;

table = createcopy(table, up(table));

double median = 0;
sorttable(table, column);
int numrows = gettablerows(table);
if (numrows % 2 == 0) // even number of rows
	median = (gettablenum(table, numrows/2, column) + gettablenum(table, numrows/2+1, column)) / 2;
else
	median = gettablenum(table, numrows/2+1, column);

destroyobject(table);

return median;

2 ·

added-lines.png (21.9 KiB)

question