Hello, I am trying to plot a 3D diagram but unsuccessful. I have the data in Excel like this: Y4,1581575710,5410784X Z1Z20,00038225 0,047405620,092425270,00032384 0,134483390,34250103 But I have a lot of rows and columns. This is just an example. I would appreciate any help. Thanks.

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

Plot 3D Diagram

joaquimgui

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.

Accepted answers

txnelson

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

example.xlsx

All comments

ian_jmp

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:

Screen Shot 2017-11-23 at 15.27.55.png

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.

Three Variables.jmp

txnelson

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

joaquimgui

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.

txnelson

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

example.xlsx

joaquimgui

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

joaquimgui

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.