Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- Copy column subset or cell range from table to table

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jul 11, 2018 12:26 AM
(4649 views)

I have two data tables. Some columns on table2 are identical to columns table1.

I wasnt to copy the data of the identical columns from table2 to table1.

That means, of course, that (1) table1 will be expanded with the number or rows in table2 and, (2) there will be missing data in table1. That's OK.

My issue is how to copy the data from table2 to table1.

E.g. - both tables have column "NAME". Table1 has 1000 rows, table2 has 2000 rows.

The results will be: table1 with 3000 rows, and "NAME" on table1 will be a "concatenation" of both NAME columns. Other columns in table1 will have 2000 missing/empty data.

BTW, in reality, tables are much larger and there are 10-20 identical columns (out of ~100). I will deal with it once I figure out the basic function. I mention it in case there's an issue with such size.

Many thanks

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

@RonB wrote:

If I understand you, I need to create table2.1 with columns e a, than reorder them to be a e, and concatenate table2.1 to table1?

You've got it, no need to reorder the columns though. Tables->Concatenate matches on column name, not position.

Subset relies on column selection, so you need to select the columns that are in common between the two tables. But that's easy because you can just get all the column names from table1 and select that whole list against table2. Any that don't exist will get ignored.

Here's an example using two of the sample data tables.

```
bc = Open( "$SAMPLE_DATA\Big Class.jmp" );
fit = Open( "$SAMPLE_DATA\Fitness.jmp" );
fit_cols = fit << get column names;
bc << select columns( fit_cols );
sub_bc = bc << Subset( All rows, Selected columns only( 1 ) );
fit << Concatenate( sub_bc );
//if you don't want a new table you can use "Append to first table"
fit << Concatenate( sub_bc, Append to first table );
```

-Jeff

15 REPLIES 15

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Copy column subset or cell range from table to table

JMP will concatenate data tables using

Tables==>Concatenate

It will line up the columns by column name when it concatenates. Missing values will automatically be added in the rows for columns that do not appear in one or the other data table.

The Concatenation Platform keeps the Name in tact for matching columns, and just adds new column names for columns that were not in the base data table.

For documentation, see:

Help==>Books==>Using JMP

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Copy column subset or cell range from table to table

Thanks. I did thought about it. However, I need to do it with a script because I have many such files and the order of the columns changes from one to one.

I'd like to figure out some code that'll do it.

Thanks

I'd like to figure out some code that'll do it.

Thanks

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Copy column subset or cell range from table to table

You can have JMP generate the code for you.

Run the

Tables==>Concatenate

interactively, and then in the new data table, go into the Tables Panel on the left side of the data table and right click on the "Source" entry, and select "Edit". There you will see the JSL required to concatenate the files together.

The JSL is basically

```
dt1=data table(<your first table>);
dt2=data table(<your second table>);
dt1 = dt1 << concatenate( dt2, Append to First Table(1));
```

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Copy column subset or cell range from table to table

Thanks.

Concatenate function won't do because I need to do it only for columns that are identical in both tables, skipping all others.

I tried storing the source column data in a matrix/list and than setting the valuse on the target column (the second table) using subscription [I,j] but I guess I'm missing something.

Concatenate function won't do because I need to do it only for columns that are identical in both tables, skipping all others.

I tried storing the source column data in a matrix/list and than setting the valuse on the target column (the second table) using subscription [I,j] but I guess I'm missing something.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Copy column subset or cell range from table to table

Concatenation does not egnore non matching columns. Here is a sample using the Big Class and Big Class Families, sample data tables. Please note, that the Big Class data table ends up will all of the columns that the Big Class Families has that are not in the original Big Class data table.

```
Names Default To Here( 1 );
dt1 = Open( "$SAMPLE_DATA/big class.jmp" );
dt2 = Open( "$SAMPLE_DATA/big class families.jmp" );
dt1 << concatenate( dt2, Append to First Table( 1 ) );
```

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Copy column subset or cell range from table to table

Thanks again. Unfortunately, concatenate won't do due to the bizzar nature of my data sets.

I need to concatenate only certain columns, ignoring the test.

I can always loop through all rows in the source table and insert rows in the target tables and than set column value one by one. But I'm afraid it'll hog CPU - my script will process over 100 files with dozens of columns and thousands of rows. Besides, it's not an elegant solution, albeit probably inevitable.

I'll simplify my question.

Can I do something like that?

Column(dt, i)[from,to] = list or matrix of values

Thanks for you patience,

I need to concatenate only certain columns, ignoring the test.

I can always loop through all rows in the source table and insert rows in the target tables and than set column value one by one. But I'm afraid it'll hog CPU - my script will process over 100 files with dozens of columns and thousands of rows. Besides, it's not an elegant solution, albeit probably inevitable.

I'll simplify my question.

Can I do something like that?

Column(dt, i)[from,to] = list or matrix of values

Thanks for you patience,

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Copy column subset or cell range from table to table

Don't give up on the builtin functions, like Tables->Concatenate so quickly.

If you only want the columns that have the same name between two tables, you can use Tables->Subset to create two tables with only those columns from each table and then concatenate those two tables.

-Jeff

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Copy column subset or cell range from table to table

OK. Good encouragement.

Table1 has columns a b c d e, table2 has columns x y e z a u v. I want to concatenate to table1 only columns e a from table2. How would you go about doing that?

If I understand you, I need to create table2.1 with columns e a, than reorder them to be a e, and concatenate table2.1 to table1?

Table1 has columns a b c d e, table2 has columns x y e z a u v. I want to concatenate to table1 only columns e a from table2. How would you go about doing that?

If I understand you, I need to create table2.1 with columns e a, than reorder them to be a e, and concatenate table2.1 to table1?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

@RonB wrote:

If I understand you, I need to create table2.1 with columns e a, than reorder them to be a e, and concatenate table2.1 to table1?

You've got it, no need to reorder the columns though. Tables->Concatenate matches on column name, not position.

Subset relies on column selection, so you need to select the columns that are in common between the two tables. But that's easy because you can just get all the column names from table1 and select that whole list against table2. Any that don't exist will get ignored.

Here's an example using two of the sample data tables.

```
bc = Open( "$SAMPLE_DATA\Big Class.jmp" );
fit = Open( "$SAMPLE_DATA\Fitness.jmp" );
fit_cols = fit << get column names;
bc << select columns( fit_cols );
sub_bc = bc << Subset( All rows, Selected columns only( 1 ) );
fit << Concatenate( sub_bc );
//if you don't want a new table you can use "Append to first table"
fit << Concatenate( sub_bc, Append to first table );
```

-Jeff

Article Labels

There are no labels assigned to this post.