Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
lwx228
Level VII

Can matching data be obtained directly without auxiliary columns?

For example, use the "age" column and "sex" column combinations of "Big class.jMP" to generate new column names on another table.
How to match the data of another table directly through "age" and "sex" column without adding auxiliary column in the big table?

 

Thanks!

2020-08-02_18-35.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Can matching data be obtained directly without auxiliary columns?

Try this

names default to here(1);

// Create the 2 Example data tables
dtBig = open("$SAMPLE_DATA/big class.jmp");
dtSmall = New Table( "Data agesex",
	Add Rows( 12 ),
	New Column( "agesex",
		Character,
		"Nominal",
		Set Values(
			{"12F", "12M", "13F", "13M", "14F", "14M", "15F", "15M", "16F", "16M",
			"17F", "17M"}
		)
	),
	New Column( "N Rows",
		Numeric,
		"Continuous",
		Format( "Fixed Dec", 12, 0 ),
		Set Values( [5, 3, 3, 4, 5, 7, 2, 5, 2, 1, 1, 2] )
	)
);

// Create the new column
dtBig << New Column( "N Rows" );

// Move the data as required
For( i = 1, i <= N Rows( dtSmall ), i++,
	foundRows = dtBig << get rows where(
		Num( Substr( dtSmall:agesex[i], 1, Length( dtSmall:agesex[i] ) - 1 ) ) == :age & 
		Substr( dtSmall:agesex[i], -1 ) == :sex
	);
	If( N Rows( foundRows ) > 0,
		dtBig:N Rows[foundRows] = dtSmall:N Rows[i]
	);
);
Jim

View solution in original post

7 REPLIES 7
Highlighted

Re: Can matching data be obtained directly without auxiliary columns?

I'm not sure I completely understand what you are trying to do. As a specific example, are you trying to determine the number of 13 year old males in the data table and put that into a column without creating the age and gender concatenation column? If that is what you want, I think this formula column would work:

 

Capture.JPG

Note that this formula just counts the number of non-missing entries in the data table. In this case I picked to count the number of non-missing entries for the Name column by age and sex. No intermediate column or new table is needed.

 

Dan Obermiller
Highlighted
txnelson
Super User

Re: Can matching data be obtained directly without auxiliary columns?

I haven't tested this code, but you should see at least the approach I am taking, and the approach will work.

Names Default To Here( 1 );
dtBig = Data Table( "Big Class" );
dtSmall = Data Table( "Big Class By (age, sex)" );
dt << New Column( "N Rows" );

For( i = 1, i <= N Rows( dtSmall ), i++,
	foundRows = dtBig << get rows where(
		Substr( :agesex[i], 1, Length( :agesex[i] ) - 1 ) == :age & Substr( agesex[i], -1 ) == :sex
	);
	If( N Rows( foundRows ) > 0,
		dtBig:N Rows[foundRows] = dtSmall:N Rows[i]
	);
);
Jim
Highlighted
lwx228
Level VII

Re: Can matching data be obtained directly without auxiliary columns?

Thank Jim!

 

The code didn't run through.

 

2020-08-04_07-58.png

Highlighted
txnelson
Super User

Re: Can matching data be obtained directly without auxiliary columns?

Well, JMP can not find the small data table. From what I could see in your primary submission, the data table name for the small data table was called "Big Class by (age, sex)". Either that is not the correct name, or that data table no longer exists. Just change the names of the data table references to the table you want to run the script on and run it.
Jim
Highlighted
lwx228
Level VII

Re: Can matching data be obtained directly without auxiliary columns?

I have modified it several times, but still have no solution.
Go ahead and ask for your help.'Thank Jim!
Highlighted
txnelson
Super User

Re: Can matching data be obtained directly without auxiliary columns?

Try this

names default to here(1);

// Create the 2 Example data tables
dtBig = open("$SAMPLE_DATA/big class.jmp");
dtSmall = New Table( "Data agesex",
	Add Rows( 12 ),
	New Column( "agesex",
		Character,
		"Nominal",
		Set Values(
			{"12F", "12M", "13F", "13M", "14F", "14M", "15F", "15M", "16F", "16M",
			"17F", "17M"}
		)
	),
	New Column( "N Rows",
		Numeric,
		"Continuous",
		Format( "Fixed Dec", 12, 0 ),
		Set Values( [5, 3, 3, 4, 5, 7, 2, 5, 2, 1, 1, 2] )
	)
);

// Create the new column
dtBig << New Column( "N Rows" );

// Move the data as required
For( i = 1, i <= N Rows( dtSmall ), i++,
	foundRows = dtBig << get rows where(
		Num( Substr( dtSmall:agesex[i], 1, Length( dtSmall:agesex[i] ) - 1 ) ) == :age & 
		Substr( dtSmall:agesex[i], -1 ) == :sex
	);
	If( N Rows( foundRows ) > 0,
		dtBig:N Rows[foundRows] = dtSmall:N Rows[i]
	);
);
Jim

View solution in original post

Highlighted
lwx228
Level VII

Re: Can matching data be obtained directly without auxiliary columns?

Thank Jim!

  • There are no ready-made functions.

dtSmall

dtBig = Open( "$SAMPLE_DATA/big class.jmp" );
dtSmall = dtBig << Summary(
	Group( :age, :sex ),
	Freq( "None" ),
	Weight( "None" ),
	Link to original data table( 0 ),
	statistics column name format( "column" )
);
c1 = Column( 1 ) << Get Name;
c2 = Column( 2 ) << Get Name;
dtSmall << Add Multiple Columns( c1 || c2, 1, after( As Column( 2 ) ), Character, "Nominal" );
Wait( 0 );
Column( 3 ) << Formula( Char( :age ) || :sex );
dtSmall << run formulas;
Column( 3 ) << deleteFormula;
dtSmall << delete columns( 1 :: 2 );
Article Labels