topic Re: Assigning Minimum value to a column in Discussions

Assigning Minimum value to a column

Jackie_ — Fri, 09 Jun 2023 23:26:47 GMT

Hi folks,

I have 2 files.

1st file:

2nd file :

I am trying to write a script which will search for the Minimum age from the 1st file and assign the lowest age in the 2nd file

For e.g.

The 1st row which has three name Sam, John, Smith so the Value in the Minimum Age Column should be Min{Sam, John, Smith} Min{21, 27, 32} = 21

my final output will look something like this:

Any suggestions please?

Thanks

Re: Assigning Minimum value to a column

Jackie_ — Mon, 30 Mar 2020 13:35:10 GMT

Attaching the files

Re: Assigning Minimum value to a column

txnelson — Mon, 30 Mar 2020 14:30:31 GMT

Here is a short script that will do what you want......the key is that most individuals do not realize that what is passed to a calculation function, can be conditional. See the script below

Names default to here(1);
dt1=data table("1st");
dt2=data table("2nd file");

current data table(dt1);
For(i=1,i<=nrows(dt2), i++,
	dt2:Minimum Age[i] =col min(If(contains(dt2:Name[i],dt1:Name),dt1:age,.));
);

Re: Assigning Minimum value to a column

Jackie_ — Mon, 30 Mar 2020 17:37:11 GMT

Hi @txnelson,

Thanks for your help.

2nd file doesn't have Minimum Age Column. How can I create the 2nd column with name "Minimum Age" and then assign the lowest value?

Re: Assigning Minimum value to a column

txnelson — Mon, 30 Mar 2020 18:09:28 GMT

What kept you from being able to take the example from "Adding new columns to a data table " and add it to your script?

Did you know that you can go to the Scripting Guide, and get examples of how to do things? If not, I very strongly recommend that you familiarize yourself with it.

Here is the sample script I gave you, with the new command to add the new column

Names default to here(1);
dt1=data table("1st");
dt2=data table("2nd file");

dt2 << New Column( "Minimum Age" );

current data table(dt1);
For(i=1,i<=nrows(dt2), i++,
	dt2:Minimum Age[i] =col min(If(contains(dt2:Name[i],dt1:Name),dt1:age,.));
);

Re: Assigning Minimum value to a column

Jackie_ — Mon, 30 Mar 2020 19:24:32 GMT

Hi @txnelson ,

What if the Age values are stored in column properties? How can I import column properties (Age) of 1st_file into the column (Minimum Age) of 2nd file

Re: Assigning Minimum value to a column

txnelson — Tue, 31 Mar 2020 00:31:58 GMT

First, I need to clarify some terminology.

In data table 1st_file, you have 9 columns
In data table 1st_file you have 1 row of data for each column. You do not have what JMP refers to as column properties. If you right click on a column header, and select "Column Properties", you will see an extensive list of items that refer to the particular column that you selected.

I mention the above, only because when you used the keywords, Column Property, I immediately went to the idea of JMP Column Properties. That setup a bunch of false assumptions which lead to a bunch of confusion for me.

That aside, here are a couple the large number of ways one can solve you issue

Names Default To Here( 1 );
dt1 = Data Table( "1st_file" );
dt2 = Data Table( "2nd file" );

dt2 << New Column( "Minimum Age" );

// Transpose the data into a standard data table format
dt3 = dt1 << Transpose(
	invisible,
	columns( :Sam, :Andy, :John, :Ed, :Jonny, :Smith, :Scott, :George, :Mathew ),
	Output Table( "Transpose of 1st_file" )
);


Current Data Table( dt3 );
For( i = 1, i <= N Rows( dt2 ), i++,
	dt2:Minimum Age[i] = Col Min( If( Contains( dt2:Name[i], dt3:Label ), dt3:Name( "Row 1" ), . ) );
	Show( dt2:minimum Age[i] );
);

Close( dt3, nosave );

Take a little more time in processing the data, but a secondary table creation isn''t required.

Names Default To Here( 1 );
dt1 = Data Table( "1st_file" );
dt2 = Data Table( "2nd file" );

dt2 << New Column( "Minimum Age" );

For( i = 1, i <= N Rows( dt2 ), i++,
	ages = {};
	For( k = 1, k <= N Cols( dt1 ), k++,
		If( Contains( dt2:Name[i], (Column( dt1, k ) << get name) ) > 0,
			Insert Into( ages, Column( dt1, k )[1] )
		);
	);
	dt2:Minimum Age[i] = Min( ages );
);

Re: Assigning Minimum value to a column

Jackie_ — Tue, 31 Mar 2020 00:51:49 GMT

Hi @txnelson ,

Thanks for your quick response.

I didn't clarified my question.

1.In data table 1st_file, you have 9 columns Yes
2. In data table 1st_file you have 1 row of data for each column.

If you right click on a column header, and select "Column Properties", you will see an extensive list of items that refer to the particular column that you selected.

Can I import these extensive list values into column of 2nd_file?

Can we use the syntax "Get Property()"

For e.g.

dt1 << Get Property("Age");

and then use for loop to assign it in the 2nd file?

Thanks again

Re: Assigning Minimum value to a column

txnelson — Tue, 31 Mar 2020 01:24:41 GMT

The syntax for get property is

obj << Get Property( the property you want to retrive );

Therefore you would ask for one of the Column Properties for the column Age as:

:Age << Get Property( "Spec Limits" );
// or
Column( "Age" ) << Get Property( "Units" );

And JMP allows one to create their own Column Properties, so you could do something like

dt1:Sam << Set Property("Age", 3);
dt1:Andy << Set Property("Age", 4);
theAge = dt1:Andy << Get Property( "Age" );

Re: Assigning Minimum value to a column

Jackie_ — Tue, 31 Mar 2020 20:12:34 GMT

Hi @txnelson,

How can I define Column index instead of Column name in the above script?

Thanks

Re: Assigning Minimum value to a column

txnelson — Tue, 31 Mar 2020 20:36:20 GMT

Here is one of the examples I gave

Column( "Age" ) << Get Property( "Units" );

If you examine the documentation on the Column() function in the JSL Syntax Reference, it reads

Column(<dt>, "name", "formatted")
Column(<dt>, n)
	Description
		Gets a reference to the data table column.
	Arguments
		dt Optional reference to a data table. If this is not supplied, the current data table is used.
		name Quoted string that is the name of the column.
		formatted Quoted string that returns the formatted string of the cell value.
		n Column number.

IF you read the example from the Syntax Guide it reads:

The Column function can also identify a column. For Big Class.jmp, the following expressions
all refer to age, the second column in the table:
	Column( "age" );
	Column( 2 );
	Column( dt, 2 );
	Column( dt, "age" );

Re: Assigning Minimum value to a column

Jackie_ — Tue, 31 Mar 2020 21:03:25 GMT

I mean in this script replacing Column name with Column Index

Names Default To Here( 1 );
dt1 = Data Table( "1st" );
dt2 = Data Table( "2nd file" );

Current Data Table( dt1 );
For( i = 1, i <= N Rows( dt2 ), i++,
	dt2:Minimum Age[i] = Col Min( If( Contains( dt2:Name[i], dt1:Name ), dt1:age, . ) )
);

Re: Assigning Minimum value to a column

Jackie_ — Tue, 31 Mar 2020 21:08:36 GMT

Hi @txnelson,

I tried to replace column name with column index but it's not running

Any suggestions?

Names default to here(1); dt1=data table("1st"); dt2=data table("2nd file"); current data table(dt1); For(i=1,i<=nrows(dt2), i++, dt2:Column(2)[i] =col min(If(contains(dt2:Column(1)[i],dt1:Column(1)),dt1:Column(2),.)); );

Re: Assigning Minimum value to a column

txnelson — Tue, 31 Mar 2020 21:31:14 GMT

The Column() function is not

d2:Column(2)[I]

it is as stated

Column( dt, 2 )[I]

And if the indexes were variables, the completed script would be

Names Default To Here( 1 );
dt1 = Data Table( "1st" );
dt2 = Data Table( "2nd file" );

indexd2minage = 2;
indexd2name = 1;
indexd1name = 1;
indexd1age = 2;


Current Data Table( dt1 );
For( i = 1, i <= N Rows( dt2 ), i++,
	Column( dt2, indexd2minage )[i] =
	Col Min(
		If(
			Contains(
				Column( dt2, indexd2Name )[i],
				as Column( dt1, indexd1Name )
			),
			Column( dt1, indexd1age ),
			.
		)
	)
);