52081_en-US

liliconcom

JSL question: select columns and replace missing values with zero

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

Re: JSL question: select columns and replace missing values with zero

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

Re: JSL question: select columns and replace missing values with zero

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

Re: JSL question: select columns and replace missing values with zero

@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 ) )
 );
);

Re: JSL question: select columns and replace missing values with zero

Thanks Brady

Re: JSL question: select columns and replace missing values with zero

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

Re: JSL question: select columns and replace missing values with zero

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.

Re: JSL question: select columns and replace missing values with zeroYou 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