Plot 3D Diagram

[#1]

Hello,

I am trying to plot a 3D diagram but unsuccessful. I have the data in Excel like this:

	Y	4,15815757	10,5410784
X		Z1	Z2
0,00038225		0,04740562	0,09242527
0,00032384		0,13448339	0,34250103

But I have a lot of rows and columns. This is just an example. I would appreciate any help.

Thanks.

[#2]

Generally, JMP Platforms expect the values for each variable (in this case 'x', 'y' and 'z') to be in their own column. So for your data I think this would be:

You could then try, for instance 'Graphs > Scatterplot 3D'.

You can also use JMP to rearrange the data into this shape, either by hand or using a script. But the details will depend on exactly how you read it in from Excel.

[#3]

More explanation of your data needs to be made.

Are you expecting all of the data to be displayed in one plot, or does each Z column, Z2,Z2, etc. denote a separate plot to be made?

Since each displayed point in a 3D plot is defined by the 3 dimensions, can you please show how your values in the displayed table would be displayed if the table was in the form of

X	Y	Z

[#4]

Thank you for replying. I will show you how my data looks like in Excel:

	Y	4,15815757	10,5410784	14,6453847	19,762652	29,5884633	40,1035851	52,7727379	64,5664014	71,6339866	80,1791963	92,5639742	108,325272	114,711013	121,815695	142,186092	160,139901	175,938007	189,208612	200,409857	209,675249	221,03791	241,182479	256,870356
X		Z1	Z2	Z3	Z4	Z5	Z6	Z7	Z8	Z9	Z10	Z11	Z12	Z13	Z14	Z15	Z16	Z17	Z18	Z19	Z20	Z21	Z22	Z23
0
-3,8225E-05		0,04740562	0,09242527	0,23259929	1,38211141	2,04180178	3,06816231	0,27673071	0,08808252	0,85756483	0,11511972	0,39746555	0,22290737	0,06869082	0,48405979	1,49275933	0,64964997	0,85255862	2,7009082	0,92420938	0,07121721	20,393539	12,7535737	2,59458619
0,00032384		0,13448339	0,34250103	0,37990033	0,38410363	1,70627509	0,89349347	0,42177943	0,20623338	1,51917679	0,26283727	0,68063752	0,75284843	0,10052593	0,3008461	0,98138204	1,02336837	0,31897393	1,47302568	0,78544606	0,1680408	0,3350895	1,47171341	7,46166212

Below you can see how my data looks like in form X,Y,Z but I have 23 points from Y and hundreds rows.

y	y1	y2	y3	y4
x1	z11	z21	z31	z41
x2	z12	z22	z32	z42

I would like to display the coordinates in one 3D plot.

That's the problem, because to show you in a displayed table X Y Z, I have to expand this matrix. For example:

x1	y1	z11
x1	y1	z12
x1	y1	z13
x1	y2	z21
x1	y2	z22
x1	y2	z23

And so on.. But I have no clue how to do this in JMP and to plot it. I know there is an automatic way to do this using Origin, but I need to do using JMP. Could you understand what I mean?

Thanks.

[#5 · Accepted solution]

I created a sample Excel file, based upon your description and have attached it to this entry.  I then developed the following JSL to read it in, and to created the 3D plot of the data.  I hope this is what you want

Names Default To Here( 1 );

// Read in Excel file
dt = Open(
	"<your path to the excel file>\example.xlsx",
	Worksheets( "example" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 0 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 1 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
);

// Get Y Matrix
Ymat = {};
For( i = 3, i <= N Col( dt ), i++,
	If( Column( i )[1] != "",
		Ymat = Ymat |/ Matrix( Num( Column( i )[1] ) )
	)
);

// Get rid of unrequired cells, and get the X and Z matrices
dt << select rows( [1, 2, 3] );
dt << delete rows;
dt << delete column( 2 );

// Convert all columns to numerics
For( i = 1, i <= N Col( dt ), i++,
	Column( dt, i ) << data type( numeric )
);

// Get X matrix
Xmat = Column( dt, 1 ) << get as matrix;
dt << delete column( 1 );

// Get Z matrix
Zmat = dt << get as matrix;

// Create new data table, and output the data

dtFinal = New Table( "Converted", New Column( "X" ), New Column( "Y" ), New Column( "Z" ) );

For( Xcnt = 1, Xcnt <= N Rows( Xmat ), Xcnt++,
	For( Ycnt = 1, Ycnt <= N Rows( Ymat ), Ycnt++,
		For( Zrowcnt = 1, Zrowcnt <= N Rows( Zmat ), Zrowcnt++,
			For( Zcolcnt = 1, Zcolcnt <= N Cols( Zmat ), Zcolcnt++,
				dtFinal << Add Rows( 1 );
				dtFinal:X[N Rows( dtFinal )] = Xmat[Xcnt];
				dtFinal:Y[N Rows( dtFinal )] = Ymat[Ycnt];
				dtFinal:Z[N Rows( dtFinal )] = Zmat[Zrowcnt, Zcolcnt];
			)
		)
	)
);
dtFinal << Scatterplot 3D(
	Y( :X, :Y, :Z ),
	Frame3D(
		Set Grab Handles( 0 ),
		Set Walls( 1 ),
		Set Rotation( -54, 0, 38 ),
		Set Text Scale( 2 ),
		Set Line Scale( 1 ),
		Set Marker Transparency( 1 ),
		Set Marker Quality( 0.25 ),
		Set Marker Scale( 1 ),
		Set View Perspective( 0.1 ),
		Set View Zoom( 1 )
	)
);

 Be sure to change the path in the JSL to point to where ever you save the attached Excel file

[#6]

Thank you very much. That is exactly what I wanted to do and it works perfectly.

[#7]

I have just one more question. Is there a way to make this code run faster, because I'm dealing with a big data and it takes hours to run and sometimes it doens't. 

My notebook is a Asus, Windowns 7, 64 bit, 4GB (RAM) and 197GB memory. 

So I don't think that the problem is with my computer, right?

Thanks.

[#8]

My first suggestion is to move the Add Rows() up higher into the looping.  Add Rows() is a pretty slow function, so if you could add all of the rows you need for the data table, to right after the initial New Table(), that would make the program much faster.  That would also mean that you would have to add in an overall row counter, to keep track of what row you are working on, instead of using N Rows().

Names Default To Here( 1 );

// Read in Excel file
dt = Open(
	"c:\users\txjim\documents\read in issue\example.xlsx"
	/*"<your path to the excel file>\example.xlsx"*/,
	Worksheets( "example" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 0 ),
		,
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 1 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
);

// Get Y Matrix
Ymat = {};
For( i = 3, i <= N Col( dt ), i++,
	If( Column( i )[1] != "",
		Ymat = Ymat |/ Matrix( Num( Column( i )[1] ) )
	)
);

// Get rid of unrequired cells, and get the X and Z matrices
dt << select rows( [1, 2, 3] );
dt << delete rows;
dt << delete column( 2 );

// Convert all columns to numerics
For( i = 1, i <= N Col( dt ), i++,
	Column( dt, i ) << data type( numeric )
);

// Get X matrix
Xmat = Column( dt, 1 ) << get as matrix;
dt << delete column( 1 );

// Get Z matrix
Zmat = dt << get as matrix;

// Create new data table, and output the data

dtFinal = New Table( "Converted", New Column( "X" ), New Column( "Y" ), New Column( "Z" ) );
dtFinal << add rows( N Rows( Xmat ) * N Rows( Ymat ) * N Rows( Zmat ) * N Cols( Zmat ) );

rowcnt = 0;

For( Xcnt = 1, Xcnt <= N Rows( Xmat ), Xcnt++,
	For( Ycnt = 1, Ycnt <= N Rows( Ymat ), Ycnt++,
		For( Zrowcnt = 1, Zrowcnt <= N Rows( Zmat ), Zrowcnt++,
			For( Zcolcnt = 1, Zcolcnt <= N Cols( Zmat ), Zcolcnt++,
				rowcnt++;
				dtFinal:X[rowcnt] = Xmat[Xcnt];
				dtFinal:Y[rowcnt] = Ymat[Ycnt];
				dtFinal:Z[rowcnt] = Zmat[Zrowcnt, Zcolcnt];
			)
		)
	)
);
dtFinal << Scatterplot 3D(
	Y( :X, :Y, :Z ),
	Frame3D(
		Set Grab Handles( 0 ),
		Set Walls( 1 ),
		Set Rotation( -54, 0, 38 ),
		Set Text Scale( 2 ),
		Set Line Scale( 1 ),
		Set Marker Transparency( 1 ),
		Set Marker Quality( 0.25 ),
		Set Marker Scale( 1 ),
		Set View Perspective( 0.1 ),
		Set View Zoom( 1 )
	)
);

[#9]

Thank you very much for your help, I really appreciated it.

However when I run the script, it shows an error in the line 70 saying that the value in the columm X can not be determined, because the number of rows (0) is invalid.

I attached the Excel data that I am working on. Would you mind to take a look? I am a new user in JSL, that's why I don't have much experience.

Thank you.

[#10]

I downloaded your excel file and saved it.  I then pointed the JSL script to the new file.  I also had to change the line in the code

Worksheets( "example" ),

to

Worksheets( "JMP" ),

because you had changed the name of the spreadsheet within the workbook.

I did not run the program to the full 104,284,944 rows that the JSL will produce if run to completion, but I did run it to a lesser number to prove to myself that the code would work.

If the above change is not the issue, the next question I would have, is what version of JMP are you using?