BookmarkSubscribe
Choose Language Hide Translation Bar
Highlighted
nrodrig1
Occasional Contributor

Can't get column names in multivariate

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

 

0 Kudos
9 REPLIES 9
txnelson
Super User

Re: Can't get column names in multivariate

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 ) )
);
Jim
nrodrig1
Occasional Contributor

Re: Can't get column names in multivariate

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

Re: Can't get column names in multivariate

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.

Learn it once, use it forever!
0 Kudos

Re: Can't get column names in multivariate

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 )
	)
);
Learn it once, use it forever!
0 Kudos
nrodrig1
Occasional Contributor

Re: Can't get column names in multivariate

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?

0 Kudos
txnelson
Super User

Re: Can't get column names in multivariate

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 @markbailey 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.

Jim
0 Kudos
nrodrig1
Occasional Contributor

Re: Can't get column names in multivariate

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.

0 Kudos
txnelson
Super User

Re: Can't get column names in multivariate

Can you share a sample of your data table?  

Jim
0 Kudos
nrodrig1
Occasional Contributor

Re: Can't get column names in multivariate

Yes, what i've attached is the table where i get hung up in the code you've been helping me with. I want to go from this table (dtTab1) to a correlation coefficients table - like the one created from the Analysis>multivariate methods > multivariate output.

 

As i mentioned even when i take over the steps to do it manually its a little funky. After dtTab1 is created, the first time i go through the steps i describe above, the prompt for the columns to do the multivariate analysis are from the inintial table (all indices in one column with the name in another column. Let me kwno if you want thtat one as well)

 

 

0 Kudos