Data Table Tools Add-in

[#1]

This addin contains several utilities for working more efficiently with data tables. With it, you can:

• Quickly set the number of decimal places displayed in the data table.
• Set and change column widths in bulk.
• Perform customized reordering and sorting of columns in bulk.
• Convert value labels to the equivalent strings.
• Push and pull column names to and from tables in bulk.
• Lock and unlock tables and columns in bulk.
• Create random categorical and correlated normal data.
• Get data from any JMP report table.
• Get (x,y) points from any graph within a JMP report.
• Click on an image to record (x,y) points.
• Span or interpolate over empy cells.
• Create custom date formulas using an intuitive interface with drop-down menus.
• Number rows in their order of occurrence within specified groups.
• Create a column whose values are 1 when the row is selected and 0 otherwise.
• Concatenate multiple files (text, JMP or excel) from a folder (optionally, its subfolders as well) into a single .jmp table.
• Concatenate a table with itself any number of times.
• Sample table rows with or without replacement.
• Convert between multiple-response and stacked formats.
• Expand a table using frequency counts (unsummarize rows).

Many of these features will be described in more detail in JMP blogs. Some will also be available as stand-alone add-ins. As it becomes available, supporting documentation will also be uploaded to this page.

Enjoy!

Brady

[Steven_Moore]

Cool beans!  Thank-you!

[tejvishnu]

Extremely useful and what a time-saver this has been. Have used this add-in already today! Thank you..

[tajrida]

A PDF help file with examples will be good to understand and use this add-in

[alxh]

Utterly fantastic, thank you!

[brady_brady]

@tajrida,

I agree. The problem is that .pdf and .docx files cannot currently be uploaded--I am not sure why, but this happened after the infrastucture switch. When this is remedied, I will get the documents posted, as I blog about them. Right now the only document that is written is the one for the custom date formula writer.

Thanks,

Brady

[quest74]

can I download this add in for JMp11

[Olaf]

"Push column names" does not allow to select the "column of column names" resulting in Error "Ensure selections are made in all 3 panes". It is empty. What am I missing?

[brady_brady]

Did you press the “Refresh table list” button in the upper left of the window? Once this has been done, you should be able to:

1. Select the new table in the upper left list box (it will not appear until you refresh the name list; for reasons I won’t get into here, the name list does not refresh itself automatically)
2. Select the column of column names in the lower left list box
3. Select the table into which you will push these names
4. Check or uncheck options, as appropriate
5. Press the “Push Names” button

Cheers,
Brady

[Olaf]

Thanks for responding Brady,

I did refresh the list and even started the add-in and JMP over again.

But that did not help either. Tested on multiple files.

Using 13.2.0 on Mac and latest add-in.

Regards, Olaf

P.S.

I recorded a little video showing what I did and how the problem presents to me, but uploading here requires some version of flash that is not installed on our systems (working on windows right now).

[Olaf]

Same on win JMP 13.1.0

[brady_brady]

Olaf, will you please shoot me an email directly? I will need to troubleshoot the app, I'm afraid. Perhaps some surgery will be required.

Cheers,

Brady

[JerryFish]

Hi Brady-- I've got an instance where we need to convert a date format from "ww/yyyy" (i.e. categorical column containing week number followed by 4 digit year, separated with forward slash delimiter) into something that JMP understands as a date.  The "Data Table Tools/Special Column Formulas/Custom Date Writer Formula" is fantastic, but doesn't handle this particular case.  Would this be an easy change to incorporate?  (Or, is there another way to handle it?)

Thanks!

Jerry

[aharding]

Brady, this is a great toolset. I am still just scratching the surface. One piece I use a lot is the Span Empty Cells, Interpolate using an X column. This has served me many time in interpolated serval columns at once. I have a set now where I have by variables. I actually have 15 sets. I am currently working through them one at a time. It is tedious but it could be worse. :) Have you ever written this code to handle by variables?

[KarenHuang]

I got the same error "Ensure selections are made in all 3 panes" when trying to push the column names. Anyone know how to resolve this?

[CASmith]

Brady, your Add-in app was just recommended to me from a JMP training session for the company I work for.  It solved my problem beautifully and has allowed me to take a complicated machine date/time column of data and convert it into something I can actually use.

[shasheminassab]

@brady_brady 

Hi Brady. I have been using this add-in quite extensively to parse text data to date. It is really a time saver! thanks for that. Now I have a dataset that time is in this format: "Mon, 30 Nov 2020 21:42:26 GMT" and the add-in does not seem to be able to convert it to continuous date. I guess it is because the month is a text and not a number. Is there any way around it?

[brady_brady]

Hi,

It should work as long as you select the "3-letter" radio button in the "Month Format" panel box. (See screenshot).

Cheers,

Brady

[shasheminassab]

Totally missed that part! thanks a lot again. This is a great tool.

[tedarinoinmn]

Thank you so much for this handy add-in.  I'm not sure if I am not using correctly or something isn't right.  I'm trying to parse a character column into a date.  The column looks like this:  yyyymmddhhmmss

I am using the fixed delimited but I can't get any output.  Thanks for any help here.

[brady_brady]

Hi, please try adding a tick-mark after the last digits (31 in your example) to complete the delimiting. Otherwise, what you have looks good.

Cheers,

Brady

[tedarinoinmn]

Thank you so much!  That worked.  I really love this tool.

[Byron_JMP]

This add-in just saved me at least an hour and a half of dinking around (again). Thanks!

[brady_brady]

Hey @Byron_JMP , glad to hear that!

[WebDesignesCrow]

Hi,

How can I use this add-in to convert numeric 45008.078125 to 3/23/2023 1:52:30 AM? 

I still can't work around on this issue after import date format from Excel.

[brady_brady]

Hi WebDesignesCrow,

It looks like your number is the number of days since 01 Jan 1900. Because of this, you do not need the addin. You need only to have a formula column that calculates like this:

asdate( indays( :z ) + informat( "1/1/1900" ) )

Where :z is the column containing the number of days. 

Indays( ) converts this to seconds, informat ( ) converts "1/1/1900" to the number of seconds representing 1/1/1900, and asDate ( ) converts this sum to a date format.

Actually though, I got 25 March, not 23 March, for your value of 45008... the rest was the same.

Cheers,

Brady

[WebDesignesCrow]

Thanks very much for the formula and explanation !

The date returned to incorrect date (extra 2 days from what Excel shows) but the time is correct.

So, I've edited the formula as below;

As Date(	In Days( :z ) + Informat ("1/1/1900",	"<MM></><D></><YYYY><hh><:><mm><:><ss><ampm>")) - In Days( 2 )

Looks good.

[brady_brady]

Hello @WebDesignesCrow ,

I am not sure what is being done in Excel, so I cannot speak to that, but JMP's calculation is correct: 45008 days from 0000, 01 Jan 1900 is 0000, 25 March 2023--not 23 March 2023.

Here is why: 

The years 1900 - 2019, inclusive, represent 30 "blocks" of 4 years each. Each "block" contains (366 + 365 + 365 + 365) = 1461 days, EXCEPT the block with year 1900. 1900 is not a leap year, because if a year is divisible by 100, but not 400, the year is not a leap year.

So we have 1461 * 30  - 1 = 43829 days in the years 1900 - 2019 inclusive.

2020, 2021, and 2022 add (366 + 365 + 365) days.

Jan, Feb of 2023 add (31 + 28) days.

March of 2023 adds 24 days to get us to 0000 on the 25th.

This total is 43829 + 366 + 365 + 365 + 31 + 28 + 24 = 45008. Any remaining decimal portion is what gives the hours, minutes and seconds on 25 March 2023.

So you will want to remove the - Indays ( 2 ) from your JMP formula. (And investigate what is happening in Excel.)

Cheers,

Brady

[WebDesignesCrow]

Hi Brady.

For the time being, I cannot remove the -Indays (2) from my JMP formula that I used in scripting because the outcome date is similar to our database. But, I will check that out with our IT team too. Thanks a lot for the suggestion.

[hogi]

Date Format Writer is sooo cool - it helps a lot to start with the actual string, split it into chunks and identify the entries.
Are there plans to include it in the std. Edit Format Pattern GUI? 

[egaderlund]

Wow, this is helpful, but, most of my date codes include fractions of a second, milliseconds to microseconds (e.g. "20240213101953.948386") and I can't get it to parse. If I just grab the seconds, it works, but, if I include the microseconds it just gives me 'no data' dots in the created column.

[brady_brady]

@egaderlund ,

When using fixed delimiting, the last breakpoint must also be specified. (See the example below).

Note that if present, the "decimal" operator needs to be region-appropriate: in the US this operator is a period, but in many other regions it is a comma.

Cheers,

Brady

[egaderlund]

Ok, thanks; that's what you were saying earlier with the 'tic' at the VERY end! I thought I had done that, but, trying again I was successful.

One feature request, ability to edit the column names when inserting. I end up getting multiple NewDate, NewDate 2, etc.  and would be easier to give more descriptive name if creating multiple columns from the tool.