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

Table update with Multiple Comparisons in JMP

ranjan_mitre_or

I have a large table with a column of uniform[0,1] random numbers. I have another column that classifies each row as belonging to a SLOW or BUSY time period. I have a second table with 21 rows and three columns, whose one column corresponds to SLOW and the second column to BUSY. These columns contain bin edges of 0-1 broken up into consecutive ascending bins. The third column contains a value.

For the INPUT value in Column 1 of each row in the first table and the corresponding entry (SLOW or BUSY) in Column3, I would first like to choose the (SLOW or BUSY) column in the second table. Then I need to find the entry in the column (chosen SLOW or BUSY) in the second table where the entry is next-larger than the INPUT value. Then I need to form a new column in Table 1 to include the value of the bin number from Column 3 in the second table.

In the attachments, Table1 is the first "large" table with BUSY and SLOW indicating the Busy and Slow periods. Traffic_CDF contains the two columns of bin edges and the third contains the value that want to add to a new column in Table1.

Here is an example.

The 6th entry in Table 1 is 0.405374 and is BUSY. This value is between the entries in rows 2 and 3 of The BUSY column of Table 2. So, I would like to enter the value '2' of Column 3 of Table 2, to the 6th row on the new column in Table 1.

Would greatly appreciate if anyone can help with this.

Thanks

Table 1.jmp

Traffic_CDF.jmp

Accepted answers

cwillden

Try this:

dt_1 = Data Table("Table 1");
dt_cdf = Data Table("Traffic_CDF");

dt_1 << New Column("Bin");

for(i = 1, i<= N Row(dt_1), i++,
	value = Column(dt_1,"Random No.")[i];
	traffic_val = dt_1:Traffic[i];
	If(traffic_val == "BUSY",
		dt_1:Bin[i]=dt_cdf:Column 3[min(dt_cdf << Get Rows Where(:BUSY >= value))],
		dt_1:Bin[i]=dt_cdf:Column 3[min(dt_cdf << Get Rows Where(:SLOW >= value))],
	)	
);

It basically works by finding the first row in the CDF table where the bin edge is greater than or equal to the value in the first table and returns the value in "Column 3" for that row.

All comments

cwillden

Hi @ranjan_mitre_or,

I'm confused about how you are wanting to match the random # from Table 1 to a bin in Traffic_PDF. The biggest issue is I can't see how Traffic_PDF represents bins. I would expect the numbers to have some ordering to them so that any number less than or equal to the smallest number would be in bin 1, any number between the smallest and second smallest number in bin 2, etc. Traffic_PDF even has a couple of numbers in Column 2 that are repeated.

Can you provide any clarification?

For example, Row 1 in Table 1 has the value 0.966434732312337 and Traffic = H. That tells me I should look in Column 2 of Traffic_PDF, but I don't see how to map the value from Table 1 to a "bin" in Traffic_PDF.

ranjan_mitre_or

I mistakenly uploaded the PDF table instead of the correct table.I have replaced the second table. It is now a CDF table that corresponds to the bin edges. I have also tried to make the problem description clearer with an example of what I want to do. Would highly appreciate your help. I do not have experience with queries or JSL, if that it what it would require. Hopefully it is doable with JMP somehow.

Thanks

ranjan_mitre_or

Your observation about my data files were correct. I have edited the post to explain it better and also uploaded the correct CDF table that delineates the bin-edges correctly, instead of the PDF that I had uploaded erroneously.

cwillden

Try this:

dt_1 = Data Table("Table 1");
dt_cdf = Data Table("Traffic_CDF");

dt_1 << New Column("Bin");

for(i = 1, i<= N Row(dt_1), i++,
	value = Column(dt_1,"Random No.")[i];
	traffic_val = dt_1:Traffic[i];
	If(traffic_val == "BUSY",
		dt_1:Bin[i]=dt_cdf:Column 3[min(dt_cdf << Get Rows Where(:BUSY >= value))],
		dt_1:Bin[i]=dt_cdf:Column 3[min(dt_cdf << Get Rows Where(:SLOW >= value))],
	)	
);

It basically works by finding the first row in the CDF table where the bin edge is greater than or equal to the value in the first table and returns the value in "Column 3" for that row.