turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Table update with Multiple Comparisons in JMP

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jan 19, 2018 10:54 AM
(670 views)

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

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jan 22, 2018 1:47 PM
(782 views)
| Posted in reply to message from ranjan_mitre_or 01/22/2018 04:13 PM

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.

-- Cameron Willden

4 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jan 19, 2018 11:38 AM
(661 views)
| Posted in reply to message from ranjan_mitre_or 01/19/2018 01:54 PM

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.

-- Cameron Willden

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jan 22, 2018 1:47 PM
(783 views)
| Posted in reply to message from ranjan_mitre_or 01/22/2018 04:13 PM

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.

-- Cameron Willden