topic Re: getting column name with specific cut-off values in Discussions

getting column name with specific cut-off values

doraemengs — Tue, 18 Jun 2024 16:05:55 GMT

Hi,

I try to count and get specific column names from these data examples (my real data contained 180 columns and 20000 rows)

I want to select the column that cut-off > 1000

No.	A*1:01	A*2:01	B*9:02	C*10:01	DR*56:01	DP*07_G	Get name
1	10.67	0	9520.23	15000.1	90.56	40.89	B9:02, C10:01
2	0	0	0	0	0	9.56
3	9040.3	345.3	345.9	999.9	345	10.5	A*1:01
4	34.4	4566	45	7	0	0	A*2:01
5	9190.2	13567	3456	6789	9088	0	A1:01,A2:01,B9:02,C10:01,DR*56:01
6	0	0	0	0	0	0
...

I try the previous script like this

Names Default To Here( 1 );

// Create a sample data table
dt = New Table( "Example",
	Add Rows( 2 ),
	New Column( "A*1:01", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 50000] ) ),
	New Column( "A*2:01", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 50000] ) ),
	New Column( "B*9:02", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 50000] ) ),
	New Column( "C*10:01", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 50000] ) ),
	New Column( "DR*56:01", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 50000] ) ),
//get wanted columns. Assumes that IMC columns start from column 1 and all IMC columns are one after another
colNames = dt << Get Column Names("String");
For(i = N Items(colNames), i > 3000 , i--,
);

//new column with IMC with value 1
dt << New Column( "Ag", Character, 
	<< Set Each Value(
		rowValues = dt[Row(), 0][1::N Items(colNames)]; //get values of row as matrix
		colIndex = Contains(rowValues, 1); //get index of
		If(colIndex > 0, //handle 
			colNames[colIndex], //get column name with the index
			""; //when no 1 found use ""
		);
	);
);

Could you please to correct my scripts, Thank you!

In addition, can I use matrix because of I need to generate the "new column" of 180 column.

Best,

Re: getting column name with specific cut-off values

txnelson — Tue, 18 Jun 2024 18:00:38 GMT

Here is how I would handle this

names default to here(1);
dt = New Table( "Example",
	Add Rows( 6 ),
	New Column( "A*1:01",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[10.67, 0, 9040.2999999999993, 34.399999999999999, 9190.2000000000007, 0
			]
		)
	),
	New Column( "A*2:01",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [0, 0, 345.30000000000001, 4566, 13567, 0] )
	),
	New Column( "B*9:02",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [9520.2299999999996, 0, 345.89999999999998, 45, 3456, 0] )
	),
	New Column( "C*10:01",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [15000.1, 0, 999.89999999999998, 7, 6789, 0] )
	),
	New Column( "DR*56:01",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [90.560000000000002, 0, 345, 0, 9088, 0] )
	),
	New Column( "DP*07_G",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [40.890000000000001, 9.5600000000000005, 10.5, 0, 0, 0] )
	)
);

dt << New Column( "Get Name",character);

colNames = dt << get column names( continuous,string );

For Each Row(
	theList = {};
	For Each( {col, i}, colNames,
		If( As Column( col ) > 3000,
			Insert Into( theList, col )
		)
	);
	:Get Name = Concat Items( theList, "," );
);

Re: getting column name with specific cut-off values

doraemengs — Tue, 18 Jun 2024 18:04:54 GMT

Hi Jim,

Thank you for your help. However, I have 180 columns and 20000 rows in difference words and I cannot put all of them as following scripts. Could you please provide me with more suggestions?

Thank you

Re: getting column name with specific cut-off values

jthi — Tue, 18 Jun 2024 18:36:10 GMT

This might be fast enough

Names Default To Here(1);

// Create a sample data table
dt = New Table("Example",
	Add Rows(20000),
	invisible
);

colcount = 200;
For(i = 1, i <= colcount, i++,
	newcol = dt << New Column(Char(i), Numeric, Continuous, Formula(
		Random Normal() * 1000
	));
	dt << run formulas;
	newcol << delete formula;
);

start = Tick Seconds();
colnames = dt << Get Column Names("String");
newcol = dt << New Column("getname", Character, Nominal, Formula(
	valid_idx = Loc(dt[Row(), colnames] > 1000);
	If(N Items(valid_idx) > 0,
		Concat Items(colnames[valid_idx], ", ")
	,
		""
	);
));
dt << run formulas;
newcol << delete formula;

wait(0);
end = Tick Seconds();
show(end - start);

Re: getting column name with specific cut-off values

doraemengs — Tue, 18 Jun 2024 18:54:38 GMT

Hi Jarmo,

Thank you so much. But the result is "end - start = 0.0333333333255723;" That's not the new table. could you please provide more solutions? please show me the following data table on JMP.

Thank you

Re: getting column name with specific cut-off values

jthi — Tue, 18 Jun 2024 18:59:26 GMT

My script creates new column "getname" with the values

With your data, after you fix your columns to numeric (and drop No column)

Re: getting column name with specific cut-off values

doraemengs — Tue, 18 Jun 2024 19:18:22 GMT

Hi Jarmo,

Thank you for your help but to be honest as in the following attachment, I cannot run the script as you did. when I checked the script (command + 1) shown below. Thank you for your help.

dt = New Table("Example",
	Add Rows(20000),
	invisible
);

colcount = 200;
For(i = 1, i <= colcount, i++,
	newcol = dt << New Column(Char(i), Numeric, Continuous, Formula(
		Random Normal() * 1000
	));
	dt << run formulas;
	newcol << delete formula;
);

start = Tick Seconds();
colnames = dt << Get Column Names("String");
newcol = dt << New Column("getname", Character, Nominal, Formula(
	valid_idx = Loc(dt[Row(), colnames] > 1000);
	If(N Items(valid_idx) > 0,
		Concat Items(colnames[valid_idx], ", ")
	,
		""
	);
));
dt << run formulas;
newcol << delete formula;

wait(0);
end = Tick Seconds();
show(end - start);
/*:

end - start = 0.683333333348855;

Re: getting column name with specific cut-off values

jthi — Tue, 18 Jun 2024 19:22:48 GMT

You will have to open your table, fix the columns and then run the part where new column is created

Names Default To Here(1);

dt = Open("$DOWNLOADS/Examples_get column name_by cutoff.jmp", invisible);

colnames = Remove(dt << Get Column Names("String"), 1); // drop No column

// Fix data and modeling types
For Each({colname}, colnames,
	Column(dt, colname) << Data Type("Numeric") << Modeling Type("Continuous");
);


newcol = dt << New Column("getname", Character, Nominal, Formula(
	valid_idx = Loc(dt[Row(), colnames] > 1000);
	If(N Items(valid_idx) > 0,
		Concat Items(colnames[valid_idx], ", ")
	,
		""
	);
));
dt << run formulas;
newcol << delete formula;

I strongly suggest you check out Scripting Guide from JMP Help

Re: getting column name with specific cut-off values

doraemengs — Tue, 13 Aug 2024 15:08:26 GMT

Hi Jarmo, Do you have any idea for extracting the values of this that are more than the cutoff (if I have 50 columns) with each column name (getname column on an examples)

Re: getting column name with specific cut-off values

jthi — Tue, 13 Aug 2024 16:47:01 GMT

How do you wish to extract them? Doesn't my solution create new column which has the column names as strings separated by , and space?

Do you wish to check values by column instead of by row? Or something else?

Edit:

If you just want different column list, I would have to know how to filter it. If you just want all columns, remove the Remove() function

Names Default To Here(1);

dt = Open("$DOWNLOADS/Examples_get column name_by cutoff.jmp", invisible);

colnames = dt << Get Column Names("String");
For Each({colname}, colnames,
	Column(dt, colname) << Data Type("Numeric") << Modeling Type("Continuous");
);


For(i = 1, i < 100, i++,
	newcol = dt << New Column(Hex(i), Numeric, Nominal, Values(J(1, N Rows(dt), Random Normal()*1000)));
);

colnames = dt << Get Column Names("String");

newcol = dt << New Column("getname", Character, Nominal, Formula(
	valid_idx = Loc(dt[Row(), colnames] > 1000);
	If(N Items(valid_idx) > 0,
		Concat Items(colnames[valid_idx], ", ")
	,
		""
	);
));
dt << run formulas;
newcol << delete formula;

Re: getting column name with specific cut-off values

txnelson — Tue, 13 Aug 2024 16:31:55 GMT

Here is an alternative method that may help with a large number of columns

Names Default To Here( 1 );

dt = Open("$DOWNLOADS/Examples_get column name_by cutoff.jmp");

colnames = Remove( dt << Get Column Names( "String" ), 1 ); // drop No column

// Fix data and modeling types
For Each({colname}, colnames,
	Column(dt, colname) << Data Type("Numeric") << Modeling Type("Continuous");
);

dtStack = dt << Stack(
	columns( colnames ),
	Source Label Column( "ColName" ),
	Stacked Data Column( "Value" ),
	Output Table( "The Stack" )
);

dtStack << select where( :value <= 1000 );
dtStack << delete rows;
dtStack << delete columns( Value );

dtSplit = dtStack << Split(
	Split By( :ColName ),
	Split( :ColName ),
	Group( :No ),
	Output Table( "Split of The Stack by ColName" ),
	Sort by Column Property
);

close( dtStack, nosave );
colnames = Remove( dtSplit << Get Column Names( "String" ), 1 );
dtSplit << Combine Columns(
	delimiter( "," ),
	columns( colnames ),
	Column Name( "Get Name" )
);
dtSplit << delete columns( colnames );
dt << Update( With( dtSplit ), Match Columns( :No = :No ) );

Close( dtSplit, nosave );

Re: getting column name with specific cut-off values

doraemengs — Tue, 13 Aug 2024 18:15:26 GMT

Thank you so much, I would like to clarify this. I have another column of interest as "alphabet of interest" and I want to get name and value of all columns with values> 1,000 and all columns match with the column alphabet of interest.

Thank you!

Re: getting column name with specific cut-off values

doraemengs — Tue, 13 Aug 2024 18:26:26 GMT

Thank you, Jim, but it does not work. please see the attached file.

Re: getting column name with specific cut-off values

jthi — Tue, 13 Aug 2024 18:43:35 GMT

Most likely doing something like Jim suggested is faster and easier, but you can do something like this with the method I did demonstrate

Names Default To Here(1);

dt = Open("$DOWNLOADS/Examples_get column name_by cutoff.jmp", invisible);

Summarize(dt, alphs = By(:"Alphabet of interest"n));
aa = Associative Array(alphs, Repeat({{}}, N Items(alphs)));

collist = dt << Get Column Names("String");
For Each({colname}, collist,
	alph = Word(1, colname, "*");
	If(Contains(aa, alph),
		Insert Into(aa[alph], colname);
		Column(dt, colname) << Set Data Type("Numeric") << Modeling Type("Continuous");
	);
);


str_template = "^col^ (^val^)";
newcol = dt << New Column("getname", Character, Nominal, Formula(
	cols = aa[:"Alphabet of interest"n];
	vals = dt[Row(), aa[:"Alphabet of interest"n]];
	valid_idx = Loc(vals > 1000);
	
	If(N Items(valid_idx) > 0,
		res = {};
		For Each({{col, val}}, Across(cols[valid_idx], vals[valid_idx]),
			Insert Into(res, Eval Insert(str_template));
		);
		Concat Items(res, ", ");
	,
		""
	);
));

dt << run formulas;
newcol << delete formula;

dt << show window(1);

Re: getting column name with specific cut-off values

txnelson — Tue, 13 Aug 2024 19:18:34 GMT

If the script was written to run with a column called "No" followed by numerical columns. Your supplied data table has 2 character columns in addition to the numeric. If you remove the columns "Alphabet of interest" and "get name and value" and then run the script, it works fine.

You should be able to easily change the script to compensate for the additional columns. Either deleting them in the script before beginning the getting the colnames retrieved, or else deleting them from the colnames list before processing the For Each should work.

Re: getting column name with specific cut-off values

doraemengs — Tue, 13 Aug 2024 20:13:48 GMT

Thank you so much. This script does work. However, I am sorry that my original data was complicated more than this. The Alphabet of interest has multiple "alphabet" and "numbers" separated with "," (A1, B9) as attached files. How can I get name and value column that correlated alphabet of interest column and column name.

Thank you for your help.

Re: getting column name with specific cut-off values

jthi — Wed, 14 Aug 2024 04:11:00 GMT

How are those "alphabets" determined? Are they everything that is after last "_"? If that is the case you should be able to make very slight modification to my script and take last word instead of the first and use different separator, so this part requires slight modification

alph = Word(1, colname, "*");

Re: getting column name with specific cut-off values

doraemengs — Wed, 14 Aug 2024 13:09:14 GMT

Thank you for your suggestion. If I want to match the first word before ":" it should be this

alph = Word(1, colname, ":");

and if I want to match the last word after "_" it should be this

alph = Word(-1, colname, "_");

What if I need to match before ":" and after "_" simultaneously? After I joined the scripts I ran for each above, I could not manage with duplicate (exactly the same thing).

Thank you

Re: getting column name with specific cut-off values

jthi — Wed, 14 Aug 2024 13:14:21 GMT

Looking at your table start and end will be different (start A*1 and end A1) so you will have to add those "extra" keys to the associative array to perform the comparison AND handle duplicates later.

Re: getting column name with specific cut-off values

doraemengs — Wed, 14 Aug 2024 13:15:39 GMT

In addition, if a column in the alphabet of interest had more than 1, the script did not run. Do you have any suggestion on this?

Thank you!