JMP-Tools Add-in overview - JMP addins written by jthi

[#1]

I have now published some of the Add-ins I have written for JMP. The codes and addins (some are encrypted) can be found from this GitHub page jthi-jmp-tools (github.com) and overview can be found from jthi-jmp-tools / JMP-Tools (github.com) repository. If you have suggestions on these add-ins, you can:

• Comment to this post
• Open an issue in correct GitHub repository related to that add-in or to generic one
• Comment on the JMP Community Add-in page if I have released Add-in here
• Send private message in JMP community

The purpose of developing these is to provide new tools, enhance the tools JMP already has and mostly improve my scripting skills while writing somewhat interesting code. All the addins can be found from jthi-jmp-tools/JMP-Tools/releases (github.com) and you should be able to download and install them from there. Some can also be found from JMP Community. The add-ins do include some stand-alone functions you can use in your own scripts by including correct scripts / copying them over to your code, so if you are interested in that, I suggest you take a look at the source codes.

The Add-Ins are structured with some basic idea I had long ago about how to structure them, so they have mostly same directory structure and some common files. For example some settings which can be saved have been saved to settings.json file. The add-ins are put together by using Add-In Manager as it makes managing multiple add-ins much much easier than the native tools JMP provides.

Most of the Add-ins will be added to Add-Ins menu under JMP-Tools:

There is also an addin for toolbar. This can be found from "main-repositorys" releases page. Mostly useful if you have installed the (all/most) addins described here.

Below is a quick overview of the addins I have created, do note that there are couple which are heavily work in progress. The scripts which can be found from community should have this "book" link in the title.

1. Analyse Columns Analyse Columns  (analyse-columns (github.com))

Idea is to provide user quick summary statistics and quick actions they can perform on columns. This can be used as the first tool you ran when you get a new data set to get an overview of the data you are working with (inspired partially by pandas-profiling (github.com)).

 

2. Character Columns to Numeric (character-columns-to-numeric (github.com))

From time to time you will data that has been wrongly coded and have wrong data type. In JMP when you change data type from Character to Numeric you might fairly easily end up with loosing some data due to how conversion works. Purpose of this tool is to convert selected character columns (which can be safely converted) to numeric columns.The tool gets the values in columns and then checks if they can be converted to numeric. And if all of them (or enough of them)  can be converted it will change the data type to numeric. It allows you to select the columns you want to convert, allows using threshold for conversion and sampling rate.

 

3. Create Order Column (create-order-column (github.com))

This tool will create Ordering column to data table with current row numbers. This is useful when you are starting analysis and want to sort the data, but still have possibility to roll-back to original order. The column will be created in the start of data table and if multiple ordering columns are created, indexing is added.  

I have also created wish list item hoping that something a bit similar could be found directly from JMP (for columns this already exists but not for rows)Add Original order property/columm to JMP datatables on open/creation

 

4. Delete Missing Value Columns (delete-missing-value-columns (github.com) 

Tool allows user to easily delete multiple columns which have more than specified amount (threshold) of missing values.

 

5. Fill Missing Cells Fill Missing Cells (fill-missing-cells (github.com))

This tool allows user to fill missing values based on previous/next/nearest found value.There are already tools which can do this in a way or another (even JMP has fill rows), but the ones I have used/seen are missing at least the grouping option this Add-in you to do, also this shouldn't require you to have data table sorted if you use Order column.

 

6. Get Unique Values Get Unique Values (get-unique-values (github.com))

Purpose of the tool is to provide user-interface with unique values listed from selected column (nominal, ordinal) in different formats from selected datatable. From this list user can either directly copy-paste values to clipboard or use specific buttons for copying.

Tool also provides an option to paste values directly to the box and get the values in different formats. This is handy when you copy paste values from excel for example. You can also format values from clipboard directly to SQL list.

 

7. Group Listagg Columns Group Listagg Columns (group-listagg-columns (github.com))

Purpose of this addin is to provide a way for user to create new table with a column(s) of grouped values in similar manner as SQL's listagg. In best case this option would be in JMP's Summary and Split platforms as from time to time I would like to keep some values even if I split or aggregate with Summary.

I have created wish list items for both of those Allow Split to create Multiple Response columns andAdd Listagg/Concatenate/GROUP_CONCAT to Summary platform table.

 

8. Join Nearest Rows Join Nearest Rows - Join tables by using continuous columns and nearest values (join-nearest-rows (github.com))

Join two datatables with continuous column. Can do join backwards, forwards, nearest/closest. Supports using tolerance / max distance for join and usage of grouping column. Data doesn't have to be sorted but it does speed up the tool execution. Inspiration partially from Python Pandas - Merge Asof

I have created Wish List item which is partially linked to this Add fuzzy join / asof merge / join by columns which do not have exact matches 

 

9. Order Selected Columns Order Selected Columns (order-selected-columns (github.com))

Most of the column ordering options found from JMP's Cols/Reorder Columns -menu do not offer possibility to order only selected columns. This add-in provides those options to JMP users.

There is a wish list item related to this (and it is the reason why I wrote this Add-in) Column reorder > Sort by Name / Data Type / Role > SELECTED COLUMNS ONLY 

10. Replace X with Y (replace-x-with-y (github.com))

Replaces full matches with other value in selected columns.

 

11. Split and Set Spec Limits Split and Set Spec Limits (split-and-set-spec-limits (github.com))

Purpose of this tool is to let JMP users split long (stacked) data and set specification limits directly from the original data if data is in correct format.

 

12. Script Launcher (script-launcher (github.com))

Tool to provide quick-access to user defined scripts (pre-configured for jthi-jmp-tools). Provides also access to launching scripts, modifying their settings.json file and opening the directory. Also allows changing current datatable.

Strongy suggested to add as shortcut with easy access, for example mine is Ctrl+Shift+Z. If the script is already open, launching it again will bring it to front.

 

13. File Indexer (file-indexer (github.com) )

This is still very much work in progress but the idea is to provide file listing of the user specified directories and allow opening files/directories.

That's it for now, I'll try to finish the still badly unfinished add-ins at somepoint and fix bugs I encounter. Most up-to-date versions of the add-ins can be found GitHub.

[#2]

No. 5 !!!!!!

[#3]

Feel free to download it from Fill Missing Cells and install the add-in and try it out.

Some similar things can be done directly in JMP with Fill:

and withData Table Tools Add-in . The Add-in I have written does have some additional benefits such as grouping columns and not requiring you to have data table sorted IF you have some column for sorting (if no sorting column is provided, it will use Row order).

[#4]

Thanks to JTHI for providing so many efficient tools.
Is it possible to make a plug-in that can quickly change Excel's VBA to JSL?

Thanks!

[#5]

VBA can do so much and it can be written in so many different ways that I don't see it being feasible to convert VBA directly to JSL with "simple" addin. Conversion can maybe done for some simple cases but otherwise it has to be done case by case. You could also use JMP to run excel macros using Run Program().

[#6]

Yes, it would be too much work to automatically convert all VBA to JSL.Is not realistic.
I want to see if VBA about downloading data from web pages can automatically convert the core syntax to JSL and keep the variables used by VBA.

Thank jthi!

[#7]

Quickly convert browser copy to JSL code with EmEditor's replacement feature
CURL (CMD) is copied from the browser F12 and quickly converted to JSL code with the EmEditor replacement function.
Of course, this process should also be implemented with JSL.

cURL(cmd)

curl "http://excalc" -H "Connection: keep-alive" -H "Origin: http://excalc.icfqs.com:7616" -H "User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36" -H "Content-Type: text/plain" -H "Accept: */*" -H "Referer: http://excalc.icfqs.com:7616/site/tdx-zbfx/page-ddlj.html?setcode=1^&code=xxxxxx^&name=ssssss^&color=0" -H "Accept-Encoding: gzip, deflate" -H "Accept-Language: zh-CN,zh;q=0.9,en;q=0.8,zh-TW;q=0.7" -H "Cookie: ASPSessionID=" --data-binary "^[^{^\^"funcId^\^":2,^\^"offset^\^":0,^\^"count^\^":200,^\^"cond^\^":^\^"time,11700,33600^|amount,14^\^",^\^"modname^\^":^\^"TickAly^\^"^}^]" --compressed --insecure

EmEditor

document.selection.Replace("\\r","\\n",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("\\n\\n","\\n",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace(" -H ","\\n",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("curl \"(.{0,})$","u=\"\\1;h=[=>];//h[\"Content-Type\"]=\"application/json\";",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);


document.selection.Replace(" --compressed","\\n--compressed",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace(" --data(.[^\"]{0,})(.{0,})$","\\njj=\\2;",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
//document.selection.Replace("\"cookie: (.{0,})$","h[\"cookie\"]=\"\\1;",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("\"cookie: (.{0,})$","h[\"cookie\"]=\"\\1;\\nzzzrs=New HTTP Request(URL(u),Method(\"GET\"),Headers(h));//rs=New HTTP Request(URL(u),Method(\"POST\"),JSON(jj),Headers(h));\\nr=rs<<Send;txt=(Blob To Char(r));t1=\"[{\";off1=Contains(txt,t1);If(off1,tx=SubStr(txt,off1,Length(txt)));t2=\"}]\";off2=Contains(tx,t2,-1);If(off2,tx=SubStr(tx,1,off2+1));\\ndt=jsontodatatable(tx);",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("\"Accept: (.{0,})$","//h[\"ac\"]=\"\\1;",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("\"User-Agent: (.{0,})$","//h[\"User-Agent\"]=\"\\1;",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);

document.selection.Replace("\"Connection:(.{0,})$","",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("\"Origin:(.{0,})$","",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("\"Connection:(.{0,})$","",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("\"Origin:(.{0,})$","",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("\"X-Requested-With:(.{0,})$","",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("\"Content-Type:(.{0,})$","",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("\"Sec-Fetch-Site:(.{0,})$","",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("\"Sec-Fetch-Mode:(.{0,})$","",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("\"Referer:(.{0,})$","",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("\"Accept-Encoding:(.{0,})$","",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("\"Accept-Language:(.{0,})$","",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("--compressed(.{0,})$","",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);

document.selection.Replace("\\n\\n","\\n",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("\\n\\n","\\n",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("\\n\\n","\\n",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("\\n\\n","\\n",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);
document.selection.Replace("zzz","\\n",eeReplaceAll | eeFindReplaceRegExp,eeExFindSeparateCRLF);


document.selection.Replace("^[","[",eeReplaceAll,0);
document.selection.Replace("^{","{",eeReplaceAll,0);

document.selection.Replace("^]","]",eeReplaceAll,0);
document.selection.Replace("^}","}",eeReplaceAll,0);
document.selection.Replace("^\\^\"","\\!\"",eeReplaceAll,0);
document.selection.Replace("^|","|",eeReplaceAll,0);
document.selection.SelectAll();
document.selection.Copy(eeCopyUnicode);

JSL(Not finishing)

u="http://excalc";h=[=>];//h["Content-Type"]="application/json";
//h["User-Agent"]="Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36";
//h["ac"]="*/*";
h["cookie"]="ASPSessionID=";

rs=New HTTP Request(URL(u),Method("GET"),Headers(h));//rs=New HTTP Request(URL(u),Method("POST"),JSON(jj),Headers(h));
r=rs<<Send;txt=(Blob To Char(r));t1="[{";off1=Contains(txt,t1);If(off1,tx=SubStr(txt,off1,Length(txt)));t2="}]";off2=Contains(tx,t2,-1);If(off2,tx=SubStr(tx,1,off2+1));
dt=jsontodatatable(tx);
jj="[{\!"funcId\!":2,\!"offset\!":0,\!"count\!":200,\!"cond\!":\!"time,11700,33600|amount,14\!",\!"modname\!":\!"TickAly\!"}]";