nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

JSL question: select columns and replace missing values with zero

Peter_Hersh

Does anyone have a script to remove columns that only contain missing values? I am stumbling my way through cleaning up a data table and this would be very helpful in automating this process.

Thanks,

Pete

Accepted answers

brady_brady

Pete, if you want to delete the columns (subject of post says replace missings with zeros) this will do it.

dt = current data table();

For( i = N Col( dt ), i >= 1, i--,
	If( Max( Is Missing( dt[0, i] ) ) > 0,
		dt << Delete Columns( i )
	)
);

All comments

MikeD_Anderson

You could do this using Missing Value Explorer… assuming the data is numeric. Just use the “show only cols with missing” option. Then (make sure you don’t have any rows selected in the data table beforehand) select the columns in the MVE that have the same number of missing values as the rows in the data table. This will select the cols back in the data table. You can then just delete the cols by right clicking or using the red triangle menu.

Best,

M

Peter_Hersh

Thanks Mike,

Do you know of a way to script this? I had come down to a similar solution, but could not find a way to have this fully contained in a script.

brady_brady

Pete, if you want to delete the columns (subject of post says replace missings with zeros) this will do it.

dt = current data table();

For( i = N Col( dt ), i >= 1, i--,
	If( Max( Is Missing( dt[0, i] ) ) > 0,
		dt << Delete Columns( i )
	)
);

Peter_Hersh

Thanks Brady

Jeff_Perkinson

@brady_brady's is probably better but I came up with using the All() and Loc() functions for Numeric and Character columns.

Something like this:

dt=New Table( "untitled 25 2",
	Add Rows( 4 ),
	New Column( "Column 1",
		Character,
		"Nominal",
		Set Values( {"a", "a", "a", "a"} )
	),
	New Column( "Column 2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [., ., ., .] )
	),
	New Column( "Column 3", Character, "Nominal", Set Values( {"", "", "", ""} ) ),
	New Column( "Column 4",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [., 0, 0, 0] )
	)
);

For( i = N Cols( dt ), i > 0, i--,
	vals = Column( dt, i ) << get values;
	show(vals);
	If( (Column( dt, i ) << get data type) == "Character",
		Show( Loc( vals, "" ) ),
		Show( all( vals ) )
	);
);

brady_brady

Hey Pete, I thought of this later... it is much faster than the first method I proposed. For example, on a 1000 row x1000 column table, the first method took 0.82 seconds, while this method took 0.017 seconds.

dt << Delete Columns( Loc( V Max( Is Missing( dt << Get All Columns As Matrix ) ) ) );

brady_brady

I spoke (typed) too soon. Once the matrix (datatable) gets a bit larger, the first method is faster. On a 100,000 row x 1000 col matrix, the first method takes 2.48 seconds while the second takes 6.51 seconds. My guess is that for truly large tables, the first method will be merely slow, while the second method will be excrutiatingly so.

Cheers,

Brady