cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Try the Materials Informatics Toolkit, which is designed to easily handle SMILES data. This and other helpful add-ins are available in the JMP® Marketplace
Choose Language Hide Translation Bar
ERobison
Level I

counting character columns in jsl

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)

0112

1

113
1001
0101
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: counting character columns in jsl

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

View solution in original post

3 REPLIES 3

Re: counting character columns in jsl

A formula is all you need.

 

formula.PNG

 

Here is the result.

 

table.PNG

ERobison
Level I

Re: counting character columns in jsl

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!

txnelson
Super User

Re: counting character columns in jsl

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