cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
lwx228
Level VIII

How to compute a match with JSL?

Hello, everyone!


In excel, "=offset(,match(,,)-1,match(,,)-1,,)" functions can be used to calculate, but the speed is slow when the data is large.

 

How to do this with JSL?Thank you!2018-10-09_16-19-31.png

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ih
Super User (Alumni) ih
Super User (Alumni)

Re: How to compute a match with JSL?

Here are a few ways, to stack with a variable number of columns, if you are using match in excel today then one of the first two methds will likely work:

 

//If the column positions do not change
d3_1 = d1 << Stack(
	columns( Concat( [2 3], 4::10 )),
	Source Label Column( "year" ),
	Stacked Data Column( "data" ),
	Drop all other columns( 1 ),
	Keep( :state )
);


//If you want all columns
d1_clean = ( d1 << Subset( All rows, columns( 1::10 ) ) );
d3_2 = d1_clean << Stack(
	columns( 2:: ncol(d1_clean) ),
	Source Label Column( "year" ),
	Stacked Data Column( "data" ),
	Drop all other columns( 1 ),
	Keep( :state )
);


//If the column names are in a specific format

// - get column names
cn = d1 << Get Column Names( string );

// - Find which columns match a format
//   (jmp does have some patter matching tools but I like regex, check out regexr.com)
matches = repeat(0, N Items(cn));
for(c=1, c<= N Items(cn), c++, if(!is missing(Regex(cn[c], "^\d+$")), matches[c] = 1));

// - Stack
d3_3 = d1 << Stack(
	columns( loc(matches) ),
	Source Label Column( "year" ),
	Stacked Data Column( "data" ),
	Drop all other columns( 1 ),
	Keep( :state )
);

View solution in original post

9 REPLIES 9
lwx228
Level VIII

Re: How to compute a match with JSL?

d1 = Open( "$SAMPLE_DATA/Presidential Elections.jmp" );
d2 = New Table( "offset",
Add Rows( 8 ),
New Column( "State",
Character,
"Nominal",
Set Values(
{"Wyoming", "Montana", "Montana", "Alaska", "Wisconsin", "Nebraska", "Nevada", "Montana"}
)
),
New Column( "year",
Character,
"Nominal",
Set Values( {"1988", "1988", "1988", "1992", "2000", "1992", "2004", "1980"} )
),
New Column( "data", Character, "Nominal", ),

);
lwx228
Level VIII

Re: How to compute a match with JSL?

Calculate the value of "data" in table "offset" based on the data in table "Presidential Elections".


Thank you!
ih
Super User (Alumni) ih
Super User (Alumni)

Re: How to compute a match with JSL?

Since you are worried about speed with large tables, you might create a 'lookup table' in a long format so there is one row per value and then use with Join or Update to do the lookup.  Here is how that might look:

 

Names default to here( 1 );

d1 = Open( "$SAMPLE_DATA/Presidential Elections.jmp" );
d2 = New Table( "offset",
	Add Rows( 8 ),
	New Column( "State",
		Character,
		"Nominal",
		Set Values(
			{"Wyoming", "Montana", "Montana", "Alaska", "Wisconsin", "Nebraska", "Nevada", "Montana"}
		)
	),
	New Column( "year",
		Character,
		"Ordinal",
		Set Values( {"1988", "1988", "1988", "1992", "2000", "1992", "2004", "1980"} )
	),
	New Column( "data",
		Numeric,
		"Continuous",
		Format( "Fixed Dec", 12, 1 ))

);

//Move data to a long format
d3 = d1 << Stack(
	columns(
		:Name( "1980" ),
		:Name( "1984" ),
		:Name( "1988" ),
		:Name( "1992" ),
		:Name( "1996" ),
		:Name( "2000" ),
		:Name( "2004" ),
		:Name( "2008" ),
		:Name( "2012" )
	),
	Source Label Column( "year" ),
	Stacked Data Column( "data" )
);
d3 << Set Name( "Long format" );
d3:year << Set Modeling Type("Ordinal");

//'Lookup' the values:
d2 << Update( with(d3), Match Columns( :state=:state, :year=:year ), Add Columns from Update table( None ));

d3 << Close window;

 

 

lwx228
Level VIII

Re: How to compute a match with JSL?

good job !Thank you!
lwx228
Level VIII

Re: How to compute a match with JSL?

If these columns are many, such as more than 1000……Let's say it's a date.

how do these columns implement the d3 table setup in the form of a variate loop.Thank you!

2018-10-09_22-12-53.png

 

 

 

ih
Super User (Alumni) ih
Super User (Alumni)

Re: How to compute a match with JSL?

Here are a few ways, to stack with a variable number of columns, if you are using match in excel today then one of the first two methds will likely work:

 

//If the column positions do not change
d3_1 = d1 << Stack(
	columns( Concat( [2 3], 4::10 )),
	Source Label Column( "year" ),
	Stacked Data Column( "data" ),
	Drop all other columns( 1 ),
	Keep( :state )
);


//If you want all columns
d1_clean = ( d1 << Subset( All rows, columns( 1::10 ) ) );
d3_2 = d1_clean << Stack(
	columns( 2:: ncol(d1_clean) ),
	Source Label Column( "year" ),
	Stacked Data Column( "data" ),
	Drop all other columns( 1 ),
	Keep( :state )
);


//If the column names are in a specific format

// - get column names
cn = d1 << Get Column Names( string );

// - Find which columns match a format
//   (jmp does have some patter matching tools but I like regex, check out regexr.com)
matches = repeat(0, N Items(cn));
for(c=1, c<= N Items(cn), c++, if(!is missing(Regex(cn[c], "^\d+$")), matches[c] = 1));

// - Stack
d3_3 = d1 << Stack(
	columns( loc(matches) ),
	Source Label Column( "year" ),
	Stacked Data Column( "data" ),
	Drop all other columns( 1 ),
	Keep( :state )
);
lwx228
Level VIII

Re: How to compute a match with JSL?

Thank you very much for your help.


It's also quick to use a dictionary in excel……Set d = CreateObject("Scripting.Dictionary")

but more than a million lines will have to be done differently.
txnelson
Super User

Re: How to compute a match with JSL?

I don't see the benefit to the JMP User Community of the statement in your last reply,

     "It's also quick to use a dictionary in excel……Set d = CreateObject("Scripting.Dictionary")"

It seems that such a comment would be more appropriate on the Excel Discussion Forum

Jim
lwx228
Level VIII

Re: How to compute a match with JSL?

Yes, excel is convenient to use worksheet functions within 10,000 rows.

Over 10,000 rows I'm used to doing this with a VBA dictionary.But dictionaries over 100,000 are slow.


Now using this method of JMP, over 10 million lines are also fast.ThankS you!