topic How can I paste a data table with text mixed in the clipboard into the specified table? in Discussions

How can I paste a data table with text mixed in the clipboard into the specified table?

lwx228 — Thu, 13 Sep 2018 08:54:44 GMT

The clipboard has copied the R square table in the decision tree report, 6 rows and 6 columns, containing text. How can I paste this table into the new table?
thank you!

RSquare RMSE N Number of Splits AICc
Training 0.502 2.5656293 3625 20 17162.6
Validation -0.77 4.6256646 3625
Test -0.70 4.3112172 3635

Re: How can I paste a data table with text mixed in the clipboard into the specified table?

lwx228 — Thu, 13 Sep 2018 08:56:02 GMT

Re: How can I paste a data table with text mixed in the clipboard into the specified table?

lwx228 — Thu, 13 Sep 2018 09:25:14 GMT

It is required to be done with JSL.Thank you!

Re: How can I paste a data table with text mixed in the clipboard into the specified table?

gzmorgan0 — Thu, 13 Sep 2018 09:37:33 GMT

The first column of the table you copied contains the group names "Training", "Validation", "Test", text. You are trying to paste it into a column that is numeric, not a string. Note the dot a symbol represents an empty numeric column.

JMP maintains data types for the entire column. JMP tables are like database tables, you do not find a person's name isn some row of a column that is expected to be their phone number. Excel, Statistica, old RS/1 are display tables. They allow the preamble of the US constitution in cell A1 and today's temperature in cell A2.

Note, if you want to make a table of many "like" tables, for example many partitions, select Make Combined Data Table, it will create one table of all partition Summary of Fit tables.

Re: How can I paste a data table with text mixed in the clipboard into the specified table?

lwx228 — Thu, 13 Sep 2018 09:49:45 GMT

Complete with this description:
I have copied the R side report of the decision tree to the clipboard with JSL provided by Jim.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA\Boston Housing.jmp" );
pp = dt << Partition(
Y( :mvalue ),
X(
:crim,
:zn,
:indus,
:chas,
:nox,
:rooms,
:age,
:distance,
:radial,
:tax,
:pt,
:b,
:lstat
),
Split Best( 3 )
);

Report( pp )[Table Box( 1 )] << copy data;

I tried many ways to paste the contents of the clipboard into the specified table without success.

text = Get Clipboard();
dt[7496,11]= Get Clipboard();

Please kindly advise.thank you!

Re: How can I paste a data table with text mixed in the clipboard into the specified table?

gzmorgan0 — Thu, 13 Sep 2018 12:02:47 GMT

You need to start reading some of the basics. Every computer language has its own syntax.

Names Default to Here(1);

dt = Open("$sample_data/Boston Housing.JMP");

dt << Make Validation Column(
	Training Set( 0.50 ),
	Validation Set( 0.25 ),
	Test Set( 0.25 ),
	Formula Random
);

cart = dt<< Partition(
	Y( :mvalue ),
	X(
		:crim,
		:zn,
		:indus,
		:chas,
		:nox,
		:rooms,
		:age,
		:distance,
		:radial,
		:tax,
		:pt,
		:b,
		:lstat
	),
	Validation( :Validation ),
	Show Tree( 0 ),
	Split History( 1 ),
	Informative Missing( 1 ),
	Initial Splits(
		:lstat >= 7.79,
		{:distance < 2.0788, {:nox >= 0.609, {:lstat >= 17.21, {:crim >= 10.8342, {},
		{:nox < 0.74}}}}, {:lstat >= 14.8, {:crim >= 0.75026, {:b < 375.87}, {:tax
		 >= 304}}, {:rooms < 6.726, {:rooms < 6.101, {}, {:tax >= 422, {}, {:lstat
		 >= 11.98, {}, {:crim < 0.11425, {:rooms < 6.405}}}}}}}},
		{:lstat >= 4.32, {:rooms < 6.8, {:rooms < 6.546, {:distance >= 4.4986,
		{:indus < 5.64, {}, {:lstat >= 6.15}}}}, {:rooms < 7.52}}, {:b >= 392.83,
		{:rooms < 7.61}, {:indus < 18.1}}}
	),
	SendToReport(
		Dispatch( {}, "Partition Report", FrameBox, {Frame Size( 480, 218 )} )
	)
);

rsq_dt = report(Cart)[TableBox(1)] << Make into Data Table;

//create a table with extra rows and extra columns
_xx=New Table("result", New Column("d1"), New Column("d2"), New Column("d3"), New Column("d4"),
      New Column("Type", Character), New Column("RSQ"),
	  new Column("RMSE"), New Column("N"), New Column ("nsplits"),
	  new column("AICc"),
	  add rows(100)
);

nr = nrow(rsq_dt);
nc = ncol(rsq_dt);
cb=5;  ce=cb + nc -1;  //Column 5 is wher I want to paste it
rb=50; re=rb + nr -1;
//if rb> nrow(_xx) or ce>ncol(_xx) or the column data types do not match the next line will throw an error.
//it is up to the scripter to do error checking 

_xx[rb::re, cb::ce] = rsq_dt[1::nr, 1::nc];

close(rsq_dt, NoSave);

current data table(_xx);
_xx << bring window to front;
_xx << go to row(rb);
_xx << next selected;

Re: How can I paste a data table with text mixed in the clipboard into the specified table?

txnelson — Thu, 13 Sep 2018 12:08:37 GMT

Within the JMP application, copying and pasting a data table typically would not be the method that would be used to move the data table contents.

Would you please explain what application etc. you are attempting to paste the clipboard into.

Re: How can I paste a data table with text mixed in the clipboard into the specified table?

lwx228 — Thu, 13 Sep 2018 13:08:00 GMT

Thank you for every expert help!
My JSL foundation is really poor. As I just got a one-month trial of the professional JMP version recently, I am in a hurry to use JSL to make multiple file decision tree calculations, and the process is automatic.Therefore, I could not learn slowly by myself and could only consult experts.

Re: How can I paste a data table with text mixed in the clipboard into the specified table?

lwx228 — Thu, 13 Sep 2018 13:19:33 GMT

I loop each file according to different segmentation times, the minimum number of segmentation, different validation columns and different Y factors, and each file produces 200 results.

These results are saved as excel files and further calculated with VBA to obtain the results that meet the requirements.

Using VBA to process the results, I am skilled and more flexible, but the decision tree operation can only use JMP: JMP is fast.

Since there is no r-square control in my decision tree JSL code, I also want to get the relevant r-squared data in the decision tree report.
I was thinking that if I can't paste the r-square table, I just need 3 of them.

thank you!

Re: How can I paste a data table with text mixed in the clipboard into the specified table?

txnelson — Thu, 13 Sep 2018 13:24:23 GMT

You can always create a JMP data table from the R Square table you are referencing, and then save that table as a csv or txt or slsx file. to then be read into your VBA script.

Re: How can I paste a data table with text mixed in the clipboard into the specified table?

lwx228 — Thu, 13 Sep 2018 13:32:13 GMT

I have a table merge in my code, and I want to write R squared at this point.But that's not the main thing.

d3 = d1 << Join( With( d2 ), By Row Number, Output Table( "Joined Data" ) );

I'm used to VBA, so I haven't adjusted my thinking.thank Jim!

Re: How can I paste a data table with text mixed in the clipboard into the specified table?

lwx228 — Thu, 13 Sep 2018 23:41:11 GMT

Thank gzmorgan0! I learned a lot from this example.

In the syntax, the row and column requirements of JSL are consistent with those of VBA.

But VBA's …… Cells(2, "").Resize(UBound(br), 1) = br …… are more flexible.

Re: How can I paste a data table with text mixed in the clipboard into the specified table?

lwx228 — Thu, 13 Sep 2018 23:42:21 GMT

Re: How can I paste a data table with text mixed in the clipboard into the specified table?

txnelson — Fri, 14 Sep 2018 02:13:12 GMT

I am not quite sure what you are attempting to ask. My thinking is that you have 2 lines that are attempting to serve the same purpose. The first line uses a JMP Join Platform to add a data table to an already existing data table. Is the second line attempting to replace values in the dt3 data table?

Re: How can I paste a data table with text mixed in the clipboard into the specified table?

lwx228 — Fri, 14 Sep 2018 09:39:07 GMT

Thank you, Jim, for your continued attention. Sorry, I haven't done this problem and it has been solved. This figure is the actual effect I got with the method provided by the gzmorgan0.

In the process, I combined multiple data tables, and then wrote the data of R square into a fixed empty cell, which can be accurately found in the VBA processing in the later stage.
Thanks again to the two experts!