766828_en-US

liliconcom

getting column name with specific cut-off values

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	B*9:02, C*10: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	A*1:01,A*2:01,B*9:02,C*10: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, 

Data Blending and CleanupRe: getting column name with specific cut-off values

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

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

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

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

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

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

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

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

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

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

 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

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

Re: getting column name with specific cut-off values

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

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

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

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

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

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

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!

Re: getting column name with specific cut-off values

My script handled the situations you did provide. Depending on the real question, the script might require total re-write or possibly just some small modifications.

Re: getting column name with specific cut-off values

Thank you Jarmo, I would like to attach the file that is compatible with what I want to do here. I want to match Alphabet column that compose of DOA and DO with each column of DOA1*0x:0x,DOB1*0x:0x_DOx. The DOA0x matches with DOA1*0x (not include after ":") and DOx or DOB1*Ox matches with DOx. All of them need to have a value of more than 1000 to be showed in the result column. I cannot correct the existing formula as you said. Thank you in advance!

Re: getting column name with specific cut-off values

Why wouldn't DOA1*0x match with DOx?

Re: getting column name with specific cut-off values

Here is some sort of a script, I'm still left with guess work for the correct rules so no idea if it does what it is supposed to do

Names Default To Here(1);

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

// fix data
For Each({colname}, dt << Get Column Names("Character", "String"),
	If(colname == "Alphabet",
		break();
	);
	Column(dt, colname) << Set Data Type("Numeric") << Set Modeling Type("Continuous");
);
dt << Delete Columns("get name and value");
dt << New Column("R", Numeric, Ordinal, Formula(Row()));

dt_stacked = dt << Stack(
	columns(
		:"DOA1*01:01,DOB1*01:01_DO1"n, :"DOA1*02:01,DOB1*01:01_DO1"n,
		:"DOA1*03:01,DOB1*01:01_DO1"n, :"DOA1*02:01,DOB1*02:01_DO2"n,
		:"DOA1*01:05,DOB1*02:01_DO2"n, :"DOA1*04:03,DOB1*03:01_DO3"n,
		:"DOA1*06:01,DOB1*04:01_DO4"n, :"DOA1*05:01,DOB1*03:01_DO7"n
	),
	Source Label Column("Label"),
	Stacked Data Column("Data"),
	Output Table("stacked")
);

rows_to_delete = dt_stacked << Get Rows Where(:Data <= 1000);
dt_stacked << Delete Rows(rows_to_delete);
dt_stacked << New Column("Match", Numeric, Nominal);

For Each Row(dt_stacked,
	alphs = Words(:Alphabet, ",");
	temp_label = Regex(:Label, "(\d\*)", "", GLOBALREPLACE);
	retval = 0;
	For Each({alph}, alphs,
		If(Contains(temp_label, alph),
			retval = 1;
			break();
		);
	);
	:Match = retval;
);

rows_to_delete = dt_stacked << Get Rows Where(:Match == 0);
dt_stacked << Delete Rows(rows_to_delete);

dt_stacked << New Column("NewStr", Character, Nominal, Formula(
	:Label || " (" || Char(:Data) || ")"
));

Summarize(dt_stacked, rs = By(:R));
fixed_rs = Transform Each({r}, rs, Num(r));
vals = Associative Array();

For Each({r}, fixed_rs,
	t = dt_stacked << Get Rows Where(:R == r);
	vals[r] = Concat Items(dt_stacked[t, "NewStr"], ",");
);
Close(dt_ stacked);

new_col = dt << New Column("Res", Character, Nominal, Formula(
	If(Contains(vals, :R),
		vals[:R]
	,
		""
	);
	
));
dt << run formulas;
new_col << Delete Formula;

 

Re: getting column name with specific cut-off values

It is not matched by biology. 

Re: getting column name with specific cut-off values

Thank you, Jarmo. It's work!