The World Statistics Day celebration continues here in the Community. We all need reliable data for sound decision making. Do you have a data source that you trust most? Head over to Discussions to tell us about it.
Choose Language Hide Translation Bar
Highlighted
Level I

Get rid of null columns

Hi All,
I need you help with this problem,
Attached is a picture of the table I am working on.
I want to get rid of any empty column. Note: whenever row 2 =0 the column is empty.
which in this case is starting at column 12 to the end of the table.
I would really appreciate all you  help,
Thank you,

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

Re: Get rid of null columns

Here's a way to do it in code.

``````dt = New Table( "Untitled 3", Add Rows( 10 ),
New Column( "Column 1", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] ) ),
New Column( "Column 2", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 3", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 4", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 5", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 6", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 7", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 8", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 9", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 10", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 11", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 12", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., 0, ., ., ., ., ., ., ., .] ) ),
New Column( "Column 13", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 14", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., 0, ., ., ., ., ., ., ., .] ) ),
New Column( "Column 15", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 16", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., 0, ., ., ., ., ., ., ., .] ) )
);

nc = ncols(dt);
for (i = nc, i >= 12, i--,
if (column(dt, i)[2] == 0,
show(i);
dt << delete columns(i);
);
);``````
2 REPLIES 2
Highlighted
Staff

Re: Get rid of null columns

Is this table typical? That is, you expect the same layout each time in import such a worksheet? If so, you can use the Excel Import Wizard to define such things as the row(s) with the column names (e.g., row 12), the row where the data starts (e.g., row 13), the first data column (e.g., column 1), and the last data column (e.g., column 11).

Another way is to use the JMP add-in for Excel. Click the Preferences button on the JMP ribbon and define the table. Select the sub-table in the Excel worksheet (including, for example, rows 12 to the end of the data and columns 1 to 11) and then click the Export button on the JMP ribbon.

In this case, you could also just select the data you want in the Excel worksheet, copy it, go to JMP and make a new empty data table, and select Edit > Paste With Column Names (Control-Shift-V on Windows, Command-Shift-V on Macintosh).

You won't have to clean up the JMP data table because the parts that you do not want won't be imported in the first place.

Learn it once, use it forever!
Highlighted
Super User

Re: Get rid of null columns

Here's a way to do it in code.

``````dt = New Table( "Untitled 3", Add Rows( 10 ),
New Column( "Column 1", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] ) ),
New Column( "Column 2", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 3", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 4", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 5", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 6", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 7", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 8", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 9", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 10", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 11", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 12", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., 0, ., ., ., ., ., ., ., .] ) ),
New Column( "Column 13", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 14", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., 0, ., ., ., ., ., ., ., .] ) ),
New Column( "Column 15", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
New Column( "Column 16", Numeric, "Continuous", Format( "Best", 12 ),
Set Values( [., 0, ., ., ., ., ., ., ., .] ) )
);

nc = ncols(dt);
for (i = nc, i >= 12, i--,
if (column(dt, i)[2] == 0,
show(i);
dt << delete columns(i);
);
);``````
Article Labels

There are no labels assigned to this post.