nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Can't get column names in multivariate

nrodrig1

Hi,

I’m trying to automate a process. Here’s an overview of the beginning part:

-Tabulate

-Make table

-Run multivariate on all continuous columns – so I can get a correlation matrix

I’m struggling on the run multivariate part. I’m not sure how to get it to put the columns that I want in there. This works if I know the column names and stick them in this part:

Multivariate(

Y(COLUMN NAMES IN HERE),

But I won’t know the column names so I’d like to have put a list variable or something to accommodate. Any help greatly appreciated!

Here’s the whole thing:

dt = data table("wpu10");
tab1= dt<<Tabulate(
	Add Table(
		Column Table(
			Grouping Columns( :series_id, :series_title ),
			Analysis Columns( :value )
		),
		Row Table( Grouping Columns( :year ) )
	)
);
dtTab1=tab1<<makeintodatatable;
dtTab1<< set name("dtTab1");
colnames = dtTab1<< get column names("Continuous");
colnameslist ={};
for(i=1,i<=n items(colnames), i++, insert into(colnameslist,colnames[i],1));

Multivariate(
	Y(colnameslist),
	Estimation Method( "Pairwise" ),
	Matrix Format( "Square" ),
	Scatterplot Matrix(
		Density Ellipses( 1 ),
		Shaded Ellipses( 0 ),
		Ellipse Color( 3 )
	)
)

Accepted answers

All comments

txnelson

You were pretty close....try this

Names Default To Here( 1 );
dt = Data Table( "wpu10" );
tab1 = dt << Tabulate(
	Add Table( Column Table( Grouping Columns( :series_id, :series_title ), Analysis Columns( :value ) ), Row Table( Grouping Columns( :year ) ) )
);
dtTab1 = tab1 << makeintodatatable;
dtTab1 << set name( "dtTab1" );

// Get Column names returns a list
colnameslist = dtTab1 << get column names( Continuous, string );


Multivariate(
	Y( Eval List( colnameslist ) ),
	Estimation Method( "Pairwise" ),
	Matrix Format( "Square" ),
	Scatterplot Matrix( Density Ellipses( 1 ), Shaded Ellipses( 0 ), Ellipse Color( 3 ) )
);

Mark_Bailey

The Y() argument expects one or column references, not names (character strings). Here is another way to make it work:

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA/Fitness.jmp" );

col name = dt << Get Column Names( "Continuous" );

col expr = Expr( Y() );

For( i = 1, i <= N Items( col name ), i++,
	Insert Into( col expr, Column( col name[i] ) );
);

Eval(
	Substitute(
		Expr(
			Multivariate(
				yyy,
				Estimation Method( "Pairwise" ),
				Matrix Format( "Square" ),
				Scatterplot Matrix( Density Ellipses( 1 ), Shaded Ellipses( 0 ), Ellipse Color( 3 ) )
			);
		),
		Expr( yyy ),
		Name Expr( col expr )
	)
);

nrodrig1

thanks for assistance but looks like i'm still having trouble. i took this out of my log in case it helps:
"Sum(value, WPU106, PPI Commodity data for Metals and metal products-Heating equipment, not seasonally adjusted)",
"Sum(value, WPU107, PPI Commodity data for Metals and metal products-Fabricated structural metal products, not seasonally adjusted)",
Role requires at least 1 columns.}
), Multivariate(/*###*/Y( Eval List( colnameslist ) ),
Estimation Method( "Pairwise" ),
Matrix Format( "Square" ),

nrodrig1

I tried adapting your solutino but couldnt' get it to give me the multivariate output.

I think the problem is i don't know the column names. i need a way to get the names after i do the "dtTab1 = tab1 << makeintodatatable;"

Maybe your code shows me but still struggling to see how. do you need the column names where you have yyy or is that goign to be a varaible that will have the names?

Mark_Bailey

I do not see how these errors are related. Are you trying to use the Tabulate labels as column names for Multivariate in the Y role?

Forget the script for now. Can you interactively achieve the result? That is, interactively obtain the summary with Tabulate, save the results in a new data table, and launch Multivariate?

Column names like "Sum( ... )" will be interpreted as a function call instead of a column reference in the Eval List() call without special handling.

txnelson

The issue you are running into, is the complex names that are being generated when you create your table from the Tabulate table. In the script below, I have swapped out the Tabulate for a Summary platform. That does not eliminate the complex name issue, but does create a data table in one quick action, which for the most part is identical to the Tabulate output. What the script does to handle the complex names, is the place around each element in the list of column names the :Name() function, to specify directly to JMP to treat the contents of the function as a column name. Finally, this list is passed into the Multivariate Platform to do the analysis.

Names Default To Here( 1 );
dt = Data Table( "wpu10" );

/*tab1 = dt << Tabulate(
	Add Table( Column Table( Grouping Columns( :series_id, :series_title ), Analysis Columns( :value) ), Row Table( Grouping Columns( :year) ) )
);
dtTab1 = tab1 << makeintodatatable;
dtTab1 << set name( "dtTab1" );*/

// Create the data table directly
dtTab1 = dt << Summary(
	Group( :year ),
	Sum( :value ),
	Subgroup( :series_id, :series_title ),
	Freq( "None" ),
	Weight( "None" )
);

// Get Column names returns a list
colnameslist = dtTab1 << get column names( Continuous,string);

// Get rid of the Row Number column from the list
colnameslist = remove( colnameslist, 2, 1 );

// Create a new list adding the :Name() function around each column name
// to handle the complex names the summary gave them
thelist={};

For( i = 1, i <= N Items( colnameslist ), i++,
	xx = colnameslist[i];
	Insert Into( thelist, Parse( Eval Insert( ":Name(\!"^xx^\!")" ) ) );
);

// Run the correlations
dtTab1 << Multivariate(
	Y( eval(thelist)  ),
	Estimation Method( "Pairwise" ),
	Matrix Format( "Square" ),
	Scatterplot Matrix( Density Ellipses( 1 ), Shaded Ellipses( 0 ), Ellipse Color( 3 ) )
);

In addition, I will support @Mark_Bailey in his comment about being able to do you analysis interactively. If this isn't an analysis you are going to do over and over, why bother creating a script.

nrodrig1

Thank you very much for your help! I ended up doing it all point and click. I tried following your last message this morning but still a little lost. The column names could definitly be a problem. Also, when I manually do it and get to the dtTab1 table then go to Analyze > Multivariate methods > multivariate JMP thinks i want to do it to the dt table. it's not until the second time i go through that process JMP realizes i want tot do it on the new table.

I'm using JMP 12 so i dont' know if that is causing me problems too.