cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
Fruit325
Level III

How to delete columns where all the rows are "NA"?

Hi I want to get some advice for data filtering. Thanks!

I have some columns where all the rows are "NA". How could I identify those columns and delete them?

The blank cells are resulted from splitting the table. Thanks!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
jthi
Super User

Re: How to delete columns where all the rows are "NA"?

There are many ways of doing this (especially when scripting), below is one fairly simple script

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(7),
	Compress File When Saved(1),
	New Column("Column 1", Numeric, "Continuous", Format("Best", 12), Set Values([1, 1, 2, 3, 4, ., .])),
	New Column("Column 2", Numeric, "Continuous", Format("Best", 12), Set Values([., 2, ., 2, ., ., 5])),
	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([., ., ., ., ., ., 5]))
);

wait(1); // for demo purposes

For Each({colname}, dt << Get Column Names("String"),
	If(Col Number(Column(dt, colname)) == 0,
		dt << Delete Columns(colname);
	);
);

You can also use Columns Viewer (in JMP earlier than 18) to select the columns and then delete them

jthi_0-1727188723703.png

 

In JMP18 you have access to columns manager which might be able to do something similar (I cannot test it as I was kicked out and I don't have access to my email currently to re-verify...)

-Jarmo

View solution in original post

5 REPLIES 5
jthi
Super User

Re: How to delete columns where all the rows are "NA"?

Are those values "NA" or missing ("" or .)?

-Jarmo
Fruit325
Level III

Re: How to delete columns where all the rows are "NA"?

Hi @jthi 

 

It is missing. 

jthi
Super User

Re: How to delete columns where all the rows are "NA"?

There are many ways of doing this (especially when scripting), below is one fairly simple script

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(7),
	Compress File When Saved(1),
	New Column("Column 1", Numeric, "Continuous", Format("Best", 12), Set Values([1, 1, 2, 3, 4, ., .])),
	New Column("Column 2", Numeric, "Continuous", Format("Best", 12), Set Values([., 2, ., 2, ., ., 5])),
	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([., ., ., ., ., ., 5]))
);

wait(1); // for demo purposes

For Each({colname}, dt << Get Column Names("String"),
	If(Col Number(Column(dt, colname)) == 0,
		dt << Delete Columns(colname);
	);
);

You can also use Columns Viewer (in JMP earlier than 18) to select the columns and then delete them

jthi_0-1727188723703.png

 

In JMP18 you have access to columns manager which might be able to do something similar (I cannot test it as I was kicked out and I don't have access to my email currently to re-verify...)

-Jarmo
Fruit325
Level III

Re: How to delete columns where all the rows are "NA"?

Hi Jarmo,

 

Thank you so much! It worked! 

jthi
Super User

Re: How to delete columns where all the rows are "NA"?

Laptop was still on so could access the email, you can perform the column selection from Columns Manager. You can even sort by the N Missing to make it even easier to select the "top" rows

jthi_0-1727189068456.png

I also have few addins which can do this BUT I'm not sure if they are still functional in JMP18, Analyse Columns and Delete Missing Value Columns (github.com) .

-Jarmo