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

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

fionaweston — Sat, 10 Jun 2023 23:27:49 GMT

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

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

fionaweston — Thu, 25 Mar 2021 16:47:22 GMT

This is just a short version. The real table could have 100+ columns.

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

Georg — Thu, 25 Mar 2021 17:03:22 GMT

Probably this formula could do:

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

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

fionaweston — Thu, 25 Mar 2021 17:09:23 GMT

Thanks Georg, that works!

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

SDF1 — Thu, 25 Mar 2021 17:51:17 GMT

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!,

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

fionaweston — Thu, 25 Mar 2021 22:43:44 GMT

Thanks Diedrich,
This is awesome!

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

SDF1 — Fri, 26 Mar 2021 12:19:43 GMT

Glad to hear it works for you!

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

SDF1 — Wed, 31 Mar 2021 12:41:00 GMT

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!,

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

fionaweston — Wed, 31 Mar 2021 17:30:49 GMT

That helps enormously Diedrich.

Thankyou