371440_en-US

liliconcom

how to count the number of columns in each row of data that fall below a certain value

If I have 4 columns in my table and I need to find out per row how many columns fall below 98% how can I write a simple formula to do this. 

There are some similar post but they are way more complicated than I need.

Thanks

Advanced Statistical ModelingConsumer and Market ResearchData Blending and CleanupMass CustomizationRe: how to count the number of columns in each row of data that fall below a certain value

That helps enormously Diedrich.

Thankyou

Re: how to count the number of columns in each row of data that fall below a certain value

Hi @fionaweston ,

 

  As mentioned, here is the data table mock-up I did with the Counts script. It will only count numeric/continuous columns and ignore any other data/modeling type columns in your table.

 

Hope this helps!,

DS

Re: how to count the number of columns in each row of data that fall below a certain valueGlad to hear it works for you!Re: how to count the number of columns in each row of data that fall below a certain valueThanks Diedrich,
This is awesome!Re: how to count the number of columns in each row of data that fall below a certain value

Hi @fionaweston ,

 

  To expand and generalize what @Georg posted, this script you can save to any data table and it will generate a new column with the counts. I wanted to add this because you mentioned that the data table could have hundreds of columns, and this can handle that, as well as however many rows you have.

Names Default To Here( 1 );
dt = Current Data Table();

dt << New Column( "Counts", Numeric, Continuous );

test = Function( {val},
 If( val < 0.98, 1, 0 )
);

For( r = 1, r <= N Rows(), r++,
 count = 0;
 ret = 0;
 For( col = 1, col <= N Col() - 1, col++, 
  If( Is Missing( Column( col )[r] ) == 1,
   count = 0,
   count = test( Column( col )[r] )
  );
  ret += count;
  :Counts[r] = ret;
 );
);

  You just click the hot button next to your data table name and select New Script, then copy/paste the script from above into the new script (and give it a name of course) and then click the green arrow to run it. You can then copy/paste that script into any data table -- of course, you would change the comparison value in the "test" function for whatever data set your using.

 

Hope this helps!,

DS

 

UPDATE: I noticed my original post didn't account for missing values in a column and corrected it to account for missing values.

Re: how to count the number of columns in each row of data that fall below a certain value

Thanks Georg, that works!

Re: how to count the number of columns in each row of data that fall below a certain value

Probably this formula could do:

test = Function( {val},
 If( val < 0.98, 1, 0 )
);
Sum( test( :A ), test( :B ), test( :C ), test( :D</img> ) );

 

 

Re: how to count the number of columns in each row of data that fall below a certain valueThis is just a short version. The real table could have 100+ columns.