I have a data table with 3 columns
I need to create a table showing % of missing data in the HEIGHT column grouped by ID and SUB_ID. In the case above the result should be:
But it is possible in some cases that there are no rows with missing data. In that case Percent Missing should be 0.
I need help in writing a JSL script for this. I will appreciate any guidance. I have three issues in my script below:
1. How can I track handle/reference to a data table so that I can recall it any time. I have always used "dt = CurrentDataTable" in the beginning of the script. But after having created few summary tables/ subset tables, I lose reference to the original table and don't know how to make it current.
2. I need this to work even when there are no missing rows. Right now script just stops in the middle.
3. I need all tables to close except the final table with Percent Missing.
// Read the original table
dt = CurrentDataTable();
main_table = Data Table( dt ) << get name; // get the table name
// Create a summary table with no of rows grouped by ID and SUB_ID
dt_summary = dt<< Summary(Group(:ID, :SUB_ID), Freq("None"), Weight("None"), output table name( "Main Table Summary"));
:N Rows << Set Name( "Total No of Rows" );
// Create a subset of the original table with only the missing rows
Current Data Table(Data Table(main_table));
dt = CurrentDataTable();
dt_missing = dt << select where( Is Missing( :HEIGHT ) == 1 ) << Subset(Output Table Name("Missing Rows"));
If( !Is Empty( dt_missing ),
dt_missing_summary = Data Table( dt_missing ) << Summary(
Group( :ID, :SUB_ID ),Freq("None"), Weight("None"), output table name( "Missing Rows Table Summary"));
:N Rows << Set Name( "No of Rows with Missing Data" );
dt_missing_summary << Update(
With( Data Table( dt_summary ) ),
Match Columns( :ID = ID, :SUB_ID = :SUB_ID ),
Add Columns from Update table( :Total No of Rows )
);
New Column( "PercentMissing",
Numeric,
"Continuous",
Format( "Percent", 12, 2 ),
Formula( :No of Rows with Missing Data / :Total No of Rows )
);
);
//Close( dt_missing, nosave );
//Close( dt_summary, nosave );Thanks
Accepted Solutions
First I strongly suggest checking out JMP Scripting guide: JMP15 Scripting Guide and Scripting Index found from Jmp / Help / Scripting Index. These are very good sources for JSL scripting.
To do this task you won't need any jsl:
1. Let's start with the example table you provided us with two missing values
2. Create a summary of the table with N Missing with Height as statistics and SUB_ID and ID as Group
3. Create new Formula by selecting N Rows and N Missing(HEIGHT) columns
4. Change format to Percent from Column Properties and rename columns as needed
Based on these steps you could also fairly easily use JMPs provided functionality to show scripts needed for steps to change this manual process to automated process (small changes should be done to make it more robust).
And for your questions 1 & 3:
1. It depends a lot how you create/open/get your datatables. If you always have the same name, you could reference that but I wouldn't trust that in long run as it is so easy to create multiple datatables with same name and JMP will add indexing after the names. But these can be referenced by Datatable(nameofdatatable).
3. You can get list of datatables with Get Data Table List(). Loop over datatables and close unnecessary ones. But in my opinion you should always close the datatable you won't need as soon as it is possible with Close(reference to datatable, No save).
Example script to perform the task:
Names Default To Here(1);
//create example data and add reference to dt variable
dt = New Table("exampledata",
Add Rows(8),
Compress File When Saved(1),
New Column("ID",
Numeric,
"Nominal",
Format("Best", 12),
Set Values([1, 1, 1, 1, 2, 2, 2, 2])
),
New Column("SUB_ID",
Character,
"Nominal",
Set Values({"A", "A", "B", "B", "A", "A", "B", "B"})
),
New Column("HEIGHT",
Numeric,
"Continuous",
Format("Best", 12),
Set Values([10, 11, ., 9, ., 12, 8, 12])
)
);
//note Link to original data table(0)
//dt_summary is reference to the new Summary table
dt_summary = dt << Summary(
Group(:ID, :SUB_ID),
N Missing(:HEIGHT),
Freq("None"),
Weight("None"),
Link to original data table(0),
output table name("Main Table Summary")
);
//close unnecessary datatable
Close(dt, no save);
//add new column with formula
dt_summary << New Column("Percent Missing",
Numeric,
"Continuous",
Format("Percent", 12, 0),
Formula(:Name("N Missing(HEIGHT)") / :"N Rows"),
);
//rename columns
Column(dt_summary, "N Rows") << Set Name("Total No of Rows");
Column(dt_summary, "N Missing(HEIGHT)") << Set Name("No of Rows with Missing Data");
