topic Re: sumif: count columns if they contain some character in Discussions

sumif: count columns if they contain some character

Valentina — Fri, 22 Mar 2024 03:02:05 GMT

Hello,

I found a discussion from 2015 (subject "sumif") that has a very helpful script for my current task.I wrote there my question but I don't know if people who participated in that discussion can see my question.

This script I found (slightly modified) and it works great for counting columns for each row where values are "0/0" (or whatever).

dt << New Column("Sum",
    Numeric, Formula(
        Local({i = 0, n = N Col(Current Data Table()) - 1},
            Sum(Repeat({i++ ; (Column(i)[] =="0/0")}, n))
        )
    )
);

However, how to edit this script to count columns that Contain "some_character"?

I have attached a data table where the last column "Sum" has a formula and needed resulted numbers.

I have a big data table and would like to have a script for counting columns for each row with specific values (as character).

I tried to modify the script above but it doesn't work:

dt << New Column("Miss",
    Numeric, Formula(
        Local({i = 0, n = N Col(Current Data Table()) - 1},
            Sum(Repeat({i++ ; (Contains(Column(i)[],"."))}, n))
        )
    )
);

Please, help

Valentina

Re: sumif: count columns if they contain some character

jthi — Fri, 22 Mar 2024 05:22:56 GMT

Do you need to have a formula for this?

Re: sumif: count columns if they contain some character

Valentina — Fri, 22 Mar 2024 09:04:28 GMT

Sorry, I don't understand your question.

I have a table ~500,000 rows and ~3,000 columns. I need to calculate Sum across columns that contain a dot - cant use formula. .

I want to have a script, any script that would work. I tried different approaches but none worked. .

Re: sumif: count columns if they contain some character

Valentina — Fri, 22 Mar 2024 09:20:11 GMT

This is from my log:

Column Sum Formula Interrupted
argument is wrong type at row 421001 in access or evaluation of 'Contains' , Bad Argument( Column( i )[] ), Contains/*###*/(Column( i )[], ".")
Formula evaluation errors have been ignored

Re: sumif: count columns if they contain some character

jthi — Fri, 22 Mar 2024 09:50:54 GMT

Most likely your loop is reaching some column which isn't character column. This part most likely needs some modification

n = N Col(Current Data Table()) - 1

how it should be done is very difficult to know without any knowledge of your data. It could be just a simple modification to the -1, getting list of character columns or calculating which columns to go through by some sort of a pattern

Re: sumif: count columns if they contain some character

Valentina — Fri, 22 Mar 2024 10:16:37 GMT

I have attached a small data table to my post. It has 3 rows and 3 cols with a header:

S1    S2    S3
0/0   ./.     0/0
./.     ./.      ./.
0/0 0/0    0/0

The script doesn't work in this sample table

Column sum Formula Interrupted
argument is wrong type at row 1 in access or evaluation of 'Contains' , Bad Argument( Column( i )[] ), Contains/*###*/(Column( i )[], ".")
Formula evaluation errors have been ignored

Re: sumif: count columns if they contain some character

Valentina — Fri, 22 Mar 2024 10:24:51 GMT

Yes, it seems all data should be the same type.

Thank you - it works!

Re: sumif: count columns if they contain some character

jthi — Fri, 22 Mar 2024 10:27:12 GMT

The original data table you attached wasn't like that as it has 4 columns (you cannot ignore Sum column)

Depending on your data, this is how I would do this if I were to use a Formula (most likely I would avoid that and just use a pure script solution)

Names Default To Here(1);

dt = Open("$DOWNLOADS/sum_help_data.jmp");
dt << Delete Column("Sum");

dt << New Column("Miss", Numeric, Continuous, Formula(
	As Constant(
		cols_of_interest = Filter Each({colname}, Current Data Table() << Get Column Names("String"),
			!IsMissing(Regex(colname, "S\d"));
		);
	);
	Local({
		res = 0;
	},
		For Each({colname}, cols_of_interest,
			res = res + (Contains(Column(colname)[], ".") > 0);
		);
		res;
	);
));