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

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

Fruit325 — Tue, 24 Sep 2024 09:06:25 GMT

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!

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

jthi — Tue, 24 Sep 2024 09:27:31 GMT

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

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

Fruit325 — Tue, 24 Sep 2024 14:30:58 GMT

Hi @jthi

It is missing.

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

jthi — Tue, 24 Sep 2024 14:39:39 GMT

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

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...)

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

Fruit325 — Tue, 24 Sep 2024 14:43:03 GMT

Hi Jarmo,

Thank you so much! It worked!

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

jthi — Tue, 24 Sep 2024 14:47:49 GMT

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

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