topic Re: counting character columns in jsl in Discussions

counting character columns in jsl

ERobison — Fri, 09 Jun 2023 22:10:30 GMT

I have a data table with several data columns with binary 0/1 responses (character, nominal). I want to create a new column (numerical, continuous) where I am counting the instances of "1"s in these columns.

can someone help me create a script to create this column? I feel like this should be easy but I am running into road blocks. Do I have to convert my character columns to numerical/continuous columns and do summation formula? Thank you!

Example table

infection (character, nominal)	blood clot (character, nominal)	surgery (character, nominal)	NEW COLUMN: number of complications (numerical, continuous)
0	1	1	2
1	1	1	3
1	0	0	1
0	1	0	1

Re: counting character columns in jsl

Mark_Bailey — Tue, 06 Apr 2021 17:15:51 GMT

A formula is all you need.

Here is the result.

Re: counting character columns in jsl

txnelson — Tue, 06 Apr 2021 17:52:23 GMT

@Mark_Bailey provided a good solution

Here is another way to do solve the problem

Names Default To Here( 1 );
dt = Current Data Table();
charNames = dt << get column names( character );

// convert char columns to numeric
For( i = 1, i <= N Items( charNames ), i++,
	Column( dt, charNames[i] ) << data type( numeric )
);

// Create the jsl required to sum all of the specified columns
theExpr = "dt << new column(\!"number of complications\!", formula(sum(" ||
Substr( Char( charnames ), 2, Length( Char( charNames ) ) - 2 ) || "));";

// Execute the line of code created
Eval( Parse() );

// Delete the formula leaving the static values
Column( dt, "number of complications" ) << delete formula;

// Convert the binary columns back to character
For( i = 1, i <= N Items( charNames ), i++,
	Column( dt, charNames[i] ) << data type( character )
);

Re: counting character columns in jsl

ERobison — Wed, 07 Apr 2021 14:00:35 GMT

Thanks to both @Mark_Bailey and @txnelson for providing two approaches to my problem. I am going to go with the script as I am trying to create a full script for my data table creation to be used again in the future.

thank you!