How to compute a match with JSL?

[#1]

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!

[#2]

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", ),

);

[#3]

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


Thank you!

[#4]

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;

[#5]

good job !Thank you!

[#6]

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!

[#7 · Accepted solution]

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

[#8]

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.

[#9]

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

[#10]

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!